Что в этой части?

Исследование выгрузки базы ГАР с целью автоматизации загрузки данных в свою базу.

Решаемая задача

Зная 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-я линия» была набрана полностью.

И, как уже отмечалось, непоследовательная работа с атрибутами is_active и is_actual. В некоторых таблицах такого атрибута нет вовсе, и приходится выбирать запись, ориентируясь на дату окончания её действия.

Эпилог

Вышеизложенного достаточно, чтобы понять, как соотносятся таблицы базы ГАР и реализовать собственную загрузку.

Вторая часть будет посвящена разработке консольного приложения Laravel, которое будет решать ряд задач:

1. Создание консольной команды для полностью автоматической загрузки полной версии выгрузки базы ГАР с использованием фасада Laravel Process (появился в Laravel 10)

2. Обращение к данной команде как из командной строки, так и из планировщика Laravel

3. Создание базового абстрактного класса для парсинга xml файлов

4. Использование очередей Laravel для асинхронного парсинга выгрузки базы ГАР

5. Написание представления (объекта базы данных) для удобного доступа к справочным данным