Возникла у меня необходимость добавить к моему приложению справочники с регионами России и городами. Первая идея которая меня посетила это поискать готовые файлы XML с регионами и городами в интернете, и в случае успеха импортировать данные в мои таблицы. Оптимизма поубавилось когда я начал искать. Конечно никто для меня специально не готовил эти данные, но это и не удивительно. Имея уже однажды опыт импортирования данных из КЛАДР в SqlServer, в структуру отличную от КЛАДР, я решил что придется снова повторить подвиг и организовывать импорт из КЛАДР в мою структуру. Я прекрасно помнил и тот факт что в КЛАДР данные хранятся в таблицах DBF, а импортировать данные нужно в таблицы базы данных MySql, и структура была в разы проще чем структура КЛАДР, то мне рисовалась следующая схема:
- Организовать выгрузку нужных мне данных из DBF в XML
- Написать парсер который бы из XML сделал запросы на вставку данных в таблицы(т.е. мне на выходе хотелось иметь боевой набор запросов на вставку регионов и городов России в мои таблицы)
Но мне было известно что помимо КЛАДР существуют ещё классификаторы адресов России, например ОКАТО. Я решил прежде чем приступать к работе, ознакомиться со всеми классификаторами которые выдаст мне google. При ознакомлении с ОКАТО я обнаружил что этот классификатор распространяется как набор запросов на вставку(скачать ОКАТО тут):
INSERT INTO `class_okato` (`id`, `name`, `code`, `control_number`, `parent_id`, `parent_code`, `node_count`, `additional_info`) VALUES(1, 'Алтайский край', '01', 2, NULL, NULL, 3, 'г Барнаул');
INSERT INTO `class_okato` (`id`, `name`, `code`, `control_number`, `parent_id`, `parent_code`, `node_count`, `additional_info`) VALUES(2, 'Районы Алтайского края /', '01200', 8, 1, '01', 60, NULL);
INSERT INTO `class_okato` (`id`, `name`, `code`, `control_number`, `parent_id`, `parent_code`, `node_count`, `additional_info`) VALUES(3, 'Алейский', '01201', 2, 2, '01200', 1, 'г Алейск');
…
Именно в том виде в котором я хотел получить регионы и города России, только с более сложной структурой и с данными которые мне были совершенно не нужны(районы например). Теперь стратегия получения конечного варианта у меня не кардинально, но всё же изменилась. Мне не нужно заморачиваться с dbf файлами, всё что мне нужно это следующее:
- Создать новую базу данных
- Создать таблицу с определенным именем и структурой
- Написать sql-запрос на выбор данных именно тех которые были необходимы мне
- Результат запроса сохранить в xml
- Написать парсер который получал на вход xml и возвращал готовый набор запросов для вставки данных
Вставив данные в таблицу новой базы данных и немного поэкспериментировав с sql-запросом я получил запрос, который выбирал именно тот набор данных который был мне необходим. А мне было необходимо выбрать все области, края, и республики а так же города и поселки расположенные в этих областях. Даже поселки мне были не нужны меня больше интересовали крупные города, но и от их присутствия я ничего не терял. Запрос я получил следующий:
select ok1.name as name_region, ok2.name as name_city
from class_okato ok1
left join class_okato ok2 on ok1.code like (substring(ok2.code,1,2))
where ok1.parent_id is null
and ok2.parent_id is not null
and ((ok2.code like ('%4__') and ok2.code not like ('%400'))
or (ok2.code like ('%5__') and ok2.code not like ('%500')
and ok2.code not like ('%550')))
order by ok1.name,ok2.name
Многие визуальные студии позволяющие подключаться и выполнять запросы к MySql или MsSql, позволяют так же и сохранять результат в виде XML. Сохранив результат в XML я получил файл со следующей структурой и наполнением:
<?xml version="1.0"?>
<SQL-query>
<row>
<name_region>Алтайский край</name_region>
<name_city>Змеиногорск</name_city>
</row>
<row>
<name_region>Алтайский край</name_region>
<name_city>Благовещенка</name_city>
</row>
<row>
<name_region>Алтайский край</name_region>
<name_city>Новоалтайск</name_city>
</row>
…
Следующим шагом разработка парсера, который бы превратив мой XML в запросы на вставку. Открыв VisualStudio я сделал два поля, в одно я предполагал вводить xml, в другом я ожидал получить запросы. Добавил кнопку по нажатию которой вся магия должна была свершиться и написал код преобразующий xml в запросы:
private void btnXmlToSql_Click(object sender, EventArgs e)
{
const string elementRegion = "name_region";
const string elementCity = "name_city";
string sqlRegions = "INSERT INTO regions (id, name) VALUES ({0}, '{1}')";
string sqlCities = "INSERT INTO cities (id, name, region_id) VALUES ({0}, '{1}', {2})";
StringBuilder resultRegions = new StringBuilder();
StringBuilder resultCities = new StringBuilder();
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlTextBox.Text);
var rowCollection = doc.GetElementsByTagName("row");
string storeRegion = null;
string valRegion = "";
int idRegion = 0;
int idCity = 0;
for (int i = 0; i < rowCollection.Count; i++)
{
foreach (XmlNode node in rowCollection[i].ChildNodes)
{
if (node.Name == elementRegion)
{
valRegion = node.InnerText;
if (storeRegion != valRegion)
{
idRegion++;
resultRegions.Append(string.Format(sqlRegions, idRegion, valRegion)+";rn");
storeRegion = valRegion;
}
}
if(node.Name == elementCity)
{
idCity++;
resultCities.Append(string.Format(sqlCities, idCity, node.InnerText, idRegion)+";rn");
}
}
}
sqlTextBox.Text =
resultRegions.ToString()+
resultCities.ToString();
}
Я получил именно тот результат который ожидал, в одно текстовое поле мне достаточно скопировать полученный XML, в другом текстовом поле получаю запросы на вставку.
Автор: Knostan
