Importing OKATO Classifier Data to MySql Database

I had to add reference books with regions of Russia and cities to my application. The first idea that I visited was to search for XML files with regions and cities on the Internet, and, if successful, import the data into my tables. Optimism diminished when I started looking. Of course, no one specifically prepared this data for me, but this is not surprising. Having once had the experience of importing data from KLADR into SqlServer, into a structure different from KLADR, I decided that I would have to repeat the feat again and organize the import from KLADR into my structure. I remembered well the fact that data are stored in KLADR in DBF tables, and import data I need the MySql database tables, and the structure was several times simpler than the KLADR structure, then the following diagram was drawn to me:

• Organize the upload of the data I need from DBF to XML
• To write a parser that would make requests for inserting data into tables from XML (that is, I wanted to have a combat set of requests for insertion of regions and cities of Russia into my tables)

But I knew that besides KLADR, there are also classifiers of addresses of Russia, for example OKATO. I decided, before starting work, to familiarize myself with all the classifiers that google will give me. When I familiarized myself with OKATO, I discovered that this classifier is distributed as a set of insert requests (download OKATO here ):

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, ' '); … 

')
It was in the form in which I wanted to get the regions and cities of Russia, only with a more complex structure and with data that I did not need at all (districts for example). Now the strategy of getting the final version is not drastically mys, but it has changed. I don't need to bother with dbf files, all I need is the following:

• Create a new database
• Create a table with a specific name and structure
• Write a sql request to select the data that I needed
• Query result to save to xml
• Write a parser that received xml as input and returned a ready-made set of queries for inserting data

After inserting data into the table of a new database and experimenting a little with the sql query, I received a query that selected the exact data set I needed. And it was necessary for me to select all regions, territories, and republics, as well as cities and towns located in these regions. Even the villages I did not need, I was more interested in large cities, but I did not lose anything from their presence. I received the following request:

 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 

Many visual studios allow you to connect and execute queries to MySql or MsSql, and you can also save the result as XML. After saving the result in XML, I received a file with the following structure and content:

 <?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> … 

The next step is to develop a parser that turns my XML into insert requests. Having opened VisualStudio I made two fields, in one I supposed to enter xml, in the other I expected to receive requests. I added a button by pressing which all the magic was supposed to happen and wrote code converting xml into requests:

  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)+";\r\n"); storeRegion = valRegion; } } if(node.Name == elementCity) { idCity++; resultCities.Append(string.Format(sqlCities, idCity, node.InnerText, idRegion)+";\r\n"); } } } sqlTextBox.Text = resultRegions.ToString()+ resultCities.ToString(); } 

I got exactly the result I expected, in one text field I just need to copy the received XML, in another text field I get insertion requests.