Адреса ФИАС в среде PostgreSQL

Как быFIAS_PostgreSQL мы не относились к качеству адресов ФИАС с ними необходимо работать, потому что это единственный общероссийский справочник адресов. Поэтому рано или поздно приходится решать задачу связи местоположения объектов недвижимости, юридических и физические адресов с адресом из ФИАС.

В этой статье изложен опыт работы со списком адресообразующих элементов ФИАС, загруженных в базу данных под управлением PostgreSQL. Для работы с адресообразующими элементами ФИАС создано несколько функций на языке PL/pgSQL.

Места постоянного размещения статьи:

В первой половине статьи изложены комментарии к реализации функций. Во второй- исходные тексты функций, а также скрипты для создания таблицы с записями адресообразующих элементов ФИАС, а также загрузки данных в это таблицу из файла в формате CSV. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению
или  Скачать все скрипты одним архивом скачать все скрипты одним архивом.

Родословная адресообразующего элемента
Начнем с примера.
Вызов функции fstf_AddressObjects_AddressObjectTree(‘bfc1236d-b5d2-4734-a238-3b1e4830e963’) в операторе SELECT приведет к получению следующего списка записей.

Таблица 1. Результат выполнения функции fstf_AddressObjects_AddressObjectTree(‘bfc1236d-b5d2-4734-a238-3b1e4830e963’).
Table1

При внимательном рассмотрении можно заметить, что в качестве аргумента функции передан идентификатор элемента (AOGUID) «улица Новая», в результате получены четыре записи:

  • запись с характеристиками самой улицы Новая;
  • три родительских записи о поселке, районе и крае, которым принадлежит улица.

У функции есть еще одни необязательный параметр признак актуальности (CurrStatus), с помощью которого можно просматривать родословную не только актуального адресообразующего элемента, но и уже устаревшего.
Полный текст функции приведен в Приложении в подразделе Создание функции fstf_AddressObjects_AddressObjectTree.

С самого начала

Если вы знаете как устроены таблицы ФИАС, то этот раздел можно пропустить.

Потребность в такой функции продиктована тем, что список адресообразующих элементов ФИАС (ADDROBJ) представляет собой древовидную структуру, которой каждый элемент ссылается на идентификатор (AOGUID) родительской записи значением поля ParentGUID. Т.е. просматривая записи исходного списка, обычно наблюдаешь длинный список улиц. Для того чтобы определить в каком населенном пункте находится улица нужно по значению ParentGUID найти записи с таким идентификатором элемента.
AddressHierarchy
Рис. 1. Иерархия адресообразующих элементов ФИАС.

Это не описка и не оговорка. По значению ParentGUID может быть найдена не одна, а несколько записей. Из чего следует, что идентификатор адресообразующего элемента не является первичным ключом для таблицы, содержащей список ADDROBJ.
Дело в том, что список адресообразующих элементов, вместе с каждым элементом хранит историю его «переименования». Т.е. под одним идентификатором элемента хранится не только текущее название элемента, но его прежние названия. Т.е., отдельная запись списка ADDROBJ хранит данные об адресообразующем элементе, а также характеристики календарного периода, в течении которого было актуальным название элемента.

Таблица 2. История улицы «Красноярский край, р-н Таймырский Долгано-Ненецкий, г Дудинка, п Левинские Пески, ул Береговая»
Table2

Порядок следования периодов актуальности названия адресообразующего элемента, может быть определен путем просмотра двух разнонаправленных списков. Для этого каждая запись о периоде актуальности элемента содержит два указателя на предыдущий (PrevID) и последующий периоды (NextID). У первого периода адресообразующего элемента отсутствует указатель на предыдущий период, а у последнего (актуального) отсутствует указатель на следующий период.

AddressFields
Рис. 2. Основные поля записи адресообразующего элемента ФИАС.

Период актуальности характеризуется датами начала и окончания периода, соответственно StartDate и EndDate. При этом даты начала первого периода и окончания последнего имеют условные значения. За дату начала первого периода принимается «01.01.1900 0:00», а за дату окончания последнего (актуального) периода принимается «06.06.2079 0:00».
Актуальное (ныне действующее) название адресообразующего элемента указано в записи о последнем периоде, при условии что он не завершен, т.е. дата окончания периода больше или равна текущей дате.
Для упрощения поиска записи об актуальном периоде элемента, кроме даты начала и окончания периода, введены еще два поля: CurrStatus и ActStatus.
ActStatus принимает вполне ожидаемые значения: «1» – актуальная версия характеристик элемента, «0» – не актуальная или историческая версия, как обозначено в справочнике.
Со значениями поля CurrStatus дело обстоит сложнее. При помощи его значений решаются две задачи одновременно: устанавливается идентификатор каждой версии записи об адресообразующем элементе и присваивается признак актуальности записи. Поэтому последняя актуальная запись об элементе содержит значение 0 в этом поле, а все исторические записи нумеруются в порядке появления – «1» самая ранняя запись, следующая за ней по времени – «2» и т.д. Таблица 2 содержит список записей с версиями описания улицы Береговой посёлка Левинские Пески. В этом списке предыдущая к актуальной записи содержит «7» в поле CurrStatus.


Как это работает
CoreStatement
Рис. 3. Упрощенная реализация функции fstf_AddressObjects_AddressObjectTree

Для реализации функции напрашивается использовать рекурсивный запрос подобный тому, который приведен на Рис. 3, где a_AOGUID идентификатор адресообразующего элемента, a_CurrStatus признак актуальности по КЛАДР. Оба значения передаются функции через параметры.
Как всякий рекурсивный запрос, этот состоит из двух частей: в первой части находится первая запись элемента с идентификатором a_AOGUID, в следующей рекурсивной части последовательно находятся все актуальные родительские записи по отношению к записям, полученным процессе предыдущих итераций. Переход к родительскому элементу выполняется по ссылке ParentGUID текущей записи.
Здесь важно, чтобы каждая итерация приводила к получению только одной записи. Для этого накладывается ограничение на значение признака CurrStatus. Пример результата применения рекурсивного запроса в условиях отсутствия уникальности записи хотя бы на одном шаге итерации приведен в Рис. 3.
Возникает вопрос – почему ограничение на значение CurrStatus реализовано через вложенный подзапрос, а не путем присвоения актуальных значений? Во-первых, потому, что в актуальной записи об элементе не всегда CurrStatus=0, как это демонстрирует Таблица 4. Во-вторых, необходимо, чтобы функция возвращала результат для неактуальных названий элемента. В том числе, даже в том случае, когда на пути между начальным и конечным элементами встретится элемент, для которого не объявлено ни одной актуальной записи.

Таблица 3. Результат выполнения функции над элементом с не уникальным признаком выбора записи
Table3

Если a_CurrStatus =0 использовать как условие выбора единственной записи для элемента, то функция не вернет для записей о городе Заозерный, записи о котором приведены в Таблица 4. При этом, если задать a_CurrStatus =51, то результат будет таким, как показано в Таблица 3.


Это условие, которое присутствует во второй части рекурсивного запроса, не совсем корректно, если исходить из правила присвоения значений для признака CurrStatus. Действительно, если среди записей адресообразующего элемента нет актуального (CurrStatus=0), то наиболее «свежей» является запись с максимальным, т.е. последним использованным, значением признака CurrStatus. Когда используется выше приведенное условие, то из записей неактуального элемента выбирается самая старая.

Таблица 4. Элемент с повторяющимся не равным нулю значением CurrStatus
Table4

Поэтому, более правильным решением будет использование следующего условия:

Но, в этом условии два подзапроса. Так что приходится выбирать между смысловой строгостью запроса и эффективностью его выполнения.
Оправданием использования первого варианта условия служит фактическое отсутствие различий названий адресообразующего элемента в исторических записях. Так по состоянию на 13 октября 2016 года, из 26728 адресообразующих элементов Красноярского края в 19865 присутствуют исторические записи. При этом лишь для 1350 элементов (6,8% от числа элементов, имеющих историю) присутствуют различия в названиях одного и того же элемента. Т.е. для 93,2% элементов первое и второе условия будут возвращать один и тот же список названий. Отличия возможны только в значениях признака CurrStatus, которыми, учитывая назначение функции, можно пренебречь.
Полностью замена признака CurrStatus на ActStatus невозможна. По условию ActStatus=1 однозначно выбирается актуальная запись об элементе, но для работы с историческими записями приходится использовать оба признака. Такое решение приведено в приложении Создание функции fstf_AddressObjects_AddressObjectTree.


Полное наименование адресообразующего элемента

Основная идея функции fsfn_AddressObjects_TreeActualName в том, чтобы возвратить соединенные в одну строку название элемента вместе с названиями всех его предков.
Например, пусть функции поиска родословной элемента (fstf_AddressObjects_AddressObjectTree) возвращает следующий список значений.

Таблица 5. Результат вызова fstf_AddressObjects_AddressObjectTree .

Table5

Тогда fsfn_AddressObjects_TreeActualName(‘bfc1236d-b5d2-4734-a238-3b1e4830e963’)  должна возвратить:
«Красноярский край, Балахтинский р-н, п Могучий, ул Новая»

У функции есть еще одни необязательный параметр массив масок (a_MaskArray),с помощью которого можно включать в результат не все названия элементов, а только те, которые нужны.

Текст функции приведен в разделе Приложения «Создание функции fsfn_AddressObjects_TreeActualName».

Как это работает

Основу реализации функции составляет вызов fstf_AddressObjects_AddressObjectTree и цикл по возвращаемым ей записям, в теле которой формируется поллное наименование адресообразующего элемента путем сцепления (конкатенации) всех наименований в одну строку. Эта строка в конце концов будет возвращения функцией fsfn_AddressObjects_TreeActualName.
Далее будут поясняться детали.
Во-первых, порой нет необходимости в том, чтобы результат функции обязательно включал наименования всех предков текущего элемента. Например, в пределах Красноярского края вместо «Красноярский край, Балахтинский р-н, п Могучий, ул Новая», чаще используют укороченную форму «Балахтинский р-н, п Могучий, ул Новая». А внутри города Красноярска вместо адреса «Красноярский край, г Красноярск, д Песчанка, ул Сергея Лазо» чаще используют «д Песчанка, ул Сергея Лазо».
Для того чтобы иметь возможность управлять разными формами написания полного наименования адресообразующего элемента введен параметр массив масок (a_MaskArray), который содержит последовательность указателей (масок) на группы элементов, которые должны быть включены в результирующую строку.

Таблица 6. Список масок функции.
Table6

Во-вторых, для того чтобы реализовать построение полного наименования в соответствии с массивом масок создана вспомогательная функция fsfn_AddressObjects_ObjectGroup, которая относит каждый адресообразующий элемент к определенной группе.

Таблица 7. Значения, возвращаемые функцией fsfn_AddressObjects_ObjectGroup.

Table7

Список значений, возвращаемых функцией fsfn_AddressObjects_ObjectGroup, приведен в Таблица 5.
Цель создания этой функции в том чтобы собрать в одном месте все особенности (если хотите, то «костыли») определения группы элемента. С детальной реализацией этой функции можно ознакомиться в разделе «Создание функции fsfn_AddressObjects_ObjectGroup».
Комбинация значений функции и поля AOLevel (уровень адресообразующего элемента) вместе с проверкой на присутствие маски группы массиве масок позволяет определять должно ли название текущего элемента включаться в строку результата.
Например, признаком того, что название основного населенного пункта должно включаться в полное наименование элемента, является истинность следующего выражения: «v_ObjectGroup=’City’ AND ‘{LM}’ <@ a_MaskArray AND v_AOLevel =4».


Поиск адресообразующего элемента

Функция fstf_AddressObjects_SearchByName предназначена для поиска адрсообразующих элементов ФИАС по их названиям. При этом поиск может осуществялться не только по названию и типу текущего элемента, но и названиям и типам одного или двух его ближайших предков.
Рассмотрим несколько примеров с примера.
Для начала найдем все адресообразующие элементы в названии которых встречается слово «Грибной».

Таблица 8. Результат выполнения функции fstf_AddressObjects_SearchByName(‘Грибной’).
Table8

В полученном результате нет ничего неожиданного, если не считать наглядного доказательства пользы от функции построения полного наименования.
Теперь изменим запрос. Найдем все адресообразующие элементы, в названии ближайшего предка которых встречается слово «Грибной».

Таблица 9. Результат выполнения функции fstf_AddressObjects_SearchByName(NULL,NULL,’Грибной’)
Table9

5ae71e68-5477-446b-b878-0a9c9bf3bdcd 7 Ачинский р-н, п Грибной, ул Южная ул Южная 0 1 п Грибной
Результат этого запроса несколько более неожиданный, т.к. в названиях найденных адресообразующих элементах нет слова «Грибной», но оно есть в названии их предка.
И, наконец, рассмотрим поиск по наименованию прародителя, в котором должно присутствовать слово «Ачинский», а в наименовании его внука должен присутствовать часть слова «Оз_рн». Здесь использован специальный символ — символ подчеркивания «_». Этот символ указывает, что на его месте может находиться любой одиночный символ. Здесь он применен, для того чтобы найти не только элементы с названиями «Озерный» или «Озерная», но и «Озёрный» или «Озёрная».

Таблица 10. Результат выполнения функции
fstf_AddressObjects_SearchByName(‘Оз_рн’,NULL,NULL,NULL,’Ачинский’)

Table10

В результате запроса найдены улицы Озерная и переулок озерный в трех населенных пунктах Ачинского и Казачинского районов Красноярского края.
Текст функции приведен в разделе Приложения «Создание функции fstf_AddressObjects_SearchByName».


Как это работает

В реализации fstf_AddressObjects_SearchByName нет ничего необычного, если не считать того, что одно из полей возвращается функцией построения полного наименования адресообразующего элемента — fsfn_AddressObjects_TreeActualName.
У функции 6 аргументов: a_FormalName, a_ShortName, a_ParentFormalName, a_ParentShortName, a_GrandParentFormalName, a_GrandParentShortName. Задавать значение можно только первому аргументу, остальные будут получать значения по умолчанию.
Если значения присвоены только первым двум аргументам – названию (a_FormalName) и типу (a_ShortName) адресообразующего элемента, то поиск осуществляется во всех записях таблицы fias_AddressObjects. Предварительно значения переданных параметров преобразуются в верхний регистр, пробелы заменяются символом «%». Этим же символом окружается значение справа и слева. Преобразованные таким образом значения используются в поисковом запросе как часть операции LIKE. Пример такого запроса приведен на Рис. 4.

SearchByName SELECT 1
Рис. 4. Простой поиск адресообразующего элемента.

Условие выбора значения CurrStatus подробно обсуждалось в разделе «Родословная адресообразующего элемента» «Как это работает».

SearchByName SELECT 2
Рис. 5. Поиск по названию и типу родительского адресообразующего элемента.

Для поиска по названию и типу родительского адресообразующего элемента необходимо присвоить значение хотя бы одному из двух аргументов: третьему (a_ParentFormalName), или четвертому (a_ParentShortName). В этом случае поиск осуществляется во всех записях таблицы, полученной соединением (INNER JOIN) всех записей fias_AddressObjects с записями родительского адресообразующего элемента по признаку pfa.AOGUID=cfa.ParentGUID. Пример такого запроса приведен на Рис. 5.
Предварительная обработка значений входных параметров осуществляется по тем же правилам, как и в случае простого поиска.

Для поиска по названию и типу прародительского адресообразующего элемента необходимо присвоить значение хотя бы одному из двух аргументов: пятому (a_GrandParentFormalName) или шестому (a_GrandParentShortName). В этом случае поиск осуществляется во всех записях таблицы, полученной двойным соединением (INNER JOIN) всех записей fias_AddressObjects с записями родительского и прародительских адресообразующих элементов. . Пример такого запроса приведен на Рис. 6.
Предварительная обработка значений входных параметров осуществляется по тем же правилам, как и в случае простого поиска.

SearchByName SELECT 3
Рис. 6. Поиск по названию и типу прародительского адресообразующего элемента.

Эпилог

Этот раздел содержит рекомендации к тому, как загрузить список адресообразующих элементов ФИАС в таблицу fias_AddressObjects.

С чего начать

Начать надо с посещения официального сайта Федеральной Налоговой Службы раздела «Федеральная информационная адресная система» (ФИАС) страницы «Обновления».
Загрузить на ваш компьютер последнее обновление или полную базу ФИАС, если вы только начинаете работать с ФИАС.
Перенести файл с архивом в рабочую папку. Извлечь файлы архива и найти файл ADDROBJ.DBF.
Далее предполагается, что загружен архив файлов с обновлением ФИАС в формате dbf.
Загруженный файл ADDROBJ.DBF преобразовать к формату csv. Для этого открыть исходный файл при помощи MS Excel и пересохранить его в формате в csv, не забыв при этом удалить строку с названиями полей записей. Далее преобразованный к формату csv будет именоваться «ADDROBJ24_20161020.csv», где 24 –код Красноярского края, а 20161020 – дата загрузки файла.
Создать таблицу fias_AddressObjects. Для этого можно воспользоваться скриптом приведенным в приложении «Создание таблицы адресообразующих элементов ФИАС fias_AddressObjects».

Загрузка ADDROBJ24_20161020.csv в базу данных

AddressObjects COPY FROM
Рис. 7 Непосредственная загрузка данных в таблицу fias_AddressObjects.

Непосредственно загрузить данные из файла ADDROBJ24_20161020.csv в таблицу fias_AddressObjects можно так как показано на Рис. 7.
Но, к сожалению, простой путь не для нас.
Непосредственно загрузить данные из файла ADDROBJ24_20161020.csv в таблицу fias_AddressObjects можно так как показано на Рис. 7.
Но, к сожалению, простой путь не для нас.
Во-первых, кроме основного списка адресообразующих элементов поставляется еще и список адресообразующих элементов которые должны быть удалены из основного списка (DADDROBJ.DBF);
Во-вторых, в основном списке присутствуют нарушения связности, например, ссылки, которые никуда не ведут, т.е. в списке нет элемента или записи с идентификатором, указанном в ссылке. Поэтому не хочется восстанавливать ошибки, которые уже один раз исправлены.
В-третьих, не хочется каждый раз работать с полным список адресообразующих элементов ФИАС, а лишь загружать изменения, которые появляются на официальном сайте Федеральной Налоговой Службы два –три раза в неделю.
Поэтому в процессе загрузки обновления ФИАС используется две временных таблицы:

  • fias_AddressObjects_temp – для обновлений основного списка адресообразующих элементов;
  • fias_DeletedAddressObjects_temp – для записей, которые должны быть удалены из основного списка.

Обе таблицы имеют туже структуру, что и основная таблица fias_AddressObjects и создаются той же последовательностью операторов PL/pgSQL, в которой название основной таблицы заменена на временную.

AddressObjects temp COPY FROM
Рис. 8. Предварительная загрузка адресообразующих элементов во временные таблицы.

Последовательность операторов создания основной таблицы приведена в разделе «Создание таблицы адресообразующих элементов ФИАС fias_AddressObjects».
Далее данные таблицы fias_AddressObjects_temp служат для замены (UPDATE) значений в уже существующих записях и добавления (INSERT) вновь созданных записей основную таблицу. С подробным текстом этих операторов можно ознакомиться в разделе «Загрузка обновлений адресообразующих элементов ФИАС в таблицу fias_AddressObjects».

Так как в процессе обновления могут быть внесены нарушения целостности, можно загрузить записи, в которых ссылки на следующую (NEXTID) или предыдущую (PREVID) запись истории указывают на несуществующую запись.
Эта ситуация очень вероятна. Вот, например, данные по результатам загрузки полной базы данных по состоянию на 10.10.2016 года.
Всего нарушений

  • в значениях NEXTID
    • по всем записям — 41109,
    • по записях Красноярского края – 1152
  • в значениях PREVID
    • по всем записям — 18577,
    • по записях Красноярского края – 981.

Поэтому прежде чем выполнять обновления основной таблицы необходимо отключить действие ограничений:

После того как обновления основной таблицы выполнены, необходимо присвоить значения NULL полям NEXTID или PREVID там, где их значения указывают на несуществующую запись. Например, так:

 

Перед завершением загрузки следует восстановить ограничения и удалить временные таблицы.

Смотрите также

Адреса ФИАС в среде PostgreSQL;

ПРИЛОЖЕНИЕ
 Скачать все скрипты одним архивом Скачать все скрипты одним архивом.


Создание функции fstf_AddressObjects_AddressObjectTree



Создание функции fsfn_AddressObjects_ObjectGroup


Создание функции fsfn_AddressObjects_TreeActualName


Создание функции fstf_AddressObjects_SearchByName

.
Создание таблицы адресообразующих элементов ФИАС fias_AddressObjects


Загрузка обновлений адресообразующих элементов ФИАС в таблицу fias_AddressObjects

 Скачать все скрипты одним архивом Скачать все скрипты одним архивом.

Прежнее место размещения: http://www.torins.ru/support/blogs.php?page=post&blog=gladkov&post_id=110

This entry was posted in Блог and tagged , , . Bookmark the permalink.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *