FIAS addresses in the PostgreSQL environment. Part 3
This is the third part of the article, which describes the search function in the list of address-forming
FIAS elements loaded into a database running PostgreSQL. Here are links to the first and second parts.
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 fstf_AddressObjects_SearchByName function is used to search for FIAS adre-forming elements by their names. In this case, the search can be carried out not only by the name and type of the current element, but also by the names and types of one or two of its closest ancestors.
Consider a few examples. And to begin with, we find all address-forming elements in the name of which the word “Mushroom” is found.
Table 8. Result of executing the fstf_AddressObjects_SearchByName ('Mushroom') function
')
There is nothing unexpected in the result obtained, except for a clear proof of the benefits of the function for constructing a full name.
Now we change the query. Find all address-forming elements, in the name of the nearest ancestor of which the word "Mushroom" is found.
Table 9. The result of the function fstf_AddressObjects_SearchByName
(NULL, NULL, 'Mushroom')
The result of this query is somewhat more unexpected, since in the names of the address-forming elements found there is no word “Mushroom”, but it is in the name of their ancestor.
And, finally, consider a search by the name of the progenitor, in which the word “Achinsky” should be present, and in the name of his grandson there should be a part of the word “Oz_rn”. A special character is used here - the underscore character “_”. This symbol indicates that any single character can be in its place. Here it is applied in order to find not only the elements with the names “Lake” or “Lake”, but also “Lake” or “Lake”.
Table 9. The result of the function fstf_AddressObjects_SearchByName (NULL, NULL, 'Mushroom')
As a result of the inquiry, Ozernaya streets and a lake lane were found in three settlements of Achinsky and Kazachinsky districts of the Krasnoyarsk Territory. The text of the function is given in the Appendix section “Creating the function fstf_AddressObjects_SearchByName” .
If values ​​are assigned only to the first two arguments - the name (a_FormalName) and the type (a_ShortName) of the address-forming element, then all records of the fias_AddressObjects table are searched. Preliminarily, the values ​​of the parameters passed are converted to uppercase, spaces are replaced with the “%” symbol. The same symbol surrounds the value on the right and left. The values ​​thus converted are used in the search query as part of the LIKE operation. An example of such a request is shown in Fig. four.
Fig. 4. Simple search for address-forming element.
The condition for choosing the CurrStatus value was discussed in detail in the first part of the article in the section “Ancestor of the Addressing Element” “How it works”.
To search by name and type of parent address-forming element, you must assign a value to at least one of two arguments: the third (a_ParentFormalName), or the fourth (a_ParentShortName). In this case, the search is performed in all records of the table received by the connection (INNER JOIN) of all records of fias_AddressObjects with records of the parent address-forming element based on the pfa.AOGUID = cfa.ParentGUID attribute.
An example of such a request is shown in Fig. five.
Fig. 5. Search by name and type of parent address-forming element.
Preliminary processing of input parameter values ​​is carried out according to the same rules as in the case of a simple search. To search by name and type of ancestral address-forming element, you must assign a value to at least one of two arguments: the fifth (a_GrandParentFormalName) or the sixth (a_GrandParentShortName). In this case, the search is performed in all records of the table obtained by double joining (INNER JOIN) of all records of fias_AddressObjects with records of the parent and ancestral address-forming elements. An example of such a request is shown in Fig. 6
Fig. 6. Search by name and type of ancestral address-forming element.
Preliminary processing of input parameter values ​​is carried out according to the same rules as in the case of a simple search.
Thanks for attention!
FIAS elements loaded into a database running PostgreSQL. Here are links to the first and second parts.
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 .
Search for address-forming element
The fstf_AddressObjects_SearchByName function is used to search for FIAS adre-forming elements by their names. In this case, the search can be carried out not only by the name and type of the current element, but also by the names and types of one or two of its closest ancestors.
Consider a few examples. And to begin with, we find all address-forming elements in the name of which the word “Mushroom” is found.
Table 8. Result of executing the fstf_AddressObjects_SearchByName ('Mushroom') function
')
| AOGUID | AOLevel | Full address | ShortName | FormalName | CurrStatus | Actstatus |
|---|---|---|---|---|---|---|
| 15faf08c-78b6-4b92-8a56-2ff70f2c4cab | 6 | Achinsky district, Mushroom | P | Mushroom | 0 | one |
| f1772172-4dd1-449d-b2d2-ab96883d8871 | 7 | Kezhemsky district, Mr. Kodinsk, per Mushroom | per | Mushroom | 0 | one |
| 146cbcb5-4ad9-4578-916f-80ebd5c2b846 | 7 | Yemelyanovsky district, Elite, Lane Mushroom | per | Mushroom | 0 | one |
| a8ee8caf-fd5f-489c-92d9-f560e3f93c8b | 7 | Sukhobuzimsky district, D. Shestakovo, per Mushroom | per | Mushroom | 0 | one |
| 84f4baa8-1db2-471d-967d-20d489bca68e | 7 | Kuraginsky district, with Tyhtyat, per Mushroom | per | Mushroom | 0 | one |
| 1f2b7975-ce05-4627-bd13-d8d6228accd7 | 7 | Mr. Sorsk, Lane Mushroom | per | Mushroom | 0 | one |
There is nothing unexpected in the result obtained, except for a clear proof of the benefits of the function for constructing a full name.
Now we change the query. Find all address-forming elements, in the name of the nearest ancestor of which the word "Mushroom" is found.
Table 9. The result of the function fstf_AddressObjects_SearchByName
(NULL, NULL, 'Mushroom')
| AOGUID | AOLevel | Full address | ShortName | FormalName | CurrStatus | Actstatus | Parent ShortName | Parent FormalName |
|---|---|---|---|---|---|---|---|---|
| 45064ade-a0a7-4258-88c8-baa57094aa2d | 7 | Achinsky district, Mushroom, ul Railway | street | Railway | 0 | one | P | Mushroom |
| ba4ec53c-50b7-4325-866a-81f97a38214c | 7 | Achinsky Rn, p Mushroom, West Street | street | Western | 0 | one | P | Mushroom |
| d6e9e0cc-e944-4deb-a09c-c545af691836 | 7 | Achinsky Rn, p Mushroom, North | street | North | 0 | one | P | Mushroom |
| 5ae71e68-5477-446b-b878-0a9c9bf3bdcd | 7 | Achinsky Rn, p Mushroom, South Street | street | South | 0 | one | P | Mushroom |
The result of this query is somewhat more unexpected, since in the names of the address-forming elements found there is no word “Mushroom”, but it is in the name of their ancestor.
And, finally, consider a search by the name of the progenitor, in which the word “Achinsky” should be present, and in the name of his grandson there should be a part of the word “Oz_rn”. A special character is used here - the underscore character “_”. This symbol indicates that any single character can be in its place. Here it is applied in order to find not only the elements with the names “Lake” or “Lake”, but also “Lake” or “Lake”.
Table 9. The result of the function fstf_AddressObjects_SearchByName (NULL, NULL, 'Mushroom')
| AOGUID | AOLevel | Full address | ShortName | FormalName | CurrStatus | Actstatus | Parent ShortName | Parent FormalName | Grand Parent ShortName | Grand parent FormalName |
|---|---|---|---|---|---|---|---|---|---|---|
| 715eef9d-48f6-4322-bcaa-9d239e89b7e4 | 7 | Achinsky district, d Barabanovka, lane Ozerny | per | Lake | 0 | one | d | Drumming | rn | Achinsky |
| 05c7b2ad-e405-4c8b-9503-6761971e858e | 7 | Achinsky district, Ilyinka St., Ozernaya St. | street | Ozernaya | 0 | one | d | Ilyinka | rn | Achinsky |
| bdfcd515-1851-4caf-83ba-12ee79f9f6a7 | 7 | Kazachinsky district, with Dudovka, ul Ozernaya | street | Ozernaya | 0 | one | with | Dudovka | rn | Kazachinsky |
As a result of the inquiry, Ozernaya streets and a lake lane were found in three settlements of Achinsky and Kazachinsky districts of the Krasnoyarsk Territory. The text of the function is given in the Appendix section “Creating the function fstf_AddressObjects_SearchByName” .
How it works
If values ​​are assigned only to the first two arguments - the name (a_FormalName) and the type (a_ShortName) of the address-forming element, then all records of the fias_AddressObjects table are searched. Preliminarily, the values ​​of the parameters passed are converted to uppercase, spaces are replaced with the “%” symbol. The same symbol surrounds the value on the right and left. The values ​​thus converted are used in the search query as part of the LIKE operation. An example of such a request is shown in Fig. four.
Fig. 4. Simple search for address-forming element.
The condition for choosing the CurrStatus value was discussed in detail in the first part of the article in the section “Ancestor of the Addressing Element” “How it works”.
To search by name and type of parent address-forming element, you must assign a value to at least one of two arguments: the third (a_ParentFormalName), or the fourth (a_ParentShortName). In this case, the search is performed in all records of the table received by the connection (INNER JOIN) of all records of fias_AddressObjects with records of the parent address-forming element based on the pfa.AOGUID = cfa.ParentGUID attribute.
An example of such a request is shown in Fig. five.
Fig. 5. Search by name and type of parent address-forming element.
Preliminary processing of input parameter values ​​is carried out according to the same rules as in the case of a simple search. To search by name and type of ancestral address-forming element, you must assign a value to at least one of two arguments: the fifth (a_GrandParentFormalName) or the sixth (a_GrandParentShortName). In this case, the search is performed in all records of the table obtained by double joining (INNER JOIN) of all records of fias_AddressObjects with records of the parent and ancestral address-forming elements. An example of such a request is shown in Fig. 6
Fig. 6. Search by name and type of ancestral address-forming element.
Preliminary processing of input parameter values ​​is carried out according to the same rules as in the case of a simple search.
ATTACHMENT
Creating the fstf_AddressObjects_SearchByName function
Function source code
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,''); Thanks for attention!
Source: https://habr.com/ru/post/316622/
All Articles