Нормализация ФИАС и поиск с использованием словаря

Нормализация ФИАС и поиск с использованием словаря

Когда-то программировал .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

Источник ^[1]