Нормализация ФИАС и поиск с использованием словаря
Когда-то программировал .com (размер файла, напомню, <=64 kb). Сегодня ограничений почти нет, но если подсчитать количество мусорной информации, собранной на дисках всего мира и затраты на обслуживание… плюс биткоины… а хватит ли угля и газа на все это…
ФИАС – думаю, что аббревиатура знакома для большинства программистов, с рождения был монстром. В данный момент в архиве fias_dbf.rar 4.5 гигабайта, fias_xml.rar 6.3 гигабайта – заметим, что архив КЛАДР с почти аналогичной функциональностью порядка 30 мегабайт. При всех своих недостататках, (из них был всего лишь один существенный, а именно – неточность информации) КЛАДР решал свою задачу в достаточной степени хорошо. С 01.01.2018 КЛАДРу предписано not update.
В моих программа (налоговый учет и прочая бухгалтерская) КЛАДР есть везде. Год назад была предпринята попытка создания аналога КЛАДР из данных ФИАС, получилось неплохо, но из-за длительности update внедрение отложил. Интернет моих клиентов на бюджетном уровне — скорость скачивания от 3 до 100 кб, посему проще скачать «готовую» базу ФИАС.
Начнем с нормализации. Смотрим на «главную» таблицу ФИАС ADDROB.DBF – после преобразований она может принять подобную структуру (почти КЛАДР):
CREATE TABLE FIASD (
ID AS_INT /* AS_INT = INTEGER NOT NULL */,
PID AS_INT /* AS_INT = INTEGER NOT NULL */,
OKTMO_ID AS_INT /* AS_INT = INTEGER NOT NULL */,
POSTCODE_ID AS_INT /* AS_INT = INTEGER NOT NULL */,
IFNS_ID AS_INT /* AS_INT = INTEGER NOT NULL */,
REG_ID AS_INT /* AS_INT = INTEGER NOT NULL */,
NAME_ID AS_INT /* AS_INT = INTEGER NOT NULL */,
SOCKR_ID AS_INT /* AS_INT = INTEGER NOT NULL */
);
ALTER TABLE FIASD ADD CONSTRAINT PK_FIASD PRIMARY KEY (ID);
ALTER TABLE FIASD ADD CONSTRAINT FK_FIASD_2 FOREIGN KEY (PID) REFERENCES GUIDS (ID);
ALTER TABLE FIASD ADD CONSTRAINT FK_FIASD_3 FOREIGN KEY (OKTMO_ID) REFERENCES OKTMOS (ID);
ALTER TABLE FIASD ADD CONSTRAINT FK_FIASD_4 FOREIGN KEY (POSTCODE_ID) REFERENCES POSTCODES (ID);
ALTER TABLE FIASD ADD CONSTRAINT FK_FIASD_5 FOREIGN KEY (IFNS_ID) REFERENCES IFNS (ID);
ALTER TABLE FIASD ADD CONSTRAINT FK_FIASD_6 FOREIGN KEY (REG_ID) REFERENCES REGIONS (ID);
ALTER TABLE FIASD ADD CONSTRAINT FK_FIASD_8 FOREIGN KEY (NAME_ID) REFERENCES OFF_NAMES (ID);
ALTER TABLE FIASD ADD CONSTRAINT FK_FIASD_9 FOREIGN KEY (SOCKR_ID) REFERENCES SOCKRS (ID);
ID, PID – ссылки на GUIDS
Все остальные справочники аналоги GUIDS — идентификатор + значение
Вернемся к таблице ФИАС ADDROB.DBF. Видим FORMALNAME VARCHAR(120), OFFNAME VARCHAR(120). Первое поле предназначено для поиска, второе – для отображения результата, по сути и начинке одно и тоже, посему оставим OFFNAME в качестве образца для дальнейшего использования.
Таблица OFF_NAMES
Теперь несколько отойдем от темы — определимся с назначением ФИАС. Это поисковая система, т.е. после ввода в строку запроса «50 уфа» сервер должен выдать похожее вот на это ( «уфа 50» при этом вернет то же самое):
Поиск ведется в наименованиях адресных объектов, но как искать? Попробуем через словарь.
Таблица WORDSF:
Таблица OFF_NW:
Обе таблицы OFF_NW и WORDSF «пополняются» за счет триггеров на OFF_NAMES – наименование разбирается на слова с фильтром lower (ch>='а' and ch<='я') OR (ch>='0' and ch<='9'), проверяется на наличие в WORDSF – insert, если нет, проверяется наличие связи в OFF_NW. Результат ниже
Т.е. при запросе
select off_names.name from wordsf
left join off_nw on wordsf.id=off_nw.wordsf_id
left join off_names on off_nw.off_name_id=off_names.id
where wordsf.aword='уфа'
вернется
с производительностью
Усложним запрос
select off_nw.off_name_id,
sum(case when wordsf.aword starting with :ss1 then 1 else 0 end),
sum(case when wordsf.aword starting with :ss2 then 1 else 0 end),
sum(case when wordsf.aword starting with :ss3 then 1 else 0 end),
sum(case when wordsf.aword starting with :ss4 then 1 else 0 end),
sum(case when wordsf.aword starting with :ss5 then 1 else 0 end),
sum(case when wordsf.aword starting with :ss6 then 1 else 0 end)
from off_nw
right join wordsf on off_nw.wordsf_id=wordsf.id
where
((:ss1 is not null) and (wordsf.aword starting with :ss1)) or
((:ss2 is not null) and (wordsf.aword starting with :ss2)) or
((:ss3 is not null) and (wordsf.aword starting with :ss3)) or
((:ss4 is not null) and (wordsf.aword starting with :ss4)) or
((:ss5 is not null) and (wordsf.aword starting with :ss5)) or
((:ss6 is not null) and (wordsf.aword starting with :ss6))
group by off_nw.off_name_id
где ss1=’50’,ss2=’лет’,ss3=’уфа’, иные = null. Смысл всего этого — получить список off_name_id с указанием количества вхождений поиска в этом наименовании.
Получим это
Видим, что OFF_NAME_ID=1295 (1459) ссылка на наименование объекта, содержащего два слова из запроса — «50» и «лет». Остается найти объекты выше уровнем, с наименование, содержащим «уфа».
Ниже «самолепный» запрос, реализующий означенный поиск
create or alter procedure LIST_FIAS (
SS1 type of AS_STR10,
SS2 type of AS_STR10,
SS3 type of AS_STR10,
SS4 type of AS_STR10,
SS5 type of AS_STR10,
SS6 type of AS_STR10)
returns (
OBJ_CODE type of AS_STR36,
FNAME type of AS_STR500,
INFNS_CODE type of column IFNS.CODE,
POST_CODE type of column POSTCODES.CODE,
OKTMO_CODE type of column OKTMOS.CODE)
AS
declare variable id type of AS_INT;
declare variable NAME_ID type of AS_INT;
declare variable CSS1 type of AS_INT;
declare variable CSS2 type of AS_INT;
declare variable CSS3 type of AS_INT;
declare variable CSS4 type of AS_INT;
declare variable CSS5 type of AS_INT;
declare variable CSS6 type of AS_INT;
declare variable S1 type of AS_STR10;
declare variable S2 type of AS_STR10;
declare variable S3 type of AS_STR10;
declare variable S4 type of AS_STR10;
declare variable S5 type of AS_STR10;
declare variable S6 type of AS_STR10;
declare variable pS1 type of AS_STR10;
declare variable pS2 type of AS_STR10;
declare variable pS3 type of AS_STR10;
declare variable pS4 type of AS_STR10;
declare variable pS5 type of AS_STR10;
declare variable pS6 type of AS_STR10;
declare variable lpid type of as_int;
declare variable wcount type of as_int;
declare variable wpcount type of as_int;
declare variable lwcount type of as_int;
declare variable rz type of as_int;
BEGIN
wcount=iif(ss1 is null,0,1)+iif(ss2 is null,0,1)+iif(ss3 is null,0,1)+
iif(ss4 is null,0,1)+iif(ss5 is null,0,1)+iif(ss6 is null,0,1);
-- wcount – кол-во «слов» в запросе – не более 6
for select off_nw.off_name_id, -- group by
-- sum(case when – счетчик вхождений
sum(case when wordsf.aword starting with :ss1 then 1 else 0 end),
sum(case when wordsf.aword starting with :ss2 then 1 else 0 end),
sum(case when wordsf.aword starting with :ss3 then 1 else 0 end),
sum(case when wordsf.aword starting with :ss4 then 1 else 0 end),
sum(case when wordsf.aword starting with :ss5 then 1 else 0 end),
sum(case when wordsf.aword starting with :ss6 then 1 else 0 end)
from off_nw
right join wordsf on off_nw.wordsf_id=wordsf.id
where
((:ss1 is not null) and (wordsf.aword starting with :ss1)) or
((:ss2 is not null) and (wordsf.aword starting with :ss2)) or
((:ss3 is not null) and (wordsf.aword starting with :ss3)) or
((:ss4 is not null) and (wordsf.aword starting with :ss4)) or
((:ss5 is not null) and (wordsf.aword starting with :ss5)) or
((:ss6 is not null) and (wordsf.aword starting with :ss6))
group by off_nw.off_name_id
into :name_id,:css1,:css2,:css3,:css4,:css5,:css6 do
begin
lwcount=css1+css2+css3+css4+css5+css6; -- найдено слов
ps1=iif(css1=0,ss1,null); -- заполняем «массив» не найденных слов
ps2=iif(css2=0,ss2,null);
ps3=iif(css3=0,ss3,null);
ps4=iif(css4=0,ss4,null);
ps5=iif(css5=0,ss5,null);
ps6=iif(css6=0,ss6,null);
-----------------------------------------
for select fiasd.id, fiasd.pid
from fiasd where fiasd.name_id=:name_id
into :id,:lpid do
begin
fname=(select fname from ret_obj_adr(:id));
if (wcount=lwcount) then – если все слова найдены
begin
fname=(select fname from ret_footer_adr(:lpid))||', '||fname;
select postcodes.code,ifns.code,oktmos.code,guids.aguid from fiasd
left join postcodes on fiasd.postcode_id=postcodes.id
left join ifns on fiasd.ifns_id=ifns.id
left join oktmos on fiasd.oktmo_id=oktmos.id
left join guids on fiasd.id=guids.id where fiasd.id=:id into
:post_code,:infns_code,:oktmo_code,:obj_code;
suspend;
end else
begin
while (lpid>0) do – пока не будут найдены все слова
begin
s1=iif(css1=0,ps1,null); -- заполняем аргументы из «массива»
s2=iif(css2=0,ps2,null);
s3=iif(css3=0,ps3,null);
s4=iif(css4=0,ps4,null);
s5=iif(css5=0,ps5,null);
s6=iif(css6=0,ps6,null);
wpcount=iif(s1 is null,0,1)+iif(s2 is null,0,1)+iif(s3 is null,0,1)+
iif(s4 is null,0,1)+iif(s5 is null,0,1)+iif(s6 is null,0,1); -- устанавливаеи счетчик
select rz,css1,css2,css3,css4,css5,css6
from test_word_in_pid(:lpid, -- здесь аналогичная процедура поиска «выше»
:s1,:s2,:s3,:s4,:s5,:s6)
into :rz,:css1,:css2,:css3,:css4,:css5,:css6;
lwcount=css1+css2+css3+css4+css5+css6;
if (rz=1) then
begin
if (wpcount=lwcount) then
begin
fname=(select fname from ret_footer_adr(:lpid))||', '||fname;
select postcodes.code,ifns.code,oktmos.code,guids.aguid from fiasd
left join postcodes on fiasd.postcode_id=postcodes.id
left join ifns on fiasd.ifns_id=ifns.id
left join oktmos on fiasd.oktmo_id=oktmos.id
left join guids on fiasd.id=guids.id where fiasd.id=:id into
:post_code,:infns_code,:oktmo_code,:obj_code;
suspend;
lpid=0;
end else
begin
fname=(select fname from ret_footer_adr(:lpid))||', '||fname;
select pid from fiasd where id=:lpid into :lpid;
end
end else
begin
fname=(select fname from ret_footer_adr(:lpid))||', '||fname;
select pid from fiasd where id=:lpid into :lpid;
end
end
end
end
end
end
Результат
109ms – это явно не тяжелый запрос.
Как заключение – описанный метод поиска можно оптимизировать за счет лучшего владения SQL – мой изложенный пока на уровне «так себе» в силу того, что иногда я просто не понимаю свой усложненный код буквально через месяц простоя и приучил себя писать код «как для всех». Но не всегда получается и приходится начинать все снова (заново) с надеждой, что успею дописать… пока не забуду
Автор: new player
