How to become an author
Search
Write a publication
Pull to refresh

Адреса ФИАС в среде PostgreSQL. Часть 4. ЭПИЛОГ

Reading time10 min
Views15K
PostgreSQL*
Это четвертая и последняя часть статьи, которая содержит примеры создания таблицы 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,
	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;



Загрузка обновлений адресообразующих элементов ФИАС в таблицу 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,
                  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;


Спасибо за внимание!
Tags:
Hubs:
Total votes 7: ↑7 and ↓0+7
Comments5
15
Karma
0
Rating
Сергей Гладков @gladkovs

Разработчик баз данных

Send message

Articles

Show the best of all time

Your account

Sections

Information

Services

Support
© 2006–2024, Habr