Для хранения информации из КЛАДР необходимо создать три таблицы.
- CREATE TABLE W_LOCALITY_TYPE(
ID | | bigserial NOT NULL PRIMARY KEY, |
NAME | | character varying(64) DEFAULT NULL UNIQUE, |
CREATE_DATE | | TIMESTAMP with time zone NOT NULL DEFAULT NOW()); |
- CREATE TABLE W_LOCALITY(
ID | | bigserial NOT NULL PRIMARY KEY, |
PARENT_ID | | bigint, |
NAME | | character varying(64) DEFAULT NULL, |
W_LOCALITY_TYPE_ID | | bigint NOT NULL references W_LOCALITY_TYPE(ID), |
KLADR_ID | | VARCHAR(19), |
CREATE_DATE | | TIMESTAMP with time zone NOT NULL DEFAULT NOW()) |
- CREATE TABLE W_LOCALITY_ALIAS(
ID | | bigserial NOT NULL PRIMARY KEY, |
W_LOCALITY_ID | | bigint NOT NULL references W_LOCALITY(ID), |
KLADR_ID | | VARCHAR(19), |
NAME | | character varying(64) NOT NULL, |
CREATE_DATE | | TIMESTAMP with time zone NOT NULL DEFAULT NOW()); |
Импортируем данные из КЛАДР в базу данных
Для загрузки данных из КЛАДР в созданную структуру необходимо выполнить следующие команды:
- INSERT INTO W_LOCALITY_TYPE(NAME)
SELECT DISTINCT trim(TYPE) FROM KLADR_BASE.W_KLADR_TBL UNION SELECT trim(TYPE) FROM KLADR_BASE.W_STREET_TBL UNION SELECT trim(C1) FROM KLADR_BASE.W_DOMA_TBL; |
- INSERT INTO W_LOCALITY(
NAME, |
W_LOCALITY_TYPE_ID, |
KLADR_ID) |
SELECTNAME, |
(SELECTFROMWHERENAME=TRIM(KLADR_BASE.W_KLADR_TBL.TYPE)) AS type_id, |
|
TRIM(CODE) |
FROM(SELECTTRIM(substring(CODE FROM 1 FOR 11)) bc, | min(cast(substring(CODE FROM 12 FOR 2) as integer)) ac | FROM GROUP BY TRIM(substring(CODE FROM 1 FOR 11))) T1 INNER JOIN |
KLADR_BASE.W_KLADR_TBL ONTRIM(KLADR_BASE.W_KLADR_TBL.CODE)=CONCAT(TRIM(T1.BC), TRIM(TO_CHAR(T1.AC, '09'))); |
|
|
- CREATE INDEX W_LOCALITY_KLADR_ID on W_LOCALITY(KLADR_ID);
- INSERT INTO W_LOCALITY_ALIAS(
NAME, |
W_LOCALITY_ID, |
KLADR_ID) |
SELECTNAME, |
(SELECTFROMWHEREKLADR_ID=CONCAT(TRIM(T1.BC), TRIM(TO_CHAR(T1.AC, '09')))), |
|
TRIM(CODE) |
FROM(SELECTsubstring(CODE FROM 1 FOR 11) bc, | min(cast(substring(CODE FROM 12 FOR 2) as integer)) ac | FROMGROUP BY substring(CODE FROM 1 FOR 11)) T1 INNER JOIN |
KLADR_BASE.W_KLADR_TBL ON(TRIM(substring(KLADR_BASE.W_KLADR_TBL.CODE FROM 1 FOR 11))= | cast(substring(CODE FROM 12 FOR 2) as integer)<>T1.ac); |
|
- INSERT INTO W_LOCALITY(
NAME, |
W_LOCALITY_TYPE_ID, |
KLADR_ID) |
SELECTNAME, |
(SELECTFROMWHERENAME=TRIM(KLADR_BASE.W_STREET_TBL.TYPE)) AS type_id, |
|
TRIM(CODE) |
FROM(SELECTTRIM(substring(CODE FROM 1 FOR 15)) bc, | min(cast(substring(CODE FROM 16 FOR 2) as integer)) ac | FROM GROUP BY TRIM(substring(CODE FROM 1 FOR 15))) T1 INNER JOIN |
KLADR_BASE.W_STREET_TBL ONTRIM(KLADR_BASE.W_STREET_TBL.CODE)=CONCAT(TRIM(T1.BC), TRIM(TO_CHAR(T1.AC, '09'))); |
|
|
- INSERT INTO W_LOCALITY_ALIAS(
NAME, |
W_LOCALITY_ID, |
KLADR_ID) |
SELECTNAME, |
(SELECTFROMWHEREKLADR_ID=CONCAT(TRIM(T1.BC), TRIM(TO_CHAR(T1.AC, '09')))), |
|
TRIM(CODE) |
FROM(SELECTsubstring(CODE FROM 1 FOR 15) bc, | min(cast(substring(CODE FROM 16 FOR 2) as integer)) ac | FROM GROUP BY substring(CODE FROM 1 FOR 15)) T1 INNER JOIN |
KLADR_BASE.W_STREET_TBL ON (TRIM(substring(KLADR_BASE.W_STREET_TBL.CODE FROM 1 FOR 15))= cast(substring(CODE FROM 16 FOR 2) as integer)<>T1.ac); |
|
- DROP INDEX W_LOCALITY_KLADR_ID;
Создание иерархической подчиненности между объектами КЛАДР
Структура кодового обозначения в поле KLADR_ID:
Длина | | Формат |
13 | | СС РРР ГГГ ППП КК |
17 | | СС РРР ГГГ ППП УУУУ КК |
19 | | СС РРР ГГГ ППП УУУУ ДДДД |
Код | | Описание |
СС | | код субъекта Российской Федерации (региона) |
РРР | | код района |
ГГГ | | код города |
ППП | | код населенного пункта |
УУУУ | | код улицы |
ДДДД | | порядковый номер позиции классификатора с обозначениями домов |
КК | | код актуальности наименования |
Связываем районы с регионами.
CREATE INDEX W_LOCALITY_KLADR_ID_TMP_IDX on W_LOCALITY(
char_length(KLADR_ID), |
substring(KLADR_ID FROM 3 FOR 9), |
substring(KLADR_ID FROM 1 FOR 2)); |
UPDATE W_LOCALITY SET
PARENT_ID=( |
SELECTFROMWHEREchar_length(T1.KLADR_ID)=13 AND | substring(T1.KLADR_ID FROM 3 FOR 9)=lpad('', 9, '0') AND | substring(T1.KLADR_ID FROM 1 FOR 2)=substring(W_LOCALITY.KLADR_ID FROM 1 FOR 2)) |
|
|
|
WHERE
char_length(W_LOCALITY.KLADR_ID)=13 AND |
substring(W_LOCALITY.KLADR_ID FROM 3 FOR 3)<>lpad('', 3, '0') AND |
substring(W_LOCALITY.KLADR_ID FROM 6 FOR 6)=lpad('', 6, '0'); |
DROP INDEX W_LOCALITY_KLADR_ID_TMP_IDX;
Связываем города с районами.
CREATE INDEX W_LOCALITY_KLADR_ID_TMP_IDX on W_LOCALITY(
char_length(KLADR_ID), |
substring(KLADR_ID FROM 6 FOR 6), |
substring(KLADR_ID FROM 1 FOR 5)); |
UPDATE W_LOCALITY SET
PARENT_ID=( |
SELECTFROMWHEREchar_length(T1.KLADR_ID)=13 AND | substring(T1.KLADR_ID FROM 6 FOR 6)=lpad('', 6, '0') AND | substring(T1.KLADR_ID FROM 1 FOR 5)=substring(W_LOCALITY.KLADR_ID FROM 1 FOR 5)) |
|
|
|
WHERE
char_length(W_LOCALITY.KLADR_ID)=13 AND |
substring(W_LOCALITY.KLADR_ID FROM 6 FOR 3)<>lpad('', 3, '0') AND |
substring(W_LOCALITY.KLADR_ID FROM 9 FOR 3)=lpad('', 3, '0'); |
DROP INDEX W_LOCALITY_KLADR_ID_TMP_IDX;
Связываем населенные пункты с городами.
CREATE INDEX W_LOCALITY_KLADR_ID_TMP_IDX on W_LOCALITY(
char_length(KLADR_ID), |
substring(KLADR_ID FROM 9 FOR 3), |
substring(KLADR_ID FROM 1 FOR 8)); |
UPDATE W_LOCALITY SET
PARENT_ID=( |
SELECTFROMWHEREchar_length(T1.KLADR_ID)=13 AND | substring(T1.KLADR_ID FROM 9 FOR 3)=lpad('', 3, '0') AND | substring(T1.KLADR_ID FROM 1 FOR 8)=substring(W_LOCALITY.KLADR_ID FROM 1 FOR 8)) |
|
|
|
WHERE
char_length(W_LOCALITY.KLADR_ID)=13 AND |
substring(W_LOCALITY.KLADR_ID FROM 9 FOR 3)<>lpad('', 3, '0'); |
DROP INDEX W_LOCALITY_KLADR_ID_TMP_IDX;
Связываем улицы с населенными пунктами
CREATE INDEX W_LOCALITY_KLADR_ID_TMP_IDX on W_LOCALITY(
char_length(KLADR_ID), |
substring(KLADR_ID FROM 1 FOR 11)); |
UPDATE W_LOCALITY SET
PARENT_ID=( |
SELECTFROMWHEREchar_length(T1.KLADR_ID)=13 AND | substring(T1.KLADR_ID FROM 1 FOR 11)=substring(W_LOCALITY.KLADR_ID FROM 1 FOR 11)) |
|
|
|
WHERE
char_length(W_LOCALITY.KLADR_ID)=17 AND |
substring(W_LOCALITY.KLADR_ID FROM 12 FOR 4)<>lpad('', 4, '0'); |
DROP INDEX W_LOCALITY_KLADR_ID_TMP_IDX;
Связываем улицы с городами
CREATE INDEX W_LOCALITY_KLADR_ID_TMP_IDX on W_LOCALITY(
char_length(KLADR_ID), |
substring(KLADR_ID FROM 1 FOR 15)); |
UPDATE W_LOCALITY SET
PARENT_ID=( |
SELECTFROMWHEREchar_length(T1.KLADR_ID)=17 AND | substring(T1.KLADR_ID FROM 1 FOR 15)=substring(W_LOCALITY.KLADR_ID FROM 1 FOR 15)) |
|
|
|
WHERE
char_length(W_LOCALITY.KLADR_ID)=19 AND |
substring(W_LOCALITY.KLADR_ID FROM 16 FOR 4)<>lpad('', 4, '0'); |
DROP INDEX W_LOCALITY_KLADR_ID_TMP_IDX;
Связываем дома с улицами
CREATE INDEX W_LOCALITY_KLADR_ID_TMP_IDX on W_LOCALITY(
char_length(KLADR_ID), |
substring(KLADR_ID FROM 1 FOR 11)); |
UPDATE W_LOCALITY SET
PARENT_ID=( |
SELECTFROMWHEREchar_length(T1.KLADR_ID)=13 AND | substring(T1.KLADR_ID FROM 1 FOR 11)=substring(W_LOCALITY.KLADR_ID FROM 1 FOR 11)) |
|
|
|
WHERE
char_length(W_LOCALITY.KLADR_ID)=19 AND |
substring(W_LOCALITY.KLADR_ID FROM 16 FOR 4)<>lpad('', 4, '0') AND |
PARENT_ID IS NULL; |
DROP INDEX W_LOCALITY_KLADR_ID_TMP_IDX;
Создание поисковых индексов
CREATE UNIQUE INDEX W_LOCALITY_PARENT_ID_IDX on W_LOCALITY(ID, PARENT_ID);
CREATE UNIQUE INDEX W_LOCALITY_NAME_IDX ON W_LOCALITY(NAME, ID);
Пример поискового запроса:
WITH RECURSIVE T(ID, LEVEL) AS (
SELECT710::BIGINT AS ID, | 1 AS LEVEL |
|
UNION ALL SELECT
W_LOCALITY.PARENT_ID, | T.LEVEL + 1 | FROM
T INNER JOIN | W_LOCALITY ON T.ID=W_LOCALITY.ID | WHEREW_LOCALITY.PARENT_ID IS NOT NULL) |
|
SELECT
array_to_string(
array_agg(concat(trim(w_locality_type.name), | ' ', | trim(w_locality.name)) |
| ORDER BY LEVEL DESC), | ',') | FROM
T INNER JOIN |
W_LOCALITY ON T.ID=W_LOCALITY.ID INNER JOIN |
W_LOCALITY_TYPE ON W_LOCALITY_TYPE.ID=w_locality.w_locality_type_id; |
|
Скрипты для импорта КЛАДР (github)