Адреса ФИАС в среде PostgreSQL. Часть 303.12.2016 12:49

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


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

c5b894825cce4aed9ce2935bbb009a4a.png

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

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

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

d96762c10c584586b34b23a12645e6d7.png

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

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

46442f7e0e8844f58bdd4f3abc097ce1.png

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

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

Комментарии (2)

  • 2 декабря 2016 в 20:48

    0

    ↑

    ↓

    А в продуктиве это используется? В каком сценарии?

    Делал когда-то подобное, только для КЛАДРа еще.

    • 3 декабря 2016 в 06:11

      0

      ↑

      ↓

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

© Habrahabr.ru