Адреса ФИАС в среде PostgreSQL. Часть 1

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

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

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

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

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

Таблица 1. Результат выполнения функции fstf_AddressObjects_AddressObjectTree

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

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

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

Полный текст функции приведен в Приложении в подразделе Создание функции fstf_AddressObjects_AddressObjectTree

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

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

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

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

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

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

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

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

Рис. 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.

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

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

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

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

Возникает вопрос – почему ограничение на значение CurrStatus реализовано через вложенный подзапрос, а не путем присвоения актуальных значений?

Во-первых, потому, что в актуальной записи об элементе не всегда CurrStatus=0, как это демонстрирует Таблица 4.

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

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

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

Условие:

ao.currstatus = (SELECT MIN(iao.currstatus) FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid)

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

Когда используется выше приведенное условие, то из записей неактуального элемента выбирается самая старая.

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

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

ao.currstatus = CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao 
                                                                 WHERE ao.aoguid = iao.aoguid)
		THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
                                                                WHERE ao.aoguid = iao.aoguid)
		ELSE 0 END

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

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

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

Полностью замена признака CurrStatus на ActStatus невозможна. По условию ActStatus=1 однозначно выбирается актуальная запись об элементе, но для работы с историческими записями приходится использовать оба признака. Такое решение приведено в приложении Создание функции fstf_AddressObjects_AddressObjectTree.

ПРИЛОЖЕНИЕ

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

BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS fstf_AddressObjects_AddressObjectTree(a_AOGUID VARCHAR(36),
                     a_CurrStatus INTEGER);
/*************************************************************************/
/* Возвращает дерево (список взаимосвязанных строк) с характеристиками   */
/* адресообразующего элемента                                            */
/*************************************************************************/	
CREATE OR REPLACE FUNCTION fstf_AddressObjects_AddressObjectTree(
  a_AOGUID VARCHAR(36), /* Глобальный уникальный идентификатор */
                                                    /* адресообразующего элемента*/
 a_CurrStatus INTEGER default NULL /* Статус актуальности КЛАДР 4:	 */
                                                   /*	0 - актуальный,  */
                                                    /* 1-50 - исторический, т.е. */
                                                    /*  элемент был переименован, */
                                                   /* в данной записи приведено одно */
                                                   /* из прежних его наименований, */
                                                   /* 51 - переподчиненный */
)
RETURNS TABLE (rtf_AOGUID VARCHAR(36), rtf_CurrStatus INTEGER, rtf_ActStatus INTEGER, 
                                rtf_AOLevel INTEGER,rtf_ShortTypeName VARCHAR(10),
                                rtf_AddressObjectName VARCHAR(100)) AS
$BODY$
DECLARE
 c_ActualStatusCode CONSTANT INTEGER :=1; /* Признак актуальной записи  */
                                    /* адресообразующего элемента */
 c_NotActualStatusCode CONSTANT INTEGER :=0;	/* Значение кода актуальной записи */
 v_AOGUID     VARCHAR(36);	 /* ИД адресообразующего элемента */
 v_ParentGUID VARCHAR(36); /* Идентификатор родительского элемента */
 v_CurrStatus    INTEGER; /* Статус актуальности КЛАДР 4*/
 v_ActStatus     INTEGER; /* Статус актуальности */
                                    /* адресообразующего элемента ФИАС. */
 v_AOLevel      INTEGER; /*Уровень адресообразующего элемента  */
 v_ShortName  VARCHAR(10); /* Краткое наименование типа элемента */
 v_FormalName VARCHAR(120); /* Формализованное наименование элемента */
 v_Return_Error INTEGER;  /* Код возврата */
--***********************************************************************
--***********************************************************************
 BEGIN
 IF a_CurrStatus IS NOT NULL THEN
    SELECT INTO  v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,
                              v_ShortName, v_FormalName
                               ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,
                              ao.ShortName, ao.FormalName
                  FROM fias_AddressObjects ao
	WHERE ao.AOGUID=a_AOGUID AND ao.CurrStatus=a_CurrStatus;
 ELSE
    SELECT INTO v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,
                              v_ShortName, v_FormalName
                              ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,
                              ao.ShortName, ao.FormalName
                   FROM fias_AddressObjects ao
	WHERE ao.AOGUID=a_AOGUID AND ao.ActStatus=c_ActualStatusCode;
   IF NOT FOUND THEN
      SELECT INTO v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,
                               v_ShortName, v_FormalName
                                  ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,
                                ao.ShortName, ao.FormalName
              FROM fias_AddressObjects ao
              WHERE ao.AOGUID=a_AOGUID 
                       AND ao.ActStatus=c_NotActualStatusCode
                      AND ao.currstatus = (SELECT MAX(iao.currstatus) 
                                                                 FROM fias_AddressObjects iao 
                                                                 WHERE ao.aoguid = iao.aoguid);
    END IF;
 END IF;
 RETURN QUERY SELECT v_AOGUID,v_CurrStatus,v_ActStatus,v_AOLevel,
                                                v_ShortName,v_FormalName;
 WHILE  v_ParentGUID IS NOT NULL LOOP
     SELECT INTO v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,
                              v_ShortName, v_FormalName
                           ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,
                             ao.ShortName,ao.FormalName
         FROM fias_AddressObjects ao
         WHERE ao.AOGUID=v_ParentGUID AND ao.ActStatus=c_ActualStatusCode;
          IF NOT FOUND THEN   
             SELECT INTO v_AOGUID,v_ParentGUID,v_CurrStatus,v_ActStatus,v_AOLevel,
                                        v_ShortName,v_FormalName
                             ao.AOGUID,ao.ParentGUID,ao.CurrStatus,ao.ActStatus,ao.AOLevel,
                                        ao.ShortName, ao.FormalName
                 FROM fias_AddressObjects ao
                 WHERE ao.AOGUID=v_ParentGUID 
                               AND ao.ActStatus=c_NotActualStatusCode
                              AND ao.currstatus = (SELECT MAX(iao.currstatus) 
                                                               FROM fias_AddressObjects iao 
                                                               WHERE ao.aoguid = iao.aoguid);
          END IF;	
          RETURN QUERY SELECT v_AOGUID,v_CurrStatus,v_ActStatus,v_AOLevel,v_ShortName,
                                                   v_FormalName;
 END LOOP;
END;
  $BODY$
 LANGUAGE plpgsql;
COMMENT ON FUNCTION fstf_AddressObjects_AddressObjectTree(a_AOGUID VARCHAR(36), 
             a_CurrStatus INTEGER)
                            IS 'Возвращает дерево (список взаимосвязанных строк) 
                                 с характеристиками адресообразующего элемента';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM fstf_AddressObjects_AddressObjectTree('719b789d-2476-430a-89cd-3fedc643d821',51) 
            ORDER BY rtf_AOLevel;
SELECT * FROM fstf_AddressObjects_AddressObjectTree('719b789d-2476-430a-89cd-3fedc643d821')
            ORDER BY rtf_AOLevel;

Спасибо за внимание!

Автор: gladkovs

Источник