FIAS houses in PostgreSQL environment
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 f function 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, it can be noted that the element identifier ( HOUSEGUID ) “d. 1, Bldg. 2, p. 26 ", as a result, six entries were received:
- three parent records with addressing elements: on the province, city and street;
- Three entries with the characteristics of the house number: house number, building number and building number.
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 what street and in which the village is the house you need to meaningfully AOGUID recording HOUSES find the corresponding entry with the same identifier list 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 - list of addressing elements;
- HOUSES - list of houses;
- STRSTAT - directory of signs of the structure;
- ESTSTAT - handbook of signs of ownership.
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 record with the oldest end of the period, which is more than the current one, is relevant. 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 is 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'ORDERBY h.ENDDATE DESC,h.STARTDATE DESC;
Attentive reader, looking at Fig. 1, for sure asked himself 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, buildings 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 the object, it is not enough to know the place, street, house number. You need to know more:
- "Possession" is or "household";
- the status of this object is defined or not determined;
- etc.
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 towards 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.
SELECT fsfn_Houses_TreeActualName(h.AOGUID,h.HOUSEGUID),h.HOUSEGUID,str.StructureStatusName,est.EstateStatusName
FROM fias_Houses h
INNERJOIN (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')
GROUPBY 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')
ORDERBY 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 record 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 village:
- 98% of FIAS houses are connected with streets in populated areas;
- 1.2% of houses - with streets in garden and garden partnerships;
- 0.3% of houses with settlements;
- 0.5% of houses with other targeted elements.
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: a garage, a 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 address 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-generating element ( AOGUID ) and the current status ( CurrStatus ).
SELECTINTO v_AOGUID,v_CurrStatus
h.AOGUID,CASEWHEN0 <
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)
ELSE0ENDFROM fias_Houses h
INNERJOIN 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'))
ORDERBY 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:
- if none of the entries in the history of the address-forming element contains 0 in the CurrStatus field, then the variable v_CurrStatus is assigned the maximum field value for this address-forming element;
- otherwise, this variable is assigned the value 0.
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 of the addressing element of the document “FIAS Addresses in the PostgreSQL Environment” section.
.Then the records of the address-forming elements are supplemented by the records of the house number, the building body, the building (see Table 1), which are created for each non-empty field about the house number, the building body, and the building.
To ensure that all output records have the same structure, and not without a certain amount of foppery, in the body of the field values are artificially-level code ( AOLevel ), current status ( CurrStatus ) and current status ( ActStatus ).
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” .
SELECT h.AOGUID, h.HOUSEGUID, h.HOUSENUM, h.BUILDNUM, h.STRUCNUM, h.ENDDATE,
CASEWHEN 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') THEN0ELSE RANK() OVER (PARTITIONBY h.AOGUID, h.HOUSEGUID ORDERBY h.ENDDATE ASC)
ENDAS HouseCurrStatus,
CASEWHEN 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') THEN1ELSE0ENDAS HouseActStatus
FROM fias_Houses h
INNERJOIN 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'))
ORDERBY h.ENDDATE DESC;
Full home address
The main idea of the fsfn_Houses_TreeActualName function is to return a single-row house number 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: “ 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.
| Value | Note |
|---|---|
| {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} | Маска — района субъекта федерации |
| {TM} | Маска — субъект федерации (регион) |
| {CY} | Маска — страна |
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. Let's 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. The result of 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 performed in the same way 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 the CSV format and it is already converted by the COPY operator to 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 .
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS fstf_Houses_AddressObjectTree(a_AOGUID VARCHAR(36), a_HOUSEGUID VARCHAR(36),a_CurrStatus INTEGER,a_ENDDATE TIMESTAMP);
/******************************************************************//* Возвращает дерево (список взаимосвязанных строк) с характеристиками *//* дома и других адресообразующих элементов *//******************************************************************/CREATEOR REPLACEFUNCTION fstf_Houses_AddressObjectTree(
a_AOGUID VARCHAR(36), /* Глобальный уникальный идентификатор *//* адресного объекта*/
a_HOUSEGUID VARCHAR(36),/* Глобальный уникальный идентификатор дома */
a_CurrStatus INTEGERdefault0,/* Статус актуальности КЛАДР 4: *//* 0 - актуальный, *//* 1-50 - исторический, т.е. объект *//* был переименован, *//* в данной записи приведено*//* одно из прежних *//* его наименований, *//* 51 - переподчиненный */
a_ENDDATE TIMESTAMPdefault'2079-06-06'/* Окончание действия записи о доме*/
)
RETURNSTABLE (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 CONSTANTINTEGER:=8;
c_HouseShortTypeName CONSTANTVARCHAR(10):='д.';
c_BuildShortTypeName CONSTANTVARCHAR(10):='корп.';
c_StructShortTypeName CONSTANTVARCHAR(10):='стр.';
c_StatusActual CONSTANTINTEGER:=1; /* Признак актуальности записи */
c_StatusNotActual CONSTANTINTEGER:=0; /* Признак неактальной записи записи */
c_MAXENDDATE CONSTANTTIMESTAMP:=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; /* Код возврата */--************************************************************ --************************************************************BEGINRETURN QUERYSELECT * FROM fstf_AddressObjects_AddressObjectTree
(a_AOGUID,a_CurrStatus);
IF a_ENDDATE ISNULLTHENSELECTINTO v_ENDDATE MAX(ENDDATE)
FROM fias_Houses WHERE AOGUID=a_AOGUID AND HOUSEGUID=a_HOUSEGUID;
ELSE
v_ENDDATE:=a_ENDDATE;
ENDIF;
SELECTINTO v_HOUSENUM,v_BUILDNUM,v_STRUCNUM,
v_ENDDATE,v_HouseCurrStatus
h.HOUSENUM,h.BUILDNUM,h.STRUCNUM,
h.ENDDATE,ah.HouseCurrStatus
FROM fias_Houses h
INNERJOIN (SELECT AOGUID,HOUSEGUID,ENDDATE,
RANK() OVER (PARTITIONBY AOGUID,
HOUSEGUID ORDERBY 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:=CASEWHEN COALESCE(v_ENDDATE,c_MAXENDDATE)=
c_MAXENDDATE THEN c_StatusActual ELSE c_StatusNotActual END;
v_HouseCurrStatus:=CASEWHEN COALESCE(v_ENDDATE,c_MAXENDDATE)=
c_MAXENDDATE THEN0ELSE v_HouseCurrStatus END;
IF v_HOUSENUM ISNOTNULLTHENRETURN QUERYSELECT a_HOUSEGUID,v_HouseCurrStatus,v_HouseActStatus,
c_HouseAOLevel,c_HouseShortTypeName,v_HOUSENUM;
ENDIF;
IF v_BUILDNUM ISNOTNULLTHENRETURN QUERYSELECT a_HOUSEGUID,v_HouseCurrStatus,v_HouseActStatus,
c_HouseAOLevel,c_BuildShortTypeName,v_BUILDNUM;
ENDIF;
IF v_STRUCNUM ISNOTNULLTHENRETURN QUERYSELECT a_HOUSEGUID,v_HouseCurrStatus,v_HouseActStatus,
c_HouseAOLevel,c_StructShortTypeName,v_STRUCNUM;
ENDIF;
END;
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION fstf_Houses_AddressObjectTree(a_AOGUID VARCHAR(36), a_HOUSEGUID VARCHAR(36),a_CurrStatus INTEGER,a_ENDDATE TIMESTAMP) IS'Возвращает дерево (список взаимосвязанных строк) с дома характеристиками и его адресного объекта';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS fstf_Houses_AddressObjectTree(a_HOUSEGUID VARCHAR(36),a_ENDDATE TIMESTAMP);
/******************************************************************//* Возвращает дерево (список взаимосвязанных строк) с характеристиками *//* дома и других адресообразующих элементов *//******************************************************************/CREATEOR REPLACEFUNCTION fstf_Houses_AddressObjectTree(
a_HOUSEGUID VARCHAR(36),/* Глобальный уникальный идентификатор дома */
a_ENDDATE TIMESTAMPdefault'2079-06-06'/* Окончание действия записи о доме*/
)
RETURNSTABLE (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 CONSTANTTIMESTAMP:=TO_TIMESTAMP('2079-06-06','YYYY-MM-DD');
c_ActualStatusCode CONSTANTINTEGER :=1;
/* Признак актуальной записи адресного объекта */
c_NotActualStatusCode CONSTANTINTEGER :=0;
/* Значени кода актуальной записи */
v_AOGUID VARCHAR(36); /* Глобальный уникальный *//* идентификатор адресного объекта*/
v_CurrStatus INTEGER; /* Статус актуальности КЛАДР 4: *//* 0 - актуальный, *//* 1-50 - исторический, *//* т.е. объект был переименован, *//* в данной записи приведено *//* одно из прежних его наименований, *//* 51 - переподчиненный*/
v_Return_Error Integer :=0; /* Код возврата */--******************************************************************* --*******************************************************************BEGINSELECTINTO v_AOGUID,v_CurrStatus h.AOGUID,
CASEWHEN0 < 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)
ELSE0ENDFROM fias_Houses h INNERJOIN fias_AddressObjects ao ON h.AOGUID=ao.AOGUID
WHERE h.HOUSEGUID=a_HOUSEGUID
AND h.ENDDATE=COALESCE(a_ENDDATE,c_MaxEndDate)
ORDERBY h.ENDDATE DESC;
RETURN QUERYSELECT * FROM fstf_Houses_AddressObjectTree(
v_AOGUID,a_HOUSEGUID,
v_CurrStatus,a_ENDDATE);
END;
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION fstf_Houses_AddressObjectTree(a_HOUSEGUID VARCHAR(36),a_ENDDATE TIMESTAMP) IS'Возвращает дерево (список взаимосвязанных строк) с дома характеристиками и его адресного объекта';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECT * FROM fstf_Houses_AddressObjectTree('8b40b028-68eb-4315-a256-ea300c604688','42301ab8-9ead-4f8e-8281-e64f2769a254') ORDERBY rtf_AOLevel;
SELECT * FROM fstf_Houses_AddressObjectTree('42301ab8-9ead-4f8e-8281-e64f2769a254') ORDERBY rtf_AOLevel;
Creating the function fsfn_Houses_TreeActualName
Comments on the source code of the function can be found here .
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS fsfn_Houses_TreeActualName(a_AOGUID VARCHAR(36),a_HOUSEGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) CASCADE;
/*****************************************************************//* Возвращает строку с адресом дома в соответствии с массивом масок *//*****************************************************************/CREATEOR REPLACEFUNCTION fsfn_Houses_TreeActualName(
a_AOGUID VARCHAR(36), /* Идентификтор адресного объекта */
a_HOUSEGUID VARCHAR(36), /* Глобальный уникальный идентификатор дома */
a_MaskArray VARCHAR(2)[10] default'{TP,LM,LP,ST,HS,BY,BG}'/* Массив масок, управляющий содержанием *//* строки с адресом дома*/
)
RETURNSVARCHAR(1000) AS
$BODY$
DECLARE
c_HouseMaskArray CONSTANTVARCHAR(2)[3]:='{HS,BY,BG}';
/* Массив масок по умолчанию*/
c_HouseNoMask CONSTANTVARCHAR(2)[1] :='{HS}';
c_BodyNoMask CONSTANTVARCHAR(2)[1] :='{BY}';/* Маска корпуса*/
c_BuildingNoMask CONSTANTVARCHAR(2)[1] :='{BG}';/* Маска строения*/
c_HouseShortTypeName CONSTANTVARCHAR(10):='д.';
c_BuildShortTypeName CONSTANTVARCHAR(10):='корп.';
c_StructShortTypeName CONSTANTVARCHAR(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);
SELECTINTO v_ENDDATE MAX(ENDDATE)
FROM fias_Houses
WHERE AOGUID=a_AOGUID AND HOUSEGUID=a_HOUSEGUID;
SELECTINTO 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||
CASEWHEN v_TreeAddressObjectName=''THEN''ELSE', ' ||c_HouseShortTypeName||' '||v_HOUSENUM
END;
ENDIF;
IF c_BodyNoMask <@ a_MaskArray
AND COALESCE(TRIM(v_BUILDNUM),'')<>''THEN
v_TreeAddressObjectName:=v_TreeAddressObjectName||
CASEWHEN v_TreeAddressObjectName=''THEN''ELSE', ' || c_BuildShortTypeName||' '||v_BUILDNUM
END;
ENDIF;
IF c_BuildingNoMask <@ a_MaskArray
AND COALESCE(TRIM(v_STRUCNUM),'')<>''THEN
v_TreeAddressObjectName:=v_TreeAddressObjectName||
CASEWHEN v_TreeAddressObjectName=''THEN''ELSE', ' || c_StructShortTypeName||' '||v_STRUCNUM
END;
ENDIF;
RETURN v_TreeAddressObjectName;
END;
$BODY$
LANGUAGE plpgsql ;
COMMENTONFUNCTION fsfn_Houses_TreeActualName(a_AOGUID VARCHAR(36),a_HOUSEGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) IS'Возвращает строку с адресом дома в соответствии с массивом масок';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS fsfn_Houses_TreeActualName(a_HOUSEGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) CASCADE;
/*****************************************************************//* Возвращает строку с адресом дома в соответствии с массивом масок *//*****************************************************************/CREATEOR REPLACEFUNCTION fsfn_Houses_TreeActualName(
a_HOUSEGUID VARCHAR(36), /* Глобальный уникальный идентификатор дома */
a_MaskArray VARCHAR(2)[10] default'{TP,LM,LP,ST,HS,BY,BG}'/* Массив масок, управляющий содержанием *//* строки с адресом дома*/
)
RETURNSVARCHAR(1000) AS
$BODY$
DECLARE
c_MaxEndDate CONSTANTTIMESTAMP:=TO_TIMESTAMP('2079-06-06','YYYY-MM-DD');
v_AOGUID VARCHAR(36); /* Идентификтор адресного объекта */
v_TreeAddressObjectName VARCHAR(1000); /* Полное в иерархии название объекта*/
v_Return_Error Integer :=0; /* Код возврата */--********************************************************** --**********************************************************BEGINSELECTINTO v_AOGUID h.AOGUID
FROM fias_Houses h
INNERJOIN fias_AddressObjects ao ON h.AOGUID=ao.AOGUID
WHERE h.HOUSEGUID=a_HOUSEGUID AND h.ENDDATE=c_MaxEndDate
ORDERBY h.ENDDATE DESC;
v_TreeAddressObjectName:=fsfn_Houses_TreeActualName
(v_AOGUID,a_HOUSEGUID,a_MaskArray);
RETURN v_TreeAddressObjectName;
END;
$BODY$
LANGUAGE plpgsql ;
COMMENTONFUNCTION fsfn_Houses_TreeActualName(a_HOUSEGUID VARCHAR(36),a_MaskArray VARCHAR(2)[10]) IS'Возвращает строку с адресом дома в соответствии с массивом масок';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
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 .
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS 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));
/*****************************************************//* Возвращает результат поиска в списке адресообразующих *//* элементов ФИАС по их названию и типу *//*****************************************************/CREATEOR REPLACEFUNCTION fstf_Houses_SearchByName(
a_HouseNum VARCHAR(20), /* Номер дома */
a_BuildNum VARCHAR(10) defaultNULL,/* Номер Корпуса */
a_StrucNum VARCHAR(10) defaultNULL, /* Номер Строения */
a_FormalName VARCHAR(150) defaultNULL, /* Оптимизированное *//* для поиска наименование адресного объекта*/
a_ShortName VARCHAR(20) defaultNULL, /* Сокращенное *//* наименование типа адресного объекта */
a_ParentFormalName VARCHAR(150) defaultNULL, /* Оптимизированное *//* для поиска наименование адресного объекта*/
a_ParentShortName VARCHAR(20) defaultNULL, /* Сокращенное *//* наименование типа адресного объекта */
a_GrandParentFormalName VARCHAR(150) defaultNULL,
/* Оптимизированное *//* для поиска наименование адресного объекта*/
a_GrandParentShortName VARCHAR(20) defaultNULL/* Сокращенное *//* наименование типа адресного объекта */
)
RETURNSTABLE (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 CONSTANTVARCHAR(2)='%';
c_BlankChar CONSTANTVARCHAR(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:=
CASEWHEN TRIM(COALESCE(a_HouseNum,''))=''THEN''ELSE LOWER(c_WildChar
||REPLACE(TRIM(a_HouseNum),c_BlankChar,c_WildChar))
END
||CASEWHEN TRIM(COALESCE(a_BuildNum,''))=''THEN''ELSE LOWER(c_WildChar
||REPLACE(TRIM(a_BuildNum),c_BlankChar,c_WildChar))
END
|| CASEWHEN 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:=CASEWHEN TRIM(COALESCE(a_HouseNum,''))=''THEN''ELSE LOWER(c_WildChar
||REPLACE(TRIM(a_HouseNum),c_BlankChar,c_WildChar))
END
||c_WildChar;
v_BuildNumTemplate:=CASEWHEN TRIM(COALESCE(a_BuildNum,''))=''THEN''ELSE LOWER(c_WildChar
||REPLACE(TRIM(a_BuildNum),c_BlankChar,c_WildChar))
END ||c_WildChar;
v_StrucNumTemplate:=CASEWHEN TRIM(COALESCE(a_StrucNum,''))=''THEN''ELSE LOWER(c_WildChar
||REPLACE(TRIM(a_StrucNum),c_BlankChar,c_WildChar))
END||c_WildChar;
IF a_FormalName ISNOTNULLAND a_ParentFormalName ISNULLAND a_ParentShortName ISNULLAND a_GrandParentFormalName ISNULLAND a_GrandParentShortName ISNULLTHENIF a_HouseNum ISNOTNULLOR a_BuildNum ISNOTNULLOR a_StrucNum ISNOTNULLTHENRETURN QUERYSELECT 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::VARCHARFROM fias_AddressObjects cfa
INNERJOIN fias_Houses h ON cfa.aoguid = h.aoguid
WHERE cfa.currstatus=
CASEWHEN0 <
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)
ELSE0ENDAND 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
ORDERBY 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;
ELSERETURN QUERYSELECT 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::VARCHARFROM fias_AddressObjects cfa
INNERJOIN fias_Houses h ON cfa.aoguid = h.aoguid
WHERE cfa.currstatus=
CASEWHEN0 < 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)
ELSE0ENDAND 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
ORDERBY 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;
ENDIF;
ELSIF a_FormalName ISNOTNULLAND a_ParentFormalName ISNOTNULLAND a_GrandParentFormalName ISNULLAND a_GrandParentShortName ISNULLTHEN
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 ISNOTNULLOR a_BuildNum ISNOTNULLOR a_StrucNum ISNOTNULLTHENRETURN QUERYSELECT 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::VARCHARFROM fias_AddressObjects pfa
INNERJOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID
INNERJOIN fias_Houses h ON cfa.aoguid = h.aoguid
WHERE cfa.currstatus=
CASEWHEN0 < 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)
ELSE0ENDAND h.EndDate=(SELECT MAX(ih.EndDate)
FROM fias_Houses ih
WHERE cfa.aoguid = ih.aoguid
AND h.HouseGUID = ih.HouseGUID)
AND pfa.currstatus=
CASEWHEN0 < 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)
ELSE0ENDAND 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
ORDERBY 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;
ELSERETURN QUERYSELECT 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::VARCHARFROM fias_AddressObjects pfa
INNERJOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID
INNERJOIN fias_Houses h ON cfa.aoguid = h.aoguid
WHERE cfa.currstatus=
CASEWHEN0 < 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)
ELSE0ENDAND h.EndDate=(SELECT MAX(ih.EndDate)
FROM fias_Houses ih
WHERE cfa.aoguid = ih.aoguid
AND h.HouseGUID = ih.HouseGUID)
AND pfa.currstatus=
CASEWHEN0 < 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)
ELSE0ENDAND 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
ORDERBY 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;
ENDIF;
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 ISNOTNULLOR a_BuildNum ISNOTNULLOR a_StrucNum ISNOTNULLTHENRETURN QUERYSELECT 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
INNERJOIN fias_AddressObjects pfa ON gpfa.AOGUID=pfa.ParentGUID
INNERJOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID
INNERJOIN fias_Houses h ON cfa.aoguid = h.aoguid
WHERE cfa.currstatus=
CASEWHEN0 < 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)
ELSE0ENDAND pfa.currstatus=
CASEWHEN0 < 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)
ELSE0ENDAND gpfa.currstatus=
CASEWHEN0 < 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)
ELSE0ENDAND 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
ORDERBY 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;
ELSERETURN QUERYSELECT 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
INNERJOIN fias_AddressObjects pfa ON gpfa.AOGUID=pfa.ParentGUID
INNERJOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID
INNERJOIN fias_Houses h ON cfa.aoguid = h.aoguid
WHERE cfa.currstatus=
CASEWHEN0 < 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)
ELSE0ENDAND pfa.currstatus=
CASEWHEN0 < 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)
ELSE0ENDAND gpfa.currstatus=
CASEWHEN0 < 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)
ELSE0ENDAND 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
ORDERBY 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;
ENDIF;
ENDIF;
END;
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION 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;COMMITTRANSACTION;
--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
BEGINTRANSACTION;
DROPTABLEIFEXISTS fias_Houses;
DROPTABLEIFEXISTS fias_EstateStatus;
DROPTABLEIFEXISTS fias_StructureStatus;
CREATETABLEIFNOTEXISTS fias_Houses(
HOUSEID VARCHAR(36) NOTNULL,
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 INTEGERNULL,
STATSTATUS INTEGERNULL,
STRSTATUS INTEGERNULL,
STARTDATE TIMESTAMPNULL,
ENDDATE TIMESTAMPNULL,
UPDATEDATE TIMESTAMPNULL,
NORMDOC VARCHAR(36) NULL,
COUNTER INTEGERNULL,
CADNUM VARCHAR(50) NULL,
DIVTYPE INTEGERNULL,
CONSTRAINT XPKfias_Houses PRIMARY KEY
(
HOUSEID
)) WITH (OIDS=False);
CREATEINDEX XIE1fias_Houses ON fias_Houses(AOGUID);
CREATEINDEX XIE2fias_Houses ON fias_Houses(HOUSEGUID);
CREATEINDEX XIE3fias_Houses ON fias_Houses(AOGUID,HOUSEGUID);
CREATEINDEX XIE4fias_Houses ON fias_Houses(HOUSENUM,BUILDNUM,STRUCNUM);
CREATEINDEX XIE5fias_Houses ON fias_Houses(HOUSENUM);
CREATEINDEX XIE6fias_Houses ON fias_Houses(BUILDNUM);
CREATEINDEX XIE7fias_Houses ON fias_Houses(STRUCNUM);
COMMENTONTABLE fias_Houses IS'HOUSE Сведения по номерам домов улиц городов и населенных пунктов, номера земельных участков и т.п';
COMMENTONCOLUMN fias_Houses.HOUSEID IS'Уникальный идентификатор записи дома';
COMMENTONCOLUMN fias_Houses.AOGUID IS'Глобальный уникальный идентификатор записи родительского объекта (улицы, города, населенного пункта и т.п.)';
COMMENTONCOLUMN fias_Houses.HOUSEGUID IS'Глобальный уникальный идентификатор дома';
COMMENTONCOLUMN fias_Houses.HOUSENUM IS'Номер дома';
COMMENTONCOLUMN fias_Houses.BUILDNUM IS'Номер корпуса';
COMMENTONCOLUMN fias_Houses.STRUCNUM IS'Номер строения';
COMMENTONCOLUMN fias_Houses.POSTALCODE IS'Почтовый индекс';
COMMENTONCOLUMN fias_Houses.IFNSFL IS'Код ИФНС ФЛ';
COMMENTONCOLUMN fias_Houses.TERRIFNSFL IS'Код территориального участка ИФНС ФЛ';
COMMENTONCOLUMN fias_Houses.IFNSUL IS'Код ИФНС ЮЛ';
COMMENTONCOLUMN fias_Houses.TERRIFNSUL IS'Код территориального участка ИФНС ЮЛ';
COMMENTONCOLUMN fias_Houses.OKATO IS'ОКАТО';
COMMENTONCOLUMN fias_Houses.OKTMO IS'ОКТМО';
COMMENTONCOLUMN fias_Houses.ESTSTATUS IS'Признак владения';
COMMENTONCOLUMN fias_Houses.STRSTATUS IS'Признак строения';
COMMENTONCOLUMN fias_Houses.STATSTATUS IS'Состояние дома';
COMMENTONCOLUMN fias_Houses.STARTDATE IS'Начало действия записи';
COMMENTONCOLUMN fias_Houses.ENDDATE IS'Окончание действия записи';
COMMENTONCOLUMN fias_Houses.UPDATEDATE IS'Дата внесения (обновления) записи';
COMMENTONCOLUMN fias_Houses.NORMDOC IS'Внешний ключ на нормативный документ';
COMMENTONCOLUMN fias_Houses.COUNTER IS'Счетчик записей домов для КЛАДР 4';
COMMENTONCOLUMN fias_Houses.CADNUM IS'Кадастровый номер здания';
COMMENTONCOLUMN fias_Houses.DIVTYPE IS'Тип деления: 0 – не определено 1 – муниципальное 2 – административное';
CREATETABLEIFNOTEXISTS fias_EstateStatus(
EstateStatusID INTEGERNOTNULL,
EstateStatusName varchar(60) NULL,
EstateStatusShortName varchar(20) NULL,
CONSTRAINT XPKfias_EstateStatus PRIMARY KEY (EstateStatusID)) WITH (OIDS=False);
COMMENTONTABLE fias_EstateStatus IS'Справочник (перечень) видов владений';
COMMENTONCOLUMN fias_EstateStatus.EstateStatusID IS'Признак владения. Принимает значение:0 – Не определено,1 – Владение,2 – Дом,3 – Домовладение';
COMMENTONCOLUMN fias_EstateStatus.EstateStatusName IS'Наименование';
COMMENTONCOLUMN fias_EstateStatus.EstateStatusShortName IS'Краткое наименование';
CREATETABLEIFNOTEXISTS fias_StructureStatus(
StructureStatusID INTEGERNOTNULL,
StructureStatusName varchar(60) NULL,
StructureStatusShortName varchar(20) NULL,
CONSTRAINT XPKfias_StructureStatus PRIMARY KEY (StructureStatusID)) WITH (OIDS=False);
COMMENTONTABLE fias_StructureStatus IS'Справочник (перечень) видов строений';
COMMENTONCOLUMN fias_StructureStatus.StructureStatusID IS'Признак строения. Принимает значение:0 – Не определено,1 – Строение,2 – Сооружение,3 – Литер';
COMMENTONCOLUMN fias_StructureStatus.StructureStatusName IS'Наименование';
COMMENTONCOLUMN fias_StructureStatus.StructureStatusShortName IS'Краткое наименование';
--ROLLBACk TRANSACTION;COMMITTRANSACTION;
Download updates of FIAS houses to fias_Houses table
BEGINTRANSACTION;
do $$BEGIN/****************************************//* Создание временных таблиц *//****************************************/DROPTABLEIFEXISTS fias_DeletedHouses_temp;
DROPTABLEIFEXISTS fias_Houses_temp;
DROPTABLEIFEXISTS fias_EstateStatus_temp;
DROPTABLEIFEXISTS fias_StructureStatus_temp;
CREATETABLE fias_Houses_temp ASSELECT * FROM fias_Houses LIMIT1;
DELETEFROM fias_Houses_temp;
CREATETABLE fias_DeletedHouses_temp ASSELECT * FROM fias_Houses LIMIT1;
DELETEFROM fias_DeletedHouses_temp;
CREATETABLE fias_EstateStatus_temp ASSELECT * FROM fias_EstateStatus LIMIT1;
DELETEFROM fias_EstateStatus_temp;
CREATETABLE fias_StructureStatus_temp ASSELECT * FROM fias_StructureStatus LIMIT1;
DELETEFROM 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
INNERJOIN fias_EstateStatus_temp t ON ds.EstateStatusID=t.EstateStatusID
WHERE ds.EstateStatusID=s.EstateStatusID;
INSERTINTO fias_EstateStatus(EstateStatusID,EstateStatusNAME,EstateStatusShortName)
SELECT EstateStatusID,EstateStatusNAME,EstateStatusShortName FROM fias_EstateStatus_temp t WHERENOTEXISTS (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
INNERJOIN fias_StructureStatus_temp t ON ds.StructureStatusID=t.StructureStatusID
WHERE ds.StructureStatusID=s.StructureStatusID;
INSERTINTO fias_StructureStatus(StructureStatusID,StructureStatusNAME,StructureStatusShortName)
SELECT StructureStatusID,StructureStatusNAME,StructureStatusShortName FROM fias_StructureStatus_temp t WHERENOTEXISTS (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<
Also popular now:
-
Note to flash designer (preloaders)
-
Create your own theme. Part 0: Introduction.
-
COMSTAR-FON (Stream-FON)
-
About magazines and printed matter.
-
We work with managers remotely
-
Recommend a simple and convenient CMS for small sites
-
IPhone Application Design: Icon for LiveJournal.app / Festival 404 Blog
-
Phishing, a classic of the genre!
-
first impression of Bitrix
-
Bourgeois tricks