Pull to refresh

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

SQL *
Awaiting invitation

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


Когда-то программировал .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 – мой изложенный пока на уровне «так себе» в силу того, что иногда я просто не понимаю свой усложненный код буквально через месяц простоя и приучил себя писать код «как для всех». Но не всегда получается и приходится начинать все снова (заново) с надеждой, что успею дописать… пока не забуду

Tags:
Hubs:
You can’t comment this post because its author is not yet a full member of the community. You will be able to contact the author only after he or she has been invited by someone in the community. Until then, author’s username will be hidden by an alias.