Структура для поиска полного адреса по КЛАДР

Для хранения информации из КЛАДР необходимо создать три таблицы.

1. 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());

2. 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())

3. 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());

Импортируем данные из КЛАДР в базу данных

Для загрузки данных из КЛАДР в созданную структуру необходимо выполнить следующие команды:

1. 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;

2. INSERT INTO W_LOCALITY(

   NAME,

   W_LOCALITY_TYPE_ID,

   KLADR_ID)

   SELECT

   NAME,

   (SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TRIM(KLADR_BASE.W_KLADR_TBL.TYPE)) AS type_id,

   TRIM(CODE)

   FROM

   (SELECT TRIM(substring(CODE FROM 1 FOR 11)) bc, min(cast(substring(CODE FROM 12 FOR 2) as integer)) ac FROM KLADR_BASE.W_KLADR_TBL GROUP BY TRIM(substring(CODE FROM 1 FOR 11))) T1 INNER JOIN

   KLADR_BASE.W_KLADR_TBL ON TRIM(KLADR_BASE.W_KLADR_TBL.CODE)= CONCAT(TRIM(T1.BC), TRIM(TO_CHAR(T1.AC, '09')));

3. CREATE INDEX W_LOCALITY_KLADR_ID on W_LOCALITY(KLADR_ID);
4. INSERT INTO W_LOCALITY_ALIAS(

   NAME,

   W_LOCALITY_ID,

   KLADR_ID)

   SELECT

   NAME,

   (SELECT ID FROM W_LOCALITY WHERE KLADR_ID=CONCAT(TRIM(T1.BC), TRIM(TO_CHAR(T1.AC, '09')))),

   TRIM(CODE)

   FROM

   (SELECT substring(CODE FROM 1 FOR 11) bc, min(cast(substring(CODE FROM 12 FOR 2) as integer)) ac FROM KLADR_BASE.W_KLADR_TBL GROUP 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))= TRIM(T1.BC) AND cast(substring(CODE FROM 12 FOR 2) as integer)<>T1.ac);

5. INSERT INTO W_LOCALITY(

   NAME,

   W_LOCALITY_TYPE_ID,

   KLADR_ID)

   SELECT

   NAME,

   (SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TRIM(KLADR_BASE.W_STREET_TBL.TYPE)) AS type_id,

   TRIM(CODE)

   FROM

   (SELECT TRIM(substring(CODE FROM 1 FOR 15)) bc, min(cast(substring(CODE FROM 16 FOR 2) as integer)) ac FROM KLADR_BASE.W_STREET_TBL GROUP BY TRIM(substring(CODE FROM 1 FOR 15))) T1 INNER JOIN

   KLADR_BASE.W_STREET_TBL ON TRIM(KLADR_BASE.W_STREET_TBL.CODE)= CONCAT(TRIM(T1.BC), TRIM(TO_CHAR(T1.AC, '09')));

6. INSERT INTO W_LOCALITY_ALIAS(

   NAME,

   W_LOCALITY_ID,

   KLADR_ID)

   SELECT

   NAME,

   (SELECT ID FROM W_LOCALITY WHERE KLADR_ID=CONCAT(TRIM(T1.BC), TRIM(TO_CHAR(T1.AC, '09')))),

   TRIM(CODE)

   FROM

   (SELECT substring(CODE FROM 1 FOR 15) bc, min(cast(substring(CODE FROM 16 FOR 2) as integer)) ac FROM KLADR_BASE.W_STREET_TBL 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))= TRIM(T1.BC) AND cast(substring(CODE FROM 16 FOR 2) as integer)<>T1.ac);

7. 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=(

SELECT ID FROM W_LOCALITY T1 WHERE char_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=(

SELECT ID FROM W_LOCALITY T1 WHERE char_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=(

SELECT ID FROM W_LOCALITY T1 WHERE char_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=(

SELECT ID FROM W_LOCALITY T1 WHERE char_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=(

SELECT ID FROM W_LOCALITY T1 WHERE char_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=(

SELECT ID FROM W_LOCALITY T1 WHERE char_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 (

SELECT 710::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 WHERE W_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)