Импортирование данных класификатора ОКАТО в базу MySql

в 8:44, , рубрики: mysql, XML, импорт данных, Программирование, метки: , ,

Возникла у меня необходимость добавить к моему приложению справочники с регионами России и городами. Первая идея которая меня посетила это поискать готовые файлы 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, в другом текстовом поле получаю запросы на вставку.

image

Автор: Knostan

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js