FIAS addresses in the PostgreSQL environment. Part 4. EPILOGUE
This is the fourth and last part of the article, which contains examples of creating a fias_AddressObjects table in a database managed by PostgreSQL, as well as loading data on FIAS addressable elements into it. After these actions, you can independently test the functions discussed in the first , second , and third parts by copying and executing scripts to create them.
The full text of the article consists of 4 parts. The first half of this part of the article contains comments on the implementation of scripts for creating a table of address-forming elements and filling it with data. In the second, the source texts of the scripts.
Those readers who are interested only in the source code, we suggest you immediately go to
Attachment .
')
We need to start by visiting the official website of the Federal Tax Service of the “Federal Information Address System” (FIAS) section of the Updates page.
Download the latest update or full FIAS database on your computer if you are just starting to work with FIAS.
Move the file with the archive to the working folder. Extract the archive files and find the file ADDROBJ.DBF.
Further it is assumed that the archive of files with the FIAS update in the dbf format is downloaded.
The downloaded ADDROBJ.DBF file is converted to csv format. To do this, open the source file using MS Excel and resave it in csv format, while not forgetting to delete the line with the names of the record fields. Further, converted to the csv format will be referred to as “ADDROBJ24_20161020.csv”, where 24 is the code of the Krasnoyarsk Territory, and 20161020 is the date of the file download.
Create a fias_AddressObjects table. To do this, you can use the script provided in the appendix “Creating a table of FII address-forming elements fias_AddressObjects” .
Fig. 7 Direct loading of data into the fias_AddressObjects table.
The data from the ADDROBJ24_20161020.csv file can be directly loaded into the fias_AddressObjects table as shown in Fig. 7
But, unfortunately, the simple way is not for us.
First, in addition to the main list of address-forming elements, a list of address-forming elements is also supplied that must be removed from the main list (DADDROBJ.DBF);
Secondly, in the main list there are violations of the connectivity, for example, links that do not lead anywhere, i.e. There is no item or record in the list with the identifier specified in the link. Therefore, you do not want to recover errors that have already been corrected once.
Thirdly, I don’t want to work every time with a complete list of FIAS addressing elements, but only to upload changes that appear on the official website of the Federal Tax Service two or three times a week.
Therefore, in the process of downloading the FIAS update, two temporary tables are used:
Fig. 8. Preloading address-forming elements into temporary tables.
Further, the fias_AddressObjects_temp table data is used to replace (UPDATE) values ​​in already existing records and add (INSERT) newly created records to the main table. The detailed text of these operators can be found in the section “Downloading Updates of Addressing Elements of FIAS to the fias_AddressObjects Table”.
Since the update process may introduce integrity violations, you can download records in which links to the next (NEXTID) or previous (PREVID) history record indicate a non-existing record.
This situation is very likely. Here, for example, data on the download results of the full database as of 10.10.2016.
Total violations:
Therefore, before you perform updates to the main table, you must disable the restrictions action:
After the updates to the main table have been completed, you must assign NULL values ​​to the NEXTID or PREVID fields where their values ​​indicate a non-existent record. For example:
Before loading is complete, restore the restrictions and delete the temporary tables.
Thanks for attention!
The full text of the article consists of 4 parts. The first half of this part of the article contains comments on the implementation of scripts for creating a table of address-forming elements and filling it with data. In the second, the source texts of the scripts.
Those readers who are interested only in the source code, we suggest you immediately go to
Attachment .
')
Epilogue
Where to start
We need to start by visiting the official website of the Federal Tax Service of the “Federal Information Address System” (FIAS) section of the Updates page.
Download the latest update or full FIAS database on your computer if you are just starting to work with FIAS.
Move the file with the archive to the working folder. Extract the archive files and find the file ADDROBJ.DBF.
Further it is assumed that the archive of files with the FIAS update in the dbf format is downloaded.
The downloaded ADDROBJ.DBF file is converted to csv format. To do this, open the source file using MS Excel and resave it in csv format, while not forgetting to delete the line with the names of the record fields. Further, converted to the csv format will be referred to as “ADDROBJ24_20161020.csv”, where 24 is the code of the Krasnoyarsk Territory, and 20161020 is the date of the file download.
Create a fias_AddressObjects table. To do this, you can use the script provided in the appendix “Creating a table of FII address-forming elements fias_AddressObjects” .
Load ADDROBJ24_20161020.csv to the database
Fig. 7 Direct loading of data into the fias_AddressObjects table.
The data from the ADDROBJ24_20161020.csv file can be directly loaded into the fias_AddressObjects table as shown in Fig. 7
But, unfortunately, the simple way is not for us.
First, in addition to the main list of address-forming elements, a list of address-forming elements is also supplied that must be removed from the main list (DADDROBJ.DBF);
Secondly, in the main list there are violations of the connectivity, for example, links that do not lead anywhere, i.e. There is no item or record in the list with the identifier specified in the link. Therefore, you do not want to recover errors that have already been corrected once.
Thirdly, I don’t want to work every time with a complete list of FIAS addressing elements, but only to upload changes that appear on the official website of the Federal Tax Service two or three times a week.
Therefore, in the process of downloading the FIAS update, two temporary tables are used:
- fias_AddressObjects_temp - for updates of the main list of addressing elements;
- fias_DeletedAddressObjects_temp - for entries to be removed from the main list.
Fig. 8. Preloading address-forming elements into temporary tables.
Further, the fias_AddressObjects_temp table data is used to replace (UPDATE) values ​​in already existing records and add (INSERT) newly created records to the main table. The detailed text of these operators can be found in the section “Downloading Updates of Addressing Elements of FIAS to the fias_AddressObjects Table”.
Since the update process may introduce integrity violations, you can download records in which links to the next (NEXTID) or previous (PREVID) history record indicate a non-existing record.
This situation is very likely. Here, for example, data on the download results of the full database as of 10.10.2016.
Total violations:
- in NEXTID values
- in PREVID values
Therefore, before you perform updates to the main table, you must disable the restrictions action:
ALTER TABLE IF EXISTS fias_AddressObjects DROP CONSTRAINT IF EXISTS fk_fias_AddressObjects_AddressObjects_PREVID; ALTER TABLE IF EXISTS fias_AddressObjects DROP CONSTRAINT IF EXISTS fk_fias_AddressObjects_AddressObjects_NEXTID. After the updates to the main table have been completed, you must assign NULL values ​​to the NEXTID or PREVID fields where their values ​​indicate a non-existent record. For example:
UPDATE fias_AddressObjects ao SET NEXTID=NULL WHERE ao.NEXTID IS NOT NULL AND NOT EXISTS(SELECT * FROM fias_AddressObjects nao WHERE nao.AOID=ao.NEXTID); UPDATE fias_AddressObjects ao SET PREVID=NULL WHERE ao.PREVID IS NOT NULL AND NOT EXISTS(SELECT * FROM fias_AddressObjects pao WHERE pao.AOID=ao.PREVID); Before loading is complete, restore the restrictions and delete the temporary tables.
ATTACHMENT
Creating a table of FIAS addressing elements fias_AddressObjects
Spoiler header
BEGIN TRANSACTION; DROP TABLE IF EXISTS fias_AddressObjects; CREATE TABLE IF NOT EXISTS fias_AddressObjects( AOID VARCHAR(36) NOT NULL, PREVID VARCHAR(36) NULL, NEXTID VARCHAR(36) NULL, AOGUID VARCHAR(36) NOT NULL, PARENTGUID VARCHAR(36) NULL, FORMALNAME VARCHAR(120) NULL, SHORTNAME VARCHAR(10) NULL, OFFNAME VARCHAR(120) NULL, POSTALCODE VARCHAR(6) NULL, OKATO VARCHAR(11) NULL, OKTMO VARCHAR(11) NULL, AOLEVEL INTEGER NULL, REGIONCODE VARCHAR(2) NULL, AUTOCODE VARCHAR(1) NULL, AREACODE VARCHAR(3) NULL, CITYCODE VARCHAR(3) NULL, CTARCODE VARCHAR(3) NULL, PLACECODE VARCHAR(3) NULL, STREETCODE VARCHAR(4) NULL, EXTRCODE VARCHAR(4) NULL, SEXTCODE VARCHAR(3) NULL, CODE VARCHAR(17) NULL, PLAINCODE VARCHAR(15) NULL, CURRSTATUS INTEGER NULL, IFNSFL VARCHAR(4) NULL, TERRIFNSFL VARCHAR(4) NULL, IFNSUL VARCHAR(4) NULL, TERRIFNSUL VARCHAR(4) NULL, ACTSTATUS INTEGER NULL, CENTSTATUS INTEGER NULL, STARTDATE TIMESTAMP NULL, ENDDATE TIMESTAMP NULL, UPDATEDATE TIMESTAMP NULL, OPERSTATUS INTEGER NULL, LIVESTATUS INTEGER NULL, NORMDOC VARCHAR(36) NULL, CONSTRAINT XPKfias_AddressObjects PRIMARY KEY (AOID)) WITH (OIDS=False); CREATE INDEX XIE1fias_AddressObjects ON fias_AddressObjects(AOGUID); CREATE INDEX XIE2fias_AddressObjects ON fias_AddressObjects(PARENTGUID); CREATE UNIQUE INDEX XAK1fias_AddressObjects ON fias_AddressObjects(CODE); CREATE INDEX XIE3fias_AddressObjects ON fias_AddressObjects (REGIONCODE,AUTOCODE,AREACODE,CITYCODE,CTARCODE,PLACECODE,STREETCODE,EXTRCODE,SEXTCODE); COMMENT ON TABLE fias_AddressObjects IS 'ADDROBJ (Object) , .'; COMMENT ON COLUMN fias_AddressObjects.AOGUID IS ' '; COMMENT ON COLUMN fias_AddressObjects.FORMALNAME IS ' '; COMMENT ON COLUMN fias_AddressObjects.REGIONCODE IS ' '; COMMENT ON COLUMN fias_AddressObjects.AUTOCODE IS ' '; COMMENT ON COLUMN fias_AddressObjects.AREACODE IS ' '; COMMENT ON COLUMN fias_AddressObjects.CITYCODE IS ' '; COMMENT ON COLUMN fias_AddressObjects.CTARCODE IS ' '; COMMENT ON COLUMN fias_AddressObjects.PLACECODE IS ' '; COMMENT ON COLUMN fias_AddressObjects.STREETCODE IS ' '; COMMENT ON COLUMN fias_AddressObjects.EXTRCODE IS ' '; COMMENT ON COLUMN fias_AddressObjects.SEXTCODE IS ' '; COMMENT ON COLUMN fias_AddressObjects.OFFNAME IS ' '; COMMENT ON COLUMN fias_AddressObjects.POSTALCODE IS ' '; COMMENT ON COLUMN fias_AddressObjects.IFNSFL IS ' '; COMMENT ON COLUMN fias_AddressObjects.TERRIFNSFL IS ' '; COMMENT ON COLUMN fias_AddressObjects.IFNSUL IS ' '; COMMENT ON COLUMN fias_AddressObjects.TERRIFNSUL IS ' '; COMMENT ON COLUMN fias_AddressObjects.OKATO IS ''; COMMENT ON COLUMN fias_AddressObjects.OKTMO IS ''; COMMENT ON COLUMN fias_AddressObjects.UPDATEDATE IS ' () '; COMMENT ON COLUMN fias_AddressObjects.SHORTNAME IS ' '; COMMENT ON COLUMN fias_AddressObjects.AOLEVEL IS ' '; COMMENT ON COLUMN fias_AddressObjects.PARENTGUID IS ' '; COMMENT ON COLUMN fias_AddressObjects.AOID IS ' . '; COMMENT ON COLUMN fias_AddressObjects.PREVID IS ' '; COMMENT ON COLUMN fias_AddressObjects.NEXTID IS ' '; COMMENT ON COLUMN fias_AddressObjects.CODE IS ' 4.0.'; COMMENT ON COLUMN fias_AddressObjects.PLAINCODE IS ' 4.0 ( )'; COMMENT ON COLUMN fias_AddressObjects.ACTSTATUS IS ' . . . 0 – , 1 - '; COMMENT ON COLUMN fias_AddressObjects.CENTSTATUS IS ' '; COMMENT ON COLUMN fias_AddressObjects.OPERSTATUS IS ' – (. OperationStatus)'; COMMENT ON COLUMN fias_AddressObjects.LIVESTATUS IS ' : 0 – , 1 - '; COMMENT ON COLUMN fias_AddressObjects.CURRSTATUS IS ' 4 ( )'; COMMENT ON COLUMN fias_AddressObjects.STARTDATE IS ' '; COMMENT ON COLUMN fias_AddressObjects.ENDDATE IS ' '; COMMENT ON COLUMN fias_AddressObjects.NORMDOC IS ' '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT COUNT(*) FROM fias_AddressObjects; Uploading updates of FIAS addressing elements to the fias_AddressObjects table
Spoiler header
BEGIN TRANSACTION; /***********************************************/ /* */ /**********************************************/ DROP TABLE IF EXISTS fias_AddressObjects_temp; DROP TABLE IF EXISTS fias_DeletedAddressObjects_temp; CREATE TABLE fias_AddressObjects_temp AS SELECT * FROM fias_AddressObjects WHERE AOID IS NULL; CREATE TABLE fias_DeletedAddressObjects_temp AS SELECT * FROM fias_AddressObjects WHERE AOID IS NULL; /**************************************************************/ /* fias_AddressObjects_temp */ /* */ /*************************************************************/ COPY fias_AddressObjects_temp(ACTSTATUS,AOGUID,AOID,AOLEVEL,AREACODE, AUTOCODE,CENTSTATUS,CITYCODE,CODE,CURRSTATUS, ENDDATE,FORMALNAME,IFNSFL,IFNSUL,NEXTID, OFFNAME,OKATO,OKTMO,OPERSTATUS,PARENTGUID, PLACECODE,PLAINCODE,POSTALCODE,PREVID, REGIONCODE,SHORTNAME,STARTDATE,STREETCODE,TERRIFNSFL, TERRIFNSUL,UPDATEDATE,CTARCODE,EXTRCODE,SEXTCODE, LIVESTATUS,NORMDOC) FROM 'W:\Projects\Enisey GIS\DB\SourceData\ADDROBJ24_20161020.csv' WITH (FORMAT csv,DELIMITER ';', ENCODING 'WIN1251'); /**************************************************************/ /* fias_DeletedAddressObjects_Temp */ /* , */ /**************************************************************/ COPY fias_DeletedAddressObjects_Temp(ACTSTATUS,AOGUID,AOID,AOLEVEL,AREACODE, AUTOCODE,CENTSTATUS,CITYCODE,CODE,CURRSTATUS, ENDDATE,FORMALNAME,IFNSFL,IFNSUL,NEXTID, OFFNAME,OKATO,OKTMO,OPERSTATUS,PARENTGUID, PLACECODE,PLAINCODE,POSTALCODE,PREVID, REGIONCODE,SHORTNAME,STARTDATE,STREETCODE,TERRIFNSFL, TERRIFNSUL,UPDATEDATE,CTARCODE,EXTRCODE,SEXTCODE, LIVESTATUS,NORMDOC) FROM 'W:\Projects\Enisey GIS\DB\SourceData\DADDROBJ24_20161020.csv' WITH (FORMAT csv,DELIMITER ';', ENCODING 'WIN1251'); /**************************************************************/ /* CONSTRAINT. */ /**************************************************************/ ALTER TABLE IF EXISTS fias_AddressObjects DROP CONSTRAINT IF EXISTS fk_fias_AddressObjects_AddressObjects_PREVID; ALTER TABLE IF EXISTS fias_AddressObjects DROP CONSTRAINT IF EXISTS fk_fias_AddressObjects_AddressObjects_NEXTID; /**************************************************************/ /* fias_DeletedAddressObjects */ /* , fias_DeletedAddressObjects_Temp */ /**************************************************************/ UPDATE fias_AddressObjects ao SET ACTSTATUS=t.ACTSTATUS, AOGUID=t.AOGUID, AOLEVEL=t.AOLEVEL, AREACODE=t.AREACODE, AUTOCODE=t.AUTOCODE, CENTSTATUS=t.CENTSTATUS, CITYCODE=t.CITYCODE, CODE=t.CODE, CURRSTATUS=t.CURRSTATUS, ENDDATE=t.ENDDATE, FORMALNAME=t.FORMALNAME, IFNSFL=t.IFNSFL, IFNSUL=t.IFNSUL, NEXTID=t.NEXTID, OFFNAME=t.OFFNAME, OKATO=t.OKATO, OKTMO=t.OKTMO, OPERSTATUS=t.OPERSTATUS, PARENTGUID=t.PARENTGUID, PLACECODE=t.PLACECODE, PLAINCODE=t.PLAINCODE, POSTALCODE=t.POSTALCODE, PREVID=t.PREVID, REGIONCODE=t.REGIONCODE, SHORTNAME=t.SHORTNAME, STARTDATE=t.STARTDATE, STREETCODE=t.STREETCODE, TERRIFNSFL=t.TERRIFNSFL, TERRIFNSUL=t.TERRIFNSUL, UPDATEDATE=t.UPDATEDATE, CTARCODE=t.CTARCODE, EXTRCODE=t.EXTRCODE, SEXTCODE=t.SEXTCODE, LIVESTATUS=t.LIVESTATUS, NORMDOC=t.NORMDOC FROM fias_AddressObjects dao INNER JOIN fias_AddressObjects_temp t ON dao.AOID=t.AOID WHERE ao.AOID=dao.AOID; /**************************************************************/ /* fias_DeletedAddressObjects , */ /* fias_DeletedAddressObjects_Temp */ /**************************************************************/ DELETE FROM fias_AddressObjects ao WHERE EXISTS(SELECT 1 FROM fias_DeletedAddressObjects_Temp delao WHERE delao.AOID=ao.AOID); /**************************************************************/ /* fias_DeletedAddressObjects */ /* , fias_DeletedAddressObjects_Temp */ /* CODE LIKE '24%' , , */ /* */ /**************************************************************/ INSERT INTO fias_AddressObjects (ACTSTATUS,AOGUID,AOID,AOLEVEL,AREACODE,AUTOCODE,CENTSTATUS, CITYCODE,CODE,CURRSTATUS,ENDDATE,FORMALNAME,IFNSFL,IFNSUL, NEXTID,OFFNAME,OKATO,OKTMO,OPERSTATUS,PARENTGUID,PLACECODE, PLAINCODE, POSTALCODE,PREVID,REGIONCODE,SHORTNAME,STARTDATE, STREETCODE,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,CTARCODE,EXTRCODE, SEXTCODE,LIVESTATUS,NORMDOC) SELECT ACTSTATUS,AOGUID,AOID,AOLEVEL,AREACODE,AUTOCODE,CENTSTATUS, CITYCODE,CODE,CURRSTATUS,ENDDATE,FORMALNAME,IFNSFL,IFNSUL, NEXTID,OFFNAME,OKATO,OKTMO,OPERSTATUS,PARENTGUID,PLACECODE, PLAINCODE,POSTALCODE,PREVID,REGIONCODE,SHORTNAME,STARTDATE, STREETCODE,TERRIFNSFL,TERRIFNSUL,UPDATEDATE,CTARCODE,EXTRCODE, SEXTCODE,LIVESTATUS,NORMDOC FROM fias_AddressObjects_temp t WHERE CODE LIKE '24%' AND NOT EXISTS(SELECT * FROM fias_AddressObjects ao WHERE ao.AOID=t.AOID) ORDER BY CODE; /**************************************************************/ /* fias_AddressObjects. */ /* NULL */ /**************************************************************/ UPDATE fias_AddressObjects ao SET NEXTID=NULL WHERE ao.NEXTID IS NOT NULL AND NOT EXISTS(SELECT * FROM fias_AddressObjects nao WHERE nao.AOID=ao.NEXTID); UPDATE fias_AddressObjects ao SET PREVID=NULL WHERE ao.PREVID IS NOT NULL AND NOT EXISTS(SELECT * FROM fias_AddressObjects pao WHERE pao.AOID=ao.PREVID); /**************************************************************/ /* CONSTRAINT. */ /**************************************************************/ ALTER TABLE fias_AddressObjects ADD CONSTRAINT fk_fias_AddressObjects_AddressObjects_PREVID FOREIGN KEY(PREVID) REFERENCES fias_AddressObjects (AOID); ALTER TABLE fias_AddressObjects ADD CONSTRAINT fk_fias_AddressObjects_AddressObjects_NEXTID FOREIGN KEY(NEXTID) REFERENCES fias_AddressObjects (AOID); /**************************************************************/ /* . */ /**************************************************************/ DROP TABLE IF EXISTS fias_AddressObjects_temp; DROP TABLE IF EXISTS fias_DeletedAddressObjects_temp; --ROLLBACK TRANSACTION; COMMIUT TRANSACTION; SELECT COUNT(*) FROM fias_AddressObjects; Thanks for attention!
Source: https://habr.com/ru/post/316856/
All Articles