Как стать автором
Поиск
Написать публикацию
Обновить
198.83
Рейтинг
Тензор
Разработчик системы СБИС

КЛАДРируем адреса произвольной формы (ч.1 — импорт)

Время на прочтение25 мин
Количество просмотров7.8K
Блог компании ТензорСистемное администрирование*PostgreSQL*SQL*ERP-системы*
Туториал

Достаточно часто при работе с вводимыми пользователем адресными данными возникает необходимость сначала подсказать ему, как правильно свой адрес указать, а потом - полученную строку адреса привести в некоторый машинно-читаемый вид.

Таким видом у нас в стране можно считать код по справочникам КЛАДР или ФИАС.

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

Давайте научимся разбирать строку адреса "туда и обратно", а заодно познакомимся с некоторыми алгоритмическими подходами и их реализацией на SQL.

Получение справочника КЛАДР

База КЛАДР в настоящее время администрируется ФНС и представлена на сайте ГНИВЦ в виде периодически (примерно раз в неделю) обновляемого архива. Для начала мы научимся его скачивать, исправлять некоторые ошибки и преобразовывать в более подходящую для наших задач структуру.

Для этого нам понадобятся wget, p7zip, dbview, recode и psql.

Исходный архив

База выкладывается на сервере ГНИВЦ всегда по одному и тому же адресу в виде 7z-архива Base.7z - версии отличаются друг от друга только заголовком Last-Modified в ответе сервера.

При этом сервер достаточно нервно реагирует на скорость загрузки и не поддерживает HEAD-запросы, увы. Поэтому нам придется скачивать архив ежесуточно полностью, но чтобы снизить нагрузку на сервер установим ограничение скорости, а заголовки ответа сохраним и разберем отдельно:

wget -S https://gnivc.ru/html/gnivcsoft/KLADR/Base.7z --limit-rate=8k 2>.hdr

В сгенерированном .hdr нас будет интересовать эта строка:

...
  Last-Modified: Thu, 05 Aug 2021 09:28:39 GMT
...

Чтобы не пытаться обрабатывать архив и обновлять данные в нашей базе повторно, будем сохранять в ней этот таймстамп для последующих сравнений.

Если же таймстамп файла не совпал с сохраненным, распакуем полученный архив:

p7zip -d Base.7z

Результатом будет 7 DBF-файлов в DOS-кодировке:

Extracting  ALTNAMES.DBF
Extracting  DOMA.DBF
Extracting  FLAT.DBF
Extracting  KLADR.DBF
Extracting  NAMEMAP.DBF
Extracting  SOCRBASE.DBF
Extracting  STREET.DBF

Переберем все эти файлы, формируя единый скрипт выгрузки данных через psql в COPY-формате:

# получаем структуру полей DBF
#   | DOS2WIN
#   | берем только описания полей (skip 2 строки)
#   | оставляем только их имена
dbview -b -t -e -o -r ALTNAMES.DBF \
  | recode CP866..CP1251 \
  | tail -n+2 \
  | xargs -l \
  | egrep -io "^[a-z0-9_]+"

# ... формируем SQL-заголовок временной таблицы

# получаем данные DBF, разделенные '~'
#   | склеиваем "висящие" строки ([\t\r\n] в теле поля данных)
#   | DOS2WIN
#   | убираем все '\t', убираем концевые '~', заменяем '~'->'\t'
dbview -d~ -b -t ALTNAMES.DBF \
  | sed -e :a -e '/[\r\t]$/N; s/[\r\t]\n//g; ta' \
  | recode CP866..CP1251 \
  | sed -e 's/\t//g; s/~\r//g; s/~,/,/g; s/~/\t/g' >>.sql

По итогу мы получаем большой-большой SQL-файл примерно такого вида:

CREATE TEMPORARY TABLE "STREET.DBF"(
  "NAME"
    varchar,
  "SOCR"
    varchar,
  "CODE"
    varchar,
  "INDEX"
    varchar,
  "GNINMB"
    varchar,
  "UNO"
    varchar,
  "OCATD"
    varchar
);
COPY "STREET.DBF"(
  "NAME",
  "SOCR",
  "CODE",
  "INDEX",
  "GNINMB",
  "UNO",
  "OCATD"
) FROM stdin;
Абадзехская     ул      01000001000000100       385013  0105            79401000000
Абрикосовая     ул      01000001000000200       385013  0105            79401000000
Авиационный     пер     01000001000000300       385006  0105            79401000000
Автодорога 7    ул      01000001000000400       385019  0105            79401000000
...

Импорт данных

Поскольку сами данные в таблицах архива представлены в DOS-кодировке, но это как-то совсем уж немодно, развернем нашу базу в WIN1251 - она тоже однобайтовая, поэтому все поиски данных не станут тяжелее:

CREATE DATABASE kladr
  WITH ENCODING='WIN1251'
    OWNER=postgres
    TEMPLATE=template0
    LC_COLLATE='ru_RU.CP1251'
    LC_CTYPE='ru_RU.CP1251'
    CONNECTION LIMIT=-1;
-- лог проверок обновлений
CREATE TABLE kladr_chk(
  id
    serial
      PRIMARY KEY,
  ts
    timestamp
      DEFAULT now(),
  hostname
    varchar
);

-- лог проведенных обновлений
CREATE TABLE kladr_upd(
  id
    serial
      PRIMARY KEY,
  ts
    timestamp
      DEFAULT now(),
  lm
    varchar,
  hostname
    varchar
);

Защита от параллельной загрузки

Поскольку импорт данных из ГНИВЦ у нас может проходить длительное время и инициироваться с разных источников (мало ли, один из инстансов прикажет долго жить), неплохо бы позаботиться о защите данных, чтобы они не пострадали - дописываем все это в начале .sql:

-- защита от автоотключения по таймауту
SET statement_timeout = 0;
-- включаем WIN-кодировку
SET client_encoding = 'WIN1251';
-- включаем application_name для мониторинга активного процесса
SET application_name = 'kladr : import [`hostname`]';
-- включаем "последовательные" транзакции
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  -- блокируем эксклюзивно upd-таблицу для "замораживания" параллельных операций
  LOCK TABLE kladr_upd IN EXCLUSIVE MODE NOWAIT;
  -- сбрасываем информацию в таблицу протокола проверок
  INSERT INTO kladr_chk(hostname) VALUES('`hostname`');
  -- сравниваем Last-Modified загруженного файла и состояния в базе
  SELECT lm FROM kladr_upd ORDER BY id DESC LIMIT 1;
  -- если lm не совпадает
    -- для каждого DBF
    CREATE TEMPORARY TABLE ...;
    COPY ... FROM stdin;
    ...
    \.
    --
    -- обновляем "версию" нашей базы
    INSERT INTO kladr_upd(lm, hostname) VALUES('$lm', '`hostname`');
COMMIT;

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

Соберем все вместе в единый скрипт:

Много bash-кода
#!/bin/sh

. `dirname "$0"`/../app.conf

echo "`date '+%F %T'` ==== Connecting to DB : $pghost:$pgport:$pgbase:$pguser"
# тестирование подключения к БД
psql -t -c 'SELECT 1' -h $pghost -p $pgport -U $pguser -w $pgbase 1>/dev/null 2>/dev/null
rv="$?"
if [ "$rv" != "0" ]; then
  echo "$pghost:$pgport:$pgbase:$pguser:$pgpass" >>~/.pgpass
  chmod 0600 ~/.pgpass
  psql -t -c 'SELECT 1' -h $pghost -p $pgport -U $pguser -w $pgbase 1>/dev/null 2>/dev/null
  rv="$?"
fi

if [ "$rv" != "0" ]; then
  echo "DB not connected : $pghost:$pgport:$pgbase:$pguser"
  exit 1
fi

# инициализация каталога _dbf
#_dbf=`mktemp -d`
mkdir ./dbf && chmod 777 ./dbf
_dbf=`readlink -f ./dbf`
rm -rf ${_dbf} 2>/dev/null
mkdir ${_dbf} 2>/dev/null
touch ${_dbf}/.sql
dir=`dirname "$0"`
dir=`readlink -f $dir`

## импорт базы КЛАДР'а в _dbf/.sql
# защита от автоотключения по таймауту
echo "SET statement_timeout = 0;" >>${_dbf}/.sql
# включаем WIN-кодировку
echo "SET client_encoding = 'WIN1251';" >>${_dbf}/.sql
# включаем application_name для мониторинга активного процесса
echo "SET application_name = 'kladr : import [`hostname`]';" >>${_dbf}/.sql
# включаем "последовательные" транзакции
echo "BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;" >>${_dbf}/.sql
# блокируем эксклюзивно upd-таблицу для "замораживания" параллельных операций
echo "LOCK TABLE kladr_upd IN EXCLUSIVE MODE NOWAIT;" >>${_dbf}/.sql
# сбрасываем информацию в таблицу протокола проверок
echo "INSERT INTO kladr_chk(hostname) VALUES('`hostname`');" >>${_dbf}/.sql

# инициализация временного каталога импорта
#tmp=`mktemp -d`
mkdir ./tmp && chmod 777 ./tmp
tmp=`readlink -f ./tmp`
cd $tmp

echo "`date '+%F %T'` ==== Downloading : $source"
# загрузка базы КЛАДР'а с ограничением по скорости или без
wget -S $source --limit-rate=8k 2>.hdr
# wget -S $source 2>.hdr

echo "`date '+%F %T'` ==== Comparing 'Last-Modified'"
rc=`cat .hdr | egrep 'HTTP/[0-9]\.[0-9] [0-9]{3}' | sed -e 's/^[ ]*HTTP\/[0-9]\.[0-9][ ]*\([0-9]*\).*$/\1/i' | egrep -v '301' | head -1`
lm=`cat .hdr | egrep 'Last-Modified' | sed -e 's/^[ ]*Last-Modified:[ ]*//i' | head -1`
echo "  -- HTTP code : $rc"
echo "  -- HTTP 'Last-Modified' : $lm"
pglm=`psql -h $pghost -p $pgport -U $pguser -w -t -c 'SELECT lm FROM kladr_upd ORDER BY id DESC LIMIT 1' $pgbase | sed -e 's/^[ ]*//i'`
echo "  -- PGDB 'Last-Modified' : $pglm"

if [ "$rc" = "200" ] && [ "$lm" != "" ] && [ "$lm" != "$pglm" ]; then
  # распаковка базы
  echo "`date '+%F %T'` ==== Unpacking 7z"
  p7zip -d Base.7z 1>/dev/null 2>/dev/null
  cp $tmp/* ${_dbf}
  cd $dir

  echo "`date '+%F %T'` ==== Processing DBF"
  # обработка всех .DBF
  for dbf in `find ${_dbf} -maxdepth 1 -iname '*.DBF'`; do
    dbfn=`basename $dbf | tr '[:lower:]' '[:upper:]'`
    # преобразование заголовков
    echo "  -- DBF : $dbfn"
    echo "    -- header"
    # получаем структуру полей DBF | DOS2WIN | берем только описания полей (skip 2 строки) | оставляем только их имена
    fld=`dbview -b -t -e -o -r $dbf | recode CP866..CP1251 | tail -n+2 | xargs -l | egrep -io "^[a-z0-9_]+"`
    echo "CREATE TEMPORARY TABLE \"$dbfn\"(" >>${_dbf}/.sql
    fl="0"
    for i in ${fld}; do
      [ "$fl" = "1" ] && echo ',' >>${_dbf}/.sql
      echo -n "  \"$i\"\n    varchar" >>${_dbf}/.sql
      fl="1"
    done
    echo ");" >>${_dbf}/.sql
    # преобразование данных
    echo "    -- data"
    echo "COPY \"$dbfn\"(" >>${_dbf}/.sql
    fl="0"
    for i in ${fld}; do
      [ "$fl" = "1" ] && echo ',' >>${_dbf}/.sql
      echo -n "  \"$i\"" >>${_dbf}/.sql
      fl="1"
    done
    echo ") FROM stdin;" >>${_dbf}/.sql
    # получаем данные DBF, разделенные '~' | склеиваем "висящие" строки ([\t\r\n] в теле поля данных) | DOS2WIN | убираем все '\t' | убираем концевые ';' | заменяем ';'->'\t'
    dbview -d~ -b -t $dbf | sed -e :a -e '/[\r\t]$/N; s/[\r\t]\n//g; ta' | recode CP866..CP1251 | sed -e 's/\t//g; s/~\r//g; s/~,/,/g; s/~/\t/g' >>${_dbf}/.sql
    echo "\\." >>${_dbf}/.sql
  done

  # интеграция процедуры обновления базы - последовательное подключение всех sql-файлов импорта
  ls ${dir}/import/*.sql | xargs -l readlink -f | xargs -l -I{} cat {} >>${_dbf}/.sql
  # вставка метки обновления
  echo "INSERT INTO kladr_upd(lm, hostname) VALUES('$lm', '`hostname`');" >>${_dbf}/.sql
fi
echo "COMMIT;" >>${_dbf}/.sql

cd $dir
rm -rf $tmp

echo "`date '+%F %T'` ==== Processing SQL"
psql -h $pghost -p $pgport -U $pguser -w -f ${_dbf}/.sql $pgbase
rv="$?"

if [ "$rv" = "0" ]; then
  rm -rf ${_dbf}/ 2>/dev/null
fi
echo "`date '+%F %T'` ==== Exit : $rv"

exit "$rv"

Тут можно использовать для импорта и временные каталоги, создаваемые через mktemp, но хватит ли у вас места на tmp-разделе?..

Реквизиты доступа к базе и КЛАДР-источник в нашем случае будут храниться в app.conf:

pghost="kladr.tensor.ru"
pgport="5432"
pguser="postgres"
pgpass="postgres"
pgbase="kladr"
source="https://gnivc.ru/html/gnivcsoft/KLADR/Base.7z"

Поисковая база

А зачем нам вообще нужна какая-то другая структура? Чем нас не устраивают таблицы в оригинальном КЛАДР-архиве?

  • хранение адресных объектов (улиц и населенных пунктов) в разных структурах

  • хранение статуса/версии объекта (00, 51, ...) вместе с его кодом

  • невозможность наложить эффективные для поиска индексы

Напомню, что оригинальный код КЛАДР, согласно документации имеет вид СС РРР ГГГ ППП УУУУ АА, где:

  • СС - код субъекта РФ (региона)

  • РРР - код района

  • ГГГ - код города

  • ППП - код населенного пункта

  • УУУУ - код улицы (отсутствует у населенных пунктов)

  • АА - признак актуальности

То есть если вынести признак актуальности в отдельное поле, то у кодов многих объектов (например, городов) в конце окажутся нули, которые стоит безболезненно отсечь. И тогда коды будут иметь строго ограниченный набор длин в соответствии с "уровнем" объекта:

  • 2 - регионы и города федерального подчинения (77 - г. Москва, 76 - Ярославская обл.)

  • 5 - районы (76 001 - Ярославская обл., Ярославский р-н)

  • 8 - города (76 000 001 - Ярославская обл., г. Ярославль)

  • 11 - населенные пункты (76 001 000 008 - Ярославская обл., Ярославский р-н, д.Алешково)

  • 15 - улицы (76 001 000 008 0001 - Ярославская обл., Ярославский р-н, д.Алешково, ул.Малиновая)

При этом, как видим, коды "вышестоящих" объектов становятся префиксами кодов объектов вложенных.

Что же получилось по структуре?

-- основная таблица хранения адресных объектов
CREATE TABLE kladr(
  code
    varchar,
  status
    varchar,
  name
    varchar,
  abbr
    varchar,
  idx
    varchar,
  ifns
    varchar,
  ocato
    varchar,
  lvl
    smallint,
  norm
    varchar,
  PRIMARY KEY(code, status)
);
-- индекс по почтовому индексу
CREATE INDEX "kladr-idx"
  ON kladr(idx);
-- префиксный индекс по названию объекта
CREATE INDEX "kladr-nm"
  ON kladr(length(code), code varchar_pattern_ops, lower(name));

-- дома, привязанные к адресным объектам
CREATE TABLE kladr_house(
  code
    varchar,
  codeExt
    varchar,
  name
    varchar,
  idx
    varchar,
  ifns
    varchar,
  ocato
    varchar,
  PRIMARY KEY(code, codeExt)
);

-- замены и объединения объектов
CREATE TABLE kladr_repl(
  oldCode
    varchar,
  newCode
    varchar,
  PRIMARY KEY(oldCode, newCode)
);
CREATE INDEX "kladr-repl-new"
  ON kladr_repl(newCode);
CREATE INDEX "kladr-repl-old"
  ON kladr_repl(oldCode);

-- аббревиатуры административно-территориальных единиц ("ул", "пер", "г")
CREATE TABLE kladr_abbr(
  code
    varchar,
  lvl
    smallint,
  name
    varchar,
  PRIMARY KEY(code, lvl)
);
-- индекс по возможному "уровню" объекта
CREATE INDEX "kladr-abbr-lvl"
  ON kladr_abbr(lvl);

Это основные таблицы, данные в которые импортируются непосредственно из соответствующих DBF по модели наложения "диффов", описанной в статье "DBA: грамотно организовываем синхронизации и импорты":

kladr-abbr
--// КЛАДР : сокращения

-- создаем временную таблицу с импортируемыми данными КЛАДР'а
CREATE TEMPORARY TABLE _kladr_abbr(
  LIKE kladr_abbr INCLUDING INDEXES
);

-- заполняем преобразованными из DBF данными
INSERT INTO _kladr_abbr(code, lvl, name)
  SELECT
    "SCNAME",
    "KOD_T_ST"::smallint,
    "SOCRNAME"
  FROM
    "SOCRBASE.DBF";

-- удаляем отсутствующие
DELETE FROM
  kladr_abbr T
USING
  kladr_abbr X LEFT JOIN
  _kladr_abbr Y
    USING(code, lvl)
WHERE
  (T.code, T.lvl) = (X.code, X.lvl) AND
  Y IS NULL;

-- обновляем оставшиеся
UPDATE
  kladr_abbr kl
SET
  name = kli.name
FROM
  _kladr_abbr kli
WHERE
  (kl.code, kl.lvl) = (kli.code, kli.lvl) AND
  (
    kl.name
  ) IS DISTINCT FROM
  (
    kli.name
  );

-- очищаем совпадающие
DELETE FROM
  _kladr_abbr kli
USING
  kladr_abbr kl
WHERE
  (kli.code, kli.lvl) = (kl.code, kl.lvl);

-- вставляем оставшиеся
INSERT INTO kladr_abbr
  SELECT
    *
  FROM
    _kladr_abbr;
kladr
--// КЛАДР : от регионов до улиц

-- создаем временную таблицу с импортируемыми данными КЛАДР'а
CREATE TEMPORARY TABLE _kladr(
  LIKE kladr INCLUDING INDEXES
);

-- заполняем преобразованными из DBF данными
INSERT INTO _kladr(code, status, name, abbr, idx, ifns, ocato, lvl)
  SELECT DISTINCT ON(code, status)
    *
  FROM
    (
      SELECT
        regexp_replace(rpad(substr("CODE", 1, length("CODE") - 2), 15, '0'), '^(.{0,}?)((((0{3})?0{3})?0{3})?0{4})?$', E'\\1', 'ig') code,
        substr("CODE", length("CODE") - 1, 2) status,
        "NAME",
        "SOCR",
        nullif("INDEX", ''),
        nullif("GNINMB", ''),
        nullif("OCATD", ''),
        "STATUS"
      FROM
        (
          SELECT
            "CODE",
            "NAME",
            "SOCR",
            "INDEX",
            "GNINMB",
            "OCATD",
            "STATUS"::smallint
          FROM
            "KLADR.DBF"
        UNION ALL
          SELECT
            "CODE",
            "NAME",
            "SOCR",
            "INDEX",
            "GNINMB",
            "OCATD",
            NULL::smallint "STATUS"
          FROM
            "STREET.DBF"
        ) T
    ) T;

-- удаляем отсутствующие
DELETE FROM
  kladr T
USING
  kladr X LEFT JOIN
  _kladr Y
    USING(code, status)
WHERE
  (T.code, T.status) = (X.code, X.status) AND
  Y IS NULL;

-- обновляем оставшиеся
UPDATE
  kladr kl
SET
  (
    name,
    abbr,
    idx,
    ifns,
    ocato,
    lvl
  ) =
  (
    kli.name,
    kli.abbr,
    kli.idx,
    kli.ifns,
    kli.ocato,
    kli.lvl
  )
FROM
  _kladr kli
WHERE
  (kl.code, kl.status) = (kli.code, kli.status) AND
  (
    kl.name,
    kl.abbr,
    kl.idx,
    kl.ifns,
    kl.ocato,
    kl.lvl
  ) IS DISTINCT FROM
  (
    kli.name,
    kli.abbr,
    kli.idx,
    kli.ifns,
    kli.ocato,
    kli.lvl
  );

-- очищаем совпадающие
DELETE FROM
  _kladr kli
USING
  kladr kl
WHERE
  (kli.code, kli.status) = (kl.code, kl.status);

-- вставляем оставшиеся
INSERT INTO kladr
  SELECT
    *
  FROM
    _kladr;

-- обновляем поисковый кэш
DELETE FROM
  kladr_kw
WHERE
  (code, status) IN (
    SELECT
      (ro).code,
      (ro).status
    FROM
      kladr$log
    WHERE
      ro IS DISTINCT FROM NULL
  );

INSERT INTO
  kladr_kw(code, status, keyword)
SELECT DISTINCT
  code,
  status,
  kw
FROM
  (
    SELECT
      (rn).code,
      (rn).status,
      regexp_split_to_table(lower((rn).name), E'[^\\-a-zа-яё0-9]+', 'i') kw
    FROM
      kladr$log
    WHERE
      rn IS DISTINCT FROM NULL
  ) T
WHERE
  kw <> '';

DELETE FROM kladr$log;

Здесь регулярное выражение используется для отсечения "хвостовых" нулей по маске до необходимой нам длины. То есть нельзя просто так взять 76 000 010 000 и убрать все 4 последних ноля, поскольку 010 тут является значимым кодом города.

kladr-house
--// КЛАДР : дома

-- создаем временную таблицу с импортируемыми данными КЛАДР'а
CREATE TEMPORARY TABLE _kladr_house(
  LIKE kladr_house INCLUDING INDEXES
);

-- заполняем преобразованными из DBF данными
INSERT INTO _kladr_house(code, codeExt, name, idx, ifns, ocato)
  SELECT
    regexp_replace(substr("CODE", 1, 15), '^(.{0,}?)((((0{3})?0{3})?0{3})?0{4})?$', E'\\1', 'ig'),
    substr("CODE", 16, 4),
    "NAME",
    nullif("INDEX", ''),
    nullif("GNINMB", ''),
    nullif("OCATD", '')
  FROM
    "DOMA.DBF";

-- удаляем отсутствующие
DELETE FROM
  kladr_house T
USING
  kladr_house X LEFT JOIN
  _kladr_house Y
    USING(code, codeExt)
WHERE
  (T.code, T.codeExt) = (X.code, X.codeExt) AND
  Y IS NULL;

-- обновляем оставшиеся
UPDATE
  kladr_house kl
SET
  (
    name,
    idx,
    ifns,
    ocato
  ) =
  (
    kli.name,
    kli.idx,
    kli.ifns,
    kli.ocato
  )
FROM
  _kladr_house kli
WHERE
  (kl.code, kl.codeExt) = (kli.code, kli.codeExt) AND
  (
    kl.name,
    kl.idx,
    kl.ifns,
    kl.ocato
  ) IS DISTINCT FROM
  (
    kli.name,
    kli.idx,
    kli.ifns,
    kli.ocato
  );

-- очищаем совпадающие
DELETE FROM
  _kladr_house kli
USING
  kladr_house kl
WHERE
  (kli.code, kli.codeExt) = (kl.code, kl.codeExt);

-- вставляем оставшиеся
INSERT INTO kladr_house
  SELECT
    *
  FROM
    _kladr_house;

-- обновляем поисковый кэш
DELETE FROM
  kladr_hs
WHERE
  (code) IN (
      SELECT
        (ro).code
      FROM
        kladr_house$log
      WHERE
        ro IS DISTINCT FROM NULL
    UNION ALL
      SELECT
        (rn).code
      FROM
        kladr_house$log
      WHERE
        rn IS DISTINCT FROM NULL
  );

-- заполняем преобразованными данными
CREATE TEMPORARY TABLE _kladr_hs0 AS
  SELECT DISTINCT ON(code, house)
    code,
    idx,
    ifns,
    ocato,
    unnest(houses) house
  FROM
    (
      SELECT
        *,
        CASE
          WHEN _range IS NULL AND name ~ E'_' THEN ARRAY[regexp_replace(name, '_', '-')]
          WHEN _range IS NULL THEN ARRAY[name]
          WHEN _range IS NOT NULL THEN ARRAY(
            SELECT
              i::text
            FROM
              generate_series(_range[1]::integer + CASE WHEN _range[4] IS NOT NULL THEN (_range[1]::integer + _range[4]::integer) % 2 ELSE 0 END, _range[2]::integer, _range[3]::integer) i
          )
          ELSE NULL
        END houses
      FROM
        (
          SELECT
            code,
            idx,
            ifns,
            ocato,
            name,
            CASE
              WHEN name ~ E'^Н\\(\\d+-\\d+\\)$' THEN regexp_split_to_array(substr(name, 3, length(name) - 3), '-') || '2'::text || '1'::text
              WHEN name ~ E'^Ч\\(\\d+-\\d+\\)$' THEN regexp_split_to_array(substr(name, 3, length(name) - 3), '-') || '2'::text || '0'::text
              WHEN name = 'Н' THEN '{1,999,2}'::text[]
              WHEN name = 'Ч' THEN '{2,998,2}'::text[]
              WHEN name ~ E'^\\d+-\\d+$' THEN regexp_split_to_array(name, '-') || '1'::text
              ELSE NULL
            END _range
          FROM
            (
              SELECT
                code,
                idx,
                ifns,
                ocato,
                unnest(regexp_split_to_array(upper(name), ',')) "name"
              FROM
                kladr_house
              WHERE
                (code) IN (
                    SELECT
                      (ro).code
                    FROM
                      kladr_house$log
                    WHERE
                      ro IS DISTINCT FROM NULL
                  UNION ALL
                    SELECT
                      (rn).code
                    FROM
                      kladr_house$log
                    WHERE
                      rn IS DISTINCT FROM NULL
                )
            ) T
        ) T
    ) T
ORDER BY
  code, house, (_range IS NULL) DESC;

CREATE INDEX ON _kladr_hs0(code, house, idx DESC NULLS LAST);

CREATE TEMPORARY TABLE _kladr_hs1 AS
  SELECT DISTINCT ON (code, house)
    code,
    idx,
    ifns,
    ocato,
    house
  FROM
    _kladr_hs0
  ORDER BY
    code, house, idx DESC NULLS LAST;

CREATE INDEX ON _kladr_hs1(code, house);

CREATE TEMPORARY TABLE _kladr_hs2 AS
  SELECT
    code,
    coalesce(
      idx, 
      coalesce(
        (
          SELECT
            idx
          FROM
            _kladr_hs1
          WHERE
            (code, house) = (T.code, regexp_replace(T.house, E'^(\\d+)(\\D)?.*$', E'\\1', 'ig'))
          LIMIT 1
        ), 
        coalesce(
          (
            SELECT
              idx
            FROM
              kladr
            WHERE
              code IN (
                substr(T.code, 1, 15),
                substr(T.code, 1, 11),
                substr(T.code, 1,  8),
                substr(T.code, 1,  5),
                substr(T.code, 1,  2)
              ) AND
--              status = '00' AND
              idx IS NOT NULL
            ORDER BY
              length(code) DESC
            LIMIT 1
          ),
          ''
        )
      )
    ) idx,
    ifns,
    ocato,
    house
  FROM
    _kladr_hs1 T;

CREATE INDEX ON _kladr_hs2(code, idx, ifns, ocato, house);

INSERT INTO kladr_hs(code, idx, ifns, ocato, houses)
  SELECT
    code,
    idx,
    ifns,
    ocato,
    array_agg(house ORDER BY house) houses
  FROM
    _kladr_hs2
  GROUP BY
    1, 2, 3, 4;

DELETE FROM kladr_house$log;

Здесь регулярными выражениями мы приводим форматы исходной базы в списки конкретных номеров домов:

  • 5-11 -> {5, 6, 7, 8, 9, 10, 11}

  • Н(5-11) -> {5, 7, 9, 11}

  • Ч(6-10) -> {6, 8, 10}

  • Н -> {1, 3, ..., 999}

  • Ч -> {2, 4, ..., 998}

kladr-repl
--// КЛАДР : замены

-- создаем временную таблицу с импортируемыми данными КЛАДР'а
CREATE TEMPORARY TABLE _kladr_repl(
  LIKE kladr_repl INCLUDING INDEXES
);

-- заполняем преобразованными из DBF данными
INSERT INTO _kladr_repl(oldCode, newCode)
  SELECT DISTINCT
    co,
    cn
  FROM
    (
      SELECT
        regexp_replace(rpad(co, 15, '0'), '^(.{0,}?)((((0{3})?0{3})?0{3})?0{4})?$', E'\\1', 'ig') co,
        so,
        regexp_replace(rpad(cn, 15, '0'), '^(.{0,}?)((((0{3})?0{3})?0{3})?0{4})?$', E'\\1', 'ig') cn,
        sn
      FROM
        (
          SELECT
            *,
            substr("OLDCODE", 1, length("OLDCODE") - 2) co,
            substr("OLDCODE", length("OLDCODE") - 1, 2) so,
            substr("NEWCODE", 1, length("NEWCODE") - 2) cn,
            substr("NEWCODE", length("NEWCODE") - 1, 2) sn
          FROM
            "ALTNAMES.DBF"
        ) T
    ) T;

-- удаляем отсутствующие
DELETE FROM
  kladr_repl T
USING
  kladr_repl X LEFT JOIN
  _kladr_repl Y
    USING(oldCode, newCode)
WHERE
  (T.oldCode, T.newCode) = (X.oldCode, X.newCode) AND
  Y IS NULL;

-- очищаем совпадающие
DELETE FROM
  _kladr_repl kli
USING
  kladr_repl kl
WHERE
  (kli.oldCode, kli.newCode) = (kl.oldCode, kl.newCode);

-- вставляем оставшиеся
INSERT INTO kladr_repl
  SELECT
    *
  FROM
    _kladr_repl;

Тут мы использовали еще две "производные" таблицы, которые выступают в качестве поисковых индексов - kladr_kw и kladr_hs:

-- ключевые слова адресных объектов
CREATE TABLE kladr_kw(
  code
    varchar,
  status
    varchar,
  keyword
    varchar
);
CREATE INDEX "kladr-kw-cd"
  ON kladr_kw(code, status);
CREATE INDEX "kladr-kw-kwcd"
  ON kladr_kw(keyword varchar_pattern_ops, code varchar_pattern_ops);
CREATE INDEX "kladr-kw-cdkw"
  ON kladr_kw(code varchar_pattern_ops, keyword varchar_pattern_ops);

-- группы номеров домов с одинаковыми признаками
CREATE TABLE kladr_hs(
  code
    varchar,
  idx
    varchar,
  ifns
    varchar,
  ocato
    varchar,
  houses
    varchar[]
);
CREATE INDEX "kladr-hs-code"
  ON kladr_hs(code);
CREATE INDEX "kladr-hs-idx"
  ON kladr_hs(idx);

-- специальные "уровневые" индексы
DO $$
DECLARE
  ln integer[] = '{2,5,8,11,15}'::integer[];
BEGIN
  FOR i IN 1..array_length(ln, 1) LOOP
    EXECUTE '
CREATE INDEX "kladr-' || lpad(ln[i]::text, 2, '0') || '"
  ON kladr(code varchar_pattern_ops, status)
WHERE
  length(code) = ' || ln[i] || ';
CREATE INDEX "kladr-kw-' || lpad(ln[i]::text, 2, '0') || '"
  ON kladr_kw(keyword varchar_pattern_ops)
WHERE
  length(code) = ' || ln[i] || ';
CREATE INDEX "kladr-kw-kwcd-' || lpad(ln[i]::text, 2, '0') || '"
  ON kladr_kw(keyword varchar_pattern_ops, code varchar_pattern_ops)
WHERE
  length(code) = ' || ln[i] || ';
    ';
  END LOOP;
END$$ LANGUAGE plpgsql;

Зачем нам понадобятся такие дополнительные структуры, и как их использовать для организации эффективного подстрочного поиска, рассмотрим в следующей части статьи, а эту мы закончим функцией нормализации названия объекта:

normalize.sql
CREATE OR REPLACE FUNCTION normalize(obj kladr) RETURNS text AS $$
  SELECT
    CASE
      -- регионы
      WHEN length(code) = 2 THEN
        regexp_replace(
          regexp_replace(
            (
              CASE
                WHEN abbr = 'Респ' AND name !~* '(ая)$' THEN
                  abbrfull || ' ' || name
                WHEN abbr = 'край' THEN
                  name || ' ' || abbr
                WHEN abbr = 'обл' THEN
                  name || ' ' || abbr || '.'
                WHEN abbr = 'г' THEN
                  abbr || '.' || name
                WHEN abbr IN ('АО', 'Аобл') THEN
                  regexp_replace(name, ' Автономный округ', '', 'ig') || ' АО'
                ELSE
                  name || ' ' || abbrfull
              END
            ),
            E'/([а-я]+)/',
            E'- \\1',
            'ig'
          ),
          E'^(.{0,}?) - ([а-я]+)(.{0,}?)$',
          E'\\1\\3 (\\2)',
          'ig'
        )
      -- районы
      WHEN length(code) = 5 THEN
        CASE
          -- (14xxx) улус
          WHEN abbr = 'у' THEN
            initcap(name) || ' ' || lower(abbrfull)
          -- (24048)
          WHEN abbr = 'АО' THEN
            initcap(name) || ' ' || abbr
          ELSE
            initcap(name) || ' ' || abbr || (CASE WHEN abbr ~ '(-|/)' THEN '' ELSE '.' END)
        END
      -- города и населенные пункты
      WHEN length(code) IN (8, 11) THEN
        CASE
          WHEN abbr IN ('тер', 'г', 'с/п', 'дп') THEN
            regexp_replace(
              regexp_replace(
                CASE
                  WHEN name ~* E'^ДНП ' THEN
                    regexp_replace(name, E'^ДНП ', 'днп ', 'ig')
                  WHEN name ~* E'^ДНТ ' THEN
                    regexp_replace(name, E'^ДНТ ', 'днт ', 'ig')
                  WHEN name ~* E'^ДСК ' THEN
                    regexp_replace(name, E'^ДСК ', 'дск ', 'ig')
                  WHEN name ~* E'^ДСПК ' THEN
                    regexp_replace(name, E'^ДСПК ', 'дспк ', 'ig')
                  WHEN name ~* E'^ДПК ' THEN
                    regexp_replace(name, E'^ДПК ', 'дпк ', 'ig')
                  WHEN name ~* E'^НСТ ' THEN
                    regexp_replace(name, E'^НСТ ', 'нст ', 'ig')
                  WHEN name ~* E'^СДТ ' THEN
                    regexp_replace(name, E'^СДТ ', 'сдт ', 'ig')
                  WHEN name ~* E'^СК ' THEN
                    regexp_replace(name, E'^СК ', 'ск ', 'ig')
                  WHEN name ~* E'^С/К ' THEN
                    regexp_replace(name, E'^С/К ', 'ск ', 'ig')
                  WHEN name ~* E'^СКТ ' THEN
                    regexp_replace(name, E'^СКТ ', 'скт ', 'ig')
                  WHEN name ~* E'^СТ ' THEN
                    regexp_replace(name, E'^СТ ', 'ст ', 'ig')
                  WHEN name ~* E'^С/Т ' THEN
                    regexp_replace(name, E'^С/Т ', 'ст ', 'ig')
                  WHEN name ~* E'^СНТ ' THEN
                    regexp_replace(name, E'^СНТ ', 'снт ', 'ig')
                  WHEN name ~* E'^ПКС ' THEN
                    regexp_replace(name, E'^ПКС ', 'пкс ', 'ig')
                  WHEN name ~* E'^ПКСТ ' THEN
                    regexp_replace(name, E'^ПКСТ ', 'пкст ', 'ig')
                  WHEN name ~* E'^ПОС ' THEN
                    regexp_replace(name, E'^ПОС ', 'пос ', 'ig')
                  WHEN name ~* E'^массив ' THEN
                    regexp_replace(name, E'^массив ', 'массив ', 'ig')
                  WHEN name ~* E' СНТ$' THEN
                    regexp_replace(name, E'^(.*) СНТ$', E'снт \\1', 'ig')
                  WHEN name ~* E' СТ$' THEN
                    regexp_replace(name, E'^(.*) СТ$', E'ст \\1', 'ig')
                  WHEN name ~* E' ДНТ$' THEN
                    regexp_replace(name, E'^(.*) ДНТ$', E'днт \\1', 'ig')
                  WHEN name ~* E' ДПК$' THEN
                    regexp_replace(name, E'^(.*) ДПК$', E'дпк \\1', 'ig')
                  WHEN name ~* E'^уч-к СДТ ' THEN
                    regexp_replace(name, E'^уч-к СДТ ', 'сдт ', 'ig')
                  WHEN name ~* E'^уч-к ' THEN
                    regexp_replace(name, E'^уч-к ', '', 'ig')
                  WHEN name ~* E'^СПК ' THEN
                    regexp_replace(name, E'^СПК ', 'спк ', 'ig')
                  WHEN name ~* E'^сот ' THEN
                    regexp_replace(name, E'^сот ', 'сот ', 'ig')
                  WHEN name ~* E'^урочище ' THEN
                    regexp_replace(name, E'^урочище ', 'урочище ', 'ig')
                  WHEN name ~* E'^КС ' THEN
                    regexp_replace(name, E'^КС ', 'кс ', 'ig')
                  WHEN name ~* E'^МО пгт.' THEN
                    regexp_replace(name, E'^МО пгт.', 'пгт ', 'ig')
                  WHEN name ~* E'^МО город ' THEN
                    regexp_replace(name, E'^МО город ', 'г.', 'ig')
                  WHEN name ~* E'^МО городское поселение ' THEN
                    regexp_replace(name, E'^МО городское поселение ', 'г/п ', 'ig')
                  WHEN name ~* E'^МО поселок ' THEN
                    regexp_replace(name, E'^МО поселок ', 'пос.', 'ig')
                  WHEN name ~* E'^МО СП ' THEN
                    regexp_replace(name, E'^МО СП (.*)', E'с/п \\1', 'ig')
                  WHEN name ~* E'^МО ГП поселок ' THEN
                    regexp_replace(name, E'^МО ГП поселок (.*)', E'пгт \\1', 'ig')
                  WHEN name ~* E'^МО сельское поселение ' THEN
                    regexp_replace(name, E'^МО сельское поселение (.*)', E'с/п \\1', 'ig')
                  WHEN name ~* E'^МО селское поселение ' THEN
                    regexp_replace(name, E'^МО селское поселение (.*)', E'с/п \\1', 'ig')
                  WHEN name ~* E'^МО (.*) сельское поселение$' THEN
                    regexp_replace(name, E'^МО (.*) сельское поселение$', E'с/п \\1', 'ig')
                  WHEN name ~* ' сельское поселение$' THEN
                    regexp_replace(name, '^(.*) сельское поселение$', E'с/п \\1', 'ig')
                  WHEN name ~* ' селское поселение$' THEN
                    regexp_replace(name, '^(.*) селское поселение$', E'с/п \\1', 'ig')
                  WHEN name ~* ' городское поселение$' THEN
                    regexp_replace(name, '^(.*) городское поселение$', E'г/п \\1', 'ig')
                  WHEN name ~* ' городской округ$' THEN
                    regexp_replace(name, '^(.*) городской округ$', E'\\1 г/о', 'ig')
                  WHEN name ~* '^Городское поселение ' THEN
                    regexp_replace(name, '^Городское поселение ', E'г/п ', 'ig')
                  ELSE
                    CASE
                      WHEN abbr IN ('с/п', 'дп') THEN abbr || ' ' || name
                      ELSE abbr || '.' || name
                    END
                END,
                E'^(г/п|с/п) (.*(?:ое))$',
                E'\\2 \\1',
                'ig'
              ),
              E'(\\S)\\(',
              E'\\1 (',
              'ig'
            )
          WHEN abbr IN ('высел') THEN
            CASE
              WHEN name ~ E'(.*((?:ие)))$' THEN
                name || ' выселки'
              ELSE
                'выселок ' || name
            END
          WHEN abbr IN ('городок') THEN
            CASE
              WHEN name ~* E'(^городок|городок$)' THEN
                name
              WHEN name ~* E'военный' THEN
                regexp_replace(name, E'(военный)(\\s?)', E'\\1 городок\\2', 'ig')
              ELSE
                abbr || ' ' || name
            END
          WHEN abbr IN ('с/а', 'с/мо') THEN
            regexp_replace(abbr || ' ' || name, E'^(' || abbr || ') (.*((?:ая)|(?:ое)|(?:ий)|(?:ый)))$', E'\\2 \\1', 'ig')
          WHEN abbr IN ('п/о', 'пгт', 'массив') THEN abbr || ' ' || name
          WHEN abbr = 'с/с' THEN (
            CASE
              WHEN name ~* E'^Рабочий поселок' THEN regexp_replace(name, E'^Рабочий поселок', 'рабочий поселок', 'ig')
              WHEN name ~* E'^р\\.п\\.' THEN regexp_replace(name, E'^р\\.п\\.', 'рабочий поселок', 'ig')
              ELSE name || ' ' || abbr
            END
          )
          WHEN abbr = 'ж/д_будка' THEN
            'ж/д будка ' || regexp_replace(name, E'(будка|будка железной дороги|железнодорожная будка)\\s*', E'', 'ig')
          WHEN abbr = 'ж/д_казарм' THEN
            'ж/д казарма ' || regexp_replace(name, E'(казарма)\\s*', E'', 'ig')
          WHEN abbr = 'ж/д_оп' THEN
            'ж/д о/п ' || name
          WHEN abbr = 'ж/д_платф' THEN
            'ж/д платформа ' || regexp_replace(name, E'(платформа|платформа железной дороги|железнодорожная платформа)\\s*', E'', 'ig')
          WHEN abbr = 'ж/д_пост' THEN
            CASE
              WHEN name ~* E'^Блокпост' THEN
                name
              WHEN name ~* E'^(железнодорожная )?будка' THEN
                regexp_replace(name, E'^(железнодорожная )?будка', E'ж/д будка', 'ig')
              WHEN name ~* E'^разъезд ' THEN
                regexp_replace(name, E'^разъезд', E'ж/д разъезд', 'ig')
              ELSE
                'ж/д пост ' || name
            END
          WHEN abbr = 'ж/д_рзд' THEN
            'ж/д разъезд ' || name
          WHEN abbr = 'ж/д_ст' THEN
            'ж/д станция ' || name
          WHEN abbr = 'казарма' THEN
            CASE
              WHEN name ~* E'^Блокпост' THEN
                name
              WHEN name ~* E'^железнодорожная\\s+' THEN
                regexp_replace(name, E'^железнодорожная\\s+', E'ж/д казарма ', 'ig')
              ELSE
                'казарма ' || regexp_replace(name, E'^казарма\\s*', E'', 'ig')
            END
          WHEN abbr = 'мкр' THEN
            CASE
              WHEN name ~* E'^(.*-й|\\d+)$' THEN
                name || ' ' || lower(abbrfull)
              WHEN name ~* E'^(\\d+[а-я])$' THEN
                lower(abbrfull) || ' ' || name
              ELSE
                abbr || '.' || name
            END
          WHEN abbr IN ('нп','с','д','тер','п','жилзона','жилрайон','кв-л','х','ст-ца','рп','рзд','м','ст','у','снт','сл') THEN
            CASE
              WHEN name ~* E'^остров\\s+' THEN
                regexp_replace(name, E'^остров', E'остров', 'ig')
              WHEN name ~* E'^казарма\\s+' THEN
                regexp_replace(name, E'^казарма', E'казарма', 'ig')
              WHEN name ~* E'^кордон\\s+' THEN
                regexp_replace(name, E'^кордон', E'кордон', 'ig')
              WHEN name ~* E'^жилой район ' THEN
                regexp_replace(name, E'^жилой район ', 'жилой район ', 'ig')
              WHEN name ~* E' жилой район$' THEN
                regexp_replace(name, E'^(.*) жилой район$', E'жилой район \\1', 'ig')
              WHEN name ~* E'^жилая зона ' THEN
                regexp_replace(name, E'^жилая зона ', 'жилая зона ', 'ig')
              WHEN name ~* E' жилая зона$' THEN
                regexp_replace(name, E'^(.*) жилая зона$', E'жилая зона \\1', 'ig')
              WHEN name ~* E'^д/о ' THEN
                regexp_replace(name, E'^д/о ', 'д/о ', 'ig')
              WHEN name ~* E'^п/о ' THEN
                regexp_replace(name, E'^п/о ', 'п/о ', 'ig')
              WHEN name ~* E'^п/л ' THEN
                regexp_replace(name, E'^п/л ', 'п/л ', 'ig')
              WHEN name ~* E'^т/б ' THEN
                regexp_replace(name, E'^т/б ', 'т/б ', 'ig')
              WHEN name ~* E'^СНТ ' THEN
                regexp_replace(name, E'^СНТ ', 'снт ', 'ig')
              WHEN name ~* E' СНТ$' THEN
                regexp_replace(name, E'^(.*) СНТ$', E'снт \\1', 'ig')
              WHEN name ~* E'^СДТ ' THEN
                regexp_replace(name, E'^СДТ ', 'сдт ', 'ig')
              WHEN name ~* E' СДТ$' THEN
                regexp_replace(name, E'^(.*) СДТ$', E'сдт \\1', 'ig')
              WHEN name ~* E'^СТ ' THEN
                regexp_replace(name, E'^СТ ', 'ст ', 'ig')
              WHEN name ~* E' СТ$' THEN
                regexp_replace(name, E'^(.*) СТ$', E'ст \\1', 'ig')
              WHEN name ~* E'^ДНТ ' THEN
                regexp_replace(name, E'^ДНТ ', 'днт ', 'ig')
              WHEN name ~* E' ДНТ$' THEN
                regexp_replace(name, E'^(.*) ДНТ$', E'днт \\1', 'ig')
              WHEN name ~* E'^ДНП ' THEN
                regexp_replace(name, E'^ДНП ', 'днп ', 'ig')
              WHEN name ~* E' ДНП$' THEN
                regexp_replace(name, E'^(.*) ДНП$', E'днп \\1', 'ig')
              WHEN name ~* E'^будка( (ж/д|железной дороги))?' THEN
                regexp_replace(name, E'^будка( (ж/д|железной дороги))?', E'ж/д будка', 'ig')
              WHEN name ~* E'^железнодорожная будка' THEN
                regexp_replace(name, E'^железнодорожная будка', E'ж/д будка', 'ig')
              WHEN name ~* E'^платф.+' THEN
                regexp_replace(name, E'^(платф)\\s+', E'платформа ', 'ig')
              WHEN name ~* E'^поселок.+' THEN
                regexp_replace(name, E'^(поселок)\\s+', E'п.', 'ig')
              WHEN name ~* E'^(рзд|разъезд).+' THEN
                regexp_replace(name, E'^(рзд|разъезд)\\s+', E'разъезд ', 'ig')
              WHEN name ~* E'^участок.+' THEN
                regexp_replace(name, E'^(участок)\\s+', E'участок ', 'ig')
              WHEN name ~* E'^(урочище|ур-ще) ' THEN
                regexp_replace(name, E'^(урочище|ур-ще) ', 'урочище ', 'ig')
--              WHEN name ~* E'^ж/д' THEN
--                name
              ELSE
                CASE
                  WHEN abbr IN ('с', 'д', 'тер', 'п') THEN
                    abbr || '.' || name
                  WHEN abbr IN ('жилзона','жилрайон','кв-л','х','ст-ца','рп','рзд','м','ст','у') THEN
                    lower(abbrfull) || ' ' || name
                  WHEN abbr IN ('снт') THEN
                    abbr || ' ' || name
                  WHEN abbr IN ('сл') THEN
                    regexp_replace(lower(abbrfull) || ' ' || name, E'^(' || lower(abbrfull) || ') (.*((?:ая)))$', E'\\2 \\1', 'ig')
                  WHEN abbr IN ('нп') THEN
                    'н/п ' || name
                  ELSE
                    name
                END
            END
          ELSE
            CASE
              WHEN length(code) = 8 THEN
                name || ' ' || abbr
              ELSE
                abbr || ' ' || name
            END
        END
      -- улицы
      WHEN length(code) = 15 THEN
        CASE
          WHEN abbr IN ('высел') THEN
            CASE
              WHEN name ~ E'(.*((?:ие)))$' THEN
                name || ' выселки'
              ELSE
                'выселок ' || name
            END
          WHEN abbr IN ('гск') THEN
            CASE
              WHEN name ~* E'^ГСК ' THEN
                regexp_replace(name, E'^ГСК ', 'гск ', 'ig')
              WHEN name ~* E'^Г СК ' THEN
                regexp_replace(name, E'^Г СК ', 'гск ', 'ig')
              ELSE
                lower(abbr) || ' ' || name
            END
          WHEN abbr IN ('ж/д_будка') THEN
            CASE
              WHEN name ~* E'^((железнодорожная )?будка|ждб|ж-д будка)' THEN
                regexp_replace(name, E'^((железнодорожная )?будка|ждб|ж-д будка)', E'ж/д будка', 'ig')
              WHEN name ~* E'^железнодорожная\\s+' THEN
                regexp_replace(name, E'^железнодорожная', E'ж/д будка', 'ig')
              WHEN name ~* E'^железнодорожная$' THEN
                regexp_replace(name, E'^железнодорожная$', E'Железнодорожная будка', 'ig')
              ELSE
                'ж/д будка ' || name
            END
          WHEN abbr IN ('ж/д_казарм') THEN
            CASE
              WHEN name ~* E'^казарма\\s+' THEN
                regexp_replace(name, E'^казарма', E'ж/д казарма', 'ig')
              ELSE
                'ж/д казарма ' || name
            END
          WHEN abbr IN ('ж/д_оп') THEN
            'ж/д о/п ' || name
          WHEN abbr = 'ж/д_платф' THEN
            'ж/д платформа ' || regexp_replace(name, E'(платформа|платформа железной дороги|железнодорожная платформа)\\s*', E'', 'ig')
          WHEN abbr IN ('аал','аул') THEN
            lower(abbrfull) || ' ' || name
          WHEN abbr = 'ж/д_пост' THEN
            CASE
              WHEN name ~* E'^Блокпост' THEN
                name
              WHEN name ~* E'^(железнодорожная )?будка' THEN
                regexp_replace(name, E'^(железнодорожная )?будка', E'ж/д будка', 'ig')
              WHEN name ~* E'^разъезд ' THEN
                regexp_replace(name, E'^разъезд', E'ж/д разъезд', 'ig')
              ELSE
                'ж/д пост ' || name
            END
          WHEN abbr = 'ж/д_рзд' THEN
            'ж/д разъезд ' || name
          WHEN abbr = 'ж/д_ст' THEN
            'ж/д станция ' || name
          WHEN abbr = 'жт' THEN
            CASE
              WHEN name ~* '^животноводческая' THEN
                name
              WHEN name ~* E'^точка\\s' THEN
                lower(abbrfull) || ' ' || regexp_replace(name, E'^точка ', E'', 'ig')
               WHEN name ~* E'^ферма ' THEN
                 regexp_replace(name, E'^ферма ', 'ферма ', 'ig')
               WHEN name ~* E' ферма$' THEN
                 regexp_replace(name, E'^(.*) ферма$', E'ферма \\1', 'ig')
              ELSE
                regexp_replace(lower(abbrfull) || ' ' || name, E'^(' || lower(abbrfull) || ') (.*((?:ая)|(?:-я)))$', E'\\2 \\1', 'ig')
            END
          WHEN abbr = 'зона' THEN
            regexp_replace(
              CASE
                 WHEN name ~* E'^зона ' THEN
                   regexp_replace(name, E'^зона ', 'зона ', 'ig')
                 WHEN name ~* E' зона$' THEN
                   name
                 WHEN name ~* E'^промзона$' THEN
                   name
                 WHEN name ~* E' промзона$' THEN
                   name
                ELSE
                  regexp_replace(lower(abbrfull) || ' ' || name, E'^(' || lower(abbrfull) || ') (.*((?:ая)|(?:-я)))$', E'\\2 \\1', 'ig')
              END,
              E' промышленная зона$',
              E' промзона',
              'ig'
            )
          WHEN abbr = 'казарма' THEN
            CASE
              WHEN name ~* E'^Блокпост' THEN
                name
              WHEN name ~* E'^железнодорожная\\s+' THEN
                regexp_replace(name, E'^железнодорожная\\s+', E'ж/д казарма ', 'ig')
              ELSE
                'казарма ' || regexp_replace(name, E'^казарма\\s*', E'', 'ig')
            END
          WHEN abbr = 'км' THEN
            CASE
              WHEN name ~* E'(\\d|-й)$' THEN
                name || ' ' || abbr
              WHEN name ~* E'^([0-9\\.]+)( )?' THEN
                regexp_replace(name, E'^([0-9\\.]+)( )?(км( |$))?', E'\\1 км ', 'ig')
              ELSE
                name
            END
          WHEN abbr = 'мкр' THEN
            CASE
              WHEN name ~* E'^(.*-й|\\d+)$' THEN
                name || ' ' || lower(abbrfull)
              WHEN name ~* E'^(\\d+[а-я])$' THEN
                lower(abbrfull) || ' ' || name
              ELSE
                abbr || '.' || name
            END
          WHEN abbr = 'остров' THEN
            CASE
              WHEN name ~* E'(.*-й$|\\d+)$' THEN
                name || ' ' || lower(abbrfull)
              ELSE
                lower(abbrfull) || ' ' || name
            END
          WHEN abbr IN ('лпх','п/о','п/р','п/ст','промзона') THEN
            regexp_replace(lower(abbr) || ' ' || name, E'^(' || lower(abbr) || ') (.*((?:ая)|(?:ое)|(?:ой)|(?:ий)|(?:ый)|(?:-й)|(?:-я)))$', E'\\2 \\1', 'ig')
          WHEN abbr IN ('сад','б-р') THEN
            CASE
              WHEN name ~* E'^(.*-й|.*-я|\\d+)$' THEN
                name || ' ' || lower(abbrfull)
              ELSE
                regexp_replace(lower(abbr) || ' ' || name, E'^(' || lower(abbr) || ') (.*((?:ой)|(?:ий)|(?:ый)|(?:-й)))$', E'\\2 \\1', 'ig')
            END
          WHEN abbr IN ('пер','пл','туп') THEN
            CASE
              WHEN name ~* E'^(.*-й|.*-я|\\d+)$' THEN
                name || ' ' || lower(abbrfull)
              ELSE
                regexp_replace(lower(abbr) || '.' || name, E'^(' || lower(abbr) || E'\\.)(.*((?:ая)|(?:ое)|(?:ой)|(?:ий)|(?:ый)|(?:-й)|(?:-я)))$', E'\\2 \\1', 'ig')
            END
          WHEN abbr IN ('х','ш') THEN
              CASE
                WHEN name ~* E'^(.*-й|.*-я|\\d+)$' THEN
                  name || ' ' || lower(abbrfull)
                ELSE
                  regexp_replace(lower(abbr) || '.' || regexp_replace(name, E' ' || lower(abbrfull) || '$', E'', 'ig'), E'^(' || lower(abbr) || E'\\.)(.*((?:ая)|(?:ое)|(?:ой)|(?:ий)|(?:ый)|(?:-й)|(?:-я)))$', E'\\2 ' || lower(abbrfull), 'ig')
              END
          WHEN abbr IN ('пр-кт') THEN
            CASE
              WHEN name ~* E'^(.*-й|.*-я|\\d+)$' THEN
                name || ' ' || lower(abbrfull)
              ELSE
                regexp_replace(lower('пр-т') || ' ' || name, E'^(' || lower('пр-т') || E')\\s(.*((?:ой)|(?:ий)|(?:ый)|(?:-й)|(?:-я)))$', E'\\2 \\1', 'ig')
            END
          WHEN abbr IN ('проезд') THEN
            CASE
              WHEN name ~* E'^(.*-й|.*-я|\\d+)$' THEN
                name || ' ' || lower(abbrfull)
              ELSE
                regexp_replace(lower('пр-д') || ' ' || name, E'^(' || lower('пр-д') || E')\\s(.*((?:ой)|(?:ий)|(?:ый)|(?:-й)|(?:-я)))$', E'\\2 \\1', 'ig')
            END
          WHEN abbr IN ('стр') THEN
            CASE
              WHEN name ~* E'^\\d+-е$' THEN
                name || ' ' || lower(abbrfull)
              WHEN name ~* E'^\\d+(-[а-я])?$' THEN
                lower(abbrfull) || ' ' || name
              ELSE
                name
            END
          WHEN abbr IN ('аллея','берег','бугор','вал','въезд','городок','дор','заезд','канал','кв-л','кольцо','коса','линия','мост','парк','переезд','платф','пл-ка','полустанок','просек','просека','проселок','проток','проулок','рзд','ряды','сквер','спуск','тракт','уч-к','ферма') THEN
            CASE
              WHEN abbr = 'дор' AND name ~* '^(а/д|автодорога)' THEN
                regexp_replace(name, E'^(а/д|автодорога)\\s', E'а/д ', 'ig')
              WHEN name ~* E'^(.*-й|.*-я|\\d+)$' THEN
                name || ' ' || lower(abbrfull)
              ELSE
                regexp_replace(lower(abbrfull) || ' ' || name, E'^(' || lower(abbrfull) || ') (.*((?:ая)|(?:ое)|(?:ой)|(?:ий)|(?:ый)|(?:ые)|(?:-й)|(?:-я)))$', E'\\2 \\1', 'ig')
            END
          WHEN abbr IN ('ул') THEN
            replace(abbr, '_', ' ') || (CASE WHEN abbr ~ '(-|/)' THEN '' ELSE '.' END) || '' || name
          WHEN abbr IN ('нп','с','д','тер','п','жилзона','жилрайон','х','ст-ца','рп','рзд','м','ст','у','снт','сл') THEN
            CASE
              WHEN name ~* E'^остров\\s+' THEN
                regexp_replace(name, E'^остров', E'остров', 'ig')
              WHEN name ~* E'^казарма\\s+' THEN
                regexp_replace(name, E'^казарма', E'казарма', 'ig')
              WHEN name ~* E'^кордон\\s+' THEN
                regexp_replace(name, E'^кордон', E'кордон', 'ig')
              WHEN name ~* E'^жилой район ' THEN
                regexp_replace(name, E'^жилой район ', 'жилой район ', 'ig')
              WHEN name ~* E' жилой район$' THEN
                regexp_replace(name, E'^(.*) жилой район$', E'жилой район \\1', 'ig')
              WHEN name ~* E'^жилая зона ' THEN
                regexp_replace(name, E'^жилая зона ', 'жилая зона ', 'ig')
              WHEN name ~* E' жилая зона$' THEN
                regexp_replace(name, E'^(.*) жилая зона$', E'жилая зона \\1', 'ig')
              WHEN name ~* E'^д/о ' THEN
                regexp_replace(name, E'^д/о ', 'д/о ', 'ig')
              WHEN name ~* E'^п/о ' THEN
                regexp_replace(name, E'^п/о ', 'п/о ', 'ig')
              WHEN name ~* E'^п/л ' THEN
                regexp_replace(name, E'^п/л ', 'п/л ', 'ig')
              WHEN name ~* E'^т/б ' THEN
                regexp_replace(name, E'^т/б ', 'т/б ', 'ig')
              WHEN name ~* E'^СНТ ' THEN
                regexp_replace(name, E'^СНТ ', 'снт ', 'ig')
              WHEN name ~* E' СНТ$' THEN
                regexp_replace(name, E'^(.*) СНТ$', E'снт \\1', 'ig')
              WHEN name ~* E'^СДТ ' THEN
                regexp_replace(name, E'^СДТ ', 'сдт ', 'ig')
              WHEN name ~* E' СДТ$' THEN
                regexp_replace(name, E'^(.*) СДТ$', E'сдт \\1', 'ig')
              WHEN name ~* E'^СТ ' THEN
                regexp_replace(name, E'^СТ ', 'ст ', 'ig')
              WHEN name ~* E' СТ$' THEN
                regexp_replace(name, E'^(.*) СТ$', E'ст \\1', 'ig')
              WHEN name ~* E'^ДНТ ' THEN
                regexp_replace(name, E'^ДНТ ', 'днт ', 'ig')
              WHEN name ~* E' ДНТ$' THEN
                regexp_replace(name, E'^(.*) ДНТ$', E'днт \\1', 'ig')
              WHEN name ~* E'^ДНП ' THEN
                regexp_replace(name, E'^ДНП ', 'днп ', 'ig')
              WHEN name ~* E' ДНП$' THEN
                regexp_replace(name, E'^(.*) ДНП$', E'днп \\1', 'ig')
              WHEN name ~* E'^будка( (ж/д|железной дороги))?' THEN
                regexp_replace(name, E'^будка( (ж/д|железной дороги))?', E'ж/д будка', 'ig')
              WHEN name ~* E'^железнодорожная будка' THEN
                regexp_replace(name, E'^железнодорожная будка', E'ж/д будка', 'ig')
              WHEN name ~* E'^платф.+' THEN
                regexp_replace(name, E'^(платф)\\s+', E'платформа ', 'ig')
              WHEN name ~* E'^поселок.+' THEN
                regexp_replace(name, E'^(поселок)\\s+', E'п.', 'ig')
              WHEN name ~* E'^(рзд|разъезд).+' THEN
                regexp_replace(name, E'^(рзд|разъезд)\\s+', E'разъезд ', 'ig')
              WHEN name ~* E'^участок.+' THEN
                regexp_replace(name, E'^(участок)\\s+', E'участок ', 'ig')
              WHEN name ~* E'^(урочище|ур-ще) ' THEN
                regexp_replace(name, E'^(урочище|ур-ще) ', 'урочище ', 'ig')
--              WHEN name ~* E'^ж/д' THEN
--                name
              ELSE
                CASE
                  WHEN abbr IN ('с', 'д', 'тер', 'п') THEN
                    abbr || '.' || name
                  WHEN abbr IN ('жилзона','жилрайон','кв-л','х','ст-ца','рп','рзд','м','ст','у') THEN
                    lower(abbrfull) || ' ' || name
                  WHEN abbr IN ('снт') THEN
                    abbr || ' ' || name
                  WHEN abbr IN ('сл') THEN
                    regexp_replace(lower(abbrfull) || ' ' || name, E'^(' || lower(abbrfull) || ') (.*((?:ая)))$', E'\\2 \\1', 'ig')
                  WHEN abbr IN ('нп') THEN
                    'н/п ' || name
                  ELSE
                    name
                END
            END
          ELSE
            CASE
              WHEN name ~* '((?:ая)|(?:ое)|(?:ой)|(?:ий)|(?:ый)|(?:-й)|(?:-я))$' THEN
                name || ' ' || replace(abbr, '_', ' ') || (CASE WHEN abbr ~ '(-|/)' THEN '' ELSE '.' END)
              ELSE
                replace(abbr, '_', ' ') || (CASE WHEN abbr ~ '(-|/)' THEN '' ELSE '.' END) || name
            END
        END
    END
  FROM
    (
      SELECT
        kl.*,
        ab.name abbrfull
      FROM
        (
          SELECT ($1).*
        ) kl LEFT JOIN
        kladr_abbr ab ON
          ab.code = kl.abbr AND (
            (ab.lvl / 100, length(kl.code)) = (5, 15) OR
            (ab.lvl / 100, length(kl.code)) = (4, 11) OR
            (ab.lvl / 100, length(kl.code)) = (3,  8) OR
            (ab.lvl / 100, length(kl.code)) = (2,  5) OR
            (ab.lvl / 100, length(kl.code)) = (1,  2)
          )
    ) T
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION kladr$norm() RETURNS trigger AS $$
BEGIN
  -- проверяем необходимость логгирования при обновлении записи
  NEW.norm = normalize(NEW);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER norm
  BEFORE INSERT OR UPDATE OF code, name, abbr
  ON kladr
    FOR EACH ROW
      EXECUTE PROCEDURE kladr$norm();
Теги:
Хабы:
Всего голосов 7: ↑6 и ↓1+9
Комментарии10
Тензор
Разработчик системы СБИС
Сайт
199
Карма
55.1
Рейтинг
Боровиков Кирилл @Kilor

Архитектура ИС: PostgreSQL, Node.js и highload

Отправить сообщение
СайтСайт

Публикации

Информация

Сайт
sbis.ru
Дата регистрации
Дата основания
Численность
1 001–5 000 человек
Местоположение
Россия

Ваш аккаунт

Разделы

Информация

Услуги

Техническая поддержка
© 2006–2024, Habr