Address FIAS in the PostgreSQL environment. Part 3

This is the third part of the article that describes the search function in the list adresovana
elements of the FIAS that are loaded into a database under PostgreSQL. Here are links to first and second part.

/ >


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 Application.

the

Search adresource


Function fstf_AddressObjects_SearchByName designed to search hadrobregmus elements of FIAS for their names. The search can osushestvlyaetsya not only by name and type of the current element, but also the names and types of one or two of his immediate ancestors.
Consider a few examples. For a start, find all adresaroj elements the title of which the word "Mushroom".

the table 8. The function execution result fstf_AddressObjects_SearchByName('Mushroom')

the the the the the the the
AOGUID AOLevel Full address ShortName FormalName CurrStatus ActStatus
15faf08c-78b6-4b92-8a56-2ff70f2c4cab 6 Achinskiy R-n, p Fungal p Mushroom 0 1
f1772172-4dd1-449d-b2d2-ab96883d8871 7 kezhemskogo R-n, g Kodinsk, per Mushroom pen Mushroom 0 1
146cbcb5-4ad9-4578-916f-80ebd5c2b846 7 Emel R-n, p Elite, per Mushroom pen Mushroom 0 1
a8ee8caf-fd5f-489c-92d9-f560e3f93c8b 7 Sukhobuzimskiy R-n, d Shestakovo, per Mushroom pen Mushroom 0 1
84f4baa8-1db2-471d-967d-20d489bca68e 7 kuraginskiy R-n, with Thtat, per Mushroom pen Mushroom 0 1
1f2b7975-ce05-4627-bd13-d8d6228accd7 7 g Sorsk, per Mushroom pen Mushroom 0 1

The result is nothing unexpected, except for clear evidence of benefit from the function of creating the full name.

Now let's modify the query. Find all adresaroj elements in the title of the nearest ancestor include the word "Mushroom".

the table 9. The function execution result fstf_AddressObjects_SearchByName
(NULL,NULL,'Mushroom')

the the the the the
AOGUID AOLevel Full address ShortName FormalName CurrStatus ActStatus Parent ShortName Parent FormalName
45064ade-a0a7-4258-88c8-baa57094aa2d 7 Achinskiy R-n, p Fungal, ul Zheleznodorozhnaya ul Train 0 1 p Mushroom
ba4ec53c-50b7-4325-866a-81f97a38214c 7 Achinskiy R-n, p Fungal, ul Zapadnaya ul West 0 1 p Mushroom
d6e9e0cc-e944-4deb-a09c-c545af691836 7 Achinskiy R-n, p Fungal, ul North ul North 0 1 p Mushroom
5ae71e68-5477-446b-b878-0a9c9bf3bdcd 7 Achinskiy R-n, p Fungal, ul South ul South 0 1 p Mushroom

The result of this query is somewhat more unexpected, since the names found adresovany elements there is no word "Mushroom" but it is in the name of their ancestor.

And finally, consider search by name of the ancestor, which must have the word "Achinsk", and in the name of his grandson needs to be part of "OSM". Here we have used a special character is an underscore "_". This symbol indicates that on its place there can be any single character. Here it is applied to find not just the items with the names "lake" or "lake" and "lake" or "lake".

the table 9. The function execution result fstf_AddressObjects_SearchByName(NULL,NULL,'Mushroom')

the the the the
AOGUID AOLevel Full address ShortName FormalName CurrStatus ActStatus Parent ShortName Grand Parent ShortName Grand Parent FormalName
715eef9d-48f6-4322-bcaa-9d239e89b7e4 7 Achinskiy R-n, d Barabanovo, per lake pen lake 0 1 d Baranovka rn Achinsk
05c7b2ad-e405-4c8b-9503-6761971e858e 7 Achinskiy R-n, d'inka, ul Ozernaya ul lake 0 1 d main rn Achinsk
bdfcd515-1851-4caf-83ba-12ee79f9f6a7 7 kazachinskiy R-n, with Dudivka, ul Ozernaya ul lake 0 1 Dudivka rn Kazachinsko

The query found the street, lake and lane lake in the three settlements Achinsk and Kazachinsko districts of the Krasnoyarsk region. The text function, see Application "Create function fstf_AddressObjects_SearchByName".

the

How it works


If the value has only the first two arguments – name (a_FormalName) and type (a_ShortName) adresource element, the search is performed in all entries of the table fias_AddressObjects. Previously the values of the passed parameters are converted to uppercase, spaces replaced with " % " symbol. The same symbol surrounded the value to the right and to the left. Thus converted values are used in the search query as part of the operation LIKE. An example of such query is shown in Fig. 4.



the Fig. 4. A simple search adresource element.

Condition select CurrStatus was discussed in the first part of the article under "Genealogy adresource elements" "How it works".

To search by title and type of the parent adresource element must be set to at least one of two arguments: the third (a_ParentFormalName), or fourth (a_ParentShortName). In this case, the search is performed in all the table entries, received by the connection (INNER JOIN) of all records fias_AddressObjects records adresource parent element on the basis of pfa.AOGUID=cfa.ParentGUID.
An example of such query is shown in Fig. 5.



the Fig. 5. Search by name and type adresource parent element.

Pre-treatment values of the input parameters is carried out according to the same rules as in the case of a simple search. To search by title and type of grandparent adresource element must be set to at least one of two arguments: the fifth (a_GrandParentFormalName) or sixth (a_GrandParentShortName). In this case, the search is performed in all entries in the table received a double connection (INNER JOIN) of all records fias_AddressObjects records of parent and grandparent adresovana elements. An example of such query is shown in Fig. 6.



the Fig. 6. Search by title and type of grandparent adresource element.

Pre-treatment values of the input parameters is carried out according to the same rules as in the case of the simple search.

the

the APPLICATION


the

Create function fstf_AddressObjects_SearchByName


the 
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));
/************************************************************************/
/* Returns a search result in the list adresovana elements FIAS */
/* by their name and type */
/***********************************************************************/ 
CREATE OR REPLACE FUNCTION fstf_AddressObjects_SearchByName(
a_FormalName VARCHAR(150), /* Optimized for the search name */
/* adresource element*/
a_ShortName VARCHAR(20) default NULL, /* Short name */
/*adresource element */
a_ParentFormalName VARCHAR(150) default NULL, /* Optimized for search */
/* name adresource element*/
a_ParentShortName VARCHAR(20) default NULL, /* Short name */

a_GrandParentFormalName VARCHAR(150) default NULL, /*Optimized for search */
/* name adresource element*/
a_GrandParentShortName VARCHAR(20) default NULL, / * Short name */
/* adresource element */
)
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); /* Pattern to search for names */
/* adresource element*/
v_ShortNameTemplate VARCHAR(20); /* Pattern type */
/* adresource element */
v_ParentFormalNameTemplate VARCHAR(150); /* Pattern to search for names */
/* adresource parent element*/
v_ParentShortNameTemplate VARCHAR(20); /* Pattern to search for the type of parent */
/* adresource element */
v_GrandParentFormalNameTemplate VARCHAR(150); /* Pattern to search for */
/* names adresource parent element*/
v_GrandParentShortNameTemplate VARCHAR(20); /* Pattern type */
/* adresource parent element */
--************************************************************
--************************************************************
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 the 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 ON cfa and 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||

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 ON cfa and 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 'Returns a search result in the list adresovana elements of FIAS by their name and type';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
--SELECT * FROM fstf_AddressObjects_SearchByName('BORODIN','g');
--SELECT * FROM fstf_AddressObjects_SearchByName('BORODIN');
--SELECT * FROM fstf_AddressObjects_SearchByName('two',NULL,'MIGNA');
--SELECT * FROM fstf_AddressObjects_SearchByName(NULL,NULL,'MIGNA');
--SELECT * FROM fstf_AddressObjects_SearchByName('Selma','UL',NULL);
SELECT * FROM fstf_AddressObjects_SearchByName('Selma','UL','Quartino',NULL,'record');

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

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

Approval of WSUS updates: import, export, copy

Изображение
the Backstory Until recently, I worked as edikasyon a major Russian company that has many offices throughout the country. In my jurisdiction there were eleven sites, located in different cities of the Far East (this is important). In each of these offices had its own, not connected with other network infrastructure — your domain AD own subnet, etc. Once the leadership gave me the task to organize the automatic updating of the OS and programs from MS and allowed to expand on accountable sites WSUS . Problem After WSUS was deployed, binding policy, computers to WSUS groups are configured, the directory synchronization content server MS held, etc., the question arose: who and how will approve the update? Imagine 11 cities that are so "fast" and "stable", that rare moment when latency of ICMP replies are only 800 MS, are perceived as unheard of luck. And in each you test all updates before deploying. The access to the servers at the sites was only v...
Далее...

Kaspersky Security Center — the fight for automation

oddly enough, I found habré only one article on the subject — the sandbox much unfinished actually contains a piece of the slightly redesigned the product help. Yes, and Google query klakaut silent. I'm not going to tell you how to administer the hierarchy of Kaspersky Security Center (hereinafter KSC) from the command line — I do not yet need never. Just want to share some thoughts about automation tools to those who need it and disassemble one case with which I had to face. If you, %habrauser%, this topic will be interesting — welcome under kat. Historically, the anti-virus protection on the job, I prefer products of Kaspersky Lab (hereafter LK). Causes and other Holy war of personal opinions, I'll leave behind the scenes. Naturally, I would like to centrally deploy, protect, to protect and to enforce the law to draw beautiful graphics to integrate into existing monitoring systems and to do other work shifting the blame to a healthy server. And if you deplo...
Далее...

The Hilbert curve vs. Z-order

Изображение
Repeatedly heard the opinion that of all swept curves . it the Hilbert curve the most promising for spatial indexing. The explanation given is that it does not contain discontinuities and therefore in some sense “well-arranged”. Whether so it actually and what is the spatial indexing, we will understand under the cut. The concluding article of the series: the the Why doesn't the straightforward approach to spatial indexing swept curves the Presentation the 3D and obvious optimization the 8D and perspectives OLAP the 3D field as it looks the 3D field benchmarks the How about indexing non-point objects? The Hilbert curve has the remarkable property — the distance between two successive points is always equal to one (step current of detail, to be precise). Therefore, data is not much different Hilbert curve values are close to each other (the inverse, incidentally, is wrong — adjacent points may be very different in meaning). For this property it is us...
Далее...