Адреса ФИАС в среде PostgreSQL. Часть 3

    Это третья часть статьи, в которой описана функция поиска в списке адресообразующих
    элементов ФИАС, загруженных в базу данных под управлением PostgreSQL. Вот ссылки на первую и вторую части.



    Полный текст статьи состоит состоит из 4 частей. В первой половине этой части статьи изложены комментарии к реализации функции. Во второй — исходный текст функции. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению.

    Поиск адресообразующего элемента


    Функция fstf_AddressObjects_SearchByName предназначена для поиска адрсообразующих элементов ФИАС по их названиям. При этом поиск может осуществялться не только по названию и типу текущего элемента, но и названиям и типам одного или двух его ближайших предков.
    Рассмотрим несколько примеров. И для начала найдем все адресообразующие элементы в названии которых встречается слово «Грибной».

    Таблица 8. Результат выполнения функции fstf_AddressObjects_SearchByName('Грибной')

    AOGUID AOLevel Полный адрес ShortName FormalName CurrStatus ActStatus
    15faf08c-78b6-4b92-8a56-2ff70f2c4cab 6 Ачинский р-н, п Грибной п Грибной 0 1
    f1772172-4dd1-449d-b2d2-ab96883d8871 7 Кежемский р-н, г Кодинск, пер Грибной пер Грибной 0 1
    146cbcb5-4ad9-4578-916f-80ebd5c2b846 7 Емельяновский р-н, п Элита, пер Грибной пер Грибной 0 1
    a8ee8caf-fd5f-489c-92d9-f560e3f93c8b 7 Сухобузимский р-н, д Шестаково, пер Грибной пер Грибной 0 1
    84f4baa8-1db2-471d-967d-20d489bca68e 7 Курагинский р-н, с Тюхтят, пер Грибной пер Грибной 0 1
    1f2b7975-ce05-4627-bd13-d8d6228accd7 7 г Сорск, пер Грибной пер Грибной 0 1

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

    Теперь изменим запрос. Найдем все адресообразующие элементы, в названии ближайшего предка которых встречается слово «Грибной».

    Таблица 9. Результат выполнения функции fstf_AddressObjects_SearchByName
    (NULL,NULL,'Грибной')


    AOGUID AOLevel Полный адрес ShortName FormalName CurrStatus ActStatus Parent ShortName Parent FormalName
    45064ade-a0a7-4258-88c8-baa57094aa2d 7 Ачинский р-н, п Грибной, ул Железнодорожная ул Железнодорожная 0 1 п Грибной
    ba4ec53c-50b7-4325-866a-81f97a38214c 7 Ачинский р-н, п Грибной, ул Западная ул Западная 0 1 п Грибной
    d6e9e0cc-e944-4deb-a09c-c545af691836 7 Ачинский р-н, п Грибной, ул Северная ул Северная 0 1 п Грибной
    5ae71e68-5477-446b-b878-0a9c9bf3bdcd 7 Ачинский р-н, п Грибной, ул Южная ул Южная 0 1 п Грибной

    Результат этого запроса несколько более неожиданный, т.к. в названиях найденных адресообразующих элементах нет слова «Грибной», но оно есть в названии их предка.

    И, наконец, рассмотрим поиск по наименованию прародителя, в котором должно присутствовать слово «Ачинский», а в наименовании его внука должен присутствовать часть слова «Оз_рн». Здесь использован специальный символ — символ подчеркивания «_». Этот символ указывает, что на его месте может находиться любой одиночный символ. Здесь он применен, для того чтобы найти не только элементы с названиями «Озерный» или «Озерная», но и «Озёрный» или «Озёрная».

    Таблица 9. Результат выполнения функции fstf_AddressObjects_SearchByName(NULL,NULL,'Грибной')

    AOGUID AOLevel Полный адрес ShortName FormalName CurrStatus ActStatus Parent ShortName Parent FormalName Grand Parent ShortName Grand Parent FormalName
    715eef9d-48f6-4322-bcaa-9d239e89b7e4 7 Ачинский р-н, д Барабановка, пер Озерный пер Озерный 0 1 д Барабановка р-н Ачинский
    05c7b2ad-e405-4c8b-9503-6761971e858e 7 Ачинский р-н, д Ильинка, ул Озерная ул Озерная 0 1 д Ильинка р-н Ачинский
    bdfcd515-1851-4caf-83ba-12ee79f9f6a7 7 Казачинский р-н, с Дудовка, ул Озерная ул Озерная 0 1 с Дудовка р-н Казачинский

    В результате запроса найдены улицы Озерная и переулок озерный в трех населенных пунктах Ачинского и Казачинского районов Красноярского края. Текст функции приведен в разделе Приложения «Создание функции fstf_AddressObjects_SearchByName».

    Как это работает


    Если значения присвоены только первым двум аргументам – названию (a_FormalName) и типу (a_ShortName) адресообразующего элемента, то поиск осуществляется во всех записях таблицы fias_AddressObjects. Предварительно значения переданных параметров преобразуются в верхний регистр, пробелы заменяются символом «%». Этим же символом окружается значение справа и слева. Преобразованные таким образом значения используются в поисковом запросе как часть операции LIKE. Пример такого запроса приведен на Рис. 4.



    Рис. 4. Простой поиск адресообразующего элемента.

    Условие выбора значения CurrStatus подробно обсуждалось в первой части статьи в разделе «Родословная адресообразующего элемента» «Как это работает».

    Для поиска по названию и типу родительского адресообразующего элемента необходимо присвоить значение хотя бы одному из двух аргументов: третьему (a_ParentFormalName), или четвертому (a_ParentShortName). В этом случае поиск осуществляется во всех записях таблицы, полученной соединением (INNER JOIN) всех записей fias_AddressObjects с записями родительского адресообразующего элемента по признаку pfa.AOGUID=cfa.ParentGUID.
    Пример такого запроса приведен на Рис. 5.



    Рис. 5. Поиск по названию и типу родительского адресообразующего элемента.

    Предварительная обработка значений входных параметров осуществляется по тем же правилам, как и в случае простого поиска. Для поиска по названию и типу прародительского адресообразующего элемента необходимо присвоить значение хотя бы одному из двух аргументов: пятому (a_GrandParentFormalName) или шестому (a_GrandParentShortName). В этом случае поиск осуществляется во всех записях таблицы, полученной двойным соединением (INNER JOIN) всех записей fias_AddressObjects с записями родительского и прародительских адресообразующих элементов. Пример такого запроса приведен на Рис. 6.



    Рис. 6. Поиск по названию и типу прародительского адресообразующего элемента.

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

    ПРИЛОЖЕНИЕ


    Создание функции fstf_AddressObjects_SearchByName


    Исходный код функции
    BEGIN TRANSACTION;
    DROP FUNCTION IF EXISTS fstf_AddressObjects_SearchByName(
      a_FormalName VARCHAR(150), a_ShortName VARCHAR(20),
      a_ParentFormalName VARCHAR(150),a_ParentShortName VARCHAR(20),
      a_GrandParentFormalName VARCHAR(150),a_GrandParentShortName VARCHAR(20));
    /************************************************************************/
    /* Возвращает результат поиска в списке адресообразующих элементов ФИАС */
    /* по их названию и типу	 		                        */
    /***********************************************************************/	
    CREATE OR REPLACE FUNCTION fstf_AddressObjects_SearchByName(
      a_FormalName VARCHAR(150),	 /* Оптимизированное для поиска наименование */
                                          /* адресообразующего элемента*/
      a_ShortName VARCHAR(20) default NULL,	/* Сокращенное наименование типа */
                                          /*адресообразующего элемента */
     a_ParentFormalName 	VARCHAR(150) default NULL, /* Оптимизированное для поиска */
                                         /* наименование адресообразующего элемента*/
     a_ParentShortName VARCHAR(20) default NULL,	/* Сокращенное наименование типа */
                                         /*адресообразующего элемента */
     a_GrandParentFormalName VARCHAR(150) default NULL, /*Оптимизированное для поиска */
                                         /* наименование адресообразующего элемента*/
     a_GrandParentShortName	VARCHAR(20) default NULL	/* Сокращенное наименование типа */
                                         /* адресообразующего элемента */
    )
    RETURNS  TABLE (rtf_AOGUID VARCHAR(36),
                    rtf_AOLevel INTEGER,
                    rtf_AddressObjectsFullName VARCHAR(1000),
                    rtf_ShortName VARCHAR(20),
                    rtf_FormalName VARCHAR(150),
                    rtf_CurrStatus INTEGER,
                    rtf_ParentShortName VARCHAR(20),
                    rtf_ParentFormalName VARCHAR(150),
                    rtf_GrandParentShortName VARCHAR(20),
                     rtf_GrandParentFormalName VARCHAR(150))
    AS		
    $BODY$
    DECLARE
    	c_WildChar   CONSTANT VARCHAR(2)='%';
    	c_BlankChar  CONSTANT VARCHAR(2)=' ';
    	v_FormalNameTemplate VARCHAR(150); /* Шаблон для поиска наименования */
                                         /* адресообразующего элемента*/
    	v_ShortNameTemplate		VARCHAR(20);	/* Шаблон для поиска типа */
                                         /* адресообразующего элемента */
    	v_ParentFormalNameTemplate VARCHAR(150); /* Шаблон для поиска наименования */
                                         /* родительского адресообразующего элемента*/
    	v_ParentShortNameTemplate VARCHAR(20); /* Шаблон для поиска типа родительского */
                                         /* адресообразующего элемента */
    	v_GrandParentFormalNameTemplate	VARCHAR(150);	/* Шаблон для поиска */
                                         /* наименования родительского адресообразующего элемента*/
    	v_GrandParentShortNameTemplate	VARCHAR(20);	/* Шаблон для поиска типа */
                                         /* родительского адресообразующего элемента */
    --************************************************************
    --************************************************************
     BEGIN
    	v_ShortNameTemplate:=UPPER(COALESCE(c_WildChar||
                                     REPLACE(TRIM(a_ShortName),c_BlankChar,c_WildChar)||
                                    c_WildChar,c_WildChar));
    	v_FormalNameTemplate:=UPPER(c_WildChar||
                                     REPLACE(TRIM(a_FormalName),c_BlankChar,c_WildChar)||
                                    c_WildChar);
    	IF a_ParentFormalName IS NULL AND a_ParentShortName IS NULL 
    			AND a_GrandParentFormalName IS NULL 
                            AND a_GrandParentShortName IS NULL THEN
    		RETURN QUERY
                    SELECT cfa.AOGUID,cfa.AOLevel,
                           fsfn_AddressObjects_TreeActualName(cfa.AOGUID),
                           cfa.ShortName,cfa.FORMALNAME,
                            cfa.currstatus,NULL::VARCHAR,NULL::VARCHAR,
    			NULL::VARCHAR,NULL::VARCHAR
                      FROM fias_AddressObjects cfa 
                      WHERE cfa.currstatus=
                            CASE WHEN 0 < ALL(SELECT iao.currstatus FROM fias_AddressObjects iao
                                                                 WHERE cfa.aoguid = iao.aoguid)
                                      THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
                                                                 WHERE cfa.aoguid = iao.aoguid)
    			           ELSE 0 
                                END
                           AND UPPER(cfa.FORMALNAME) LIKE v_FormalNameTemplate
                           AND  UPPER(cfa.ShortName) LIKE v_ShortNameTemplate
                      ORDER BY cfa.AOLevel,cfa.ShortName,cfa.FORMALNAME;
    	ELSIF a_ParentFormalName IS NOT NULL
    			AND a_GrandParentFormalName IS NULL 
    			AND a_GrandParentShortName IS NULL THEN
    		v_ParentShortNameTemplate:=UPPER(COALESCE(c_WildChar||
                                     REPLACE(TRIM(a_ParentShortName),c_BlankChar,c_WildChar)||
                                     c_WildChar,c_WildChar));
    		v_ParentFormalNameTemplate:=UPPER(c_WildChar||
                                     REPLACE(TRIM(a_ParentFormalName),c_BlankChar,c_WildChar)||
                                     c_WildChar);
    		v_FormalNameTemplate:=COALESCE(v_FormalNameTemplate,c_WildChar);
                    RETURN QUERY 
                    SELECT cfa.AOGUID,cfa.AOLevel,fsfn_AddressObjects_TreeActualName(cfa.AOGUID),
                                 cfa.ShortName,cfa.FORMALNAME,cfa.currstatus,
                                 pfa.ShortName,pfa.FORMALNAME,
                                 NULL::VARCHAR,NULL::VARCHAR
                     FROM fias_AddressObjects pfa
                             INNER JOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID
                     WHERE cfa.currstatus=CASE WHEN 0 < 
                            ALL (SELECT iao.currstatus FROM fias_AddressObjects iao 
                                                                WHERE cfa.aoguid = iao.aoguid)
                                      THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
                                                                WHERE cfa.aoguid = iao.aoguid)
                                      ELSE 0 END
                         AND pfa.currstatus=CASE WHEN 0 < 
                             ALL(SELECT iao.currstatus FROM fias_AddressObjects iao
                                                               WHERE pfa.aoguid = iao.aoguid)
                                      THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
                                                              WHERE pfa.aoguid = iao.aoguid)
                                      ELSE 0 END
                         AND UPPER(pfa.FORMALNAME) LIKE v_ParentFormalNameTemplate 
                         AND  UPPER(pfa.ShortName) LIKE v_ParentShortNameTemplate
                         AND UPPER(cfa.FORMALNAME) LIKE v_FormalNameTemplate 
                         AND  UPPER(cfa.ShortName) LIKE v_ShortNameTemplate
                    ORDER BY pfa.ShortName,pfa.FORMALNAME,
                        cfa.AOLevel,cfa.ShortName,cfa.FORMALNAME;	
    	ELSE
    		v_GrandParentShortNameTemplate:=UPPER(COALESCE(c_WildChar||
                                     REPLACE(TRIM(a_GrandParentShortName),c_BlankChar,c_WildChar)||
                                     c_WildChar,c_WildChar));
    		v_GrandParentFormalNameTemplate:=UPPER(c_WildChar||
                                     REPLACE(TRIM(a_GrandParentFormalName),c_BlankChar,c_WildChar)||
                                     c_WildChar);
    		v_ParentShortNameTemplate:=COALESCE(UPPER(COALESCE(c_WildChar||
                                     REPLACE(TRIM(a_ParentShortName),c_BlankChar,c_WildChar)||
                                     c_WildChar,c_WildChar)),c_WildChar);
    		v_ParentFormalNameTemplate:=COALESCE(UPPER(c_WildChar||
                                    REPLACE(TRIM(a_ParentFormalName),c_BlankChar,c_WildChar)||
                                    c_WildChar),c_WildChar);
    		v_FormalNameTemplate:=COALESCE(v_FormalNameTemplate,c_WildChar);
                    RETURN QUERY 
                    SELECT cfa.AOGUID,cfa.AOLevel,fsfn_AddressObjects_TreeActualName(cfa.AOGUID),
                                cfa.ShortName,cfa.FORMALNAME,
                                cfa.currstatus,pfa.ShortName,pfa.FORMALNAME,
                                gpfa.ShortName,gpfa.FORMALNAME
                      FROM fias_AddressObjects gpfa 
                               INNER JOIN fias_AddressObjects pfa ON gpfa.AOGUID=pfa.ParentGUID
                              INNER JOIN fias_AddressObjects cfa ON pfa.AOGUID=cfa.ParentGUID
                      WHERE cfa.currstatus=CASE WHEN 0 < 
                            ALL(SELECT iao.currstatus FROM fias_AddressObjects iao
                                                                WHERE 	cfa.aoguid = iao.aoguid)
                                      THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
                                                                WHERE cfa.aoguid = iao.aoguid)
                                     ELSE 0 END
                            AND pfa.currstatus=CASE WHEN 0 < 
                              ALL(SELECT iao.currstatus FROM fias_AddressObjects iao 
                                                               WHERE pfa.aoguid = iao.aoguid)
                                      THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
                                                               WHERE pfa.aoguid = iao.aoguid)
                                      ELSE 0 END
                            AND gpfa.currstatus=CASE WHEN 0 < 
                              ALL(SELECT iao.currstatus FROM fias_AddressObjects iao
                                                               WHERE gpfa.aoguid = iao.aoguid)
                                      THEN (SELECT MAX(iao.currstatus) FROM fias_AddressObjects iao 
                                                               WHERE gpfa.aoguid = iao.aoguid)
                                      ELSE 0 END
                            AND UPPER(gpfa.FORMALNAME) LIKE v_GrandParentFormalNameTemplate
                            AND  UPPER(gpfa.ShortName) LIKE v_GrandParentShortNameTemplate
                            AND UPPER(pfa.FORMALNAME) LIKE v_ParentFormalNameTemplate 
                            AND  UPPER(pfa.ShortName) LIKE v_ParentShortNameTemplate
                            AND UPPER(cfa.FORMALNAME) LIKE v_FormalNameTemplate 
                            AND  UPPER(cfa.ShortName) LIKE v_ShortNameTemplate
                      ORDER BY gpfa.ShortName,gpfa.FORMALNAME,
                            pfa.ShortName,pfa.FORMALNAME,
                            cfa.AOLevel,cfa.ShortName,cfa.FORMALNAME; 
    	END IF;
    END;  $BODY$
    LANGUAGE plpgsql;
    COMMENT ON FUNCTION fstf_AddressObjects_SearchByName(
       a_FormalName VARCHAR(150),a_ShortName VARCHAR(20),
       a_ParentFormalName VARCHAR(150),a_ParentShortName VARCHAR(20),
       a_GrandParentFormalName VARCHAR(150),a_GrandParentShortName VARCHAR(20))
       IS 'Возвращает результат поиска в списке адресообразующих элементов ФИАС по их названию и типу';
    --ROLLBACK TRANSACTION;
    COMMIT TRANSACTION;
    --SELECT * FROM fstf_AddressObjects_SearchByName('БОРОДИН','г');
    --SELECT * FROM fstf_AddressObjects_SearchByName('БОРОДИН');
    --SELECT * FROM fstf_AddressObjects_SearchByName('два',NULL,'МИГНА');
    --SELECT * FROM fstf_AddressObjects_SearchByName(NULL,NULL,'МИГНА');
    --SELECT * FROM fstf_AddressObjects_SearchByName('Зел_ная','УЛ',NULL);
    SELECT * FROM fstf_AddressObjects_SearchByName('Зел_ная','УЛ','Куваршино',NULL,'Емельян');
    


    Спасибо за внимание!
    Поделиться публикацией

    Комментарии 5

      0
      А в продуктиве это используется? В каком сценарии?

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

            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

            Самое читаемое