Geekly Articles each Day
📜 ⬆️ ⬇️

FIAS houses in PostgreSQL environment

The previous article describing FIAS addresses and functions for working with them in the PostgreSQL environment has aroused interest among a small number of readers.

Therefore, it makes sense to describe similar functions in the PL / pgSQL language for working with the list of FIAS houses loaded into the database running PostgreSQL.


The first half of the article contains comments on the implementation of functions. In the second, the source texts of the functions, as well as scripts for creating a table with FIAS house entries, as well as loading data into this table from a CSV file. For those readers who are interested only in the source code, we suggest that you go straight to the Appendix.

This article is closely related to the materials of the series of articles “FIAS addresses in the PostgreSQL environment” ( beginning , continuation 1 , continuation 2 , ending ).

Pedigree home


Let's start with an example.

Calling the function f stf_Houses_AddressObjectTree ('42301ab8-9ead-4f8e-8281-e64f2769a254') will result in the following list of entries.


Table 1. The result of the function.
')


On closer examination, you can see that the element identifier ( HOUSEGUID ) “d. 1, Bldg. 2, p. 26 ", as a result, six entries were received:



The function has one more optional parameter - the record expiration date ( EndDate ), with which you can view the pedigree of not only the current home record, but also already obsolete records.

The full text of the function is given in the Appendix in the subsection Creating a function fstf_Houses_AddressObjectTree .


From the very beginning


If you know how the table of FIAS houses is arranged, then this section can be skipped.
FIAS houses ( HOUSES ) are a child list for the list of FIAS addressing elements ( ADDROBJ ). Each entry in the list of houses refers to the FIAS addressing element by the value of the AOGUID field. In order to determine on which street and in which settlement the house is located, by the value of the AOGUID of the HOUSES entry, find the corresponding entry with the same list identifier ADDROBJ .


With all the external simplicity of the mechanism of interaction of the list of houses with a list of address-forming elements in their interaction, the features that complicate the implementation of functions on HOUSES .


First, each entry in the list of houses by AOGUID identifies a group of address-forming elements, one of which is relevant.


Secondly, in the list of FIAS there are several records with the same set of characteristics of a house number: house number, building number, building number.


Thirdly, the record about the house is not always inherited from the record about the street of the settlement.


But first things first.

For further consideration of the storage of information about houses in FIAS, it suffices to limit to 4 tables (DBF files):





ADDROBJ was described in detail in the previous publication “FIAS addresses in PostgreSQL” , therefore here its features will be mentioned exactly as much as is necessary to describe the characteristics of houses.


Table 2. House history “Krasnoyarsk Territory, Taymyr district, Dolgan-Nenetsky, Dudinka city, ul. Dudinskaya, 1 "




As can be seen from the table, in contrast to the address-forming objects, the records of the history of the house have no special signs of relevance. The current record is the one with the highest end of the period, which is greater than the current one. While current records of houses are marked with the date "06/06/2079." All other records about the house are considered historical, and the start and end dates characterize the period of relevance of each record.


The list of FIAS houses does not contain pointers to the previous and next home entries. Therefore, the order of the records from the actual home to the history of the house are determined by decreasing the end date and after it the start date of the period, respectively EndDate and StartDate .



SELECT * FROM fias_Houses h WHERE h.HOUSEGUID='2fd64ecd-4b4b-4fc8-bd15-4a4a2f310b21' ORDER BY h.ENDDATE DESC,h.STARTDATE DESC;

Attentive reader, looking at Fig. 1, I probably asked myself the question: why are reference guides for the signs of structure and ownership mentioned? More than 10 such references are used in FIAS, so why are these two highlighted?


The answer will surprise many - from the point of view of the “FIAS logic”, the address of the house is not fully identified by the street address, house numbers, building and buildings. The term “FIAS logic” was used in the answer of the FTS officer to my question why there are over 250 pair addresses of houses in the list of houses in the Krasnoyarsk Territory. In the same answer it was said that the uniqueness of the record is provided by the values ​​of AOGUID, HOUSENUM, BUILDNUM, STRUCNUM, STRSTATUS, ESTSTATUS.





In other words, to find an object, it is not enough to know the place, street, house number. You must also know:





Here is a sample from the general list of FIAS houses with duplicate addresses.
The fact that different objects have the same address is not surprising. The building and the land under it; house, garage, sauna at one owner. They all have the same address. But FIAS is an address register, i.e. address list. Therefore, it is natural to expect that addresses, and not buildings, structures, and structures will be unique in it.


Those. The list of FIAS houses from the list of addresses of houses began to develop in the direction of the list of ground structures. And FIAS users need to take this into account.


Anyone can check the availability of houses with duplicate addresses by executing a SELECT statement like a follower. In this case, the fsfn_Houses_TreeActualName function can be omitted, since it is used only to reduce the number of columns of the result. It is not necessary to use reference books fias_StructureStatus (analog STRSTAT) and fias_EstateStatus (analog ESTSTAT), since The marked effect can be traced to the codes of signs of structure and ownership.




operator source code
 SELECT fsfn_Houses_TreeActualName(h.AOGUID,h.HOUSEGUID),h.HOUSEGUID,str.StructureStatusName,est.EstateStatusName FROM fias_Houses h INNER JOIN (SELECT AOGUID,HOUSENUM,BUILDNUM,STRUCNUM,COUNT(*) FROM fias_Houses h WHERE EndDate =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') GROUP BY AOGUID,HOUSENUM,BUILDNUM,STRUCNUM HAVING COUNT(*)>1) hg ON h.AOGUID=hg.AOGUID AND h.HOUSENUM=hg.HOUSENUM AND COALESCE(h.BUILDNUM,'')=COALESCE(hg.BUILDNUM,'') AND COALESCE(h.STRUCNUM,'')=COALESCE(hg.STRUCNUM,'') LEFT OUTER JOIN fias_StructureStatus str ON h.STRSTATUS=str.StructureStatusID LEFT OUTER JOIN fias_EstateStatus est ON h.ESTSTATUS=est.EstateStatusID WHERE h.EndDate =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') ORDER BY h.AOGUID,h.HOUSENUM,h.BUILDNUM,h.STRUCNUM,h.STRSTATUS,h.ESTSTATUS;


And finally, another feature of the list of FIAS houses. Each entry about the house of this list contains a link to the address-forming element, the list of which is a hierarchy of such elements. At each level of the hierarchy are address-forming elements belonging to different types. So the root element is the region (Krasnoyarsk Territory in our case), at the next level an autonomous region, district or city of regional subordination. And so on. (For details, see “FIAS addresses in PostgreSQL”).


Formally, a home entry allows you to refer to a hierarchy element of any level. Fortunately, there are no houses that refer to the region or region among the data of the Krasnoyarsk Territory. Nevertheless, not all houses refer to the street of the settlement:




Fig. 2

Reproduction of addresses of the house by owner (FIAS vs map)


It describes a problem that leads to an ambiguous interpretation of the family tree at home. (Igor Leonidovich Timoschenkov, GIS Specialist, Aigeo LLC, Krasnoyarsk, drew my attention to this problem).


The above shows how several entries contain the same home address. What can be explained by the desire of the tax inspectorate to keep not only a record about a private house, but also about the surrounding buildings: garage, shed, etc. But there are reverse examples, when several fias_Houses table entries with different values ​​of the rooms of this house correspond to the same building (house).





Look at this drawing. On the left is a screen shot with a map of the village where the houses for two owners are located. These are ordinary one-story houses with two entrances. On the right is one family, and on the left another. They can still be represented as the houses of two apartments.


And now look at the table on the right. In it, almost every house for two owners corresponds to 3 entries. Those. The FIAS house table shows both the address of a separate house (“d. 1”) and the addresses of parts of the house (“d. 1/1”, “d. 1/2”) belonging to the same owner.


How it works


The fstf_Houses_AddressObjectTree function has two versions: with four or with two parameters. In the version of the function with two parameters, the house identifier ( HouseGUID ) and the record expiration date (EndDate ) are transmitted. The version with four parameters additionally requires the identifier of the address-forming element ( AOGUID ) and the current status ( CurrStatus ).




operator source code
 SELECT INTO v_AOGUID,v_CurrStatus h.AOGUID,CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid) THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid) ELSE 0 END FROM fias_Houses h INNER JOIN fias_AddressObjects ao ON h.AOGUID=ao.AOGUID WHERE h.HOUSEGUID=a_HOUSEGUID AND h.ENDDATE= COALESCE(a_ENDDATE, TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS')) ORDER BY h.ENDDATE DESC;


The function with a smaller number of parameters calculates the values ​​of the missing parameters and calls the function with a large number of parameters. For this, the pre-identifier of the address-forming element is simply extracted from the corresponding field of the table of houses (f ias_Houses ). And the current status value ( CurrStatus ) is calculated according to the following rules:



A function with a large number of parameters first calls the fstf_AddressObjects_AddressObjectTree function, which returns the parent address-forming elements for the house. More information about the fstf_AddressObjects_AddressObjectTree function can be found in the Pedigree section of the addressing element of the document “FIAS addresses in PostgreSQL”

.

Then the records about the address-forming elements are supplemented with the records of the house number, the housing, the building (see Table 1), which are created for each non-empty field about the house number, the housing, and the building.


In order for all displayed records to have the same structure and not without a certain amount of fakeness, the values ​​of the level code ( AOLevel ), current status ( CurrStatus ) and current status ( ActStatus ) are artificially created in the function body.


The level code of the house (building, building) is always assigned a value of 8, see the reference book “Levels of Addressable Objects” from the document Information on the composition of FIAS information ).


The relevance status is set to 1 if the end date of the entry ( EndDate ) is 06.06.2079 , and 0 otherwise.


With the values ​​of the CurrStatus field , the situation is more complicated. Using its values, two tasks are solved simultaneously: the identifier of each version of the record about the address-forming element is set and the sign of the relevance of the record is assigned. Therefore, the last actual entry for the element contains the value 0 in this field, and all historical records are numbered in the order of appearance - “1” is the earliest entry, following it in time - “2”, etc. The procedure for assigning values ​​to the CurrStatus field is discussed in more detail in the publication FIAS Addresses in PostgreSQL .




Spoiler header
 SELECT h.AOGUID, h.HOUSEGUID, h.HOUSENUM, h.BUILDNUM, h.STRUCNUM, h.ENDDATE, CASE WHEN COALESCE(h.ENDDATE, TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS')) =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') THEN 0 ELSE RANK() OVER (PARTITION BY h.AOGUID, h.HOUSEGUID ORDER BY h.ENDDATE ASC) END AS HouseCurrStatus, CASE WHEN COALESCE (h.ENDDATE, TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS')) =TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') THEN 1 ELSE 0 END AS HouseActStatus FROM fias_Houses h INNER JOIN fias_AddressObjects ao ON h.AOGUID=ao.AOGUID WHERE h.AOGUID=a_AOGUID AND h.HOUSEGUID=a_HOUSEGUID AND h.ENDDATE= COALESCE(a_ENDDATE, TO_TIMESTAMP('2079-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS')) ORDER BY h.ENDDATE DESC;


Full home address


The main idea of ​​the fsfn_Houses_TreeActualName function is to return the house number connected in one line along with the names of all its ancestors - addressing elements.


For example, let the home pedigree function (fstf_Houses_AddressObjectTree) return the following list of values.


Table 4. Result of executing the fstf_Houses_AddressObjectTree function ('0c0d670f-097c-4e1d-bcac-9e9b22fb1b99')



Then fsfn_Houses_TreeActualName ('0c0d670f-097c-4e1d-bcac-9e9b22fb1b99') should return: “ Mr. Krasnoyarsk, ul. Sergey Lazo, d. 34A, building. 6, p. 17 .


The function fsfn_Houses_TreeActualName can be simplified as the aggregate function STRING_AGG on the result of the function that returns the family tree at home.


The function in question has one more optional parameter - an array of masks ( a_MaskArray ), with which you can include in the result not all names of elements, but only those that are needed.


Table 5. List of function masks.



text version of the table
ValueNote
{HS}Mask - house number
{BY}Mask - hull number
{Bg}Mask - building number
{ST}Mask - street
{ZC}Mask - Zip Code
{DT}Maska - urban area
{LP}Mask - subordinate settlement
{LM}Mask - the main settlement
{TP}Mask - the region of the subject of the federation
{TM}Mask - subject of the federation (region)
{CY}Mask - Country


See also the section “ Full name of the address-forming element” of the publication “FIAS addresses in PostgreSQL ”.
The text of the function is given in the Appendix section “ Creating a function fsfn_Houses_TreeActualName ”.

Search House FIAS


The fstf_Houses_SearchByName function is designed to search for addresses of FIAS houses by their numbers and names of address-forming elements. 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 first we find all the houses with the number "220".


Table 6. The result of the function fstf_Houses_SearchByName ('220')



In contrast to the function of searching for address-forming elements ( fstf_AddressObjects_SearchByName ), the result of this function does not contain the effect of “floating” through the levels of address-forming elements. The first parameter of the function always contains the search pattern of the house number, the second the hull number, the third building number.


Now we change the query. Find all the houses of address-forming elements, the number of which contains the number "1", and the word "Krasnoyarsk" is found in the names.


Table 7. Result of executing the function fstf_Houses_SearchByName ('1', NULL, NULL, 'Krasnoyarsk')



The assignment of the remaining parameters is exactly the same as the assignment of the parameters of the function of the search for address-forming elements (fstf_AddressObjects_SearchByName).
The text of the function is given in the Appendix section “ Creating a function fstf_Houses_SearchByName ”

.

How it works


The implementation of fstf_Houses_SearchByName is in many ways similar to the implementation of the function of searching for addressing elements (fstf_AddressObjects_SearchByName) . The main difference is that the search is carried out in two, related tables fias_Houses and fias_AddressObjects .


The function has 9 arguments. The first three of them are house numbers ( a_HouseNum ), buildings (a_BuildNum ), and buildings ( a_StrucNum ). The remaining 6 ( a_FormalName , a_ShortName , a_ParentFormalName , a_ParentShortName , a_GrandParentFormalName , a_GrandParentShortName ) completely coincide with the parameters of the function.

If you specify only the value of the “house number” parameter, the function will return all addresses in the house number, which are indicated by the specified character. If the house number is NULL or an empty string (""), then the addresses of all houses whose address elements are specified by a set of other parameters will be returned.



Epilogue


This section contains recommendations on how to load the list of FIAS houses into the fias_Houses table.


Loading data into the table of houses is approximately the same as loading data into the table of address-forming elements . Only the source file will be HOUSE99.DBF , not ADDROB99.DBF . Here 99 is the number of the region (Republic, region, region). For example, for the Krasnoyarsk region, the source is the HOUSE24.DBF file.


First, the next archive with the update is downloaded from the “ Updates ” page of FIAS. The HOUSE99.DBF file is extracted from it .

.

Then the HOUSE99.DBF file is converted to CSV format and it is already converted by the COPY operator into the fias_Houses_Temp temporary table.


And finally, the temporary data is used to update the main table, i.e. nonexistent in fias_Houses are added, and existing ones are replaced.
An example of the update script for the house table is given in the section “ Downloading FIAS house updates to the fias_Houses table ”.


application


Create fstf_Houses_AddressObjectTree function


Comments on the source code of the function can be found here .

function code
 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS fstf_Houses_AddressObjectTree(a_AOGUID VARCHAR(36), a_HOUSEGUID VARCHAR(36),a_CurrStatus INTEGER,a_ENDDATE TIMESTAMP); /******************************************************************/ /*   (  )   */ /*      */ /******************************************************************/ CREATE OR REPLACE FUNCTION fstf_Houses_AddressObjectTree( a_AOGUID VARCHAR(36), /*    */ /*  */ a_HOUSEGUID VARCHAR(36),/*     */ a_CurrStatus INTEGER default 0,/*    4: */ /* 0 - , */ /* 1-50 - , ..  */ /*  , */ /*    */ /*    */ /*  , */ /* 51 -  */ a_ENDDATE TIMESTAMP default '2079-06-06' /*     */ ) RETURNS TABLE (rtf_GUID VARCHAR(36), rtf_CurrStatus INTEGER,rtf_ActStatus INTEGER, rtf_AOLevel INTEGER,rtf_ShortTypeName VARCHAR(10),rtf_AddressObjectName VARCHAR(100)) AS $BODY$ DECLARE c_HouseAOLevel CONSTANT INTEGER:=8; c_HouseShortTypeName CONSTANT VARCHAR(10):='.'; c_BuildShortTypeName CONSTANT VARCHAR(10):='.'; c_StructShortTypeName CONSTANT VARCHAR(10):='.'; c_StatusActual CONSTANT INTEGER:=1; /*    */ c_StatusNotActual CONSTANT INTEGER:=0; /*     */ c_MAXENDDATE CONSTANT TIMESTAMP:=to_timestamp('2079-06-06 00:00:00', 'YYYY-MM-DD'); v_HouseActStatus INTEGER; /*    */ v_HouseCurrStatus INTEGER; /*     */ v_ENDDATE TIMESTAMP; /*    */ v_HOUSEGUID VARCHAR(36); /*     */ v_HOUSENUM VARCHAR(10); /*   */ v_BUILDNUM VARCHAR(10); /*   */ v_STRUCNUM VARCHAR(10); /*   */ v_Return_Error Integer :=0; /*   */ --************************************************************ --************************************************************ BEGIN RETURN QUERY SELECT * FROM fstf_AddressObjects_AddressObjectTree (a_AOGUID,a_CurrStatus); IF a_ENDDATE IS NULL THEN SELECT INTO v_ENDDATE MAX(ENDDATE) FROM fias_Houses WHERE AOGUID=a_AOGUID AND HOUSEGUID=a_HOUSEGUID; ELSE v_ENDDATE:=a_ENDDATE; END IF; SELECT INTO v_HOUSENUM,v_BUILDNUM,v_STRUCNUM, v_ENDDATE,v_HouseCurrStatus h.HOUSENUM,h.BUILDNUM,h.STRUCNUM, h.ENDDATE,ah.HouseCurrStatus FROM fias_Houses h INNER JOIN (SELECT AOGUID,HOUSEGUID,ENDDATE, RANK() OVER (PARTITION BY AOGUID, HOUSEGUID ORDER BY ENDDATE ASC) AS HouseCurrStatus FROM fias_Houses insh WHERE insh.AOGUID=a_AOGUID AND insh.HOUSEGUID=a_HOUSEGUID) as ah ON h.AOGUID=ah.AOGUID AND h.HOUSEGUID=ah.HOUSEGUID AND h.ENDDATE=ah.ENDDATE WHERE h.ENDDATE=v_ENDDATE; v_HouseActStatus:=CASE WHEN COALESCE(v_ENDDATE,c_MAXENDDATE)= c_MAXENDDATE THEN c_StatusActual ELSE c_StatusNotActual END; v_HouseCurrStatus:=CASE WHEN COALESCE(v_ENDDATE,c_MAXENDDATE)= c_MAXENDDATE THEN 0 ELSE v_HouseCurrStatus END; IF v_HOUSENUM IS NOT NULL THEN RETURN QUERY SELECT a_HOUSEGUID,v_HouseCurrStatus,v_HouseActStatus, c_HouseAOLevel,c_HouseShortTypeName,v_HOUSENUM; END IF; IF v_BUILDNUM IS NOT NULL THEN RETURN QUERY SELECT a_HOUSEGUID,v_HouseCurrStatus,v_HouseActStatus, c_HouseAOLevel,c_BuildShortTypeName,v_BUILDNUM; END IF; IF v_STRUCNUM IS NOT NULL THEN RETURN QUERY SELECT a_HOUSEGUID,v_HouseCurrStatus,v_HouseActStatus, c_HouseAOLevel,c_StructShortTypeName,v_STRUCNUM; END IF; END; $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION fstf_Houses_AddressObjectTree(a_AOGUID VARCHAR(36), a_HOUSEGUID VARCHAR(36),a_CurrStatus INTEGER,a_ENDDATE TIMESTAMP) IS '  (  )       '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS fstf_Houses_AddressObjectTree(a_HOUSEGUID VARCHAR(36),a_ENDDATE TIMESTAMP); /******************************************************************/ /*   (  )   */ /*      */ /******************************************************************/ CREATE OR REPLACE FUNCTION fstf_Houses_AddressObjectTree( a_HOUSEGUID VARCHAR(36),/*     */ a_ENDDATE TIMESTAMP default '2079-06-06'/*     */ ) RETURNS TABLE (rtf_GUID VARCHAR(36), rtf_CurrStatus INTEGER,rtf_ActStatus INTEGER, rtf_AOLevel INTEGER,rtf_ShortTypeName VARCHAR(10),rtf_AddressObjectName VARCHAR(100)) AS $BODY$ DECLARE c_MaxEndDate CONSTANT TIMESTAMP:=TO_TIMESTAMP('2079-06-06','YYYY-MM-DD'); c_ActualStatusCode CONSTANT INTEGER :=1; /*      */ c_NotActualStatusCode CONSTANT INTEGER :=0; /*     */ v_AOGUID VARCHAR(36); /*   */ /*   */ v_CurrStatus INTEGER; /*    4: */ /* 0 - , */ /* 1-50 - , */ /* ..   , */ /*     */ /*     , */ /* 51 - */ v_Return_Error Integer :=0; /*   */ --******************************************************************* --******************************************************************* BEGIN SELECT INTO v_AOGUID,v_CurrStatus h.AOGUID, CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid) THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE ao.aoguid = iao.aoguid) ELSE 0 END FROM fias_Houses h INNER JOIN fias_AddressObjects ao ON h.AOGUID=ao.AOGUID WHERE h.HOUSEGUID=a_HOUSEGUID AND h.ENDDATE=COALESCE(a_ENDDATE,c_MaxEndDate) ORDER BY h.ENDDATE DESC; RETURN QUERY SELECT * FROM fstf_Houses_AddressObjectTree( v_AOGUID,a_HOUSEGUID, v_CurrStatus,a_ENDDATE); END; $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION fstf_Houses_AddressObjectTree(a_HOUSEGUID VARCHAR(36),a_ENDDATE TIMESTAMP) IS '  (  )       '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM fstf_Houses_AddressObjectTree('8b40b028-68eb-4315-a256-ea300c604688','42301ab8-9ead-4f8e-8281-e64f2769a254') ORDER BY rtf_AOLevel; SELECT * FROM fstf_Houses_AddressObjectTree('42301ab8-9ead-4f8e-8281-e64f2769a254') ORDER BY rtf_AOLevel;



Create fsfn_Houses_TreeActualName function


Comments on the source code of the function can be found here .
function code
 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS fsfn_Houses_TreeActualName(a_AOGUID VARCHAR(36),a_HOUSEGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) CASCADE; /*****************************************************************/ /*           */ /*****************************************************************/ CREATE OR REPLACE FUNCTION fsfn_Houses_TreeActualName( a_AOGUID VARCHAR(36), /*    */ a_HOUSEGUID VARCHAR(36), /*     */ a_MaskArray VARCHAR(2)[10] default '{TP,LM,LP,ST,HS,BY,BG}' /*  ,   */ /*    */ ) RETURNS VARCHAR(1000) AS $BODY$ DECLARE c_HouseMaskArray CONSTANT VARCHAR(2)[3]:='{HS,BY,BG}'; /*    */ c_HouseNoMask CONSTANT VARCHAR(2)[1] :='{HS}'; c_BodyNoMask CONSTANT VARCHAR(2)[1] :='{BY}';/*  */ c_BuildingNoMask CONSTANT VARCHAR(2)[1] :='{BG}';/*  */ c_HouseShortTypeName CONSTANT VARCHAR(10):='.'; c_BuildShortTypeName CONSTANT VARCHAR(10):='.'; c_StructShortTypeName CONSTANT VARCHAR(10):='.'; v_ENDDATE TIMESTAMP; /*    */ v_HOUSENUM VARCHAR(10); /*   */ v_BUILDNUM VARCHAR(10); /*   */ v_STRUCNUM VARCHAR(10); /*   */ v_TreeAddressObjectName VARCHAR(1000); /*     */ v_Return_Error Integer :=0; /*   */ --******************************************************* --******************************************************* BEGIN v_TreeAddressObjectName:=fsfn_AddressObjects_TreeActualName (a_AOGUID,a_MaskArray); SELECT INTO v_ENDDATE MAX(ENDDATE) FROM fias_Houses WHERE AOGUID=a_AOGUID AND HOUSEGUID=a_HOUSEGUID; SELECT INTO v_HOUSENUM,v_BUILDNUM,v_STRUCNUM HOUSENUM, BUILDNUM,STRUCNUM FROM fias_Houses WHERE AOGUID=a_AOGUID AND HOUSEGUID=a_HOUSEGUID AND ENDDATE=v_ENDDATE; IF c_HouseNoMask <@ a_MaskArray AND COALESCE(TRIM(v_HOUSENUM),'')<>'' THEN v_TreeAddressObjectName:=v_TreeAddressObjectName|| CASE WHEN v_TreeAddressObjectName='' THEN '' ELSE ', ' ||c_HouseShortTypeName||' '||v_HOUSENUM END; END IF; IF c_BodyNoMask <@ a_MaskArray AND COALESCE(TRIM(v_BUILDNUM),'')<>'' THEN v_TreeAddressObjectName:=v_TreeAddressObjectName|| CASE WHEN v_TreeAddressObjectName='' THEN '' ELSE ', ' || c_BuildShortTypeName||' '||v_BUILDNUM END; END IF; IF c_BuildingNoMask <@ a_MaskArray AND COALESCE(TRIM(v_STRUCNUM),'')<>'' THEN v_TreeAddressObjectName:=v_TreeAddressObjectName|| CASE WHEN v_TreeAddressObjectName='' THEN '' ELSE ', ' || c_StructShortTypeName||' '||v_STRUCNUM END; END IF; RETURN v_TreeAddressObjectName; END; $BODY$ LANGUAGE plpgsql ; COMMENT ON FUNCTION fsfn_Houses_TreeActualName(a_AOGUID VARCHAR(36),a_HOUSEGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) IS '         '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS fsfn_Houses_TreeActualName(a_HOUSEGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) CASCADE; /*****************************************************************/ /*           */ /*****************************************************************/ CREATE OR REPLACE FUNCTION fsfn_Houses_TreeActualName( a_HOUSEGUID VARCHAR(36), /*     */ a_MaskArray VARCHAR(2)[10] default '{TP,LM,LP,ST,HS,BY,BG}' /*  ,   */ /*    */ ) RETURNS VARCHAR(1000) AS $BODY$ DECLARE c_MaxEndDate CONSTANT TIMESTAMP:=TO_TIMESTAMP('2079-06-06','YYYY-MM-DD'); v_AOGUID VARCHAR(36); /*    */ v_TreeAddressObjectName VARCHAR(1000); /*     */ v_Return_Error Integer :=0; /*   */ --********************************************************** --********************************************************** BEGIN SELECT INTO v_AOGUID h.AOGUID FROM fias_Houses h INNER JOIN fias_AddressObjects ao ON h.AOGUID=ao.AOGUID WHERE h.HOUSEGUID=a_HOUSEGUID AND h.ENDDATE=c_MaxEndDate ORDER BY h.ENDDATE DESC; v_TreeAddressObjectName:=fsfn_Houses_TreeActualName (v_AOGUID,a_HOUSEGUID,a_MaskArray); RETURN v_TreeAddressObjectName; END; $BODY$ LANGUAGE plpgsql ; COMMENT ON FUNCTION fsfn_Houses_TreeActualName(a_HOUSEGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) IS '         '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT fsfn_Houses_TreeActualName('8b40b028-68eb-4315-a256-ea300c604688','42301ab8-9ead-4f8e-8281-e64f2769a254'); SELECT fsfn_Houses_TreeActualName('42301ab8-9ead-4f8e-8281-e64f2769a254');



Creating fstf_Houses_SearchByName function


Comments on the source code of the function can be found here .
function code
 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS fstf_Houses_SearchByName(a_HouseNum VARCHAR(20),a_BuildNum VARCHAR(10),a_StrucNum VARCHAR(10),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_Houses_SearchByName( a_HouseNum VARCHAR(20), /*   */ a_BuildNum VARCHAR(10) default NULL,/*   */ a_StrucNum VARCHAR(10) default NULL, /*   */ a_FormalName VARCHAR(150) default NULL, /*  */ /*     */ 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_HOUSEGUID VARCHAR(36),rtf_AOLevel INTEGER,rtf_HousesFullName VARCHAR(1000),rtf_HouseNum VARCHAR(20),rtf_BuildNum VARCHAR(10),rtf_StrucNum VARCHAR(10),rtf_EndDate TIMESTAMP,rtf_ShortName VARCHAR(20),rtf_FormalName VARCHAR(150), rtf_CurrStatus INTEGER,rtf_ActStatus 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_HouseNumTemplate VARCHAR(150); /*     */ v_BuildNumTemplate VARCHAR(150); /*     */ v_StrucNumTemplate VARCHAR(150); /*     */ 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(COALESCE(c_WildChar ||REPLACE(TRIM(a_FormalName),c_BlankChar,c_WildChar) ||c_WildChar,c_WildChar)); v_HouseNumTemplate:= CASE WHEN TRIM(COALESCE(a_HouseNum,''))='' THEN '' ELSE LOWER(c_WildChar ||REPLACE(TRIM(a_HouseNum),c_BlankChar,c_WildChar)) END ||CASE WHEN TRIM(COALESCE(a_BuildNum,''))='' THEN '' ELSE LOWER(c_WildChar ||REPLACE(TRIM(a_BuildNum),c_BlankChar,c_WildChar)) END || CASE WHEN TRIM(COALESCE(a_StrucNum,''))='' THEN '' ELSE LOWER(c_WildChar ||REPLACE(TRIM(a_StrucNum),c_BlankChar,c_WildChar)) END; v_HouseNumTemplate:=v_HouseNumTemplate||c_WildChar; v_HouseNumTemplate:=CASE WHEN TRIM(COALESCE(a_HouseNum,''))='' THEN '' ELSE LOWER(c_WildChar ||REPLACE(TRIM(a_HouseNum),c_BlankChar,c_WildChar)) END ||c_WildChar; v_BuildNumTemplate:=CASE WHEN TRIM(COALESCE(a_BuildNum,''))='' THEN '' ELSE LOWER(c_WildChar ||REPLACE(TRIM(a_BuildNum),c_BlankChar,c_WildChar)) END ||c_WildChar; v_StrucNumTemplate:=CASE WHEN TRIM(COALESCE(a_StrucNum,''))='' THEN '' ELSE LOWER(c_WildChar ||REPLACE(TRIM(a_StrucNum),c_BlankChar,c_WildChar)) END||c_WildChar; IF a_FormalName IS NOT NULL AND a_ParentFormalName IS NULL AND a_ParentShortName IS NULL AND a_GrandParentFormalName IS NULL AND a_GrandParentShortName IS NULL THEN IF a_HouseNum IS NOT NULL OR a_BuildNum IS NOT NULL OR a_StrucNum IS NOT NULL THEN RETURN QUERY SELECT cfa.AOGUID,h.HouseGUID,cfa.AOLevel, fsfn_Houses_TreeActualName(h.AOGUID,h.HouseGUID), h.HouseNum,h.BuildNum,h.StrucNum, h.EndDate,cfa.ShortName,cfa.FORMALNAME, cfa.currstatus,cfa.Actstatus, NULL::VARCHAR,NULL::VARCHAR,NULL::VARCHAR,NULL::VARCHAR FROM fias_AddressObjects cfa INNER JOIN fias_Houses h ON cfa.aoguid = h.aoguid 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 h.EndDate=(SELECT MAX(ih.EndDate) FROM fias_Houses ih WHERE cfa.aoguid = ih.aoguid AND h.HouseGUID = ih.HouseGUID) AND UPPER(cfa.FORMALNAME) LIKE v_FormalNameTemplate AND UPPER(cfa.ShortName) LIKE v_ShortNameTemplate AND TRIM(LOWER(COALESCE(h.HouseNum,''))) LIKE v_HouseNumTemplate AND TRIM(LOWER(COALESCE(h.BuildNum,''))) LIKE v_BuildNumTemplate AND TRIM(LOWER(COALESCE(h.StrucNum,''))) LIKE v_StrucNumTemplate ORDER BY cfa.AOLevel,cfa.ShortName,cfa.FORMALNAME, TO_NUMBER(SUBSTRING(h.HouseNum,E'\\d+'),'9999'),h.HouseNum, TO_NUMBER(SUBSTRING(h.BuildNum,E'\\d+'),'9999'),h.BuildNum, TO_NUMBER(SUBSTRING(h.StrucNum,E'\\d+'),'9999'),h.StrucNum; ELSE RETURN QUERY SELECT cfa.AOGUID,h.HouseGUID,cfa.AOLevel, fsfn_Houses_TreeActualName(h.AOGUID,h.HouseGUID), h.HouseNum,h.BuildNum,h.StrucNum,h.EndDate, cfa.ShortName,cfa.FORMALNAME,cfa.currstatus,cfa.Actstatus, NULL::VARCHAR,NULL::VARCHAR,NULL::VARCHAR,NULL::VARCHAR FROM fias_AddressObjects cfa INNER JOIN fias_Houses h ON cfa.aoguid = h.aoguid 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 h.EndDate=(SELECT MAX(ih.EndDate) FROM fias_Houses ih WHERE cfa.aoguid = ih.aoguid AND h.HouseGUID = ih.HouseGUID) AND UPPER(cfa.FORMALNAME) LIKE v_FormalNameTemplate AND UPPER(cfa.ShortName) LIKE v_ShortNameTemplate ORDER BY cfa.AOLevel,cfa.ShortName,cfa.FORMALNAME, TO_NUMBER(SUBSTRING(h.HouseNum,E'\\d+'),'9999'),h.HouseNum, TO_NUMBER(SUBSTRING(h.BuildNum,E'\\d+'),'9999'),h.BuildNum, TO_NUMBER(SUBSTRING(h.StrucNum,E'\\d+'),'9999'),h.StrucNum; END IF; ELSIF a_FormalName IS NOT NULL AND 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); IF a_HouseNum IS NOT NULL OR a_BuildNum IS NOT NULL OR a_StrucNum IS NOT NULL THEN RETURN QUERY SELECT cfa.AOGUID,h.HouseGUID,cfa.AOLevel, fsfn_Houses_TreeActualName(h.AOGUID,h.HouseGUID), h.HouseNum,h.BuildNum,h.StrucNum,h.EndDate, cfa.ShortName,cfa.FORMALNAME,cfa.currstatus, cfa.Actstatus,pfa.ShortName,pfa.FORMALNAME, NULL::VARCHAR,NULL::VARCHAR FROM fias_AddressObjects pfa INNER JOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID INNER JOIN fias_Houses h ON cfa.aoguid = h.aoguid 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 h.EndDate=(SELECT MAX(ih.EndDate) FROM fias_Houses ih WHERE cfa.aoguid = ih.aoguid AND h.HouseGUID = ih.HouseGUID) AND pfa.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 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 AND TRIM(LOWER(COALESCE(h.HouseNum,''))) LIKE v_HouseNumTemplate AND TRIM(LOWER(COALESCE(h.BuildNum,''))) LIKE v_BuildNumTemplate AND TRIM(LOWER(COALESCE(h.StrucNum,''))) LIKE v_StrucNumTemplate ORDER BY pfa.ShortName,pfa.FORMALNAME,cfa.AOLevel, cfa.ShortName,cfa.FORMALNAME, TO_NUMBER(SUBSTRING(h.HouseNum,E'\\d+'),'9999'), h.HouseNum,TO_NUMBER(SUBSTRING(h.BuildNum,E'\\d+'),'9999'), h.BuildNum,TO_NUMBER(SUBSTRING(h.StrucNum,E'\\d+'),'9999'), h.StrucNum; ELSE RETURN QUERY SELECT cfa.AOGUID,h.HouseGUID,cfa.AOLevel, fsfn_Houses_TreeActualName(h.AOGUID,h.HouseGUID), h.HouseNum,h.BuildNum,h.StrucNum, h.EndDate,cfa.ShortName,cfa.FORMALNAME, cfa.currstatus,cfa.Actstatus,pfa.ShortName, pfa.FORMALNAME,NULL::VARCHAR,NULL::VARCHAR FROM fias_AddressObjects pfa INNER JOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID INNER JOIN fias_Houses h ON cfa.aoguid = h.aoguid 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 h.EndDate=(SELECT MAX(ih.EndDate) FROM fias_Houses ih WHERE cfa.aoguid = ih.aoguid AND h.HouseGUID = ih.HouseGUID) AND pfa.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 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, TO_NUMBER(SUBSTRING(h.HouseNum,E'\\d+'),'9999'), h.HouseNum, TO_NUMBER(SUBSTRING(h.BuildNum,E'\\d+'),'9999'), h.BuildNum, TO_NUMBER(SUBSTRING(h.StrucNum,E'\\d+'),'9999'),h.StrucNum; END IF; ELSE v_GrandParentShortNameTemplate:=COALESCE(UPPER( COALESCE(c_WildChar ||REPLACE(TRIM(a_GrandParentShortName),c_BlankChar,c_WildChar) ||c_WildChar,c_WildChar)),c_WildChar); v_GrandParentFormalNameTemplate:=COALESCE(UPPER( c_WildChar ||REPLACE(TRIM(a_GrandParentFormalName),c_BlankChar,c_WildChar) ||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); IF a_HouseNum IS NOT NULL OR a_BuildNum IS NOT NULL OR a_StrucNum IS NOT NULL THEN RETURN QUERY SELECT cfa.AOGUID,h.HouseGUID,cfa.AOLevel, fsfn_Houses_TreeActualName(h.AOGUID,h.HouseGUID), h.HouseNum,h.BuildNum,h.StrucNum, h.EndDate,cfa.ShortName,cfa.FORMALNAME, cfa.currstatus,cfa.Actstatus,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 INNER JOIN fias_Houses h ON cfa.aoguid = h.aoguid 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 cfa.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 cfa.aoguid = iao.aoguid) THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE gpfa.aoguid = iao.aoguid) ELSE 0 END AND h.EndDate=(SELECT MAX(ih.EndDate) FROM fias_Houses ih WHERE cfa.aoguid = ih.aoguid AND h.HouseGUID = ih.HouseGUID) 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 AND TRIM(LOWER(COALESCE(h.HouseNum,''))) LIKE v_HouseNumTemplate AND TRIM(LOWER(COALESCE(h.BuildNum,''))) LIKE v_BuildNumTemplate AND TRIM(LOWER(COALESCE(h.StrucNum,''))) LIKE v_StrucNumTemplate ORDER BY gpfa.ShortName,gpfa.FORMALNAME,pfa.ShortName, pfa.FORMALNAME,cfa.AOLevel,cfa.ShortName, cfa.FORMALNAME, TO_NUMBER(SUBSTRING(h.HouseNum,E'\\d+'),'9999'), h.HouseNum, TO_NUMBER(SUBSTRING(h.BuildNum,E'\\d+'),'9999'), h.BuildNum, TO_NUMBER(SUBSTRING(h.StrucNum,E'\\d+'),'9999'),h.StrucNum; ELSE RETURN QUERY SELECT cfa.AOGUID,h.HouseGUID,cfa.AOLevel, fsfn_Houses_TreeActualName(h.AOGUID,h.HouseGUID), h.HouseNum,h.BuildNum,h.StrucNum, h.EndDate,cfa.ShortName,cfa.FORMALNAME, cfa.currstatus,cfa.Actstatus,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 INNER JOIN fias_Houses h ON cfa.aoguid = h.aoguid 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 cfa.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 cfa.aoguid = iao.aoguid) THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao WHERE gpfa.aoguid = iao.aoguid) ELSE 0 END AND h.EndDate=(SELECT MAX(ih.EndDate) FROM fias_Houses ih WHERE cfa.aoguid = ih.aoguid AND h.HouseGUID = ih.HouseGUID) 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, TO_NUMBER(SUBSTRING(h.HouseNum,E'\\d+'),'9999'), h.HouseNum, TO_NUMBER(SUBSTRING(h.BuildNum,E'\\d+'),'9999'), h.BuildNum, TO_NUMBER(SUBSTRING(h.StrucNum,E'\\d+'),'9999'), h.StrucNum; END IF; END IF; END; $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION fstf_Houses_SearchByName(a_HouseNum VARCHAR(20),a_BuildNum VARCHAR(10),a_StrucNum VARCHAR(10),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_Houses_SearchByName('220'); --SELECT * FROM fstf_Houses_SearchByName(NULL,NULL,'220'); SELECT * FROM fstf_Houses_SearchByName('1',NULL,NULL,''); SELECT * FROM fstf_Houses_SearchByName(NULL,NULL,NULL,'','','',NULL,'');


Creating a table of houses FIAS fias_Houses


script code
 BEGIN TRANSACTION; DROP TABLE IF EXISTS fias_Houses; DROP TABLE IF EXISTS fias_EstateStatus; DROP TABLE IF EXISTS fias_StructureStatus; CREATE TABLE IF NOT EXISTS fias_Houses( HOUSEID VARCHAR(36) NOT NULL, AOGUID VARCHAR(36) NULL, HOUSEGUID VARCHAR(36) NULL, HOUSENUM VARCHAR(10) NULL, BUILDNUM VARCHAR(10) NULL, STRUCNUM VARCHAR(10) NULL, POSTALCODE VARCHAR(6) NULL, OKATO VARCHAR(11) NULL, OKTMO VARCHAR(11) NULL, IFNSFL VARCHAR(4) NULL, TERRIFNSFL VARCHAR(4) NULL, IFNSUL VARCHAR(4) NULL, TERRIFNSUL VARCHAR(4) NULL, ESTSTATUS INTEGER NULL, STATSTATUS INTEGER NULL, STRSTATUS INTEGER NULL, STARTDATE TIMESTAMP NULL, ENDDATE TIMESTAMP NULL, UPDATEDATE TIMESTAMP NULL, NORMDOC VARCHAR(36) NULL, COUNTER INTEGER NULL, CADNUM VARCHAR(50) NULL, DIVTYPE INTEGER NULL, CONSTRAINT XPKfias_Houses PRIMARY KEY ( HOUSEID )) WITH (OIDS=False); CREATE INDEX XIE1fias_Houses ON fias_Houses(AOGUID); CREATE INDEX XIE2fias_Houses ON fias_Houses(HOUSEGUID); CREATE INDEX XIE3fias_Houses ON fias_Houses(AOGUID,HOUSEGUID); CREATE INDEX XIE4fias_Houses ON fias_Houses(HOUSENUM,BUILDNUM,STRUCNUM); CREATE INDEX XIE5fias_Houses ON fias_Houses(HOUSENUM); CREATE INDEX XIE6fias_Houses ON fias_Houses(BUILDNUM); CREATE INDEX XIE7fias_Houses ON fias_Houses(STRUCNUM); COMMENT ON TABLE fias_Houses IS 'HOUSE         ,     .'; COMMENT ON COLUMN fias_Houses.HOUSEID IS '   '; COMMENT ON COLUMN fias_Houses.AOGUID IS '      (, ,    ..)'; COMMENT ON COLUMN fias_Houses.HOUSEGUID IS '   '; COMMENT ON COLUMN fias_Houses.HOUSENUM IS ' '; COMMENT ON COLUMN fias_Houses.BUILDNUM IS ' '; COMMENT ON COLUMN fias_Houses.STRUCNUM IS ' '; COMMENT ON COLUMN fias_Houses.POSTALCODE IS ' '; COMMENT ON COLUMN fias_Houses.IFNSFL IS '  '; COMMENT ON COLUMN fias_Houses.TERRIFNSFL IS '    '; COMMENT ON COLUMN fias_Houses.IFNSUL IS '  '; COMMENT ON COLUMN fias_Houses.TERRIFNSUL IS '    '; COMMENT ON COLUMN fias_Houses.OKATO IS ''; COMMENT ON COLUMN fias_Houses.OKTMO IS ''; COMMENT ON COLUMN fias_Houses.ESTSTATUS IS ' '; COMMENT ON COLUMN fias_Houses.STRSTATUS IS ' '; COMMENT ON COLUMN fias_Houses.STATSTATUS IS ' '; COMMENT ON COLUMN fias_Houses.STARTDATE IS '  '; COMMENT ON COLUMN fias_Houses.ENDDATE IS '  '; COMMENT ON COLUMN fias_Houses.UPDATEDATE IS '  () '; COMMENT ON COLUMN fias_Houses.NORMDOC IS '    '; COMMENT ON COLUMN fias_Houses.COUNTER IS '     4'; COMMENT ON COLUMN fias_Houses.CADNUM IS '  '; COMMENT ON COLUMN fias_Houses.DIVTYPE IS ' : 0 –   1 –  2 – '; CREATE TABLE IF NOT EXISTS fias_EstateStatus( EstateStatusID INTEGER NOT NULL, EstateStatusName varchar(60) NULL, EstateStatusShortName varchar(20) NULL, CONSTRAINT XPKfias_EstateStatus PRIMARY KEY (EstateStatusID)) WITH (OIDS=False); COMMENT ON TABLE fias_EstateStatus IS ' ()  '; COMMENT ON COLUMN fias_EstateStatus.EstateStatusID IS ' .  :0 –  ,1 – ,2 – ,3 – '; COMMENT ON COLUMN fias_EstateStatus.EstateStatusName IS ''; COMMENT ON COLUMN fias_EstateStatus.EstateStatusShortName IS ' '; CREATE TABLE IF NOT EXISTS fias_StructureStatus( StructureStatusID INTEGER NOT NULL, StructureStatusName varchar(60) NULL, StructureStatusShortName varchar(20) NULL, CONSTRAINT XPKfias_StructureStatus PRIMARY KEY (StructureStatusID)) WITH (OIDS=False); COMMENT ON TABLE fias_StructureStatus IS ' ()  '; COMMENT ON COLUMN fias_StructureStatus.StructureStatusID IS ' .  :0 –  ,1 – ,2 – ,3 – '; COMMENT ON COLUMN fias_StructureStatus.StructureStatusName IS ''; COMMENT ON COLUMN fias_StructureStatus.StructureStatusShortName IS ' '; --ROLLBACk TRANSACTION; COMMIT TRANSACTION;



Download updates of FIAS houses to fias_Houses table


script source code
 BEGIN TRANSACTION; do $$ BEGIN /****************************************/ /*    */ /****************************************/ DROP TABLE IF EXISTS fias_DeletedHouses_temp; DROP TABLE IF EXISTS fias_Houses_temp; DROP TABLE IF EXISTS fias_EstateStatus_temp; DROP TABLE IF EXISTS fias_StructureStatus_temp; CREATE TABLE fias_Houses_temp AS SELECT * FROM fias_Houses LIMIT 1; DELETE FROM fias_Houses_temp; CREATE TABLE fias_DeletedHouses_temp AS SELECT * FROM fias_Houses LIMIT 1; DELETE FROM fias_DeletedHouses_temp; CREATE TABLE fias_EstateStatus_temp AS SELECT * FROM fias_EstateStatus LIMIT 1; DELETE FROM fias_EstateStatus_temp; CREATE TABLE fias_StructureStatus_temp AS SELECT * FROM fias_StructureStatus LIMIT 1; DELETE FROM fias_StructureStatus_temp; /*****************************************************/ /*     fias_EstateStatus  */ /*  " "   */ /*****************************************************/ COPY fias_EstateStatus_temp(EstateStatusID,EstateStatusNAME,EstateStatusShortName) FROM 'W:\Projects\Enisey GIS\DB\SourceData\ESTSTAT_20180827.csv' WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8'); /*************************************************/ /*     */ /* " "     */ /*************************************************/ UPDATE fias_EstateStatus s SET EstateStatusNAME=t.EstateStatusNAME, EstateStatusShortName=t.EstateStatusShortName FROM fias_EstateStatus ds INNER JOIN fias_EstateStatus_temp t ON ds.EstateStatusID=t.EstateStatusID WHERE ds.EstateStatusID=s.EstateStatusID; INSERT INTO fias_EstateStatus(EstateStatusID,EstateStatusNAME,EstateStatusShortName) SELECT EstateStatusID,EstateStatusNAME,EstateStatusShortName FROM fias_EstateStatus_temp t WHERE NOT EXISTS (SELECT * FROM fias_EstateStatus os WHERE t.EstateStatusID=os.EstateStatusID); /******************************************************/ /*     fias_StructureStatus  */ /*  " "  */ /******************************************************/ COPY fias_StructureStatus_temp(StructureStatusID,StructureStatusNAME,StructureStatusShortName) FROM 'W:\Projects\Enisey GIS\DB\SourceData\STRSTAT_20180827.csv' WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8'); /*****************************************************************/ /*     " "  */ /*   */ /*****************************************************************/ UPDATE fias_StructureStatus s SET StructureStatusNAME=t.StructureStatusNAME, StructureStatusShortName=t.StructureStatusShortName FROM fias_StructureStatus ds INNER JOIN fias_StructureStatus_temp t ON ds.StructureStatusID=t.StructureStatusID WHERE ds.StructureStatusID=s.StructureStatusID; INSERT INTO fias_StructureStatus(StructureStatusID,StructureStatusNAME,StructureStatusShortName) SELECT StructureStatusID,StructureStatusNAME,StructureStatusShortName FROM fias_StructureStatus_temp t WHERE NOT EXISTS (SELECT * FROM fias_StructureStatus os WHERE t.StructureStatusID=os.StructureStatusID); /***********************************************************************/ /*     fias_Houses_temp     */ /**********************************************************************/ COPY fias_Houses_temp(AOGUID,BUILDNUM,ENDDATE,ESTSTATUS,HOUSEGUID,HOUSEID,HOUSENUM,STATSTATUS,IFNSFL,IFNSUL,OKATO,OKTMO,POSTALCODE,STARTDATE,STRUCNUM,STRSTATUS,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,NORMDOC,COUNTER,CADNUM,DIVTYPE) FROM 'W:\Projects\Enisey GIS\DB\SourceData\HOUSE24_20180827.csv' WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8'); /************************************************************/ /*     fias_DeletedHouses_temp , */ /*        */ /************************************************************/ /*  DHOUSE24      . */ /*         */ /************************************************************/ /* COPY fias_DeletedHouses_temp(AOGUID,BUILDNUM,ENDDATE,ESTSTATUS,HOUSEGUID,HOUSEID,HOUSENUM,STATSTATUS,IFNSFL,IFNSUL,OKATO,OKTMO,POSTALCODE,STARTDATE,STRUCNUM,STRSTATUS,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,NORMDOC,COUNTER,CADNUM,DIVTYPE) FROM 'W:\Projects\Enisey GIS\DB\SourceData\DHOUSE24_20180827.csv' WITH (FORMAT csv,DELIMITER ';', ENCODING 'UTF8'); */ /***********************************************************************/ /*        */ /***********************************************************************/ UPDATE fias_Houses h SET AOGUID=t.AOGUID, BUILDNUM=t.BUILDNUM, ENDDATE=t.ENDDATE, ESTSTATUS=t.ESTSTATUS, HOUSEGUID=t.HOUSEGUID, HOUSENUM=t.HOUSENUM, STATSTATUS=t.STATSTATUS, IFNSFL=t.IFNSFL, IFNSUL=t.IFNSUL, OKATO=t.OKATO, OKTMO=t.OKTMO, POSTALCODE=t.POSTALCODE, STARTDATE=t.STARTDATE, STRUCNUM=t.STRUCNUM, STRSTATUS=t.STRSTATUS, TERRIFNSFL=t.TERRIFNSFL, TERRIFNSUL=t.TERRIFNSUL, UPDATEDATE=t.UPDATEDATE, NORMDOC=t.NORMDOC, COUNTER=t.COUNTER, CADNUM=t.CADNUM, DIVTYPE=t.DIVTYPE FROM fias_Houses dh INNER JOIN fias_Houses_Temp t ON t.HOUSEID=dh.HOUSEID WHERE h.HOUSEID=dh.HOUSEID; /****************************************************/ /*       */ /*       */ /* fias_DeletedHouses_temp */ /****************************************************/ DELETE FROM fias_Houses h WHERE EXISTS(SELECT 1 FROM fias_DeletedHouses_temp delh WHERE delh.HOUSEID=h.HOUSEID); /****************************************************/ /*       */ /*  fias_Houses,  */ /*    fias_Houses_Temp */ /****************************************************/ INSERT INTO fias_Houses(AOGUID,BUILDNUM,ENDDATE,ESTSTATUS,HOUSEGUID,HOUSEID,HOUSENUM,STATSTATUS,IFNSFL,IFNSUL,OKATO,OKTMO,POSTALCODE,STARTDATE,STRUCNUM,STRSTATUS,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,NORMDOC,COUNTER,CADNUM,DIVTYPE) SELECT AOGUID,BUILDNUM,ENDDATE,ESTSTATUS,HOUSEGUID,HOUSEID,HOUSENUM,STATSTATUS,IFNSFL,IFNSUL,OKATO,OKTMO,POSTALCODE,STARTDATE,STRUCNUM,STRSTATUS,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,NORMDOC,COUNTER,CADNUM,DIVTYPE FROM fias_Houses_Temp t WHERE NOT EXISTS(SELECT * FROM fias_Houses h WHERE t.HOUSEID=h.HOUSEID); /************************************/ /*    */ /************************************/ DROP TABLE IF EXISTS fias_DeletedHouses_temp; DROP TABLE IF EXISTS fias_Houses_temp; DROP TABLE IF EXISTS fias_EstateStatus_temp; DROP TABLE IF EXISTS fias_StructureStatus_temp; END; $$LANGUAGE plpgsql; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT (SELECT COUNT(*) FROM fias_Houses) AS HouseCount, (SELECT COUNT(*) FROM fias_EstateStatus) AS EStatusCount, (SELECT COUNT(*) FROM fias_StructureStatus) AS SStatusCount;

Source: https://habr.com/ru/post/425287/

More articles:


All Articles