Адреса ФИАС в среде PostgreSQL. Часть 3

в 14:22, , рубрики: postgresql, метки: , ,

Это третья часть статьи, в которой описана функция поиска в списке адресообразующих
элементов ФИАС, загруженных в базу данных под управлением PostgreSQL. Вот ссылки на первую и вторую части.

Адреса ФИАС в среде PostgreSQL. Часть 3 - 1


Полный текст статьи состоит состоит из 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.

Адреса ФИАС в среде PostgreSQL. Часть 3 - 2

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

Условие выбора значения CurrStatus подробно обсуждалось в первой части статьи в разделе «Родословная адресообразующего элемента» «Как это работает».

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

Адреса ФИАС в среде PostgreSQL. Часть 3 - 3

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

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

Адреса ФИАС в среде PostgreSQL. Часть 3 - 4

Рис. 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,'Емельян');

Спасибо за внимание!

Автор: gladkovs

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js