Адреси ФІАС в середовищі PostgreSQL. Частина 2

Це друга частина статті, в якій викладено досвід роботи зі списком адресообразующих елементів ФІАС, завантаженим в базу даних під управлінням PostgreSQL. З першою частиною статті можна ознайомитися тут.



Повний текст статті складається складається з 4 частин. У першій половині цієї частини статті викладені коментарі до реалізації функції. У другій — вихідний текст функції. Тим із читачів, кого цікавлять тільки вихідні тексти, пропонуємо одразу перейти до Додатком.

Повне найменування адресообразующего елемента
Основна ідея функції fsfn_AddressObjects_TreeActualName в тому, щоб повернути сполучені в один рядок назва елемента разом з назвами всіх його предків. Наприклад, нехай функції пошуку родоводу елемента (fstf_AddressObjects_AddressObjecttree) повертає наступний список значень.

Таблиця 5. результат виконання функції fstf_AddressObjects_AddressObjecttree('bfc1236d-b5d2-4734-a238-3b1e4830e963')
AOGUID CurrStatus ActStatus AOLevel ShortName FormalName ObjectGroup
db9c4f8b-b706-40e2-b2b4-d31b98dcd3d1 0 1 1 край Красноярський Region
625497d3-22de-4390-b4b4-2febfbfc15ce 0 1 3 р-н Балахтинский Territory
39da6405-b3e6-4baf-b332-d47b73b4d5fb 0 1 6 п Могутній Locality
bfc1236d-b5d2-4734-a238-3b1e4830e963 0 1 7 м.хмельницький Нова Street
Тоді fsfn_AddressObjects_TreeActualName('bfc1236d-b5d2-4734-a238-3b1e4830e963') повинна повернути:

«Красноярський край, Балахтинский р-н, п Могутній, вул Нова»

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

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

Як це працює
Основу реалізації функції становить виклик fstf_AddressObjects_AddressObjecttree (описана в першій частині статті) та цикл з її обчислене їй записів, в тілі якої формується повне найменування адресообразующего елемента шляхом зчеплення (конкатенації) всіх найменувань в один рядок. Ця рядок в кінці кінців буде повернення функцією fsfn_AddressObjects_TreeActualName.

Далі будуть пояснюватися деталі.

По-перше, часом немає необхідності в тому, щоб результат функції обов'язково включав найменування всіх предків поточного елемента. Наприклад, у межах Красноярського краю замість «Красноярський край, Балахтинский р-н, п Могутній, вул Нова», частіше використовують укорочену форму «Балахтинский р-н, п Могутній, вул Нова». А всередині міста Красноярська замість адреси «Красноярський край, м Красноярськ, д Піщанка, вул Сергія Лазо» частіше використовують «д Піщанка, вул Сергія Лазо».

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

Таблиця 6. Список масок функції
Примітка
{ST} Маска — вулиця
{ZC} Маска — поштовий індекс
{DT} Маска — міський район
{LP} Маска — підлеглий населений пункт
{LM} Маска — основний населений пункт
{TP} — Маска району суб'єкта федерації
{TM} Маска — суб'єкт федерації (регіон)
{CY} Маска — країна
По-друге, для того щоб реалізувати побудову повного найменування згідно з масивом масок створена допоміжна функція fsfn_AddressObjects_ObjectGroup, яка відносить кожен адресообразующий елемент до певної групи.

Таблиця 7. Значення, що повертаються функцією fsfn_AddressObjects_ObjectGroup
Примітка
Country Ознака групи — Країна
Region Ознака групи — Регіон
Центр Ознака групи — Основний населений пункт
Territory Ознака групи — район
Locality Ознака групи — населений пункт підлеглий основним
MotorRoad Ознака групи — автомобільна дорога
RailWayObject Ознака групи — залізниця
VillageCouncil Ознака групи — сільрада
Street Ознака групи — вулиця в населеному пункті
AddlTerritory Ознака групи — додаткова територія
PartAddlTerritory Ознака групи — частина додаткової території
Список значень, що повертаються функцією fsfn_AddressObjects_ObjectGroup, наведено в Таблиця 5.

Мета створення цієї функції в тому, щоб зібрати в одному місці всі особливості (якщо хочете, то «милиці») визначення групи елемента. З детальною реалізацією цієї функції можна ознайомитися в розділі Програми «Створення функції fsfn_AddressObjects_ObjectGroup».

Комбінація значень функції і поля AOLevel (рівень адресообразующего елемента) одночасно з перевіркою на присутність маски групи масиві масок дозволяє визначати має назву поточного елемента включатися в рядок результату.

Наприклад, ознакою того, що назва основного населеного пункту повинно включатися у повне найменування елемента, є істинність наступного виразу:

v_ObjectGroup='City' AND '{LM}' <@ a_MaskArray AND v_AOLevel =4

ДОДАТОК

Створення функції fsfn_AddressObjects_ObjectGroup
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS fsfn_AddressObjects_ObjectGroup(a_AOGUID VARCHAR(36),a_CurrStatus INTEGER);
/*****************************************************************************/
/* Повертає ознаку групи адресообразующего елемента по його ідентифікатору */
/* fias_AddressObjects */
/*****************************************************************************/
CREATE OR REPLACE FUNCTION fsfn_AddressObjects_ObjectGroup(
a_AOGUID VARCHAR(36), /* Глобальний унікальний ідентифікатор */
/* адресообразующего елемента*/
a_CurrStatus INTEGER default NULL /* Статус актуальності КЛАДР 4: */
/* 0 - актуальний, */
/* 1-50 - історичний, */
/* тобто елемент був перейменований, */
/* в даній запису наведено одне */
/* з його колишніх найменувань, */
/* 51 - переподчиненный */
)
RETURNS VARCHAR(50) /* Група адресообразующего елемента */
AS
$BODY$
DECLARE
c_CountryGroupValue CONSTANT VARCHAR(50):='Country';
c_RegionGroupValue CONSTANT VARCHAR(50):='Region';
c_CityGroupValue CONSTANT VARCHAR(50):='City';
c_TerritoryGroupValue CONSTANT VARCHAR(50):='Territory';
c_LocalityGroupValue CONSTANT VARCHAR(50):='Locality';
c_MotorRoadValue CONSTANT VARCHAR(50):='MotorRoad';
c_RailWayObjectValue CONSTANT VARCHAR(50):='RailWayObject';
c_VillageCouncilValue CONSTANT VARCHAR(50):='VillageCouncil';
c_StreetGroupValue CONSTANT VARCHAR(50):='Street';
c_AddlTerritoryValue CONSTANT VARCHAR(50):='AddlTerritory';
c_PartAddlTerritoryValue CONSTANT VARCHAR(50):='PartAddlTerritory';
v_ShortTypeName VARCHAR(10); /* Тип адресообразующего елемента */ 
v_AddressObjectName VARCHAR(100); /* Назва адресообразующего елемента */ 
v_AOLevel INTEGER; /* Рівень адресообразующего елемента*/ 
v_CurrStatus INTEGER; /* Поточний статус адресообразующего елемента*/
v_ObjectGroup VARCHAR(50); /* Група адресообразующего елемента */
v_Return_Error Integer :=0; /* Код повернення */
--************************************************************************** 
--**************************************************************************
BEGIN
SELECT INTO v_CurrStatus COALESCE(a_CurrStatus,MIN(addrobj.currstatus)) 
FROM fias_AddressObjects addrobj WHERE addrobj.AOGUID=a_AOGUID;
SELECT INTO v_ShortTypeName,v_AddressObjectName,v_AOLevel
ShortName,FormalName,AOLevel 
FROM fias_AddressObjects addrobj 
WHERE addrobj.AOGUID=a_AOGUID AND addrobj.currstatus = v_CurrStatus 
LIMIT 1;
IF v_AOLevel = 1 AND UPPER(v_ShortTypeName) <> 'Г' THEN /* рівень регіону */ 
v_ObjectGroup:=c_RegionGroupValue;
ELSIF v_AOLevel = 1 AND UPPER(v_ShortTypeName) = '' THEN /* рівень міста */
/* як регіону */ 
v_ObjectGroup:=c_CityGroupValue;
ELSIF v_AOLevel = 3 THEN /* рівень району */
v_ObjectGroup:=c_TerritoryGroupValue;
ELSIF (v_AOLevel = 4 AND UPPER(v_ShortTypeName) NOT IN ('З','/А','/','/МО')) 
OR (v_AOLevel = 1 AND UPPER(v_ShortTypeName) <> 'Г') THEN /* рівень міста */ 
v_ObjectGroup:=c_CityGroupValue;
ELSIF v_AOLevel IN (4,6) AND UPPER(v_ShortTypeName) IN ('З','/А','/','/МО') 
AND UPPER(v_ShortTypeName) NOT LIKE ('Ж/Д%') THEN /* рівень сільради */ 
v_ObjectGroup:=c_VillageCouncilValue; 
ELSIF v_AOLevel = 6 AND UPPER(v_ShortTypeName) NOT IN ('З','/А','/','/МО',
'САД','СНТ','ТЕР',
'АВТОДОРОГА',
'ПРОМЗОНА',
'ДП','МКР')
AND UPPER(v_ShortTypeName) NOT LIKE ('Ж/Д%') THEN /* рівень населеного */
/* пункту */ 
v_ObjectGroup:=c_LocalityGroupValue;
ELSIF UPPER(v_ShortTypeName) IN ('АВТОДОРОГА') THEN /* рівень */
/* автомобільної дороги */ 
v_ObjectGroup:=c_MotorRoadValue;
ELSIF v_AOLevel IN (6,7) AND UPPER(v_ShortTypeName) LIKE ('Ж/Д%') THEN 
/* рівень елемент */
/* на залізниці */ 
v_ObjectGroup:=c_RailWayObjectValue; 
ELSIF v_AOLevel = 7 AND UPPER(v_ShortTypeName) NOT LIKE ('Ж/Д%') 
AND UPPER(v_ShortTypeName) NOT IN ('УЧ-ДО','ГСК','ПЛ-КА','СНТ','ТЕР') 
OR (v_AOLevel = 6 AND UPPER(v_ShortTypeName) IN ('МКР') ) THEN 
/* рівень вулиці */
v_ObjectGroup:=c_StreetGroupValue;
ELSIF v_AOLevel = 90 OR v_AOLevel = 6 AND UPPER(v_ShortTypeName) IN ('САД',
'СНТ','ТЕР','ПРОМЗОНА','ДП')
OR v_AOLevel = 7 
AND UPPER(v_ShortTypeName) IN ('УЧ-ДО','ГСК','ПЛ-КА','СНТ','ТЕР') THEN
/* рівень додаткових */
/* територій */
v_ObjectGroup:=c_AddlTerritoryValue;
ELSIF v_AOLevel = 91 THEN /* рівень підлеглих додатковим територіям */
/* об'єктів */ 
v_ObjectGroup:=c_PartAddlTerritoryValue;
END IF; 
RETURN v_ObjectGroup;
END;
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION fsfn_AddressObjects_ObjectGroup(a_AOGUID VARCHAR(36),
a_CurrStatus INTEGER)
IS 'Повертає ознаку групи адресного об'єкта по його ідентифікатору в таблиці fias_AddressObjects';

--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;

SELECT fsfn_AddressObjects_ObjectGroup('719b789d-2476-430a-89cd-3fedc643d821',51);
SELECT fsfn_AddressObjects_ObjectGroup('db9c4f8b-b706-40e2-b2b4-d31b98dcd3d1');
SELECT fsfn_AddressObjects_ObjectGroup('625497d3-22de-4390-b4b4-2febfbfc15ce');
SELECT fsfn_AddressObjects_ObjectGroup('39da6405-b3e6-4baf-b332-d47b73b4d5fb');
SELECT fsfn_AddressObjects_ObjectGroup('bfc1236d-b5d2-4734-a238-3b1e4830e963');

Створення функції fsfn_AddressObjects_TreeActualName
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS fsfn_AddressObjects_TreeActualName(a_AOGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) CASCADE;
/*****************************************************************************/
/* Повертає рядок з повною назвою адресообразующего елемента */
/*****************************************************************************/ 
CREATE OR REPLACE FUNCTION fsfn_AddressObjects_TreeActualName(
a_AOGUID VARCHAR(36) DEFAULT NULL, /* Идентификтор */
/* адресообразующего елемента */
a_MaskArray VARCHAR(2)[10] default '{TP,LM,LP,ST}' /* Масив масок, */
/* керуючий вмістом рядка */ 
/* з адресою будинку*/
)
RETURNS VARCHAR(1000) AS
$BODY$
DECLARE
c_CountryGroupValue CONSTANT VARCHAR(50):='Country'; /* Ознака групи - Країна*/ 
c_RegionGroupValue CONSTANT VARCHAR(50):='Region'; /* Ознака групи - Регіон*/ 
c_CityGroupValue CONSTANT VARCHAR(50):='City'; /* Ознака групи - Основний */
/* населений пункт*/ 
c_TerritoryGroupValue CONSTANT VARCHAR(50):='Territory';/* Ознака групи - район */ 
c_LocalityGroupValue CONSTANT VARCHAR(50):='Locality';/* Ознака групи - */
/* населений пункт, */
/* підлеглий основній */ 
c_MotorRoadValue CONSTANT VARCHAR(50):='MotorRoad';/* Ознака групи - */
/* автомобільна дорога */ 
c_RailWayObjectValue CONSTANT VARCHAR(50):='RailWayObject';/* Ознака групи - */
/* залізниця */ 
c_VillageCouncilValue CONSTANT VARCHAR(50):='VillageCouncil';
/* Ознака групи - сільрада */
c_StreetGroupValue CONSTANT VARCHAR(50):='Street';
/* Ознака групи - */
/* вулиця в населеному пункті */ 
c_AddlTerritoryValue CONSTANT VARCHAR(50):='AddlTerritory';/* Ознака групи - */
/* додаткова територія*/ 
c_PartAddlTerritoryValue CONSTANT VARCHAR(50):='PartAddlTerritory';/* Ознака групи */
/* - частина додаткової території*/ 
c_StreetMask CONSTANT VARCHAR(2)[1] :='{ST}';/* Маска вулиця */
c_PostIndexMask CONSTANT VARCHAR(2)[1] :='{ZC}';/* Маска поштовий індекс */
c_DistrictMask CONSTANT VARCHAR(2)[1] :='{DT}';/* Маска міський район*/
c_PartLocalityMask CONSTANT VARCHAR(2)[1] :='{LP}';/* Маска підлеглий */
/* населений пункт*/
c_MainLocalityMask CONSTANT VARCHAR(2)[1] :='{LM}';/* Маска основною */
/* населений пункт*/
c_PartTerritoryMask CONSTANT VARCHAR(2)[1] :='{TP}';/* Маска району */
/* суб'єкта федерації*/
c_MainTerritoryMask CONSTANT VARCHAR(2)[1] :='{TM}';/* Маска суб'єкт федерації */
/* (регіон)*/
c_CountryMask CONSTANT VARCHAR(2)[1] :='{CY}';/* Маска країна*/
v_ShortTypeName VARCHAR(10); /* Тип адресообразующего елемента */ 
v_AddressObjectName VARCHAR(100); /* Назва адресообразующего елемента */
v_AOLevel INTEGER; /* Рівень адресообразующего елемента*/ 
v_MinCurrStatus INTEGER; /* Мінімальне значення поточного статусу */
/* адресообразующего елемента*/ 
v_TreeAddressObjectName VARCHAR(1000); /* Повне в ієрархії назва елемента*/ 
v_ObjectGroup VARCHAR(50); /* Група адресообразующего елемента */
v_TreeLeverCount INTEGER; /* Лічильник циклу*/
v_Return_Error_i Integer := 0; /* Код повернення*/
cursor_AddressObjectTree RefCURSOR; /* вказівник по ієрархії адреси*/
v_Return_Error Integer :=0; /* Код повернення */
--****************************************************************************** 
--******************************************************************************
BEGIN
SELECT INTO v_MinCurrStatus MIN(addrobj.currstatus) 
FROM fias_AddressObjects addrobj
WHERE aoguid=a_AOGUID;
OPEN cursor_AddressObjectTree FOR SELECT rtf_ShortTypeName,
REPLACE(rtf_AddressObjectName,' ',' '),
rtf_AOLevel,fsfn_AddressObjects_ObjectGroup(rtf_AOGUID )
FROM fstf_AddressObjects_AddressObjecttree(a_AOGUID) 
ORDER BY rtf_AOLevel;
v_TreeLeverCount:=0;
v_TreeAddressObjectName:=";
FETCH FIRST FROM cursor_AddressObjectTree INTO v_ShortTypeName,v_AddressObjectName,
v_AOLevel,v_ObjectGroup;
WHILE FOUND
LOOP
v_TreeLeverCount:=v_TreeLeverCount+1; 
IF v_ObjectGroup=c_CountryGroupValue AND c_CountryMask <@ a_MaskArray 
AND v_AOLevel =0 THEN
v_TreeAddressObjectName:=v_TreeAddressObjectName||
CASE WHEN v_TreeAddressObjectName=" THEN" 
ELSE ', ' END ||
v_AddressObjectName||' '||v_ShortTypeName;
ELSIF v_ObjectGroup=c_RegionGroupValue 
AND c_MainTerritoryMask <@ a_MaskArray
AND v_AOLevel <=2 THEN
v_TreeAddressObjectName:=v_TreeAddressObjectName||
CASE WHEN v_TreeAddressObjectName=" THEN "
ELSE ', ' END ||
CASE WHEN UPPER(v_ShortTypeName) LIKE 
UPPER('%Респ%') THEN 'Республіка' ||
v_AddressObjectName ELSE v_AddressObjectName||
''||v_ShortTypeName END;
ELSIF v_ObjectGroup=c_TerritoryGroupValue 
AND c_PartTerritoryMask <@ a_MaskArray 
AND v_AOLevel =3 THEN
v_TreeAddressObjectName:=v_TreeAddressObjectName||
CASE WHEN v_TreeAddressObjectName=" THEN "
ELSE ', ' END ||
v_AddressObjectName||' '||v_ShortTypeName;
ELSIF v_ObjectGroup=c_CityGroupValue
AND c_MainLocalityMask <@ a_MaskArray AND v_AOLevel =4 THEN
v_TreeAddressObjectName:=v_TreeAddressObjectName||
CASE WHEN v_TreeAddressObjectName=" THEN "
ELSE ', ' END ||
CASE WHEN UPPER(LEFT(v_AddressObjectName,6+
LENGTH(v_ShortTypeName)))='ЗАТЕ '||
UPPER(TRIM(v_ShortTypeName))||'.' THEN
v_AddressObjectName
ELSE v_ShortTypeName ||' '|| v_AddressObjectName END;
ELSIF v_ObjectGroup=c_LocalityGroupValue 
AND c_DistrictMask <@ a_MaskArray AND v_AOLevel =5 THEN
v_TreeAddressObjectName:=v_TreeAddressObjectName||
CASE WHEN v_TreeAddressObjectName=" THEN" 
ELSE ', ' END ||
v_AddressObjectName||' '||v_ShortTypeName ;
ELSIF v_ObjectGroup=c_LocalityGroupValue 
AND c_PartLocalityMask <@ a_MaskArray 
AND v_AOLevel =6 THEN
v_TreeAddressObjectName:=v_TreeAddressObjectName||
CASE WHEN v_TreeAddressObjectName=" THEN "
ELSE ', ' END ||
v_ShortTypeName ||' '|| v_AddressObjectName;
ELSIF v_ObjectGroup=c_StreetGroupValue 
AND c_StreetMask <@ a_MaskArray 
AND v_AOLevel =7 THEN
v_TreeAddressObjectName:=v_TreeAddressObjectName||
CASE WHEN v_TreeAddressObjectName=" THEN" 
ELSE ', ' END ||
v_ShortTypeName ||' '|| v_AddressObjectName;
END IF;
FETCH NEXT FROM cursor_AddressObjectTree INTO v_ShortTypeName,
v_AddressObjectName,
v_AOLevel,v_ObjectGroup;
END LOOP;
CLOSE cursor_AddressObjectTree;
RETURN v_TreeAddressObjectName;
END;
$BODY$
LANGUAGE plpgsql ;
COMMENT ON FUNCTION fsfn_AddressObjects_TreeActualName(a_AOGUID VARCHAR(36),
a_MaskArray VARCHAR(2)[10])
IS 'Повертає рядок з повною назвою адресообразующего елемента';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT fsfn_AddressObjects_TreeActualName('bfc1236d-b5d2-4734-a238-3b1e4830e963','{TM,TP,LM,LP,ST}');
SELECT fsfn_AddressObjects_TreeActualName('bfc1236d-b5d2-4734-a238-3b1e4830e963');

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

0 коментарів

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