Address FIAS in the PostgreSQL environment. Part 2

This is the second part of the article that describes the experience of working with a list adresovana elements of the FIAS that are loaded into a database under PostgreSQL. The first part of the article is available here.



The full text of the article is composed of 4 parts. In the first half of this part of the article provides comments on the implementation of the function. The second source text of the function. Those readers who are only interested in the source code, we offer go directly to the App.

the

Full name adresource


The basic idea of the function fsfn_AddressObjects_TreeActualName to return United in one string the element name along with the names of all its ancestors. For example, suppose the search function pedigree element (fstf_AddressObjects_AddressObjecttree) returns the following list of values.

the table 5. the function execution result fstf_AddressObjects_AddressObjecttree('bfc1236d-b5d2-4734-a238-3b1e4830e963')
the the the the the
AOGUID CurrStatus ActStatus AOLevel ShortName FormalName ObjectGroup
db9c4f8b-b706-40e2-b2b4-d31b98dcd3d1 0 1 1 edge Krasnoyarsk Region
625497d3-22de-4390-b4b4-2febfbfc15ce 0 1 3 rn Balahtinskoj Territory
39da6405-b3e6-4baf-b332-d47b73b4d5fb 0 1 6 p Mighty Locality
bfc1236d-b5d2-4734-a238-3b1e4830e963 0 1 7 ul Street

Then fsfn_AddressObjects_TreeActualName('bfc1236d-b5d2-4734-a238-3b1e4830e963') should return:

"the Krasnoyarsk territory, Balakhtinsky R-n, n Mighty, ul New"

The function there is another optional parameter is the array of masks (a_MaskArray), which can be included in the result, all the element names, but only those that are needed.

The text function, see the Applications section "Create function fsfn_AddressObjects_TreeActualName".

the

How it works


The core of the implementation of the function is the call fstf_AddressObjects_AddressObjecttree (described in the first part of the article) and the loop return it to the records, the body of which is formed by the full name adresource element by chaining (concatenation) of all names in one line. This line in the end will return a function fsfn_AddressObjects_TreeActualName.

Next will be explained the details.

First, sometimes it is not necessary to the function were included the names of all ancestors of the current element. For example, within the Krasnoyarsk territory instead of "Krasnoyarsk Krai, Balakhtinsky R-n, n Mighty, ul New", often used shortened form of "Balahtinskoj R-n, n Mighty, ul New." And in the city of Krasnoyarsk is the "address Krasnoyarskiy Kray, Krasnoyarsk g, d gerbil, ul Sergeya Lazo" often used "d gerbil, ul Sergeya Lazo".

In order to be able to manage different forms of writing the full name of the element introduced adresource parameter is an array of masks (a_MaskArray), which contains a sequence of pointers (masks) on the group elements.

the table 6. The list of masks
the the the the the the the the the
Value note
{ST} Mask — street
{ZC} Mask — postcode
{DT} Mask — urban area
{LP} Mask — slave settlement
{LM} the Mask is the main town
{TP} Mask of the area of the Federal subject
{TM} the Mask — the subject of Federation (region)
{CY} Mask — country

Second, in order to realize the construction of full names in accordance with the array of masks created by the helper function fsfn_AddressObjects_ObjectGroup, which assigns each adresource item to a particular group.

the table 7. The value returned by the function fsfn_AddressObjects_ObjectGroup
the the the the the the the the the the the the
Value note
Country Sign of the group — Country
Region Sign of the group — Region
City Sign of the group — the Main town
Territory Sign of the group — district
Locality Sign of the group — locality subordinate main
MotorRoad Sign of the group — the road
RailWayObject Sign of the group — railroad
VillageCouncil Sign of the group SEL
Street Sign of the group — a street in the settlement
AddlTerritory Feature group — additional territory
PartAddlTerritory Sign of the group — part of the additional territory

The list of values returned by the function fsfn_AddressObjects_ObjectGroup given in table 5.

The purpose of this function is to collect in one place all the features (if you want, "spikes") of a group definition element. The detailed implementation of this function can be found in the Applications section, "Create function fsfn_AddressObjects_ObjectGroup".

The combination of the function values and fields AOLevel (level adresource element) along with a check for the presence of the mask group array masks allows you to determine whether the name of the current element included in the line result.

For example, a sign that the name of the main settlement to be included in the full name of the element, is the truth of the following expression:

the 
 v_ObjectGroup='City' AND '{LM}' <@ a_MaskArray AND v_AOLevel =4

the

the APPLICATION



the

Create function fsfn_AddressObjects_ObjectGroup


the 
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS fsfn_AddressObjects_ObjectGroup(a_AOGUID VARCHAR(36),a_CurrStatus INTEGER);
/*****************************************************************************/
/* Returns whether group adresource element by its ID */
/* fias_AddressObjects */
/*****************************************************************************/
CREATE OR REPLACE FUNCTION fsfn_AddressObjects_ObjectGroup(
a_AOGUID VARCHAR(36), /* Global unique identifier */
/* adresource element*/
a_CurrStatus INTEGER default NULL /* Status relevance KLADR 4: */
/* 0 is relevant */
/* 1-50 - historical, */
/* ie, the element was renamed */
/* this entry is given one */
/* of its former names */
/* 51 - perepodchinenie */
)
RETURNS VARCHAR(50) /* Group adresource element */
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); /* Type adresource element */ 
v_AddressObjectName VARCHAR(100); /* Name adresource element */ 
v_AOLevel INTEGER; /* Level adresource element*/ 
v_CurrStatus INTEGER; /* Current status adresource element*/
v_ObjectGroup VARCHAR(50); /* Group adresource element */
v_Return_Error Integer :=0; /* return Code */
--************************************************************************** 
--**************************************************************************
BEGIN
SELECT INTO v_CurrStatus COALESCE(a_CurrStatus,MIN(addrobj.currstatus)) 
Fias_AddressObjects FROM 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) <> 'G' THEN /* region */ 
v_ObjectGroup:=c_RegionGroupValue;
ELSIF v_AOLevel = 1 AND UPPER(v_ShortTypeName) = 'G' THEN /* city level */
/* as a region */ 
v_ObjectGroup:=c_CityGroupValue;
ELSIF v_AOLevel = 3 THEN /* district */
v_ObjectGroup:=c_TerritoryGroupValue;
ELSIF (v_AOLevel = 4 AND UPPER(v_ShortTypeName) NOT IN ('C/S','s/A','C/O','/MO')) 

v_ObjectGroup:=c_CityGroupValue;
ELSIF v_AOLevel IN (4,6) AND UPPER(v_ShortTypeName) IN ('C/S','s/A','C/O','/MO') 
AND UPPER(v_ShortTypeName) NOT LIKE ('W/D%') THEN /* SEL */ 
v_ObjectGroup:=c_VillageCouncilValue; 
ELSIF v_AOLevel = 6 AND UPPER(v_ShortTypeName) NOT IN ('C/S','s/A','C/O','/MO',
THE 'GARDEN','SNT','TER',
'ROAD',
'INDUSTRIAL area',
'DP','MD')
AND UPPER(v_ShortTypeName) NOT LIKE ('W/D%') THEN /* level of the village */
/* paragraph */ 
v_ObjectGroup:=c_LocalityGroupValue;
ELSIF UPPER(v_ShortTypeName) IN ('MOTORWAY') THEN /* level */
/* roads */ 
v_ObjectGroup:=c_MotorRoadValue;
ELSIF v_AOLevel IN (6,7) AND UPPER(v_ShortTypeName) LIKE ('W/D%') THEN 
/* level */
/* rail */ 
v_ObjectGroup:=c_RailWayObjectValue; 
ELSIF v_AOLevel = 7 AND UPPER(v_ShortTypeName) NOT LIKE ('W/D%') 
AND UPPER(v_ShortTypeName) NOT IN ('UCH-TO','GSK','PL-KA','SNT','TER') 
OR (v_AOLevel = 6 AND UPPER(v_ShortTypeName) IN ('MD') ) THEN 
/* street level */
v_ObjectGroup:=c_StreetGroupValue;
ELSIF v_AOLevel = 90 OR v_AOLevel = 6 AND UPPER(v_ShortTypeName) IN ('GARDEN',
'SNT','TER','INDUSTRIAL ZONE','DP')
OR v_AOLevel = 7 
AND UPPER(v_ShortTypeName) IN ('UCH-TO','GSK','PL-KA','SNT','TER') THEN
/* additional */
/* areas */
v_ObjectGroup:=c_AddlTerritoryValue;
ELSIF v_AOLevel = 91 THEN /* level subordinates additional territories */
/* objects */ 
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 'Returns the index of a group address object ID in the table 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');

the

Create function fsfn_AddressObjects_TreeActualName


the 
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS fsfn_AddressObjects_TreeActualName(a_AOGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) CASCADE;
/*****************************************************************************/
/* Returns a string with the full name adresource element */
/*****************************************************************************/ 
CREATE OR REPLACE FUNCTION fsfn_AddressObjects_TreeActualName(
a_AOGUID VARCHAR(36) DEFAULT NULL, /* Identifier */
/* adresource element */
a_MaskArray VARCHAR(2)[10] default '{TP,LM,LP,ST}' /* Array of masks */
/* managing the contents of the string */ 
/* home address*/
)
RETURNS VARCHAR(1000) AS
$BODY$
DECLARE
c_CountryGroupValue CONSTANT VARCHAR(50):='Country'; /* Sign of group - Country*/ 
c_RegionGroupValue CONSTANT VARCHAR(50):='Region'; /* Sign of group - Region*/ 
c_CityGroupValue CONSTANT VARCHAR(50):='City'; /* Indication of base */
/* locality*/ 
c_TerritoryGroupValue CONSTANT VARCHAR(50):='Territory';/* the Symptom groups - area */ 
c_LocalityGroupValue CONSTANT VARCHAR(50):='Locality';/* Sign of the group - */
/* locality */
/* slave core */ 
c_MotorRoadValue CONSTANT VARCHAR(50):='MotorRoad';/* Sign of the group - */
/* road */ 
c_RailWayObjectValue CONSTANT VARCHAR(50):='RailWayObject';/* Sign of the group - */
/* railroad */ 
c_VillageCouncilValue CONSTANT VARCHAR(50):='VillageCouncil';
/* A sign of the group - SEL */
c_StreetGroupValue CONSTANT VARCHAR(50):='Street';
/* A sign of the group - */
/* a street in the settlement */ 
c_AddlTerritoryValue CONSTANT VARCHAR(50):='AddlTerritory';/* Sign of the group - */
/* territory*/ 
c_PartAddlTerritoryValue CONSTANT VARCHAR(50):='PartAddlTerritory';/* Sign */
/* - part of the optional areas*/ 
c_StreetMask CONSTANT VARCHAR(2)[1] :='{ST}';/* Mask street */
c_PostIndexMask CONSTANT VARCHAR(2)[1] :='{ZC}';/* Mask postcode */
c_DistrictMask CONSTANT VARCHAR(2)[1] :='{DT}';/* Mask urban area*/

/* locality*/
c_MainLocalityMask CONSTANT VARCHAR(2)[1] :='{LM}';/* Mask main */
/* locality*/
c_PartTerritoryMask CONSTANT VARCHAR(2)[1] :='{TP}';/* Mask area */
/* subject of the Russian Federation*/
c_MainTerritoryMask CONSTANT VARCHAR(2)[1] :='{TM}';/* Mask the subject of the Federation */
/* (region)*/
c_CountryMask CONSTANT VARCHAR(2)[1] :='{CY}';/* Mask state*/
v_ShortTypeName VARCHAR(10); /* Type adresource element */ 
v_AddressObjectName VARCHAR(100); /* Name adresource element */
v_AOLevel INTEGER; /* Level adresource element*/ 
v_MinCurrStatus INTEGER; /* Minimum value of the current status */
/* adresource element*/ 
v_TreeAddressObjectName VARCHAR(1000); /* Full hierarchy name of the element*/ 
v_ObjectGroup VARCHAR(50); /* Group adresource element */
v_TreeLeverCount INTEGER; /* loop Counter*/
v_Return_Error_i Integer := 0; /* return Code*/
cursor_AddressObjectTree RefCURSOR; /* cursor through the hierarchy of address*/
v_Return_Error Integer :=0; /* return Code */
--****************************************************************************** 
--******************************************************************************
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('%REP%') THEN 'Republic' ||
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)))='BUT '||
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 'Returns a string with the full name adresource element';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT fsfn_AddressObjects_TreeActualName('bfc1236d-b5d2-4734-a238-3b1e4830e963','{TM,TP,LM,LP,ST}');

Thank you for your attention!
Article based on information from habrahabr.ru

Комментарии

Отправить комментарий

Популярные сообщения из этого блога

Looking for books as you want

Изображение
this last week or two, as curious users can watch Russian search service on books from Google . However, the General public before the official announcement from Google itself remains in the dark. Meanwhile, already there is something to see and something to tell. Besides, Google has not gone unnoticed in Yandex, which last week began offering users with Store cards and the output presented there books. What, in turn, did not fail to say publicly. Let's take a closer look at what is offered book lovers the giants of the search. Let's start with Google. The California company offers us their traditional approach. Typing in the familiar search bar the title of the book, we get... well, what we will get, greatly depends on many factors. Mostly while where a book has been published. If it's a popular publication in the United States (as an example from reference service — exposing the social ills of America's best-selling book " Fast Food Nation "), the...
Далее...

Automatically create Liquibase migrations for PostgreSQL

Изображение
Working with Liquibase always brings confidence in the fact that the database objects are in the same condition in which you want it. It is this confidence and makes system migrations so popular databases. Today I want to tell you how to make using liquibase a bit easier. If you write a migration by hand, and your target database is PostgreSQL, then read this article, I am sure, the time spent on reading the article will be repaid with interest. I hope you already know how to work with Liquibase, so only two words describe how the system works. In order to make changes in the database, you need to create a migration file (changeset) that you need to specify in the file change log (changelog), after which the migration can be successfully applied to the target database. Undeniable and obvious benefit of this approach is the ability to rollback the changes. In my opinion, liquibase, there is only one drawback — the automatic rollback of changes only works only if the mi...
Далее...

Vkontakte sync with address book for iPhone. How it was done

Изображение
the Background application on androidphone when you install the application Vkontakte is possible to use it as a provider of contacts to synchronize the contacts from Facebook with contacts in your address book. In this case stretches the avatar, date of birth, address, phone numbers, etc. When I after prolonged use HTC Wildfire S rated and are so used to this that he wanted to find a similar to the iPhone. Not found, so we took it and made his own — Synchronizer VKontakte :) It was back in November of last year. The application was done fairly quickly — it uses quite a few functions of Vkontakte API and functionality with the address book of the iPhone. But when we started to deal with the automatic matching of contacts, it was fun. Compare head-on by name, last name and phone number was easy to do, easy, and worked through it quickly. But found only 20 percent of coincidences of the contacts from what is provider in Android. Of course, only direct hits do not sui...
Далее...