Достаточно часто при работе с вводимыми пользователем адресными данными возникает необходимость сначала подсказать ему, как правильно свой адрес указать, а потом - полученную строку адреса привести в некоторый машинно-читаемый вид.
Таким видом у нас в стране можно считать код по справочникам КЛАДР или ФИАС.
Первый из них уже несколько лет считается устаревающим, но отличается более простой структурой и исторически продолжает использоваться во множестве систем, поскольку вполне подходит для большинства задач.
Давайте научимся разбирать строку адреса "туда и обратно", а заодно познакомимся с некоторыми алгоритмическими подходами и их реализацией на 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();Комментарии (10)
TakashiNord
19.08.2021 15:20+1начало - зубодробительное.
7zip ! 7 DBF-файлов в DOS-кодировке ! ax- ax
andrydl
19.08.2021 15:58Раньше был вариант сходить в налоговую с 3 дюймовой дискеткой , а лучше с 2-мя (мало ли одна не прочитается) забрать dbf - ки самому) С тех пор структура файлов не менялась) А сейчас можно скачать, прогресс...
jonie
Как-то так можно не выкачивая всё получить заголовки (правда у меня были баги вида файл менялся, а last-modified нет, посему используется сочетание разных заголовков.
Kilor Автор
А с
I-флагом разве сгенерируется неHEAD-запрос?jonie
нет, не HEAD. Добавьте -v к curl-у и убедитесь сами
Kilor Автор
Тогда тоже можно как вариант оптимизации использовать.
jonie
на самом деле не "можно", а "нужно" ИМХО, т.к. это в разы быстрее чем качать весь файл если он не менялся и не создаёт паразитной долго играющей нагрузки. Я б попросил вас доработать ваш скрипт - сейчас понабегут какие-нибудь настоящие программисты, накопипастят и выгрузку вовсе прикроют каким-нибудь 2FA
Kilor Автор
Это будет очень интересным кейсом доступа к открытым гос-данным. :)
jonie
да бросьте - были же уже прецеденты типа добавления капчи на ГАС "Выборы" или "ждите 15 минут прежде чем войти в госуслуги".