Адреси ФІАС в середовищі PostgreSQL. Частина 4. ЕПІЛОГ

Це четверта і остання частина статті, яка містить приклади створення таблиці fias_AddressObjects в базі даних під керуванням PostgreSQL, а також завантаження даних про адреснообразующих елементах ФІАС. Після цих дій можна самостійно випробувати функції, розглянуті в першою, другий, третій частинах, скопіювавши і виконавши скрипти на їх створення.


Повний текст статті складається складається з 4 частин.
У першій половині цієї частини статті викладені коментарі до реалізації сценаріїв створення таблиці адресообразующих елементів і заповнення її даними. У другій — вихідні тексти скриптів.
Тим із читачів, кого цікавлять тільки вихідні тексти, пропонуємо одразу перейти до
Додатком.

Епілог
З чого починати
Почати треба з відвідування офіційного сайту Федеральної Податкової Служби розділу «Федеральна інформаційна адресна система» (ФІАС) сторінки «Оновлення».
Завантажити на ваш комп'ютер останнє оновлення або повну базу ФІАС, якщо ви тільки починаєте працювати з ФІАС.
Перенести файл з архівом в робочу папку. Витягти файли з архіву і знайти файл ADDROBJ.DBF.
Далі передбачається, що завантажений архів файлів з оновленням ФІАС у форматі dbf.
Завантажений файл ADDROBJ.DBF перетворити до формату csv. Для цього відкрити вихідний файл за допомогою MS Excel та пересохранить його у форматі csv, не забувши при цьому видалити рядок з назвами полів записів. Далі перетворений до формату csv буде іменуватися «ADDROBJ24_20161020.csv», де 24 –код Красноярського краю, а 20161020 – дата завантаження файлу.
Створити таблицю fias_AddressObjects. Для цього можна скористатися скриптом наведеним у програму «Створення таблиці адресообразующих елементів ФІАС fias_AddressObjects».

Завантаження ADDROBJ24_20161020.csv в базу даних



Рис. 7 Безпосередня завантаження даних в таблицю fias_AddressObjects.

Безпосередньо завантажити дані з файлу ADDROBJ24_20161020.csv в таблицю fias_AddressObjects можна так, як показано на Рис. 7.
Але, на жаль, простий шлях не для нас.
По-перше, крім основного списку адресообразующих елементів поставляється ще й список адресообразующих елементів, які повинні бути видалені з основного списку (DADDROBJ.DBF);
По-друге, в основному списку присутні порушення зв'язності, наприклад, посилання, які нікуди не ведуть, тобто в списку немає елемента або запису з ідентифікатором, зазначеному в засланні. Тому не хочеться відновлювати помилки, які вже один раз виправлені.
По-третє, не хочеться кожен раз працювати з повним список адресообразующих елементів ФІАС, а лише завантажувати зміни, які з'являються на офіційному сайті Федеральної Податкової Служби два –три рази на тиждень.
Тому в процесі завантаження оновлення ФІАС використовується дві тимчасові таблиці:
  • fias_AddressObjects_temp – для оновлення основного списку адресообразующих елементів;
  • fias_DeletedAddressObjects_temp – для записів, які повинні бути видалені з основного списку.




Рис. 8. Попереднє завантаження адресообразующих елементів в тимчасові таблиці.

Далі дані таблиці fias_AddressObjects_temp служать для заміни (UPDATE) значень у вже існуючих записах та додавання (INSERT) знову створених записів основну таблицю. З докладним текстом цих операторів можна ознайомитися в розділі «Завантаження оновлень адресообразующих елементів ФІАС в таблицю fias_AddressObjects».
Так як в процесі оновлення можуть бути внесені порушення цілісності, можна завантажити записи, в яких посилання на наступну (NEXTID) або попередню (PREVID) запис історії вказують на інший запис.
Ця ситуація дуже ймовірна. Ось, наприклад, дані за результатами завантаження повної бази даних за станом на 10.10.2016 року.
Всього порушень:
  • у значеннях NEXTID
  • у значеннях PREVID
Тому перш ніж виконувати оновлення основної таблиці необхідно відключити дію обмежень:
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. 

Після того як оновлення основної таблиці виконані, необхідно присвоїти значення NULL полів NEXTID або PREVID там, де їх значення вказують на інший запис. Наприклад, так:
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);

Перед завершенням завантаження слід відновити обмеження і видалити тимчасові таблиці.
ДОДАТОК

Створення таблиці адресообразующих елементів ФІАС fias_AddressObjects
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,
A LIST 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.A list IS 'Закінчення дії записи';
COMMENT ON COLUMN fias_AddressObjects.NORMDOC IS 'Зовнішній ключ на нормативний документ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT COUNT(*) FROM fias_AddressObjects;



Завантаження оновлень адресообразующих елементів ФІАС в таблицю fias_AddressObjects
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,
A LIST,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,
A LIST,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,
A list=t.A list,
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 FROM 1 
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,A LIST,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,A LIST,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;

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.