© 2007 Андрей Дмитриев
|
- А вы думаете, это нам поможет? - Я думаю, это нам не помешает! Из "Одесских джентльменов" |
На сегодняшний день из всех официальных классификаторов и справочников, применяемых в РФ, на мой взгляд, самым неоднозначным как по применению, так и по содержанию, является так обзываемый КЛАДР (классификатор адресов). Придуманный и ведомый совместно налоговой и почтовой службами страны, он должен был по видимому служить для облегчения работы с адресными объектами в прикладных задачах. Да что-то, похоже, не задалось. Удобство его применения сведено к минимуму, про наполнение и актуальность тоже много нелестных слов. Но вот раз нет другого источника адресной информации – приходится работать с тем, что имеем. И так, о чём это я? Ах, да...
В последнее время достаточно часто стал натыкаться на просторах Интернета на вопрос типа: "Люди, а кто как использует в своих задачах КЛАДР?" А так как тема сия меня очень интересует (периодически занимаюсь налоговой отчётностью, да вот сейчас работаю в сфере учёта имущества), то внимательно следил за ответами. Да вот только ничего интересного в ответах не появилось: то ли народ в эту сторону особо не думал, то ли говорить никто не хочет, то ли и правда в этом вопросе всё так запущено. Посему решил поделиться своими наработками – благо чуток свободного времени образовалось. Представленное решение, возможно, не бесспорное, но как это работает меня (да и пользователей) вполне устраивает. Чуть-чуть общих слов про это дело.
Суть работы с КЛАДРом проста – составить необходимую адресную строку на основе информации, заложенной в базе данных. Ничего вроде сложного, но хотелось бы делать это быстро (по крайней мере, не медленнее ручного ввода) и удобно (удобство – понятие достаточно неопределённое, но почему-то легко ощущаемое, когда оно есть). Поэтому при разработке части задачи, связанной с составлением адреса, в первую очередь упор делался на создание удобного интерфейса для пользователя. Ну и как всегда оказалось, что сделать что-нибудь приемлемое, можно только решая задачу комплексно. В результате, подстраивая и меняя друг под друга то интерфейсные дела, то структуру хранения данных, то методы получения информации из базы, я пришёл к некоему устраивающему меня решению. Кстати, долго шёл, года три получится, проверил кучу вариантов, поэтому в спорных местах, на вопрос – А почему так? – могу только сказать – Так было удобнее в целом для задачи. А так как интерфейс рисовался на Delphi, решил уместным сбросить статью именно сюда.
Итак, чем делиться буду. Сперва своими мыслями по поводу структуры данных КЛАДР. Поругать чужое – без этого никак. Вторым этапом потребуется пояснить, почему я перекроил эту самую структуру (а без этого не обошлось), и что из этого получилось. Третьим делом разрисую прелести выбранного интерфейса, с комментариями и картинками. Четвёртый шаг – выдам некую тестовую утилиту, которая, являясь вполне самостоятельной программой, продемонстрирует всё изложенное. В принципе желающие могут начать просмотр с последнего пункта, с демонстрации. А то потом вдруг и читать будет не интересно.
Теперь, что и как переносим. Первым делам создаём набор требуемых таблиц, несколько отличающийся по структуре от оригинального (комментарии по ходу). Первая переносимая таблица – SOCRBASE.DBF. Честно говоря, не понял, зачем она нужна, если наименования типов адресов имеются в основных таблицах KLADR.DBF и STREET.DBF. По-хорошему, вроде должно быть или там, или там. Единственная идея - ради описательного поля (полного названия типа), которое никто, наверное, и не использует. Поэтому создаём из "ненужной" таблицы типов "нужный и правильный" справочник, а таблицы адресов свяжем с ним внешним ключом.
create table KLADR_ABBREV (
ABBREV_LEVEL smallint not null,
ABBREV_ID smallint not null,
ABBREV_POS smallint default 0 not null,
ABBREV_NAME as varchar(40) not null,
ABBREV_SHNAME as varchar(10) not null
);
alter table KLADR_ABBREV add constraint PK_KLADR_ABBREV primary key (ABBREV_LEVEL, ABBREV_ID);
Комментарии.
Далее создаём таблицу адресов. И сразу комментарии. Если сравнить оригинальные таблицы KLADR.DBF и STREET.DBF, то бросается в глаза, что их структура практически не отличается. Мысль объединить эти таблицы, честно говоря, была не первой, но, в конце концов, она почти вынужденно возникла из таких соображений. Во-первых, я всё таки в своих задачах стараюсь хранить адресные коды (где вместо, где вместе, с полным написанием адреса), получая определённые преимущества в обработке данных. Во-вторых, в отличие от налоговой отчётности, в других областях, где может применяться КЛАДР, адрес часто не имеет полного написания. Т.е. вполне обычно иметь ссылку на некий населённый пункт, как на адрес, плюс некое произвольное описание расположения объекта. А вполне может быть и полный адрес с улицей и прочими причиндалами. В обоих этих случаях заманчиво иметь возможность ссылаться на одну таблицу адресных объектов, а не на разные. Т.е. имеем единую точку входа в справочник адресных объектов. И при этом никаких null-значений для внешних ключей. Вот из этих соображений таблица адресов была сделана единой, и пока я считаю это решение весьма правильным.
Ещё один комментарий касается структуры связей между адресными объектами. Связи эти есть и это дерево. Причём дерево с нежёсткой иерархией. Т.е. уровень родителя в общем случае не предопределён. Региональный уровень (1) запросто цепляется с уровнем улиц (5), а к уровню района (2), как правило, цепляется уровень посёлков (4). Код родительского адреса "зашит" в самой структуре кода. Это показалось мне неправильным и трудоёмким для последующей обработки, поэтому я разношу код по двум полям: непосредственно ключевому и полю-ссылке на родителя. Таким образом, создаётся древовидная структура в хорошо известном виде. Функция, которая этим занимается, приведена ниже.
{********************************************************
* Возвращает код региона (если всё правильно распарсено) или 0
* sa – код из dbf файла
* adr, pro – получаемые коды адресной строки и её родителя
* lvl, suf – код уровня адреса и поля модификации
********************************************************}
function TFrmMain.DecodeAdrCode(const sa: string;
var adr, pro: int64; var lvl, suf: integer): integer;
var
cc1, cc2, cc3, cc4, cc5, ccd, ln, pos: integer;
begin
Result := 0;
ln := Length(sa);
if (ln <> 13) and (ln <> 17) then Exit;
pos := 1;
try
cc1 := StrToInt(Copy(sa, 1, 2)); // Регион
cc2 := StrToInt(Copy(sa, 3, 3)); // Район
cc3 := StrToInt(Copy(sa, 6, 3)); // Город
cc4 := StrToInt(Copy(sa, 9, 3)); // Нас пункт
if ln = 13 then
begin
cc5 := 0; pos := 12;
end else begin
cc5 := StrToInt(Copy(sa, 12, 4)); // Улица
pos := 16;
end;
ccd := StrToInt(Copy(sa, pos, 2)); // Доп. индекс
except
cc1 := 0;
end;
if cc1 = 0 then
begin
adr := 0; pro := 0; lvl := 0; suf := 0; Exit;
end;
// Вычисление кода родителя
if (cc5 > 0) then
begin
lvl := 5; pro := cc2 * 10000000000 + cc3 * 10000000 + cc4 * 10000;
end else
if cc4 > 0 then
begin
lvl := 4; pro := cc2 * 10000000000 + cc3 * 10000000;
end else
if cc3 > 0 then
begin
lvl := 3; pro := cc2 * 10000000000;
end else
if cc2 > 0 then
begin
lvl := 2; pro := 0;
end else begin
lvl := 1; pro := 0;
end;
adr := cc2 * 10000000000 + cc3 * 10000000 + cc4 * 10000 + cc5;
suf := ccd;
Result := cc1;
end;
А теперь о самой таблице. Хотя нет, всё-таки не об одной. Так как я рассказываю ещё и о своих изысканиях, то продемонстрирую два варианта хранения данных по КЛАДР. При первом варианте в адресной таблице хранится как информация, так и связи между адресными объектами. В этом случае речь может идти только о действующих записях (переносятся записи с полем модификации в адресном коде равным 0), иначе возникают проблемы ссылочной целостности данных. Такая структура более понятна и проста, но позволяет хранить только актуальный срез данных, что вполне достаточно для многих задач. Как хранить историю изменения адресов должны, понятно, придумать авторы КЛАДР (если конечно оно им нужно) или другие заинтересованные структуры. ГИС-Ассоциация к примеру концепцию адресного реестра РФ собирается создать, подключив кучу солидных структур. В общем, работа где-то кипит, а ждать не можется. Поэтому экспериментирую уже сейчас. На мой взгляд, в общих чертах это должно быть так: отдельная таблица для адресных объектов во всех их версиях, на которую и идут ссылки из прикладных задач. И отдельная таблица(ы) по связям между адресными объектами (кросс-таблица). Вопросов тут возникает масса, но для пробы я сделал и такой вариант хранения КЛАДР. Для себя я определил следующие термины к этим вариантам: первый вариант – "актуальная база" (окончание в имени таблиц _2), второй – "база с историей" (окончание _20). Этими словами и буду оперировать. Теперь сами варианты.
Актуальная база:
create TABLE KLADR_ADDRESS_2 (
ADDRESSREGION_ID smallint not null,
ADDRESS_ID bigint not null,
ADDRESS_PAR bigint not null,
ADDRESS_NAME varchar(40) not null,
ABBREV_LEVEL smallint not null,
ABBREV_ID smallint not null,
ABBREV_POS smallint default -1 not null,
...
);
alter table KLADR_ADDRESS_2 add constraint PK_KLADR_ADDRESS_2
primary key (ADDRESSREGION_ID, ADDRESS_ID);
alter table KLADR_ADDRESS_2 add constraint FK_KLADR_ADDRESS_2_1
foreign key (ABBREV_LEVEL, ABBREV_ID)
references KLADR_ABBREV (ABBREV_LEVEL, ABBREV_ID);
alter table KLADR_ADDRESS_2 add constraint FK_KLADR_ADDRESS_2_2
foreign key (ADDRESSREGION_ID, ADDRESS_PAR)
references KLADR_ADDRESS_2 (ADDRESSREGION_ID, ADDRESS_ID);
База с историей:
create table KLADR_ADDRESS_20 (
ADDRESSREGION_ID smallint not null,
ADDRESS_ID bigint not null,
ADDRESS_VERSION smallint default 0 not null,
ADDRESS_ACTUAL smallint default 0 not null,
ADDRESS_NAME varchar(40) not null,
ABBREV_LEVEL smallint not null,
ABBREV_ID smallint not null,
ABBREV_POS smallint default -1 not null,
...
);
create table KLADR_ADDRESS_20_DEPEND (
ADDRESSREGION_ID smallint not null,
ADDRESS_ID bigint not null,
ADDRESS_VERSION smallint default 0 not null,
ADDRESS_PAR bigint not null,
ADDRESS_PARVER smallint default 0 not null
);
alter table KLADR_ADDRESS_20 add constraint PK_KLADR_ADDRESS_20
primary key (ADDRESSREGION_ID, ADDRESS_ID, ADDRESS_VERSION);
alter table KLADR_ADDRESS_20 add constraint FK_KLADR_ADDRESS_20_1
foreign key (ABBREV_LEVEL, ABBREV_ID)
references KLADR_ABBREV (ABBREV_LEVEL, ABBREV_ID);
alter table KLADR_ADDRESS_20_DEPEND
add constraint FK_KLADR_ADDRESS_20_DEPEND_1
foreign key (ADDRESSREGION_ID, ADDRESS_ID, ADDRESS_VERSION)
references KLADR_ADDRESS_20_DEPEND (ADDRESSREGION_ID, ADDRESS_ID, ADDRESS_VERSION);
alter table KLADR_ADDRESS_20_DEPEND
add constraint FK_KLADR_ADDRESS_20_DEPEND_2
foreign key (ADDRESSREGION_ID, ADDRESS_CHILD, ADDRESS_CHILDVERS)
references KLADR_ADDRESS_20 (ADDRESSREGION_ID, ADDRESS_ID, ADDRESS_VERSION);
Комментарии.
Ещё некоторые замечания по базе. Несколько удивила заметная разница в скорости доступа к данным через прямой запрос и через представление, созданное на базе этого же запроса (это касается Firebird, про Oracle что-то уже не помню). Может это только у меня так, но для проверки сделал отдельные представления под требуемые выборки. Разбираюсь. В демо-программе можно выбрать, каким образом получать данные.
Для формирования итоговой адресной строки на основании имеющегося (выбранного) адресного кода сделана отдельная процедура. Возможен вывод адреса, как в общепринятом виде, так и в виде, требуемом для налоговой отчётности.
Периодически приходится запускать поиск адресного объекта по всей базе. Эта благородная задача так же возложена на sql-сервер, для чего была тоже написана процедура. В общем, всё есть в скрипте, который прилагается.
Ну вот, вроде с базой разобрались, теперь к тому, что же делалось на Delphi.
Теперь несколько картинок, демонстрирующих, как всё выше описанное выглядит на практике. Для отображения выбрана родимая Свердловская область.
Рис.1. На данном рисунке изображено построенное дерево адресных объектов по Свердловской области. Красным обведены поля, в которых отображены временнЫе характеристики построения. Для примера выбран город с наибольшим числом улиц в данном регионе.
Рис.2. Здесь отображено состояние окна при работе фильтров. Дерево адресных объектов отфильтровано по строке "таги", а список улиц – по строке "вост" (обведены соответствующие поля). Скорость работы проверьте сами.
Рис. 3. Демонстрирует прикладные возможности утилиты. Можно создать полный адрес и скопировать его в буфер обмена. Сейчас копируется только вариант для налоговой.
Рис.4. Демонстрирует возможности поиска по всей базе. Выделенные поля – искомая строка и время построения списка найденных объектов.
P.S. Что идёт в придачу к статье. Во-первых, конечно же скрипт для создания базы данных (Firebird, версия вроде не принципиальна). Во-вторых, собственно сама программа, демонстрирующая вышесказанное (в качестве клиентской библиотеки на всякий случай используется gds32.dll). В-третьих – исходники. К исходникам следующие комментарии (опять начинаю загибать пальцы).
P.P.S. Из всего выше сказанного и показанного, мне самому больше всего нравится некая общая идея поиска по древовидной структуре с сокрытием ненужных узлов и ветвей. На счёт новизны – не скажу: в чужих проектах не встречал, от других не слышал, честное слово – сам додумался. Сейчас пользую часто, и помногу: данные в виде древовидной структуры – дело обычное. Так многие справочники под эту методу хорошо выстраиваются. Однако же есть единственный недостаток: структура дерева должна быть полностью построена на момент поиска, что несколько противоречит стандартной идее динамической подгрузки данных из базы по мере необходимости. Если данные и задача позволяют, грузите их смело. И тогда всё у вас будет хорошо.
Copyright© 2007 Андрей Дмитриев Специально для Delphi Plus
Пожалуйста, оцените статью