How to become an author
Search
Write a publication
Pull to refresh

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

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



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

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


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

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


Спасибо за внимание!
Tags:
Hubs:
Total votes 6: ↑6 and ↓0+6
Comments8
16
Karma
0
Rating
Сергей Гладков @gladkovs

Разработчик баз данных

Send message

Articles

Your account

Sections

Information

Services

Support
© 2006–2024, Habr