Обработка и геокодирование данных Росстата

[ilya.kuzminov]

Уважаемые Коллеги,

Хочу поделиться своей идеей и некоторыми скромными результатами, достигнутыми на пути ее реализации, в части геокодирования данных Росстата.

Как экономико-географ, я при работе над НИРами вынужден часто и много строить тематические карты, в основном, картограммы и картодиаграммы по субъектам РФ, муниципальным районам, городам и ПГТ. К сожалению, я не нашел способа подключиться вживую к реляционным базам данных Росстата и Спарка. Приходится качать сборники Росстата, в первую очередь, "Регионы России", брать таблицы из Word, переводить их в базу данных, и уже оттуда соединять таблицу статистики с таблицей геоданных по имени геосущности, ибо Росстат в своих сборниках не дает колонки с универсальными идентификаторами ОКАТО и ОКТМО.

При геокодировании проблемы возинкали на каждом шагу:
1) Имена отдельных регионов в разных сборниках Росстата разные
2) Архангельская область и Тюменская область имеют в своем составе "в том числе" автономные округа. Отсюда - высчитать по ним отдельно относительные показатели нельзя.
3) Наконец, полная фантасмагория: в отдельных именах в сборниках Росстата вместо русских букв идут латинские, которые точно так же выглядят. Что это - диверсия против западных шпионов или внимательность набивщиков данных?

Ответом стало решение написать программу и таблицы замен, которая позволит быстро парсить и вгружать в SQL сборники Росстата, а также обрабатывать исключения и уведомлять пользователя о новых артефактах, появившихся в очередном году.

Пока же я написал отдельные макросы VBA и процедуры в SQL, далекие от абстракции и не имеющие пока встроенной обработки ошибок, просто чтобы пристреляться к проблеме. Выкладываю полученные результаты на суд публики и предлагаю обсудить нужно ли полноценное приложение для парсинга таблиц Росстата, какова может быть его архитектура, а также обсудить качество работы Росстата.


Этюд №1.

Документ Росстата с данными о населении всех муниципальных образований первого уровня. Архив скачивается отсюда. Из архива берется файл Tabl-23-10.xls. К нему применяется макрос:

Municipalities_and_Cities_Population_gis_lab.rar

Макрос для обработки таблицы Росстата с данными о населении всех муниципальных образований первого уровня.

(359.22 КБ) 407 скачиваний

Макрос призван вытащить в отдельные колонки данные, которые в файле присутствуют имплицитно (через отступы, через взаимное расположение строк, через арифметику численности населения). Получается таблица вполне годная для работы с ней в SQL. Хотя бесспорно таблицу можно и дальше менять уже в SQL в рамках нормализации БД.

Этюд №2.

Полученная в результате применения макроса таблица импортируется в MS SQL. Туда же импортируется таблица, взятая DBF-редактором из слоев OSM, содержащих данные о муниципальных образованиях и взятых здесь.

Пример такой таблицы (2313 муниципальных образований):

Municipalities_OSM_2313.xls

Таблица, взятая из слоев OSM с данными о муниципальных образованиях первого уровня

(858 КБ) 455 скачиваний

Выполняется соединение двух таблиц по условию Регион = Регион, Муниципалитет = Муниципалитет. Обнаруживается некоторое количество несовпадений, которые устраняются рядом апдейтов одной из таблиц. Пример набора таких апдейтов - в приложенном sql-запросе:

Rendering_OSM_2313_Municipalities_2.rar

Обработка одной из таблиц с тем, чтобы данные о численности населения привязались ко всем 2313 муниципальным образованиям

(1.56 КБ) 366 скачиваний

Результат - численность населения, привязанная к кодам OKATO и OKTMO. Теперь можно наконец уйти от имен и пользоваться идентификаторами. Чтобы составить полную привязку населения ко всем кодам, нужно будет, видимо, использовать полдюжины выгрузок из OSM, поскольку, как я уже писал ранее, выгрзука от выгрзуки немного отличается набором муниципалитетов. В каждую из них какие-то да не попадают. С выходом новых сборников Росстата можно будет использовать идентификаторы + регулярные выражения для имен + некую fuzzy logic, подвязанную к тому факту, что численность населения меняется постепенно (такая конструкция нужна будет потому что Росстат не публикует идентификаторы).

Получившаяся пока привязка численности населения к кодам ОКАТО и ОКТМО опубликована следующим постом, т.к. лимит файлов в посте - 3.

[ilya.kuzminov]

Обещанный файл привязки кодов к населению:

Численность населения, привязанная к ОКАТО и ОКТМО.xls

Привязка численности населения к кодам ОКАТО и ОКТМО

(171 КБ) 479 скачиваний

Этюд №3

Безобразное качество таблиц в сборнике "Регионы России. Социально-экономические показатели" потребовало написания двух макросов: 1) первый удаляет лишние пробелы, заменят обманчивые латинские буквы, выглядящие как русские, на русские, поправляет названия регионов, удаляет сноски, 2) второй следует применять только к таблицам с абсолютными показателями; он создает отдельные строки для Архангельской области и Тюменской области без учета автономных округов:

Rosstat_Script_1.rar

Макрос для обработки таблиц сборника "Регионы России. Социально-экономические показатели"

(26.68 КБ) 379 скачиваний

Полученные данные уже легко геокодируются с использованием слоев ADC WorldMap, что позволяет быстро строить тематические карты по официальной статистике.