Перенос базы данных КЛАДР на Firebird

Задача: реализовать подключение к федеральной адресной базе КЛАДР. Наши приложения работают с СУБД Firebird 2.5, поэтому было принято решение создать базу данных Firebird и импортировать данные из КЛАДР. В этой статье рассматриваются три вопроса: (1) как легко и (сравнительно) быстро импортировать данные из КЛАДР в Firebird, (2) как работать с получившейся базой из своего приложения и (3) как обращаться к этим данным из других баз данных, используя PSQL (хранимые процедуры или блоки).

Импорт базы данных КЛАДР в Firebird 2.5

База данных КЛАДР имеет одно неприятное свойство: она довольно часто меняется. Это значит, что нужно предусмотреть какие-то средства для автоматизации переноса данных. Писать отдельно приложение для импорта данных мне показалось нецелесообразным, поэтому я ограничился тем, что написал пару скриптов, которые можно запускать в бесплатной программе IBExpert, в редакторе скриптов.

Первый скрипт использует механизм ibeblock, который используется здесь только для импорта данных из формата DBF в базу Firebird. Этот скрипт будет работать только в IBExpert.

Надо отметить, что выполнение скрипта отнимает довольно много времени. Так, на моём рабочем компьютере с процессором Pentium Dual-Core E5200, 2 Гб памяти и обычным жёстким диском перенос данных с локально установленной СУБД Firebird отнимал приблизительно 1 час 45 минут. Размер получившейся базы (на начало 2014 года) примерно 570 Мб.

Второй скрипт написан на чистом PSQL и предназначен для создания индексов, хранимых процедур, описания таблиц и прочих необходимых метаданных. Он создаёт 4 хранимые процедуры, которые потом используются библиотекой с интерфейсом пользователя (см. ниже), но можно использовать их и по отдельности.

Все параметры (пути к базам и прочее) вписываются в текст скриптов в самом начале.

Скрипты совместимы со всеми версиями КЛАДР 2013 и начала 2014 года (надеюсь, что структура этой базы и дальше не будет сильно меняться).

Обновлено 27.10.2021 — в связи с тем, что Google очередной раз поменял настройки Google Drive, исправлен адрес для скачивания.

Скачать скрипты

Важные замечания относительно импортированных данных

0. Самое важное замечание: после выполнения всех скриптов не забудьте выполнить операции backup и restore! Иначе обращения к базе данных будут заметно «тормозить».

1. Второй скрипт совместим только с СУБД Firebird 2.5. В частности, хранимые процедуры используют регулярные выражения, которые работают только начиная с этой версии СУБД.

2. Импортируется не вся база, а только актуальные записи (там ещё есть большой блок неактуальных записей — не существующие более адреса, устаревшие названия городов и т. п.). Это кого-то может не устроить, тогда вполне можно переделать скрипты. Из этого следует, что не импортируется таблица КЛАДР ALTNAMES.DBF (устаревшие названия объектов).

3. В таблицах сохраняются 17-значные строковые коды для всех значений, но к ним добавляются целочисленные коды в отдельных столбцах (4 для основной таблицы и 5 для таблицы с улицами). Эти коды получаются в результате разбиения 17-значных кодов на отдельные составляющие (код региона, код города и т. д.). Так сделано для более быстрого и удобного поиска данных, чтобы в SQL-запросах не нужно было писать громоздкие и неэффективные конструкции вида

where substring(KLADR_CODE from 12 for 4) = :STREET_CODE

или даже использовать регулярные выражения. Возможно, имело бы смысл продолжить разбиение и для номеров домов, так как сейчас для разбора сложных номеров используется хранимая процедура с регулярными выражениями. Но это пока не реализовано.

4. База получается в кодировке Win-1251. Если нужно UTF-8 или какая-нибудь ещё кодировка, надо в скриптах заменить при помощи любого текстового редактора все вхождения подстроки WIN1251 на UTF8 (или что нужно).

5. Скрипт создаёт роль KLADR_RO с правами на чтение всех нужных таблиц, но учётную запись пользователя нужно создавать вручную, т. к. в Firebird она привязана к серверу, а не к базе данных.

Замечания о структуре КЛАДР

(Добавлено 05.08.2015 по мотивам обсуждения на форуме http://www.sql.ru/forum/1140940/eshhe-raz-pro-kladr)

В КЛАДРе иерархия составных частей адреса выглядит так:

Регион —› [ Район ] —› [ Город | Населённый пункт ] —› [ Улица ] —› [ Список домов ]

В квадратных скобках указаны необязательные элементы. Город и населённый пункт — вещи взаимоисключающие, и никогда в одном адресе одновременно не встречаются. Более того, сам смысл слов «город» и «населённый пункт» весьма размыт, иногда два одинаковых соседних небольших городка классифицируютсяя по-разному: один — как город, а другой — как населённый пункт.

Улица может «принадлежать» не только городу или другому населённому пункту, но и району, и даже целому региону. Типичный пример: Москва и Санкт-Петербург, — они в КЛАДРе считаются регионами, но содержат улицы, минуя все остальные уровни иерархии. Более того, никто не запрещает иметь внутри такого региона ещё и посёлки и города (пригороды), в которых тоже есть улицы, причём иногда даже с такими же названиями. Например, в Санкт-Петербурге (регион 78) есть Садовая улица. А также Садовая улица есть в 6 населённых пунктах и 5 городках, которые все входят в тот же 78-й регион. Не является уникальным внутри региона или района и код улицы.

Примеры:

1. запрос select * from KLADR_STREET where CODE1 = 78 and NAME = 'Садовая' возвращает сразу 11 Садовых улиц в разных местах, но в одном регионе Санкт-Петербург,
2. запрос вида select * from KLADR_STREET where CODE1 = 78 and CODE5 = 20 вернёт 22 разных улицы в том же регионе, включая одну из тех самых Садовых улиц.

Для полноты картины можно добавить, что улицы тоже являются необязательными элементами адреса, то есть существуют дома, которые «принадлежат» посёлку или городу, не имея даже улицы в составе адреса.

Вывод из всего перечисленного такой: в любом запросе для поиска объекта КЛАДР рекомендуется указывать все 4 (а для улиц 5) кода. Если нужны улицы, скажем, Москвы или Санкт-Петербурга, то нужно чётко указывать в условии запроса CODE2 = 0 and CODE3 = 0 and CODE4 = 0, не ограничиваясь указанием CODE1 и т. д.

Подключение к базе КЛАДР из PSQL

Одной из интересных особенностей СУБД Firebird 2.5 является возможность выполнять запросы к «внешней» базе данных из скриптов на языке PSQL. Иными словами, если импортировать БД КЛАДР, используя приведённые ранее скрипты, то можно извлекать из неё данные, используя запросы из других баз данных.

Скорее ради спортивного интереса, чем по острой необходимости я написал хранимую процедуру, которая получает все компоненты адреса, имея только 17-значный код, № дома и корпуса (плюс здесь в качестве входных параметров фигурируют путь к базе, имя пользователя, имя роли и пароль, но в реальной базе это можно оформить более элегантно). Эту процедуру нужно вставить в вашу базу данных (не в саму БД КЛАДР!).

Отмечу, что хотя эта процедура возвращает значение, вызывать её внутри SQL-запроса, который должен вывести сразу целый список адресов, не рекомендуется, поскольку такой запрос может выполняться очень медленно.

---

SET NAMES WIN1251;

CONNECT 'D:\TEST.FDB' USER 'SYSDBA' PASSWORD 'masterkey';

CREATE PROCEDURE GET_KLADR_ADDRESS(
    KLADR_CODE VARCHAR(17),
    HOUSE VARCHAR(10) NOT NULL,
    BUILDING VARCHAR(10),
    KLADR_DB_PATH VARCHAR(250),
    KLADR_USERNAME VARCHAR(250),
    KLADR_PASSWORD VARCHAR(250),
    KLADR_ROLE VARCHAR(250))
RETURNS (
    POSTCODE CHAR(6),
    STREET VARCHAR(40),
    STREET_ABBR VARCHAR(29),
    STREET_SHORT_ABBR VARCHAR(10),
    REGION VARCHAR(40) NOT NULL,
    REGION_ABBR VARCHAR(29),
    REGION_SHORT_ABBR VARCHAR(10),
    AREA VARCHAR(40),
    AREA_ABBR VARCHAR(29),
    AREA_SHORT_ABBR VARCHAR(10),
    CITY VARCHAR(40),
    CITY_ABBR VARCHAR(29),
    CITY_SHORT_ABBR VARCHAR(10),
    SUBURB VARCHAR(40),
    SUBURB_ABBR VARCHAR(29),
    SUBURB_SHORT_ABBR VARCHAR(10))
AS
declare variable CODE1 smallint;
declare variable CODE2 smallint;
declare variable CODE3 smallint;
declare variable CODE4 smallint;
declare variable CODE5 smallint;
begin
  CODE1 = substring(KLADR_CODE from 1 for 2);
  CODE2 = substring(KLADR_CODE from 3 for 3);
  CODE3 = substring(KLADR_CODE from 6 for 3);
  CODE4 = substring(KLADR_CODE from 9 for 3);
  CODE5 = substring(KLADR_CODE from 12 for 4);

  execute statement 'select
  S.NAME,
  S.SOCR,
  A.SOCRNAME
from
  KLADR_STREET S
inner join
  KLADR_ABBR A
on
  A.KOD_T_ST = S.KOD_T_ST
where
  S.CODE1 = ' || CODE1 || ' and
  S.CODE2 = ' || CODE2 || ' and
  S.CODE3 = ' || CODE3 || ' and
  S.CODE4 = ' || CODE4 || ' and
  S.CODE5 = ' || CODE5
      on external DATA SOURCE :KLADR_DB_PATH
      as user :KLADR_USERNAME password :KLADR_PASSWORD role KLADR_ROLE
      into :STREET, :STREET_SHORT_ABBR, :STREET_ABBR;

  execute statement 'select
  RESULT
from
  GET_KLADR_POSTCODE(' || CODE1 || ',' || CODE2 || ',' || CODE3 || ',' ||
    CODE4 || ',' || CODE5 || ',' || '''' || HOUSE || BUILDING || ''')'
      on external DATA SOURCE :KLADR_DB_PATH
      as user :KLADR_USERNAME password :KLADR_PASSWORD role KLADR_ROLE
      into :POSTCODE;

  execute statement 'select
  K.NAME,
  K.SOCR,
  A.SOCRNAME
from
  KLADR K
inner join
  KLADR_ABBR A
on
  A.KOD_T_ST = K.KOD_T_ST
where
  K.CODE1 = ' || CODE1 || ' and
  K.CODE2 = 0 and
  K.CODE3 = 0 and
  K.CODE4 = 0'
      on external DATA SOURCE :KLADR_DB_PATH
      as user :KLADR_USERNAME password :KLADR_PASSWORD role KLADR_ROLE
      into :REGION, :REGION_ABBR, :REGION_SHORT_ABBR;

  if (CODE2 <> 0) then
    execute statement 'select
  K.NAME,
  K.SOCR,
  A.SOCRNAME
from
  KLADR K
inner join
  KLADR_ABBR A
on
  A.KOD_T_ST = K.KOD_T_ST
where
  K.CODE1 = ' || CODE1 || ' and
  K.CODE2 = ' || CODE2 || ' and
  K.CODE3 = 0 and
  K.CODE4 = 0'
        on external DATA SOURCE :KLADR_DB_PATH
        as user :KLADR_USERNAME password :KLADR_PASSWORD role KLADR_ROLE
        into :AREA, :AREA_ABBR, :AREA_SHORT_ABBR;

  if (CODE3 <> 0) then
    execute statement 'select
  K.NAME,
  K.SOCR,
  A.SOCRNAME
from
  KLADR K
inner join
  KLADR_ABBR A
on
  A.KOD_T_ST = K.KOD_T_ST
where
  K.CODE1 = ' || CODE1 || ' and
  K.CODE2 = ' || CODE2 || ' and
  K.CODE3 = ' || CODE3 || ' and
  K.CODE4 = 0'
        on external DATA SOURCE :KLADR_DB_PATH
        as user :KLADR_USERNAME password :KLADR_PASSWORD role KLADR_ROLE
        into :CITY, :CITY_ABBR, :CITY_SHORT_ABBR;

  if (CODE4 <> 0) then
    execute statement 'select
  K.NAME,
  K.SOCR,
  A.SOCRNAME
from
  KLADR K
inner join
  KLADR_ABBR A
on
  A.KOD_T_ST = K.KOD_T_ST
where
  K.CODE1 = ' || CODE1 || ' and
  K.CODE2 = ' || CODE2 || ' and
  K.CODE3 = ' || CODE3 || ' and
  K.CODE4 = ' || CODE4
        on external DATA SOURCE :KLADR_DB_PATH
        as user :KLADR_USERNAME password :KLADR_PASSWORD role KLADR_ROLE
        into :SUBURB, :SUBURB_ABBR, :SUBURB_SHORT_ABBR;

  suspend;
  when any do
  begin
    POSTCODE = null;
    STREET = null;
    STREET_ABBR = null;
    STREET_SHORT_ABBR = null;
    REGION = null;
    REGION_ABBR = null;
    REGION_SHORT_ABBR = null;
    AREA = null;
    AREA_ABBR = null;
    AREA_SHORT_ABBR = null;
    CITY = null;
    CITY_ABBR = null;
    CITY_SHORT_ABBR = null;
    SUBURB = null;
    SUBURB_ABBR = null;
    SUBURB_SHORT_ABBR = null;
    suspend;
  end
end 

---

Интерфейс пользователя (DLL)

Для подключения к созданной базе данных используется динамическая библиотека (DLL). Такое решение продиктовано обстоятельствами: у нас имеется несколько приложений, все они должны работать с базой адресов. Проще было один раз сделать такую DLL и вызывать по мере необходимости, чем копировать один и тот же код в разные приложения.

Библиотека экспортирует функцию, которая может получать на вход 17-значный код, номер дома, корпуса и квартиры (всё это необязательно), а возвращает полный адрес, включающий почтовый индекс, полные и сокращённые названия частей адреса и т. д. При вызове эта функция выводит на экран окно для ввода адреса. Выглядит оно следующим образом.

Поскольку наши студенты преимущественно живут в Санкт-Петербурге и его окрестностях, в окне было отдельно выделено такое отсутствующее в КЛАДР понятие, как группа адресов. Всего таких групп сделано четыре: Санкт-Петербург, пригороды Санкт-Петербурга, Ленинградская область и другие регионы. Для других приложений, которые не нуждаются в такой географической привязке, это можно достаточно легко исправить. В перспективе я думал вынести этот список во внешний файл, чтобы для каждой программы можно было настраивать отдельно, а где-то этот список вообще убирать. Это не так сложно сделать, просто сейчас нет времени и для наших проектов это неактуально.

Если функция получила на вход какие-нибудь фрагменты адреса (например, код), то соответствующие поля ввода уже будут заполнены.

Существует три способа ввести адрес. Во-первых, можно начать с группы, региона и т. д. (т. е. последовательно двигаться сверху вниз). Если все поля будут заполнены правильно (кроме квартиры — она в БД КЛАДР отсутствует), в правом верхнем углу появится почтовый индекс. Во-вторых, можно начать с индекса. В этом случае большинство полей будет заполнено автоматически сразу — как правило, нужно только уточнить улицу и дом. И, наконец, можно воспользоваться быстрым поиском.

Для быстрого поиска адреса по части строки имеется отдельное окошко. Его работу легче всего понять из рисунка ниже.

Нужно ввести часть названия города, посёлка и т. д., и тут же будет выведен список всех объектов, содержащих эту подстроку в названии. Правда, это работает только до уровня населённого пункта: улицы в поиске не участвуют. Двойной щелчок на нужной строке — и все поля ввода в окне будут заполнены соответствующими значениями.

Дом можно ввести обычным образом или посмотреть выпадающий список вариантов. Последнее актуально, если помимо номера дома имеется ещё и корпус, № строения и другие варианты.

Пока все компоненты адреса не будут заполнены, на кнопке «ОК» будет виден треугольный значок. При наведении мыши появится подсказка с подробностями.

Ссылка для скачивания

(Добавлено 08.10.2015)

Скачать DLL и демонстрационную программу (с исходным текстом демонстрационной программы на Delphi 7) можно по этой ссылке. В принципе, этого достаточно для подключения DLL к своему проекту.

Только нужно на всякий случай уточнить, что это 32-битная DLL, которая экспортирует одну-единственную функцию. Написано было на Delphi, но, полагаю, без проблем может подключаться к проекту на любом языке (там используется соглашение stdcall и простейшие типы данных). Библиотека не для .NET (т.е. unmanaged).

Если вас заинтересовала эта библиотека, пишите — ссылка на профиль автора внизу страницы. Если кому-нибудь нужно, могу даже прислать исходные тексты библиотеки.