Что в этой части?
Исследование выгрузки базы ГАР с целью автоматизации загрузки данных в свою базу.
Готовое приложение описано во второй части.
Решаемая задача
Зная UUID дома (ранее идентификатор ФИАС) требуется найти почтовый индекс, город, улицу и номер дома для отдельно взятого региона, и на этих данных создать собственные справочники. Используемая РСУБД — PostgeSQL, используемый фреймворк — Laravel 10.
Кому ещё может оказаться полезен данный материал
Всем, кто решает схожую задачу. Тем, кто решает задачу от противного, т.е. приводит в порядок свои базы, находя «Идентификатор ФИАС» по наименованиям городов, улиц и т.п. А так же всем, кто хочет разобраться в структуре базы ГАР, в рамках решаемой задачи.
Что в статье
Подробный анализ выгрузки базы ГАР, описание полей, нахождение реляций, создание миграций и приложения для парсинга выгрузки базы ГАР в рамках решаемой задачи. Готовое приложение будет описано во второй части статьи и выложено на github.com.
Пролог
В работе приходится использовать сторонние справочники потребителей услуг, формируемых нашими контрагентами.
В таких справочниках часто присутствует пересортица. Например «пр-т Текстильщиков» может храниться как «пр. Текстильщиков», «Текстильщиков пр.» «проспект Текстильщиков». Возможны и более тяжёлые случаи. Например, в одих строках населённый пункт именуется как «село Сергиевское», в других как «деревня Сергеевка». Не говоря уже об опечатках. И весь этот винегрет может находиться в одном справочнике.
Если справочник нужен лишь для отображения адресной части, то проблем не возникает. Что контрагент в справочник записал, то и получил. Проблемы возникают в случае, если эти данные нужно агрегировать. Например, требуется разослать письма должникам, проживающем в определённом доме.
Если ничего не делать с адресными данными, то во-первых, для построения фильтра придётся использовать медленный select distinct по нескольким полям, во-вторых придётся использовать текстовый поиск. И то и другое не добавляют производительности.
Можно создать свой собственный адресный справочник. Это решило бы две предыдущих проблемы. Но в этом справочнике всё равно будет разносортица. И если пользователь укажет: «пр-т Текстильщиков», то из результирующего набора данных выпадут «пр. Текстильщиков», «Текстильщиков пр.» и т.п.
К нашему счастью, многие позиции в справочниках контрагентов обладали UUID, известным как «идентификатор ФИАС». Этот идентификатор благополучно перекочевал в базу ГАР.
Загружаем выгрузку базы ГАР
Свежая версия справочника доступна по адресу: https://fias.nalog.ru/Frontend. На странице также выложен «архив XSD схем выгрузки БД в формате ГАР» и архив «сведений о составе информации Государственного адресного реестра», содержимое которого составляют файлы в формате .docx. К сожалению, документацию не назовёшь полной, сведения из файлов .docx показались мне не очень полезными. Быстрее и проще заглянуть в .xml файл. А вот XSD схемы оказались намного полезнее. Тем не менее, полное понимание взаимосвязей в выгруженных таблицах пришло только после того, как я «потыкал в выгрузку палкой».
Содержимое архива gar_xml.zip
В архиве содержится 99 папок именованных с 01 до 99, в которых содержатся сведения относящиеся к одноимённому региону. В корне архива лежит 10 общих справочников и файл version.txt, содержащий дату, на которую осуществлялась выгрузка. Содержимое .xml файлов представляет собою дерево XML, из которого убрали все переводы строк и пробелы. Для чего так сделано — непонятно.
Пробелы и переводы строк прекрасно ужались бы архиватором. А вот читабельность документа снизилась до нуля. К счастью формат дерева оказался очень простым. Один корневой элемент в который вложены строки выгрузки. В качестве примера диаграмма из .docx файла, описывающего таблицу AS_HOUSE_TYPES.
Отношения между таблицами
Для поиска отношений я воспользовался обычной электронной таблицей. «Плясать» начал от дома. Поля, содержащие ключи, выделил цветом, свой цвет для каждой связи. В заголовках после названия таблицы в системе ГАР привожу «нормальное» название, соответствующее соглашениям Laravel и далее обращаюсь к этим именам таблиц.
Таблица AS_HOUSE далее houses
На рисунке отсутствует ряд полей. Их пришлось убрать, чтобы картинка осталось читаемой, в том числе и важные — start_date и end_date. Красной заливкой выделено поле object_id. Это идентификатор ГАР. Поле object_guid содержит идентификатор ФИАС. В самой таблице содержатся искомые нами значения: house_num — номер дома, поля add_num_1 и add_num_2 могут содержать номера корпуса и строений. Например, для дома с UUID 3bf1c329-e556-4d56-82d0-3b1b42b298da мы получим дом 1, корпус 1, строение 102.
Поле house_type — является внешним ключом к справочной таблице AS_HOUSE_TYPES (рис. 5).
Поля add_type_1, add_type_2 — являются внешними ключами к справочной таблице AS_ADDHOUSE_TYPES.
Обратите внимание на поля is_actual и is_active. В базе ГАР хранится вся история изменений, проводившихся над адресным объектом. И выбирать, в общем случае, надо именно ту строку, которая актуальна и активна. Поля start_date и end_date задают временной отрезок, в течение которого информация из справочника была актуальной. Но мною были замечены и исключения из этого правила. Например, были обнаружены следующие строки:
Получается, информация ещё не «протухла», считается актуальной, но почему-то не активной. В работе мне попадались дома, входящие в эту выборку. Кроме того, что эти дома были «деактивированы» в таблице с домами, также были деактивированы данные в некоторых справочных таблицах (не во всех). Позже я вернусь к этому вопросу.
Таблица AS_HOUSE_TYPES далее house_types
Это маленькая справочная таблица, поле таблицы houses.house_type ссылается на houses_types.id.
Обратите внимание, что данная таблица не содержит поля is_actual, только is_active.
В базе ГАР некоторые таблицы не имеют поля is_actual, а некоторые даже не имеют поля is_active. В таких таблицах приходится ориентироваться на поле end_date.
Таблица AS_ADDHOUSE_TYPES далее house_add_type
Структура таблицы полностью повторяет структуру предыдущей таблицы (рис. 5), с той разницей, что на поле house_add_type.id ссылаются поля houses.add_type_1, houses.add_type_2
Таблица AS_HOUSES_PARAMS далее house_params
Самая пухлая таблица в папке моего региона. В ней содержится всякая всячина, показанная на рис. 6:
Структура таблицы house_params:
Красной заливкой выделен house_params.object_id, он равен houses.object_id, описанной в самом начале (рис. 3). Меня интересует значение в поле value. Для типа 5 это поле будет содержать искомый мною почтовый индекс.
Расшифровка типов полей содержится в таблице AS_PARAM_TYPES (рис. 6), далее param_types. Для моих целей таблица не нужна, я её загрузил только для того, чтобы разобраться с типами параметров. Достаточно отфильтровать значение по полю house_params.type_id = 5. Обратите внимание, что в таблице house_params отсутствует поле is_active, поэтому актуальную запись следует искать по условию, что конечная дата больше или равна текущей.
Получаем вот такую конструкцию:
SELECT h.house_num, hp.value as postal_code
FROM houses h
LEFT JOIN house_params hp
ON hp.object_id = h.object_id
AND hp.type_id = 5 AND hp.end_date >= now()Как показала практика, для некоторых домов данный запрос может вернуть в postal_code null.
Таблица AS_ADM_HIERARCHY далее adm_hierarchies
Таблица содержит сведения по иерархии в административном делении. Малозначимые поля удалены.
Данные в ней собраны в виде дерева. Значение поля adm_hierarchies.object_id с красной заливкой равно houses.object_id. Данная строка несёт мало полезной информации, разве что код региона (в моём случае он не нужен), и ключ на родительскую запись в этой же самой таблице в поле parent_obj_id. Для того, чтобы не собирать дерево прыгая от записи к записи, в поле path уже собрали идентификаторы всех предков. Но для нас сейчас существенно значение в поле parent_obj_id, залито синим.
Таблица AS_ADDR_OBJ далее addr_objs
Таблица содержит классификатор адресообразующих элементов. Малозначимые поля удалены.
Выйти на эту запись можно зная значение adm_hierarchies.parent_obj_id. Полученная строка содержит наименование улицы (поле name), наименование её типа (поле type_name), а также внешнюю ссылку (поле level, залито фиолетовым) на таблицу AS_OBJECT_LEVELS.
Важно! Здесь я писал о ситуациях, когда данные помечены, как неактивные. Чтобы активировать их следует установить is_active в true не только у таблицы houses, но и для таблицы addr_objs. Не забывайте, что строк может быть несколько, ориентируйтесь на поле end_date. Сначала находим самую свежую запись
select id from addr_objs
where object_id = 597041
order by end_date desc
limit 1Затем выставляем is_active в true
update addr_objs set is_active = true
where id = 723807где 723807 id, полученный в предыдущем запросе.
Остальные справочники «актуализировать» не нужно.
Примечание! Данный способ «актуализации» корректно работает только на вновь загруженной полной базе. В случае применения дельт после подобной «актуализации» — могут возникнуть коллизии. В настоящее время я не изучал вопрос о применении дельт. Возможно, я опишу решение этой проблемы позднее.
Таблица AS_OBJECT_LEVELS далее object_levels
Является справочником для таблицы addr_objs. Для моей задачи, скорее избыточна, но пусть будет. На момент написания статьи содержит всего 17 строк.
Получение типа и наименования населённого пункта
Чтобы получить тип и наименование населённого пункта, нам снова потребуется таблица adm_hierarchies и addr_objs (см. рис 8-9), только соединять их нужно чуть иначе.
Берём ключ parent_object_id (выделен синем на рис. 8) соединяем его с object_id (рис. 11) и находим parent_object_id, выделено зелёным.
Найденное на рис. 11 значение поля parent_object_id (выделено зелёным) соединяем с полем object_id таблицы addr_objs (рис. 12).
Мы нашли тип и наименование населённого пункта. Обратите внимание, что на рисунке три записи. В начале тип населённого пункта был «г», потом стал «г.», а затем его опять превратили в «г». Поэтому не забывайте во время выборки делать проверку, на is_active = true.
Поле level ссылается на справочник object_levels (рис. 10)
Неувязки в базе ГАР
Разные типы у столбцов, выполняющих одну и ту же функцию, в различных таблицах. Например, поля is_active и is_actual в некоторых таблицах объявлены, как целое число, в диапазоне от 0 до 1 (рис. 13).
В то время, как в других, с которыми первые объединяются, как логический тип (рис. 14).
Некоторые поля в дочерних таблицах имеют строковый тип, и могут содержать лидирующий ноль (рис. 15)
В то время, как родительская таблица содержит поле целого типа (рис. 16)
Отсутствует единый стандарт заполнения базы ГАР. То, с чем я столкнулся: для г. Иваново улицы с числительными в названии выглядят так: «1-я Московская», для г. Шуи уже так: «Московская 1-я», но основные проблемы возникают с улицами, чьи именования похожи на тип улицы. Например, мне встречалась улица «20-я» с типом «линия», в то время, как «1-я линия» была набрана полностью.
Не логично названы поля. Например, start_date — end_date. Логично было бы назвать finish_date, или begin_date.
И, как уже отмечалось, непоследовательная работа с атрибутами is_active и is_actual. В некоторых таблицах такого атрибута нет вовсе, и приходится выбирать запись, ориентируясь на дату окончания её действия.
Эпилог
Вышеизложенного достаточно, чтобы понять, как соотносятся таблицы базы ГАР и реализовать собственную загрузку.
Вторая часть посвящена созданию консольного приложения Laravel, способного по команде artisan загрузить свежую выгрузку с сервера ГАР, распарсить её, занести данные в таблицы, используя многопоточность, и выдать данные в удобном табличном виде, используя представление базы данных.
Не требует web-сервера и запущенных воркеров Laravel.
Готовое приложение, расположенное на github.com прилагается.
В качестве бонуса: реализация «актуализации» устаревшей, но тем не менее, годной к употреблению информации.
