Address FIAS in the PostgreSQL environment. Part 4. EPILOGUE

This is the fourth and last part of the article, which contains examples of creating tables fias_AddressObjects in the database under PostgreSQL, and load data into it about adrenokrome elements of FIAS. After these steps, you can independently test the functions described in first, second, the third parts, copying and running scripts on their creation.



The full text of the article is composed of 4 parts. In the first half of this part of the article provides comments to the implementation of the scripts to create tables adresovana elements and populate it with data. The second source scripts.

Those readers who are only interested in the source code offered at once to go to
Application.

the

epilogue


the

where to start


You should start with visiting the official website of the Federal Tax Service of the section "Federal information address system" (FIAS) of the page Updates.

Upload on your PC latest update or the full database FIAS, if you are just beginning to work with FIAS.

Transfer the file from the archive into a working folder. Extract the files and find the file ADDROBJ.DBF.
it is Further assumed that the downloaded archive file with the update of the FIAS in the dbf format.
The downloaded file ADDROBJ.DBF convert to csv format. To do this, open the source file using MS Excel and resave it in csv format, not forgetting to delete the row with the field names of the records. Further converted to the csv format will be referred to as "ADDROBJ24_20161020.csv", where 24 code of Krasnoyarsk territory, and 20161020 – upload date of the file.

Create table fias_AddressObjects. You can use the script shown in application "Create table adresovana elements FIAS fias_AddressObjects".

the

Download ADDROBJ24_20161020.csv to database





the Fig. 7 Direct download of data into a table fias_AddressObjects.

Directly upload data from a file ADDROBJ24_20161020.csv to table fias_AddressObjects possible as shown in Fig. 7.

But, unfortunately, the easy path is not for us.

First, in addition to the main list adresovana elements comes a list adresovana elements which must be removed from the main list (DADDROBJ.DBF);

Secondly, in the main list, there are disorders of connectivity, for example, links that lead nowhere, i.e. is not in the list item or record ID specified in the link. So I don't want to repair error that once corrected.

Third, don't want to work with a full list adresovany elements of FIAS, but only upload changes that appear on the official website of the Federal Tax Service two or three times a week.

So in the process of downloading the updates FIAS uses two temporary tables:

the
    the
  • fias_AddressObjects_temp – updates main adresovana list of elements;
    • the
  • fias_DeletedAddressObjects_temp – for records that should be removed from the main list.



the Fig. 8. Preload adresovana elements into temporary table.

Next, the data table fias_AddressObjects_temp are used to replace (UPDATE) the values in existing records and add (INSERT) the newly created records in the main table. The detailed text of these statements can be found in the section "Downloading updates adresovana elements of FIAS in table fias_AddressObjects".

As the upgrade process may be made by integrity violations, you can load the records in which the references to the next (NEXTID) or previous (PREVID) history record indicates a non-existent record.
This situation is very likely. For example, data on results download the full database as of 10.10.2016 year.

Total violations:

the
    the
  • values NEXTID
    • the
  • values PREVID

Therefore, before performing the update of the main table you need to disable a constraint:

the 
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 updating the main table is made, you need to assign NULL values to fields PREVID or NEXTID where their values indicate a non-existent record. For example:

the 
UPDATE fias_AddressObjects ao SET NEXTID=NULL
WHERE ao.NEXTID IS NOT NULL AND NOT EXISTS(SELECT * FROM fias_AddressObjects WHERE nao nao.AOID=ao.NEXTID);
UPDATE fias_AddressObjects ao SET PREVID=NULL 
WHERE ao.PREVID IS NOT NULL AND NOT EXISTS(SELECT * FROM fias_AddressObjects WHERE pao pao.AOID=ao.PREVID);

Before the download is complete, you should restore the constraints and delete temporary tables.

the

the APPLICATION


the

Create table adresovany elements FIAS fias_AddressObjects


the 
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,
XPKfias_AddressObjects CONSTRAINT 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) contains the codes, names and types adresovana elements.';
COMMENT ON COLUMN fias_AddressObjects.AOGUID IS 'globally unique identifier adresource element';
COMMENT ON COLUMN fias_AddressObjects.FORMALNAME IS the 'Formal name';
COMMENT ON COLUMN fias_AddressObjects.REGIONCODE IS 'region Code';
COMMENT ON COLUMN fias_AddressObjects.AUTOCODE IS 'Code authority';
COMMENT ON COLUMN fias_AddressObjects.AREACODE IS 'area Code';
COMMENT ON COLUMN fias_AddressObjects.CITYCODE IS 'area Code';
COMMENT ON COLUMN fias_AddressObjects.CTARCODE IS 'local area Code';
COMMENT ON COLUMN fias_AddressObjects.PLACECODE IS 'location Code';
COMMENT ON COLUMN fias_AddressObjects.STREETCODE IS 'street Code';
COMMENT ON COLUMN fias_AddressObjects.EXTRCODE IS 'Code adresource additional element';
COMMENT ON COLUMN fias_AddressObjects.SEXTCODE IS 'additional Code subordinate adresource element';
COMMENT ON COLUMN fias_AddressObjects.OFFNAME IS 'Official designation';
COMMENT ON COLUMN fias_AddressObjects.POSTALCODE IS 'postcode';
COMMENT ON COLUMN fias_AddressObjects.IFNSFL IS 'Code on FL';
COMMENT ON COLUMN fias_AddressObjects.TERRIFNSFL IS 'Code territorial area, on FL';
COMMENT ON COLUMN fias_AddressObjects.IFNSUL IS 'Code on Yul';
COMMENT ON COLUMN fias_AddressObjects.TERRIFNSUL IS 'Code territorial area on Yul';
COMMENT ON COLUMN fias_AddressObjects.OKATO IS 'OKATO';
COMMENT ON COLUMN fias_AddressObjects.OKTMO IS 'OKTMO';
COMMENT ON COLUMN fias_AddressObjects.UPDATEDATE IS 'date of entry (update) the records';
COMMENT ON COLUMN fias_AddressObjects.SHORTNAME IS a Short name of element type';
COMMENT ON COLUMN fias_AddressObjects.AOLEVEL IS 'Level adresource element ';
COMMENT ON COLUMN fias_AddressObjects.PARENTGUID IS 'the element ID of the parent element';
COMMENT ON COLUMN fias_AddressObjects.AOID IS 'Unique identifier of the entry. Key field';
COMMENT ON COLUMN fias_AddressObjects.PREVID IS the ID of the record linking to the previous historical record';
COMMENT ON COLUMN fias_AddressObjects.NEXTID IS the ID of the record linking with the subsequent historical record';
COMMENT ON COLUMN fias_AddressObjects.CODE IS 'Code adresource element in one line with the actuality of KLADR 4.0.'; 
COMMENT ON COLUMN fias_AddressObjects.PLAINCODE IS 'Code adresource item from KLADR 4.0 in one line with no sign of any relevance (last two digits)';
COMMENT ON COLUMN fias_AddressObjects.ACTSTATUS IS 'the Status of the relevance adresource element of the FIAS. The current address for the current date. Usually the last entry about adresource element. 0 – Not relevant, 1 - Relevant';

COMMENT ON COLUMN fias_AddressObjects.OPERSTATUS IS 'the action Status on the record the reason for the appearance of record (see table description OperationStatus)';
COMMENT ON COLUMN fias_AddressObjects.LIVESTATUS IS 'a Sign of the current adresource item: 0 – invalid address element, 1 - active';
COMMENT ON COLUMN fias_AddressObjects.CURRSTATUS IS 'the Status of the relevance of KLADR 4 (last two digits in the code)';
COMMENT ON COLUMN fias_AddressObjects.STARTDATE IS 'Start record action';
COMMENT ON COLUMN fias_AddressObjects.ENDDATE IS 'End of record';
COMMENT ON COLUMN fias_AddressObjects.NORMDOC IS 'Foreign key to the regulatory document';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT COUNT(*) FROM fias_AddressObjects;

the

download the updates adresovana elements of FIAS in table fias_AddressObjects


the 
BEGIN TRANSACTION;
/***********************************************/
/* Create temp tables */
/**********************************************/
DROP TABLE IF EXISTS fias_AddressObjects_temp;
DROP TABLE IF EXISTS fias_DeletedAddressObjects_temp;

Fias_AddressObjects_temp CREATE TABLE AS SELECT * FROM fias_AddressObjects WHERE AOID IS NULL;
Fias_DeletedAddressObjects_temp CREATE TABLE AS SELECT * FROM fias_AddressObjects WHERE AOID IS NULL;
/**************************************************************/
/* Load into a temporary table fias_AddressObjects_temp changes */
/* the main list elements adresovana FIAS */
/*************************************************************/

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');
/**************************************************************/
/* Load into a temporary table fias_DeletedAddressObjects_Temp */
/* entries that must be removed from osnovno list */
/**************************************************************/

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');
/**************************************************************/
/* Disable constraints 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;
/**************************************************************/
/* Update existing records basic list fias_DeletedAddressObjects */ 
/* records the data update from the temporary table 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 t ON fias_AddressObjects_temp dao.AOID=t.AOID
WHERE ao.AOID=dao.AOID; 

/**************************************************************/
/* Delete existing records fias_DeletedAddressObjects the main list of records */
/* based on the data from the temporary table fias_DeletedAddressObjects_Temp */
/**************************************************************/

DELETE FROM fias_AddressObjects ao WHERE EXISTS(SELECT 1 FROM 
fias_DeletedAddressObjects_Temp WHERE delao delao.AOID=ao.AOID);

/**************************************************************/
/* Add the newly received record to the main list, fias_DeletedAddressObjects */
/* records the data from the temporary table fias_DeletedAddressObjects_Temp */
/* Condition CODE LIKE '24%' means that only the selected entries */
/* Krasnoyarsk Krai */
/**************************************************************/

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) 

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;
/**************************************************************/
/* Fix integrity violations fias_AddressObjects. */
/* Non-empty references to previous and subsequent records are replaced with 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); 
/**************************************************************/
/* Restore limits CONSTRAINT. */
/**************************************************************/

ALTER TABLE fias_AddressObjects 
Fk_fias_AddressObjects_AddressObjects_previd ADD CONSTRAINT FOREIGN KEY(PREVID)
REFERENCES fias_AddressObjects (AOID);
ALTER TABLE fias_AddressObjects
Fk_fias_AddressObjects_AddressObjects_nextid ADD CONSTRAINT FOREIGN KEY(NEXTID)
REFERENCES fias_AddressObjects (AOID);

/**************************************************************/
/* Delete temporary tables from the database. */
/**************************************************************/

DROP TABLE IF EXISTS fias_AddressObjects_temp;
DROP TABLE IF EXISTS fias_DeletedAddressObjects_temp;
--ROLLBACK TRANSACTION;
COMMIUT TRANSACTION;
SELECT COUNT(*) FROM fias_AddressObjects;

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

Комментарии

Отправить комментарий

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

Car navigation in detail

Изображение
This article is for those who are going in the near future to buy a car Navigator, but lacks information. For example, a typical Navigator Lexand Si-515 Pro HD I will try to elaborate what is now proposed on the market such devices. Also describe the impressions of three of the tested navigation systems, one of which passed in June, about three thousand kilometres through two countries. And at the end of the article will result in a large comparative table 23 current models of navigators Lexand, to show what can be possible in these devices. The choice of now is large enough. Prices and performance, thanks to competition, differ not so strongly. Typically, when buying are interested in the reliability and usability of the device. The functionality and relevance of the cards of 100% define the installed navigation system. It is therefore very important to be able to update it easily, and even better if you can run several different. the Characteristics and sp...
Далее...

PostgreSQL: Analytics for DBA

Many users of PostgreSQL know that the server at the time of their work collects a variety of statistics, but not everyone knows that it is useful to analyze and how to extract it. This small Toolkit collected some useful queries that give some idea about how to use this "hidden knowledge", which constantly accumulates. These queries can be used to monitor the status of PostgreSQL (manually or with plugins for monitoring systems like Nagios, Cacti or Zabbix), to search for bottlenecks in the server and many other similar tasks. Remember that this is only the tip of the iceberg; in the documentation you can find descriptions of several dozen system ideas that can also be useful for the PostgreSQL administrator. For correct work need to enable Toolkit options stats_block_level and stats_row_level in postgresql.conf and set the parameter of stats_reset_on_server_start in its sole discretion. If each time you restart the PostgreSQL server you change some important configu...
Далее...