Адреса ФИАС в среде PostgreSQL. Часть 3
Это третья часть статьи, в которой описана функция поиска в списке адресообразующих
элементов ФИАС, загруженных в базу данных под управлением PostgreSQL. С первой и
второй частями статьи можно ознакоматься здесь: habrahabr.ru/post/316314,
habrahabr.ru/post/316380
Полный текст статьи состоит состоит из 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,'Емельян'); ссылка на оригинал статьи https://habrahabr.ru/post/316622/
Добавить комментарий