gladkovs 29 ноя 2016 в 11:10

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

10 мин

11K

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

Полный текст статьи состоит состоит из 4 частей. В первой половине этой части статьи изложены комментарии к реализации функции. Во второй — исходный текст функции. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению.

Полное наименование адресообразующего элемента

Основная идея функции fsfn_AddressObjects_TreeActualName в том, чтобы возвратить соединенные в одну строку название элемента вместе с названиями всех его предков. Например, пусть функции поиска родословной элемента (fstf_AddressObjects_AddressObjectTree) возвращает следующий список значений.

Таблица 5. результат выполнения функции fstf_AddressObjects_AddressObjectTree('bfc1236d-b5d2-4734-a238-3b1e4830e963')

AOGUID	ActStatus	AOLevel	ShortName	FormalName	ObjectGroup
db9c4f8b-b706-40e2-b2b4-d31b98dcd3d1	1	1	край	Красноярский	Region
625497d3-22de-4390-b4b4-2febfbfc15ce	1	3	р-н	Балахтинский	Territory
39da6405-b3e6-4baf-b332-d47b73b4d5fb	1	6	п	Могучий	Locality
bfc1236d-b5d2-4734-a238-3b1e4830e963	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	Признак группы — Регион
City	Признак группы — Основной населенный пункт
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');

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

Теги:

Хабы:

PostgreSQL

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

Полное наименование адресообразующего элемента

Как это работает

ПРИЛОЖЕНИЕ

Создание функции fsfn_AddressObjects_ObjectGroup

Создание функции fsfn_AddressObjects_TreeActualName

Публикации

Истории

Ближайшие события