Предыдущая статья с описанием адресов ФИАС и функций для работы с ними в среде PostgreSQL вызвала интерес у небольшой части читателей.
Поэтому имеет смысл описать аналогичные функции на языке PL/pgSQL для работы со списком домов ФИАС, загруженным в базу данных под управлением PostgreSQL.
В первой половине статьи изложены комментарии к реализации функций. Во второй- исходные тексты функций, а также скрипты для создания таблицы с записями домов ФИАС, а также загрузки данных в эту таблицу из файла в формате CSV. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению или скачать все скрипты одним архивом.
Эта статья тесно связана с материалом статьи «Адреса ФИАС в среде PostgreSQL» .
Место постоянного размещения статьи:Дома ФИАС в среде PostgreSQL..
Родословная дома
Начнем с примера.
Вызов функции fstf_Houses_AddressObjectTree (‘42301ab8-9ead-4f8e-8281-e64f2769a254’) приведет к получению следующего списка записей.
Таблица 1. Результат выполнения функции.
При внимательном рассмотрении можно заметить, что в качестве аргумента функции передан идентификатор элемента (HOUSEGUID) «д. 1, корп. 2, стр. 26», в результате получены шесть записей:
- три родительских записи с адресообразующими элементами: о крае, городе и улице;
- три записи с характеристиками номера дома: номер дома, номер корпуса и номер строения.
У функции есть еще одни необязательный параметр дата окончания действия записи (EndDate), с помощью которого можно просматривать родословную не только актуальной записи о доме, но и уже устаревших записей.
Полный текст функции приведен в Приложении в подразделе Создание функции fstf_Houses_AddressObjectTree.
С самого начала
Если вы знаете как устроена таблица домов ФИАС, то этот раздел можно пропустить.
Дома ФИАС (HOUSES) представляют собой дочерний список для списка адресообразующих элементов ФИАС (ADDROBJ). Каждая запись списка домов ссылается на адресообразующий элемент ФИАС значением поля AOGUID. Для того чтобы определить на какой улице и в каком населенном пункте находится дом нужно по значению AOGUID записи HOUSES найти соответствующую запись с таким же идентификатором списка ADDROBJ.
При всей внешней простоте механизма взаимодействия списка домов со списком адресообразующих элементов в их взаимодействии особенности, осложняющие реализацию функций на HOUSES.
Во-первых, каждая запись списка домов по идентификатору AOGUID ссылается на группу адресообразующих элементов, один из которых является актуальным.
Во-вторых, в списке ФИАС несколько записей с одним и тем же набором характеристик номера дома: номер дома, номер корпуса, номер строения.
В-третьих, запись о доме не всегда наследуется от записи об улице населенного пункта.
Но, обо всем по порядку.
Для дальнейшего рассмотрения хранения сведений о домах в ФИАС достаточно ограничиться на 4 таблицах (DBF-файлах):
- ADDROBJ – список адресообразующих элементов;
- HOUSES – список домов;
- STRSTAT – справочник признаков строения;
- ESTSTAT – справочник признаков владения.
ADDROBJ подробно рассмотрена в предыдущей публикации «Адреса ФИАС в PostgreSQL», поэтому здесь о ее особенностях будет говорится ровно столько, сколько необходимо для описания характеристик домов.
Таблица 2. История дома «Красноярский край, р-н Таймырский Долгано-Ненецкий, г Дудинка, ул. Дудинская, д. 1»
Как видно из таблицы, в отличии от адресообразующих объектов, записи истории дома не имеют специальных признаков актуальности. Актуальной является запись с самой старшей датой окончания периода, которая больше текущей. Пока актуальные записи о домах помечаются датой «06.06.2079». Все остальные записи о доме считаются историческими, а даты начала и окончания характеризуют период актуальности каждой записи.
Список домов ФИАС не содержит указателей на предыдущую и следующую записи о доме. Поэтому порядок следования записей от актуальной в глубь истории дома определяются по убыванию даты окончания и за ней даты начала периода, соответственно EndDate и StartDate.
1 2 |
SELECT * FROM fias_Houses h WHERE h.HOUSEGUID='2fd64ecd-4b4b-4fc8-bd15-4a4a2f310b21' ORDER BY h.ENDDATE DESC,h.STARTDATE DESC; |
Внимательный читатель, глядя на Рис. 1, наверняка задал себе вопрос: зачем упомянуты справочники признаков строения и владения? В ФИАС используется свыше 10 подобного рода справочников, так почему особо выделены эти два?
Ответ многих удивит – с точки зрения «логики ФИАС» адрес дома не полностью идентифицируется адресом улицы, номерами дома, корпуса и строения. Термин «логики ФИАС» использован в ответе сотрудника ФНС на мой вопрос почему в списке домов Красноярского края находится свыше 250 парных адресов домов. В этом же ответе было сказано, что уникальность записи обеспечивают значения AOGUID, HOUSENUM, BUILDNUM, STRUCNUM, STRSTATUS, ESTSTATUS.
Другими словами, для нахождения объекта недостаточно знать населенных пункт, улицу, номер дома. Необходимо еще знать:
- «владение» это или «домовладение»;
- определен статус этого объекта или не определен;
- и т.д.
Вот так выглядит выборка из общего списка домов ФИАС с повторяющимися адресами.
В том, что разные объекты имеют одинаковый адрес нет ничего удивительного. Здание и земельный участок под ним; дом, гараж, баня у одного хозяина. Все они имеют один и тот же адрес. Но ФИАС – это адресный реестр, т.е. список адресов. Поэтому естественно ожидать, что уникальными в нем будут адреса, а не здания, строения, сооружения.
Т.е. список домов ФИАС из списка адресов домов начал развиваться в сторону перечня наземных строений. И пользователям ФИАС необходимо это учитывать.
Каждый желающий может сам проверить наличие домов с повторяющимися адресами, выполнив оператор SELECT подобный следящему. При этом, функцию fsfn_Houses_TreeActualName можно не применять, т.к. она использована только для того, чтобы сократить число колонок результата. Не обязательно использовать справочники fias_StructureStatus (аналог STRSTAT) и fias_EstateStatus (аналог ESTSTAT), т.к. отмеченный эффект можно проследить и на кодах признаков строения и владения.
И наконец, еще одна особенность списка домов ФИАС. Каждая запись о доме этого списка содержит ссылку на адресообразующий элемент, список которых представляет собой иерархию таких элементов. На каждом уровне иерархии находятся адресообразующие элементы, относящиеся к различным типам. Так корневым элементом является регион (Красноярский край в нашем случае), на следующем уровне автономный округ, район или город регионального подчинения. И так далее. (Подробности смотри в «Адреса ФИАС в PostgreSQL»).
Формально запись о доме позволяет ссылаться на элемент иерархии любого уровня. К счастью, домов, ссылающихся на район или регион, среди данных Красноярского края не встретилось. И тем не менее не все дома ссылаются на улицу населенного пункта:
- 98% домов ФИАС связаны с улицами в населенных пунктах;
- 1,2% домов¬ — с улицами в садово-огородных товариществах;
- 0,3% домов- с населенными пунктами;
- 0,5% домов- с прочими адресными элементами.
Рис. 2.Схема подчиненности домов видам адресных элементов
Размножение адресов дома по владельцам (ФИАС vs карта)
Здесь описана проблема, которая приводит к неоднозначному толкованию родословной дома. (На эту проблему мое внимание обратил Игорь Леонидович Тимощенков, ГИС-специалист, компании ООО «Айгео», Красноярск).
Выше показано как несколько записей содержат один и тот же адрес дома. Что можно объяснить желанием налоговой инспекции хранить не только запись о частном доме, но и об окружающих постройках: гараже, сарае, и т.д. Но есть обратные примеры, когда одному строению (дому) соответствуют несколько записей таблицы fias_Houses с различными значениями номеров этого дома.
Вглядитесь в этот рисунок. Слева на нем снимок экрана с картой поселка, где расположены домами на два хозяина. Это обычные одноэтажные дома с двумя входами. Справа живет одна семья, а слева другая. Их еще можно представлять, как дома из двух квартир.
А теперь посмотрите на таблицу справа. В ней практически каждому дому на два хозяина соответствует по 3 записи. Т.е. таблице домов ФИАС указаны как адрес отдельного дома («д. 1»), так и адреса частей дома («д. 1/1», «д. 1/2»), принадлежащих одному хозяину.
Как это работает
Функция fstf_Houses_AddressObjectTree имеет две версии: с четырьмя или с двумя параметрами. В версии функции с двумя параметрам передается идентификатор дома (HouseGUID) и дата окончания действия записи (EndDate). Версия с четырьмя параметрами дополнительно требует идентификатор адресообразующего элемента (AOGUID) и текущий статус (CurrStatus).
Функция с меньшим числом параметров вычисляет значения недостающих параметров и вызывает функцию с большим числом параметров. Для этого предварительно идентификатор адресообразующего элемента просто извлекается из соответствующего поля таблицы домов (fias_Houses). А значение текущего статуса (CurrStatus) вычисляется по следующим правилам:
- если ни одна из записей истории адресообразующего элемента не содержит 0 в поле CurrStatus, то переменной v_CurrStatus присваивается максимальное значение поля для этого адресообразующего элемента;
- иначе этой переменной присваивается значение 0.
Функция с большим числом параметров сначала вызывает функцию fstf_AddressObjects_AddressObjectTree, которая возвращает родительские адресообразующие элементы для дома. Подробнее о функции fstf_AddressObjects_AddressObjectTree можно прочитать в разделе Родословная адресообразующего элемента документа «Адреса ФИАС в среде PostgreSQL».
Затем записи об адресообразующих элементах дополняются записями о номерах дома, корпуса, строения (см. Таблица 1), которые создаются для каждого непустого поля о номере дома, корпуса и строения.
Для того чтобы все выводимые записи имели одинаковую структуру и не без некоторой доли пижонства, в теле функции искусственно создаются значения полей код уровня (AOLevel), текущий статус (CurrStatus) и статус актуальности (ActStatus).
Коду уровня дома (корпуса, строения) всегда присваивается значение 8, смотри справочник «Уровней адресных объектов» из документа Сведения о составе информации ФИАС).
Статусу актуальности присваивается значение 1, если дата окончания действия записи (EndDate) равна 06.06.2079, и 0 в противном случае.
Со значениями поля CurrStatus дело обстоит сложнее. При помощи его значений решаются две задачи одновременно: устанавливается идентификатор каждой версии записи об адресообразующем элементе и присваивается признак актуальности записи. Поэтому последняя актуальная запись об элементе содержит значение 0 в этом поле, а все исторические записи нумеруются в порядке появления – «1» самая ранняя запись, следующая за ней по времени – «2» и т.д. Подробнее порядок присвоения значений полю CurrStatus рассмотрен в публикации «Адреса ФИАС в PostgreSQL».
Полный адрес дома
Основная идея функции fsfn_Houses_TreeActualName в том, чтобы возвратить соединенные в одну строку номер дома вместе с названиями всех его предков – адресообразующих элементов.
Например, пусть функция родословной дома (fstf_Houses_AddressObjectTree) возвращает следующий список значений.
Таблица 4. Результат выполнения функции fstf_Houses_AddressObjectTree(‘0c0d670f-097c-4e1d-bcac-9e9b22fb1b99’)
Тогда fsfn_Houses_TreeActualName (‘0c0d670f-097c-4e1d-bcac-9e9b22fb1b99’) должна возвратить: «г Красноярск, ул им Сергея Лазо, д. 34А, корп. 6, стр. 17».
Функцию fsfn_Houses_TreeActualName упрощенно можно представлять, как агрегатную функцию STRING_AGG над результатом функции, возвращающей родословную дома.
У рассматриваемой функции есть еще одни необязательный параметр — массив масок (a_MaskArray), с помощью которого можно включать в результат не все названия элементов, а только те, которые нужны.
Таблица 5. Список масок функции.
Смотри также раздел «Полное наименование адресообразующего элемента» публикации «Адреса ФИАС в PostgreSQL».
Текст функции приведен в разделе Приложения «Создание функции fsfn_Houses_TreeActualName».
Поиск дома ФИАС
Функция fstf_Houses_SearchByName предназначена для поиска адресов домов ФИАС по их номерам и названиям адресообразующих элементов. При этом поиск может осуществялться не только по названию и типу текущего элемента, но и названиям и типам одного или двух его ближайших предков.
Рассмотрим несколько примеров. И для начала найдем все дома с номером «220».
Таблица 6. Результат выполнения функции fstf_Houses_SearchByName(‘220’)
В отличие от функции поиска адресообразующих элементов
(fstf_AddressObjects_SearchByName) результат этой функции не содержит эфекта «плавания» по уровням адресообразующих элементов. Первый параметр функции всегда содержит шаблон поиска номера дома, второй – номера корпуса, третий номера строения.
Теперь изменим запрос. Найдем все дома адресообразующих элементов, номер которых содержит цифру «1», и в названиях встречается слово «Красноярск».
Таблица 7. Результат выполнения функции fstf_Houses_SearchByName(‘1′,NULL,NULL,’Красноярск’)
Назначение остальных параметров в точности совпадает с назначением параметров функции поиска адресообразующих эелементов (fstf_AddressObjects_SearchByName).
Текст функции приведен в разделе Приложения «Создание функции fstf_Houses_SearchByName»
Как это работает
Реализация fstf_Houses_SearchByName во многом похожа на реализацию функции поиска адресообразующих элементов (fstf_AddressObjects_SearchByName). Главное отличие заключается в том, что поиск осуществляется в двух, связанных таблицах fias_Houses и fias_AddressObjects.
У функции 9 аргументов. Первые три из них – это номера дома (a_HouseNum), корпуса (a_BuildNum) и строения (a_StrucNum). Остальные 6 (a_FormalName, a_ShortName, a_ParentFormalName, a_ParentShortName, a_GrandParentFormalName, a_GrandParentShortName) полностью совпадают с параметрами функции.
Если задать только значение параметра «номер дома», то функция возвратит все адреса в номере дома, которых встречаются указанная последовательность символом. Если в качестве номера дома передать значение NULL или пустую строку («»), то будут возвращены адреса всех домов, адресные элементы которых заданы набором прочих параметров.
Эпилог
Этот раздел содержит рекомендации к тому, как загрузить список домов ФИАС в таблицу fias_Houses.
Загрузка данных в таблицу домов выполняется примерно также как загрузка данных в таблицу адресообразующих элементов. Только исходным файлом станет HOUSE99.DBF, а не ADDROB99.DBF. Здесь 99 – это номер региона (Республики, области, края). Например, для Красноярского края исходным является файл HOUSE24.DBF.
Сначала с страницы «Обновления» ФИАС скачивается очередной архив с обновлением. Из него извлекается файл HOUSE99.DBF.
Затем файл HOUSE99.DBF преобразуется к формату CSV и уже преобразованный он загружается оператором COPY во временную таблицу fias_Houses_Temp.
И, наконец, данные временной используют для обновления основной таблицы, т.е. несуществующие в fias_Houses добавляются, а уже существующие – заменяются.
Пример скрипта обновления таблицы домов приведен в разделе «Загрузка обновлений домов ФИАС в таблицу fias_Houses».
Смотрите также
Адреса ФИАС в среде PostgreSQL;
Приложение
Скачать все скрипты одним архивом.
Создание функции fstf_Houses_AddressObjectTree
Комментарии к исходному коду функции можно посмотреть здесь.
Создание функции fsfn_Houses_TreeActualName
Комментарии к исходному коду функции можно посмотреть здесь.
Создание функции fstf_Houses_SearchByName
Комментарии к исходному коду функции можно посмотреть здесь.