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

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



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

Пошук адресообразующего елемента
Функція fstf_AddressObjects_SearchByName призначена для пошуку адрсообразующих елементів ФІАС за їх назвами. При цьому пошук може осуществялться не лише за назвою та типом поточного елемента, але і назв і типів одного або двох його найближчих предків.
Розглянемо кілька прикладів. І для початку знайдемо всі адресообразующие елементи в назві яких зустрічається слово «Грибний».

Таблиця 8. Результат виконання функції fstf_AddressObjects_SearchByName('Гриб')

AOGUID AOLevel Повна адреса ShortName FormalName CurrStatus ActStatus
15faf08c-78b6-4b92-8a56-2ff70f2c4cab 6 Ачинський р-н, п Грибний п Грибний 0 1
f1772172-4dd1-449d-b2d2-ab96883d8871 7 Кежемский р-н, м Кодинск, пер Грибний пер Грибний 0 1
146cbcb5-4ad9-4578-916f-80ebd5c2b846 7 Омелянівська р-н, п Еліта, пер Грибний пер Грибний 0 1
a8ee8caf-fd5f-489c-92d9-f560e3f93c8b 7 Сухобузимский р-н, д Шестаково, пер Грибний пер Грибний 0 1
84f4baa8-1db2-471d-967d-20d489bca68e 7 Курагинский р-н, с Тюхтят, пер Грибний пер Грибний 0 1
1f2b7975-ce05-4627-bd13-d8d6228accd7 7 р Сорск, пер Грибний пер Грибний 0 1
В отриманому результаті немає нічого несподіваного, якщо не вважати наочного докази користі від функції побудови повного найменування.

Тепер змінимо запит. Знайдемо всі адресообразующие елементи, в назві найближчого предка яких зустрічається слово «Грибний».

Таблиця 9. Результат виконання функції fstf_AddressObjects_SearchByName
(NULL,NULL,'Гриб')


AOGUID AOLevel Повна адреса ShortName FormalName CurrStatus ActStatus Parent ShortName Parent FormalName
45064ade-a0a7-4258-88c8-baa57094aa2d 7 Ачинський р-н, п Грибний, вул Залізнична м.хмельницький Залізнична 0 1 п Грибний
ba4ec53c-50b7-4325-866a-81f97a38214c 7 Ачинський р-н, п Грибний, вул Західна м.хмельницький Західна 0 1 п Грибний
d6e9e0cc-e944-4deb-a09c-c545af691836 7 Ачинський р-н, п Грибний, вул Північна м.хмельницький Північна 0 1 п Грибний
5ae71e68-5477-446b-b878-0a9c9bf3bdcd 7 Ачинський р-н, п Грибний, вул Південна м.хмельницький Південна 0 1 п Грибний
Результат цього запиту дещо несподіваний, т. к. в назвах знайдених адресообразующих елементах немає слова «Грибний», але воно є в назві їх предка.

І, нарешті, розглянемо пошук по найменуванню прабатька, в якому має бути присутнім слово «Ачинський», а в найменуванні його онука повинен бути присутнім частина слова «Оз_рн». Тут використаний спеціальний символ — символ підкреслення «_». Цей символ вказує, що на його місці може знаходитися будь-який одиночний символ. Тут було застосовано для того, щоб знайти не тільки елементи з назвами «Озерний» або «Озерна», але і «Озерний» або «Озерна».

Таблиця 9. Результат виконання функції fstf_AddressObjects_SearchByName NULL,NULL,'Гриб')

AOGUID AOLevel Повна адреса ShortName FormalName CurrStatus ActStatus Parent ShortName Parent FormalName Grand Parent ShortName Grand Parent FormalName
715eef9d-48f6-4322-bcaa-9d239e89b7e4 7 Ачинський р-н, д Барабановка, пер Озерний пер Озерний 0 1 д Барабановка р-н Ачинський
05c7b2ad-e405-4c8b-9503-6761971e858e 7 Ачинський р-н, д Іллінка, вул Озерна м.хмельницький Озерна 0 1 д Іллінка р-н Ачинський
bdfcd515-1851-4caf-83ba-12ee79f9f6a7 7 Казачинский р-н, с Дудовка, вул Озерна м.хмельницький Озерна 0 1 Дудовка р-н Казачинский
В результаті запиту знайдено вулиці Озерна і провулок озерний у трьох населених пунктах Ачинського і Казачинське районів Красноярського краю. Текст функції наведено в розділі Програми «Створення функції fstf_AddressObjects_SearchByName».

Як це працює
Якщо значення присвоєно лише першим двох аргументів – назві (a_FormalName) і типу (a_ShortName) адресообразующего елемента, то пошук здійснюється по всіх записах таблиці fias_AddressObjects. Попередньо значення переданих параметрів перетворюються у верхній регістр, прогалини замінюються символом «%». Цим же символом оточується значення праворуч і ліворуч. Перетворені таким чином значення використовуються в пошуковому запиті як частина операції LIKE. Приклад такого запиту наведено на Рис. 4.



Рис. 4. Простий пошук адресообразующего елемента.

Умова вибору значення CurrStatus детально обговорювалася в першій частині статті в розділі «Родовід адресообразующего елемента» «Як це працює».

Для пошуку за назвою та типом батьківського адресообразующего елемента необхідно присвоїти значення хоча б одного з двох аргументів: третій (a_ParentFormalName), або четвертому (a_ParentShortName). У цьому випадку пошук здійснюється по всіх записах таблиці, отриманої сполукою (INNER JOIN) всіх записів fias_AddressObjects з записами батьківського адресообразующего елемента за ознакою pfa.AOGUID=cfa.ParentGUID.
Приклад такого запиту наведено на Рис. 5.



Рис. 5. Пошук за назвою та типом батьківського адресообразующего елемента.

Попередня обробка значень вхідних параметрів здійснюється за тими ж правилами, як і у випадку простого пошуку. Для пошуку за назвою та типом прабатьківського адресообразующего елемента необхідно присвоїти значення хоча б одного з двох аргументів: п'ятого (a_GrandParentFormalName) або шостій (a_GrandParentShortName). У цьому випадку пошук здійснюється по всіх записах таблиці, отриманої подвійним з'єднанням (INNER JOIN) всіх записів fias_AddressObjects з записами батьківського і прабатьківських адресообразующих елементів. Приклад такого запиту наведено на Рис. 6.



Рис. 6. Пошук за назвою та типом прабатьківського адресообразующего елемента.

Попередня обробка значень вхідних параметрів здійснюється за тими ж правилами, як і у випадку простого пошуку.

ДОДАТОК
Створення функції fstf_AddressObjects_SearchByName
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS fstf_AddressObjects_SearchByName(
a_FormalName VARCHAR(150), a_ShortName VARCHAR(20),
a_ParentFormalName VARCHAR(150),a_ParentShortName VARCHAR(20),
a_GrandParentFormalName VARCHAR(150),a_GrandParentShortName VARCHAR(20));
/************************************************************************/
/* Повертає результат пошуку у списку адресообразующих елементів ФІАС */
/* по їх назві і типу */
/***********************************************************************/ 
CREATE OR REPLACE FUNCTION fstf_AddressObjects_SearchByName(
a_FormalName VARCHAR(150), /* Оптимізоване для пошуку найменування */
/* адресообразующего елемента*/
a_ShortName VARCHAR(20) default NULL, /* Скорочене найменування типу */
/*адресообразующего елемента */
a_ParentFormalName VARCHAR(150) default NULL, /* Оптимізоване для пошуку */
/* найменування адресообразующего елемента*/
a_ParentShortName VARCHAR(20) default NULL, /* Скорочене найменування типу */
/*адресообразующего елемента */
a_GrandParentFormalName VARCHAR(150) default NULL, /*Оптимізоване для пошуку */
/* найменування адресообразующего елемента*/
a_GrandParentShortName VARCHAR(20) default NULL /* Скорочене найменування типу */
/* адресообразующего елемента */
)
RETURNS TABLE (rtf_AOGUID VARCHAR(36),
rtf_AOLevel INTEGER,
rtf_AddressObjectsFullName VARCHAR(1000),
rtf_ShortName VARCHAR(20),
rtf_FormalName VARCHAR(150),
rtf_CurrStatus INTEGER,
rtf_ParentShortName VARCHAR(20),
rtf_ParentFormalName VARCHAR(150),
rtf_GrandParentShortName VARCHAR(20),
rtf_GrandParentFormalName VARCHAR(150))
AS 
$BODY$
DECLARE
c_WildChar CONSTANT VARCHAR(2)='%';
c_BlankChar CONSTANT VARCHAR(2)=' ';
v_FormalNameTemplate VARCHAR(150); /* Шаблон для пошуку найменування */
/* адресообразующего елемента*/
v_ShortNameTemplate VARCHAR(20); /* Шаблон для пошуку типу */
/* адресообразующего елемента */
v_ParentFormalNameTemplate VARCHAR(150); /* Шаблон для пошуку найменування */
/* батьківського адресообразующего елемента*/
v_ParentShortNameTemplate VARCHAR(20); /* Шаблон для пошуку типу батьківського */
/* адресообразующего елемента */
v_GrandParentFormalNameTemplate VARCHAR(150); /* Шаблон для пошуку */
/* найменування батьківського адресообразующего елемента*/
v_GrandParentShortNameTemplate VARCHAR(20); /* Шаблон для пошуку типу */
/* батьківського адресообразующего елемента */
--************************************************************
--************************************************************
BEGIN
v_ShortNameTemplate:=UPPER(COALESCE(c_WildChar||
REPLACE(TRIM(a_ShortName),c_BlankChar,c_WildChar)||
c_WildChar,c_WildChar));
v_FormalNameTemplate:=UPPER(c_WildChar||
REPLACE(TRIM(a_FormalName),c_BlankChar,c_WildChar)||
c_WildChar);
IF a_ParentFormalName IS NULL AND a_ParentShortName IS NULL 
AND a_GrandParentFormalName IS NULL 
AND a_GrandParentShortName IS NULL THEN
RETURN QUERY
SELECT cfa.AOGUID,cfa.AOLevel,
fsfn_AddressObjects_TreeActualName(cfa.AOGUID),
cfa.ShortName,cfa.FORMALNAME,
cfa.currstatus,NULL::VARCHAR,NULL::VARCHAR,
NULL::VARCHAR,NULL::VARCHAR
FROM fias_AddressObjects cfa 
WHERE cfa.currstatus=
CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao
WHERE cfa.aoguid = iao.aoguid)
THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
WHERE cfa.aoguid = iao.aoguid)
ELSE 0 
END
AND UPPER(cfa.FORMALNAME) LIKE v_FormalNameTemplate
AND UPPER(cfa.ShortName) LIKE v_ShortNameTemplate
ORDER BY cfa.AOLevel,cfa.ShortName,cfa.FORMALNAME;
ELSIF a_ParentFormalName IS NOT NULL
AND a_GrandParentFormalName IS NULL 
AND a_GrandParentShortName IS NULL THEN
v_ParentShortNameTemplate:=UPPER(COALESCE(c_WildChar||
REPLACE(TRIM(a_ParentShortName),c_BlankChar,c_WildChar)||
c_WildChar,c_WildChar));
v_ParentFormalNameTemplate:=UPPER(c_WildChar||
REPLACE(TRIM(a_ParentFormalName),c_BlankChar,c_WildChar)||
c_WildChar);
v_FormalNameTemplate:=COALESCE(v_FormalNameTemplate,c_WildChar);
RETURN QUERY 
SELECT cfa.AOGUID,cfa.AOLevel,fsfn_AddressObjects_TreeActualName(cfa.AOGUID),
cfa.ShortName,cfa.FORMALNAME,cfa.currstatus,
pfa.ShortName,pfa.FORMALNAME,
NULL::VARCHAR,NULL::VARCHAR
FROM fias_AddressObjects pfa
INNER JOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID
WHERE cfa.currstatus=CASE WHEN 0 < 
ALL (SELECT iao.currstatus FROM fias_AddressObjects iao 
WHERE cfa.aoguid = iao.aoguid)
THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
WHERE cfa.aoguid = iao.aoguid)
ELSE 0 END
AND pfa.currstatus=CASE WHEN 0 < 
ALL(SELECT iao.currstatus FROM fias_AddressObjects iao
WHERE pfa.aoguid = iao.aoguid)
THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
WHERE pfa.aoguid = iao.aoguid)
ELSE 0 END
AND UPPER(pfa.FORMALNAME) LIKE v_ParentFormalNameTemplate 
AND UPPER(pfa.ShortName) LIKE v_ParentShortNameTemplate
AND UPPER(cfa.FORMALNAME) LIKE v_FormalNameTemplate 
AND UPPER(cfa.ShortName) LIKE v_ShortNameTemplate
ORDER BY pfa.ShortName,pfa.FORMALNAME,
cfa.AOLevel,cfa.ShortName,cfa.FORMALNAME; 
ELSE
v_GrandParentShortNameTemplate:=UPPER(COALESCE(c_WildChar||
REPLACE(TRIM(a_GrandParentShortName),c_BlankChar,c_WildChar)||
c_WildChar,c_WildChar));
v_GrandParentFormalNameTemplate:=UPPER(c_WildChar||
REPLACE(TRIM(a_GrandParentFormalName),c_BlankChar,c_WildChar)||
c_WildChar);
v_ParentShortNameTemplate:=COALESCE(UPPER(COALESCE(c_WildChar||
REPLACE(TRIM(a_ParentShortName),c_BlankChar,c_WildChar)||
c_WildChar,c_WildChar)),c_WildChar);
v_ParentFormalNameTemplate:=COALESCE(UPPER(c_WildChar||
REPLACE(TRIM(a_ParentFormalName),c_BlankChar,c_WildChar)||
c_WildChar),c_WildChar);
v_FormalNameTemplate:=COALESCE(v_FormalNameTemplate,c_WildChar);
RETURN QUERY 
SELECT cfa.AOGUID,cfa.AOLevel,fsfn_AddressObjects_TreeActualName(cfa.AOGUID),
cfa.ShortName,cfa.FORMALNAME,
cfa.currstatus,pfa.ShortName,pfa.FORMALNAME,
gpfa.ShortName,gpfa.FORMALNAME
FROM fias_AddressObjects gpfa 
INNER JOIN fias_AddressObjects pfa ON gpfa.AOGUID=pfa.ParentGUID
INNER JOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID
WHERE cfa.currstatus=CASE WHEN 0 < 
ALL(SELECT iao.currstatus FROM fias_AddressObjects iao
WHERE cfa.aoguid = iao.aoguid)
THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
WHERE cfa.aoguid = iao.aoguid)
ELSE 0 END
AND pfa.currstatus=CASE WHEN 0 < 
ALL(SELECT iao.currstatus FROM fias_AddressObjects iao 
WHERE pfa.aoguid = iao.aoguid)
THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
WHERE pfa.aoguid = iao.aoguid)
ELSE 0 END
AND gpfa.currstatus=CASE WHEN 0 < 
ALL(SELECT iao.currstatus FROM fias_AddressObjects iao
WHERE gpfa.aoguid = iao.aoguid)
THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
WHERE gpfa.aoguid = iao.aoguid)
ELSE 0 END
AND UPPER(gpfa.FORMALNAME) LIKE v_GrandParentFormalNameTemplate
AND UPPER(gpfa.ShortName) LIKE v_GrandParentShortNameTemplate
AND UPPER(pfa.FORMALNAME) LIKE v_ParentFormalNameTemplate 
AND UPPER(pfa.ShortName) LIKE v_ParentShortNameTemplate
AND UPPER(cfa.FORMALNAME) LIKE v_FormalNameTemplate 
AND UPPER(cfa.ShortName) LIKE v_ShortNameTemplate
ORDER BY gpfa.ShortName,gpfa.FORMALNAME,
pfa.ShortName,pfa.FORMALNAME,
cfa.AOLevel,cfa.ShortName,cfa.FORMALNAME; 
END IF;
END; $BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION fstf_AddressObjects_SearchByName(
a_FormalName VARCHAR(150),a_ShortName VARCHAR(20),
a_ParentFormalName VARCHAR(150),a_ParentShortName VARCHAR(20),
a_GrandParentFormalName VARCHAR(150),a_GrandParentShortName VARCHAR(20))
IS 'Повертає результат пошуку у списку адресообразующих елементів ФІАС за їх назвою та типом';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
--SELECT * FROM fstf_AddressObjects_SearchByName('БОРОДІН','м');
--SELECT * FROM fstf_AddressObjects_SearchByName('БОРОДІН');
--SELECT * FROM fstf_AddressObjects_SearchByName('два',NULL,'МИГНА');
--SELECT * FROM fstf_AddressObjects_SearchByName NULL,NULL,'МИГНА');
--SELECT * FROM fstf_AddressObjects_SearchByName('Зел_ная','УЛ',NULL);
SELECT * FROM fstf_AddressObjects_SearchByName('Зел_ная','УЛ','Куваршино',NULL,'Омелян');

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

0 коментарів

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