Это четвертая и последняя часть статьи, которая содержит примеры создания таблицы 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;ссылка на оригинал статьи https://habrahabr.ru/post/316856/
Добавить комментарий