Адреси ФІАС в середовищі 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 Дата закінчення періоду a list
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 і a list. При цьому дати початку першого періоду і закінчення останнього мають умовні значення. За дату початку першого періоду приймається «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 SELECT QUERY 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 SELECT QUERY 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;

Спасибі за увагу!
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.