FIAS addresses in the PostgreSQL environment. Part 2
This is the second part of the article, which outlines the experience with the list of FIAS addressing elements loaded into the database running PostgreSQL. The first part of the article can be found here .
The full text of the article consists of 4 parts. The first half of this part of the article contains comments on the implementation of the function. In the second - the source code of the function. For those readers who are interested only in the source code, we suggest that you go straight to the Appendix.
The main idea of ​​the fsfn_AddressObjects_TreeActualName function is to return a single-named element name along with the names of all its ancestors. For example, let the pedigree element search functions (fstf_AddressObjects_AddressObjectTree) return the following list of values.
Table 5. The result of the function fstf_AddressObjects_AddressObjectTree ('bfc1236d-b5d2-4734-a238-3b1e4830e963')
Then fsfn_AddressObjects_TreeActualName ('bfc1236d-b5d2-4734-a238-3b1e4830e963') should return:
')
"Krasnoyarsk Territory, Balakhta district, p Moguchy, New Street"
The function has one more optional parameter array of masks (a_MaskArray), with which you can include in the result not all the names of the elements, but only those that are needed.
The text of the function is given in the Appendix section “Creating a function fsfn_AddressObjects_TreeActualName”.
The implementation of the function is based on the fstf_AddressObjects_AddressObjectTree call (described in the first part of the article) and the cycle of records returned to it, in the body of which the full name of the addressing element is formed by concatenating (concatenating) all names into one string. This string will eventually be returned by the fsfn_AddressObjects_TreeActualName function.
Further details will be explained.
First, sometimes it is not necessary that the result of the function necessarily include the names of all the ancestors of the current element. For example, within the Krasnoyarsk Territory, instead of “Krasnoyarsk Territory, Balakhta district, Moguchy, Novaya street”, they often use the shortened form “Balakhta district, Moguchy village, Novaya street”. And inside the city of Krasnoyarsk, instead of the address "Krasnoyarsk Territory, Krasnoyarsk, D. Peschanka, St. Sergey Lazo" more often use "D. Peschanka, St. Sergey Lazo".
In order to be able to manage different forms of writing the full name of the addressing element, the parameter is an array of masks (a_MaskArray) parameter that contains a sequence of pointers (masks) to groups of elements.
Table 6. List of function masks
Secondly, in order to implement the construction of the full name in accordance with the array of masks, an auxiliary function fsfn_AddressObjects_ObjectGroup has been created, which refers each address-forming element to a specific group.
Table 7. Values ​​returned by the fsfn_AddressObjects_ObjectGroup function
The list of values ​​returned by the fsfn_AddressObjects_ObjectGroup function is given in Table 5.
The purpose of creating this function is to collect in one place all the features (if you want, "crutches") of the definition of the element group. A detailed implementation of this function can be found in the Appendix section “Creating a function fsfn_AddressObjects_ObjectGroup”.
The combination of the values ​​of the function and the AOLevel field (the level of the address-forming element), along with checking for the presence of a mask group mask array, allows you to determine whether the name of the current element should be included in the result string.
For example, a sign that the name of the main locality should be included in the full name of the element is the truth of the following expression:
Thanks for attention!
The full text of the article consists of 4 parts. The first half of this part of the article contains comments on the implementation of the function. In the second - the source code of the function. For those readers who are interested only in the source code, we suggest that you go straight to the Appendix.
Full name addressing element
The main idea of ​​the fsfn_AddressObjects_TreeActualName function is to return a single-named element name along with the names of all its ancestors. For example, let the pedigree element search functions (fstf_AddressObjects_AddressObjectTree) return the following list of values.
Table 5. The result of the function fstf_AddressObjects_AddressObjectTree ('bfc1236d-b5d2-4734-a238-3b1e4830e963')
| AOGUID | CurrStatus | Actstatus | AOLevel | ShortName | FormalName | ObjectGroup |
|---|---|---|---|---|---|---|
| db9c4f8b-b706-40e2-b2b4-d31b98dcd3d1 | 0 | one | one | edge | Krasnoyarsk | Region |
| 625497d3-22de-4390-b4b4-2febfbfc15ce | 0 | one | 3 | rn | Balakhtinsky | Territory |
| 39da6405-b3e6-4baf-b332-d47b73b4d5fb | 0 | one | 6 | P | Mighty | Locality |
| bfc1236d-b5d2-4734-a238-3b1e4830e963 | 0 | one | 7 | street | New | Street |
Then fsfn_AddressObjects_TreeActualName ('bfc1236d-b5d2-4734-a238-3b1e4830e963') should return:
')
"Krasnoyarsk Territory, Balakhta district, p Moguchy, New Street"
The function has one more optional parameter array of masks (a_MaskArray), with which you can include in the result not all the names of the elements, but only those that are needed.
The text of the function is given in the Appendix section “Creating a function fsfn_AddressObjects_TreeActualName”.
How it works
The implementation of the function is based on the fstf_AddressObjects_AddressObjectTree call (described in the first part of the article) and the cycle of records returned to it, in the body of which the full name of the addressing element is formed by concatenating (concatenating) all names into one string. This string will eventually be returned by the fsfn_AddressObjects_TreeActualName function.
Further details will be explained.
First, sometimes it is not necessary that the result of the function necessarily include the names of all the ancestors of the current element. For example, within the Krasnoyarsk Territory, instead of “Krasnoyarsk Territory, Balakhta district, Moguchy, Novaya street”, they often use the shortened form “Balakhta district, Moguchy village, Novaya street”. And inside the city of Krasnoyarsk, instead of the address "Krasnoyarsk Territory, Krasnoyarsk, D. Peschanka, St. Sergey Lazo" more often use "D. Peschanka, St. Sergey Lazo".
In order to be able to manage different forms of writing the full name of the addressing element, the parameter is an array of masks (a_MaskArray) parameter that contains a sequence of pointers (masks) to groups of elements.
Table 6. List of function masks
| Value | Note |
|---|---|
| {ST} | Mask - street |
| {ZC} | Mask - Zip Code |
| {DT} | Maska - urban area |
| {LP} | Mask - subordinate settlement |
| {LM} | Mask - the main settlement |
| {TP} | Mask - region of the subject of the federation |
| {TM} | Mask - subject of the federation (region) |
| {CY} | Mask - Country |
Secondly, in order to implement the construction of the full name in accordance with the array of masks, an auxiliary function fsfn_AddressObjects_ObjectGroup has been created, which refers each address-forming element to a specific group.
Table 7. Values ​​returned by the fsfn_AddressObjects_ObjectGroup function
| Value | Note |
|---|---|
| Country | Group Sign - Country |
| Region | Group Tag - Region |
| City | Group Sign - Primary Settlement |
| Territory | Group Sign - District |
| Locality | Sign of the group - a settlement subordinate to the main |
| Motorroad | Sign of the group - the road |
| Railwayaybject | Sign of the group - the railway |
| Villagecouncil | Group Sign - Village Council |
| Street | Sign of the group - the street in the village |
| AddlTerritory | Sign of the group - additional territory |
| PartAddlTerritory | Group tag - part of additional territory |
The list of values ​​returned by the fsfn_AddressObjects_ObjectGroup function is given in Table 5.
The purpose of creating this function is to collect in one place all the features (if you want, "crutches") of the definition of the element group. A detailed implementation of this function can be found in the Appendix section “Creating a function fsfn_AddressObjects_ObjectGroup”.
The combination of the values ​​of the function and the AOLevel field (the level of the address-forming element), along with checking for the presence of a mask group mask array, allows you to determine whether the name of the current element should be included in the result string.
For example, a sign that the name of the main locality should be included in the full name of the element is the truth of the following expression:
v_ObjectGroup='City' AND '{LM}' <@ a_MaskArray AND v_AOLevel =4 ATTACHMENT
Creating the fsfn_AddressObjects_ObjectGroup Function
function source code
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'); Creating the fsfn_AddressObjects_TreeActualName function
function source code
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'); Thanks for attention!
Source: https://habr.com/ru/post/316380/
All Articles