[Из песочницы] Адреса ФИАС в среде PostgreSQL. Часть 1
В этой статье изложен опыт работы со списком адресообразующих элементов ФИАС, загруженным в базу данных под управлением PostgreSQL. Для работы с адресообразующими элементами ФИАС создано несколько функций на языке PL/pgSQL.
Полный текст статьи состоит из 4 частей. В первой половине этой части статьи изложены комментарии к реализации функции. Во второй- исходный текст функции. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению.
Родословная адресообразующего элемента
Начнем с примера. Вызов функции fstf_AddressObjects_AddressObjectTree ('bfc1236d-b5d2–4734-a238–3b1e4830e963') приведет к получению следующего списка записей.
Таблица 1. Результат выполнения функции fstf_AddressObjects_AddressObjectTree
| AOGUID | CurrStatus | ActStatus | AOLevel | ShortName | FormalName |
|---|---|---|---|---|---|
| db9c4f8b-b706–40e2-b2b4-d31b98dcd3d1 | 0 | 1 | 1 | край | Красноярский |
| 625497d3–22de-4390-b4b4–2febfbfc15ce | 0 | 1 | 3 | р-н | Балахтинский |
| 39da6405-b3e6–4baf-b332-d47b73b4d5fb | 0 | 1 | 6 | п | Могучий |
| bfc1236d-b5d2–4734-a238–3b1e4830e963 | 0 | 1 | 7 | ул | Новая |
При внимательном рассмотрении можно заметить, что в качестве аргумента функции передан идентификатор элемента (AOGUID) «улица Новая», в результате получены четыре записи:
- запись с характеристиками самой улицы Новая;
- три родительских записи о поселке, районе и крае, которым принадлежит улица.
У функции есть еще одни необязательный параметр признак актуальности (CurrStatus), с помощью которого можно просматривать родословную не только актуального адресообразующего элемента, но и уже устаревшего.
Полный текст функции приведен в Приложении в подразделе Создание функции fstf_AddressObjects_AddressObjectTree
С самого начала
Если вы знаете как устроены таблицы ФИАС, то этот раздел можно пропустить.
Потребность в такой функции продиктована тем, что список адресообразующих элементов ФИАС (ADDROBJ) представляет собой древовидную структуру, которой каждый элемент ссылается на идентификатор (AOGUID) родительской записи значением поля ParentGUID.Т. е. просматривая записи исходного списка, обычно наблюдаешь длинный список улиц. Для того чтобы определить в каком населенном пункте находится улица нужно по значению ParentGUID найти записи с таким идентификатором элемента.
Рис. 1. Иерархия адресообразующих элементов ФИАС.
Это не описка и не оговорка. По значению ParentGUID может быть найдена не одна, а несколько записей. Из чего следует, что идентификатор адресообразующего элемента не является первичным ключом для таблицы, содержащей список ADDROBJ.
Дело в том, что список адресообразующих элементов, вместе с каждым элементом хранит историю его «переименования». Т.е. под одним идентификатором элемента хранится не только текущее название элемента, но его прежние названия. Т.е., отдельная запись списка ADDROBJ хранит данные об адресообразующем элементе, а также характеристики календарного периода, в течении которого было актуальным название элемента.
Таблица 2. История улицы «Красноярский край, р-н Таймырский Долгано-Ненецкий, г Дудинка, п Левинские Пески, ул Береговая»
| ИД записи AOID | ИД предыдущей записи PrevID | ИД следующей записи NextID | Признак по КЛАДР CurrStatus | Признак актуальности ActStatus | Дата начала периода StartDate | Дата окончания периода EndDate |
|---|---|---|---|---|---|---|
| fcf51361–5494–4edc-a6bc-d5c0d471c729 | 2a993f3b-5743–426c-8b7d-b5c7affe49cd | 0 | 1 | 25.11.2015 0:00 | 06.06.2079 0:00 | |
| 2a993f3b-5743–426c-8b7d-b5c7affe49cd | 9199c92b-18a5–431a-8b13-f54abe36e84f | fcf51361–5494–4edc-a6bc-d5c0d471c729 | 7 | 0 | 30.09.2015 0:00 | 25.11.2015 0:00 |
| 9199c92b-18a5–431a-8b13-f54abe36e84f | b06ff65e-aadb-42eb-9c70-a8548a40645c | 2a993f3b-5743–426c-8b7d-b5c7affe49cd | 6 | 0 | 28.09.2015 0:00 | 30.09.2015 0:00 |
| b06ff65e-aadb-42eb-9c70-a8548a40645c | a997aa7c-f2b1–4b7e-9471–5fc1f51a6752 | 9199c92b-18a5–431a-8b13-f54abe36e84f | 5 | 0 | 08.04.2013 0:00 | 28.09.2015 0:00 |
| a997aa7c-f2b1–4b7e-9471–5fc1f51a6752 | b27d8e44-ade1–4dd0-b4b5–5eaa7205ff0b | b06ff65e-aadb-42eb-9c70-a8548a40645c | 4 | 0 | 11.01.2013 0:00 | 08.04.2013 0:00 |
| b27d8e44-ade1–4dd0-b4b5–5eaa7205ff0b | 327b28cc-5171–43c6-bd88-a0a2172bbf71 | a997aa7c-f2b1–4b7e-9471–5fc1f51a6752 | 3 | 0 | 07.12.2012 0:00 | 11.01.2013 0:00 |
| 327b28cc-5171–43c6-bd88-a0a2172bbf71 | fb7b54db-7efb-4aaf-b4cb-2364b35b80b3 | b27d8e44-ade1–4dd0-b4b5–5eaa7205ff0b | 2 | 0 | 01.02.2012 0:00 | 07.12.2012 0:00 |
| 327b28cc-5171–43c6-bd88-a0a2172bbf71 | fb7b54db-7efb-4aaf-b4cb-2364b35b80b3 | b27d8e44-ade1–4dd0-b4b5–5eaa7205ff0b | 1 | 0 | 01.02.2012 0:00 | 07.12.2012 0:00 |
Порядок следования периодов актуальности названия адресообразующего элемента, может быть определен путем просмотра двух разнонаправленных списков. Для этого каждая запись о периоде актуальности элемента содержит два указателя на предыдущий (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. Результат выполнения функции над элементом с неуникальным признаком выбора записи
| AOGUID | CurrStatus | ActStatus | AOLevel | ShortName | FormalName |
|---|---|---|---|---|---|
| db9c4f8b-b706–40e2-b2b4-d31b98dcd3d1 | 0 | 1 | 1 | край | Красноярский |
| db9c4f8b-b706–40e2-b2b4-d31b98dcd3d1 | 0 | 1 | 1 | край | Красноярский |
| 3d4c8618–9e22–4838–8f89–80da6851da90 | 0 | 1 | 3 | р-н | Рыбинский |
| 719b789d-2476–430a-89cd-3fedc643d821 | 51 | 0 | 4 | г | Заозерный |
| 719b789d-2476–430a-89cd-3fedc643d821 | 51 | 1 | 4 | г | Заозерный |
Если 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.
| AOGUID | CurrStatus | ActStatus | AOLevel | ShortName | FormalName |
|---|---|---|---|---|---|
| 719b789d-2476–430a-89cd-3fedc643d821 | 51 | 0 | 4 | г | Заозерный |
| 719b789d-2476–430a-89cd-3fedc643d821 | 51 | 1 | 4 | г | Заозерный |
Поэтому, более правильным решением будет использование следующего условия:
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;
Спасибо за внимание!
Комментарии (1)
28 ноября 2016 в 16:41
0↑
↓
, а работает быстро?
