База данных по странам, регионам и городам.

База данных по странам, регионам и городам содержит:

Страны		218
Регионы		1611
Города		17287

Страна		Количество регионов		Количество городов
Абхазия		1		10
Австралия		9		208
Австрия		9		186
Азербайджан		3		76
Албания		12		41
Алжир		1		10
Ангола		1		7
Ангуилья		1		2
Андорра		8		19
Антигуа и Барбуда		1		8
Антильские о-ва		1		1
Аргентина		24		184
Армения		12		247
Арулько		1		1
Афганистан		1		6
Багамские о-ва		1		1
Бангладеш		1		2
Барбадос		1		1
Бахрейн		1		2
Беларусь		6		153
Белиз		1		1
Бельгия		11		203
Бенин		1		1
Бермуды		1		1
Болгария		29		371
Боливия		8		34
Босния/Герцеговина		3		26
Ботсвана		1		3
Бразилия		21		99
Британские Виргинские о-ва		1		1
Бруней		1		1
Буркина Фасо		1		2
Бурунди		1		1
Бутан		1		1
Валлис и Футуна о-ва		1		1
Вануату		1		2
Великобритания		17		468
Венгрия		23		83
Венесуэла		23		72
Восточный Тимор		1		1
Вьетнам		6		11
Габон		1		2
Гаити		6		6
Гайана		1		1
Гамбия		1		1
Гана		1		1
Гваделупа		2		2
Гватемала		11		20
Гвинея		1		2
Гвинея-Бисау		1		1
Германия		16		2080
Гернси о-в		1		1
Гибралтар		1		6
Гондурас		1		1
Гонконг		1		1
Гренада		1		1
Гренландия		1		1
Греция		51		333
Грузия		2		66
Дания		16		318
Джерси о-в		1		1
Джибути		1		1
Доминиканская республика		1		1
Египет		8		10
Замбия		1		3
Западная Сахара		1		2
Зимбабве		1		2
Израиль		8		71
Индия		22		63
Индонезия		1		1
Иордания		1		1
Ирак		3		3
Иран		12		16
Ирландия		26		131
Исландия		11		17
Испания		52		590
Италия		100		814
Йемен		1		2
Кабо-Верде		1		1
Казахстан		19		251
Камбоджа		1		1
Камерун		4		4
Канада		13		248
Катар		1		1
Кения		5		12
Кипр		2		6
Кирибати		1		9
Китай		30		255
Колумбия		19		54
Коморские о-ва		1		1
Конго (Brazzaville)		1		3
Конго (Kinshasa)		1		1
Коста-Рика		7		32
Кот-д''Ивуар		1		2
Куба		15		69
Кувейт		2		2
Кука о-ва		1		1
Кыргызстан		5		73
Лаос		1		1
Латвия		1		57
Лесото		1		1
Либерия		1		1
Ливан		1		2
Ливия		2		2
Литва		1		81
Лихтенштейн		6		6
Люксембург		4		38
Маврикий		1		1
Мавритания		1		1
Мадагаскар		1		6
Македония		24		28
Малави		1		1
Малайзия		1		1
Мали		1		3
Мальдивские о-ва		1		1
Мальта		1		40
Марокко		2		2
Мартиника о-в		1		1
Мексика		32		170
Мозамбик		1		4
Молдова		1		61
Монако		1		7
Монголия		1		3
Мьянма (Бирма)		1		2
Мэн о-в		1		6
Намибия		1		3
Науру		1		3
Непал		1		1
Нигер		1		5
Нигерия		1		2
Нидерланды (Голландия)		12		280
Никарагуа		4		7
Новая Зеландия		14		21
Новая Каледония о-в		1		1
Норвегия		20		248
Норфолк о-в		1		1
О.А.Э.		2		2
Оман		1		3
Пакистан		1		3
Панама		5		13
Папуа Новая Гвинея		1		2
Парагвай		5		9
Перу		23		62
Питкэрн о-в		1		1
Польша		60		325
Португалия		21		277
Пуэрто Рико		1		15
Реюньон		1		1
Россия		78		2533
Руанда		1		1
Румыния		42		264
Сальвадор		6		6
Самоа		1		2
Сан-Марино		3		4
Сан-Томе и Принсипи		1		1
Саудовская Аравия		1		6
Свазиленд		1		1
Святая Люсия		1		1
Святой Елены о-в		1		1
Северная Корея		1		1
Сейшеллы		1		1
Сен-Пьер и Микелон		1		1
Сенегал		1		1
Сент-Винсент и Гренадины		1		1
Сент Китс и Невис		1		1
Сербия		3		20
Сингапур		1		1
Сирия		1		1
Словакия		7		16
Словения		3		12
Соломоновы о-ва		1		1
Сомали		1		1
Судан		1		8
Суринам		1		1
США		53		1591
Сьерра-Леоне		1		1
Таджикистан		5		58
Таиланд		3		3
Тайвань		1		1
Танзания		1		4
Того		1		1
Токелау о-ва		1		1
Тонга		1		1
Тринидад и Тобаго		1		1
Тувалу		1		1
Тунис		1		1
Туркменистан		5		40
Туркс и Кейкос		1		2
Турция		36		37
Уганда		2		2
Узбекистан		13		108
Украина		26		765
Уругвай		11		20
Фарерские о-ва		1		1
Фиджи		1		2
Филиппины		1		7
Финляндия		7		301
Франция		93		546
Французская Гвинея		1		3
Французская Полинезия		1		1
Хорватия		12		32
Чад		1		3
Черногория		1		7
Чехия		15		116
Чили		13		63
Швейцария		26		222
Швеция		22		285
Шри-Ланка		1		1
Эквадор		13		28
Экваториальная Гвинея		1		1
Эритрея		1		1
Эстония		1		39
Эфиопия		1		3
ЮАР		1		1
Южная Корея		17		31
Южная Осетия		1		2
Ямайка		1		1
Япония		38		122

Устанавливаем GPS координаты для объектов КЛАДР

Устанавливаем модули:

pgsql-http

1. git clone https://github.com/pramsey/pgsql-http
2. sudo yum install libcurl-devel
3. sudo PATH=/usr/pgsql-9.3/bin/:$PATH make USE_PGXS=1 install

postgis2_93

1. sudo yum install postgis2_93

Устанавливаем расширения в PostgreSQL

1. sudo su - postgres
2. psql
3. \c YourDatabase
4. CREATE EXTENSION xml2;
5. CREATE EXTENSION http;
6. CREATE EXTENSION postgis;
7. CREATE EXTENSION postgis_topology;
8. CREATE EXTENSION fuzzystrmatch;
9. CREATE EXTENSION postgis_tiger_geocoder;

Добавляем поле в таблицу W_LOCALITY

1. ALTER TABLE W_LOCALITY ADD COORD POINT DEFAULT NULL;

Создаем функцию для получения gps координат

CREATE OR REPLACE FUNCTION update_yandex_gps_coord(v_cnt integer) RETURNS integer AS $$ DECLARE v_res integer; BEGIN WITH T1 AS ( SELECT ID FROM W_LOCALITY_TYPE WHERE LOWER(NAME)='дом'), T2 AS ( SELECT W_LOCALITY.ID AS ID, (SELECT xpath( '/xmlns1:ymaps/xmlns1:GeoObjectCollection/xmlns2:featureMember/xmlns1:GeoObject/xmlns2:Point/xmlns2:pos/text()'::varchar, "content"::xml, ARRAY[ ARRAY['xmlns1', 'http://maps.yandex.ru/ymaps/1.x'], ARRAY['xmlns2', 'http://www.opengis.net/gml']]) AS COORD FROM http_get( concat( 'http://geocode-maps.yandex.ru/1.x/?geocode=', get_kladr_full_address(W_LOCALITY.ID)))) AS CONTENT FROM W_LOCALITY, T1 WHERE W_LOCALITY_TYPE_ID=T1.ID AND W_LOCALITY.COORD IS NULL LIMIT v_cnt), T3 AS ( SELECT T2.ID AS ID, unnest(T2.CONTENT) AS COORD FROM T2), T4 AS ( SELECT ID, string_to_array(T3.COORD::varchar, ' ') AS COORD FROM T3), T5 AS ( SELECT ID, POINT( COORD[1]::double precision, COORD[2]::double precision) AS COORD FROM T4) UPDATE W_LOCALITY SET COORD=T6.COORD FROM (SELECT ID, COORD FROM T5) T6 WHERE W_LOCALITY.ID=T6.ID; GET DIAGNOSTICS v_res = ROW_COUNT; RETURN v_res; END; $$ LANGUAGE plpgsql;

Запускаем функцию

SELECT * FROM update_yandex_gps_coord(10);

Обновляем первые найденные 10 объектов W_LOCALITY у которых W_LOCALITY.COORD IS NULL.

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

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

Зафиксируем формат поисковой строки.

S	->	(([тип адреса] [значение адреса])|([значение адреса] [тип адреса]))O
O	->	((,[тип адреса] [значение адреса])|(,[значение адреса] [тип адреса]))O|ε

Для примера выберем адрес: Московская обл, г Жуковский, ул Дугина, дом 10

Создадим индексы

1. CREATE UNIQUE INDEX W_LOCALITY_CODE_LEVEL_IDX ON

   W_LOCALITY( LOWER(NAME), get_locality_level(KLADR_ID), ID);

2. CREATE UNIQUE INDEX W_LOCALITY_PARENT_TYPE_NAME_IDX ON

   W_LOCALITY( PARENT_ID, W_LOCALITY_TYPE_ID, LOWER(NAME), ID);

3. CREATE UNIQUE INDEX W_LOCALITY_NAME_IDX ON

   W_LOCALITY( LOWER(NAME), ID);

4. CREATE UNIQUE INDEX W_LOCALITY_TYPE_IDX ON

   W_LOCALITY_TYPE( ID, LOWER(NAME));

Создадим функцию

CREATE OR REPLACE FUNCTION get_locality_level(v_code varchar)
RETURNS integer AS $$
DECLARE

v_res integer := NULL;

BEGIN

IF v_code IS NOT NULL THEN

CASE WHEN char_length(v_code)=13 THEN BEGIN v_res := 0; IF substring(v_code from 1 for 2)<>lpad('', 2, '0') THEN v_res := v_res + 1; END IF; IF substring(v_code from 3 for 3)<>lpad('', 3, '0') THEN v_res := v_res + 1; END IF; IF substring(v_code from 6 for 3)<>lpad('', 3, '0') THEN v_res := v_res + 1; END IF; IF substring(v_code from 9 for 3)<>lpad('', 3, '0') THEN v_res := v_res + 1; END IF; END; WHEN char_length(v_code)=17 THEN BEGIN v_res := 0; IF substring(v_code from 1 for 2)<>lpad('', 2, '0') THEN v_res := v_res + 1; END IF; IF substring(v_code from 3 for 3)<>lpad('', 3, '0') THEN v_res := v_res + 1; END IF; IF substring(v_code from 6 for 3)<>lpad('', 3, '0') THEN v_res := v_res + 1; END IF; IF substring(v_code from 9 for 3)<>lpad('', 3, '0') THEN v_res := v_res + 1; END IF; IF substring(v_code from 12 for 4)<>lpad('', 4, '0') THEN v_res := v_res + 1; END IF; END; WHEN char_length(v_code)=19 THEN BEGIN v_res := 0; IF substring(v_code from 1 for 2)<>lpad('', 2, '0') THEN v_res := v_res + 1; END IF; IF substring(v_code from 3 for 3)<>lpad('', 3, '0') THEN v_res := v_res + 1; END IF; IF substring(v_code from 6 for 3)<>lpad('', 3, '0') THEN v_res := v_res + 1; END IF; IF substring(v_code from 9 for 3)<>lpad('', 3, '0') THEN v_res := v_res + 1; END IF; IF substring(v_code from 12 for 4)<>lpad('', 4, '0') THEN v_res := v_res + 1; END IF; IF substring(v_code from 16 for 4)<>lpad('', 4, '0') THEN v_res := v_res + 1; END IF; END; ELSE v_res := NULL; END CASE; END IF; RETURN v_res;

END;
$$ LANGUAGE plpgsql IMMUTABLE;

Запрос для поиска адреса

WITH RECURSIVE T1 AS (

select TRIM( unnest( string_to_array( 'Московская обл, г Жуковский, ул Дугина, дом 10', ','))) as PART),

T2 AS (

SELECT row_number() over() as RN, string_to_array(PART, ' ') AS PART FROM T1 WHERE array_length(string_to_array(PART, ' '), 1) = 2),

T2_1 AS (

SELECT COUNT(*) AS CNT FROM T1),

T2_2 AS (

SELECT COUNT(*) AS CNT FROM T2),

T2_3 AS (

SELECT T2.RN AS RN, W_LOCALITY.ID AS ID, W_LOCALITY_TYPE.NAME AS PREFIX, W_LOCALITY.NAME AS NAME, W_LOCALITY.PARENT_ID AS PARENT_ID FROM W_LOCALITY INNER JOIN W_LOCALITY_TYPE ON W_LOCALITY_TYPE.ID=W_LOCALITY.W_LOCALITY_TYPE_ID, T2, T2_1, T2_2 WHERE get_locality_level(W_LOCALITY.KLADR_ID) < 3 AND ((LOWER(W_LOCALITY_TYPE.NAME)=LOWER(TRIM(T2.PART[1])) AND LOWER(W_LOCALITY.NAME)=LOWER(TRIM(T2.PART[2]))) OR (LOWER(W_LOCALITY_TYPE.NAME)=LOWER(TRIM(T2.PART[2])) AND LOWER(W_LOCALITY.NAME)=LOWER(TRIM(T2.PART[1])))) AND T2_1.CNT=T2_2.CNT),

T3 AS (

SELECT T2_3.ID AS ID, T2_3.PREFIX AS PREFIX, T2_3.NAME AS NAME, T2_3.PARENT_ID AS PARENT_ID FROM T2_3),

T4(ID, PARENT_ID, LEVEL) AS (

SELECT ID, PARENT_ID, 1 AS LEVEL FROM T3 UNION ALL SELECT T4.ID AS ID, W_LOCALITY.PARENT_ID AS PARENT_ID, T4.LEVEL + 1 AS LEVEL FROM W_LOCALITY INNER JOIN T4 ON T4.PARENT_ID=W_LOCALITY.ID WHERE T4.PARENT_ID IS NOT NULL AND T4.PARENT_ID IN (SELECT ID FROM T3)),

T5(ID, LEVEL) AS (

SELECT ID, MAX(LEVEL) AS LEVEL FROM T4 WHERE PARENT_ID IN (SELECT ID FROM T3) GROUP BY ID),

T6 AS (

SELECT COUNT(*) AS CNT FROM T3),

T7 AS (

SELECT T5.ID AS ID, T5.LEVEL AS LEVEL FROM W_LOCALITY INNER JOIN T5 ON T5.ID=W_LOCALITY.ID, T6 WHERE (T6.CNT > 1 AND    (SELECT ID FROM T3 WHERE T3.ID=W_LOCALITY.PARENT_ID) IS NOT NULL) OR T6.CNT=1),

T8 AS (

SELECT ID, LEVEL FROM T7 WHERE (SELECT ID FROM T4 WHERE PARENT_ID=T7.ID LIMIT 1) IS NULL),

T9(ROOT_ID, ID, PARENT_ID, LEVEL) AS (

SELECT T8.ID AS ROOT_ID, W_LOCALITY.ID AS ID, W_LOCALITY.PARENT_ID AS PARENT_ID, T8.LEVEL AS LEVEL FROM T8 INNER JOIN W_LOCALITY ON T8.ID=W_LOCALITY.ID UNION ALL SELECT T9.ROOT_ID AS ROOT_ID, W_LOCALITY.ID AS ID, W_LOCALITY.PARENT_ID AS PARENT_ID, T9.LEVEL - 1 AS LEVEL FROM W_LOCALITY INNER JOIN T9 ON W_LOCALITY.ID=T9.PARENT_ID WHERE T9.PARENT_ID IS NOT NULL),

T9_1 AS (

SELECT RN, PART FROM T2 WHERE T2.RN NOT IN (SELECT RN FROM T2_3 INNER JOIN T9 ON T9.ID=T2_3.ID)),

T9_2 AS (

SELECT MAX(LEVEL) AS LEVEL FROM T9),

T9_3 AS (

SELECT T9.ROOT_ID AS ROOT_ID, ID AS ID, T9.LEVEL + 1 AS LEVEL FROM T9, T9_2 WHERE T9.LEVEL=T9_2.LEVEL),

T9_4 AS (

SELECT T9_1.RN AS RN, T9_3.ROOT_ID AS ROOT_ID, W_LOCALITY.ID AS ID, W_LOCALITY.PARENT_ID AS PARENT_ID, T9_3.LEVEL AS LEVEL FROM W_LOCALITY INNER JOIN W_LOCALITY_TYPE ON W_LOCALITY_TYPE.ID=W_LOCALITY.W_LOCALITY_TYPE_ID INNER JOIN T9_3 ON W_LOCALITY.PARENT_ID=T9_3.ID, T9_1 WHERE ((LOWER(W_LOCALITY_TYPE.NAME)=LOWER(TRIM(T9_1.PART[1])) AND    LOWER(W_LOCALITY.NAME)=LOWER(TRIM(T9_1.PART[2]))) OR (LOWER(W_LOCALITY_TYPE.NAME)=LOWER(TRIM(T9_1.PART[2])) AND    LOWER(W_LOCALITY.NAME)=LOWER(TRIM(T9_1.PART[1]))))),

T9_5 AS (

SELECT T9.ROOT_ID AS ROOT_ID, T9.ID AS ID, T9.PARENT_ID AS PARENT_ID, T9.LEVEL AS LEVEL FROM T9 UNION ALL SELECT T9_4.ROOT_ID AS ROOT_ID, T9_4.ID AS ID, T9_4.PARENT_ID AS PARENT_ID, T9_4.LEVEL AS LEVEL FROM T9_4),

T9_6 AS (

SELECT RN, PART FROM T2 WHERE T2.RN NOT IN (SELECT RN FROM T2_3 INNER JOIN T9 ON T9.ID=T2_3.ID)),

T9_7 AS (

SELECT T9_6.RN AS RN, T9_4.ROOT_ID AS ROOT_ID, W_LOCALITY.ID AS ID, W_LOCALITY.PARENT_ID AS PARENT_ID, T9_4.LEVEL + 1 AS LEVEL FROM W_LOCALITY INNER JOIN W_LOCALITY_TYPE ON W_LOCALITY_TYPE.ID=W_LOCALITY.W_LOCALITY_TYPE_ID INNER JOIN T9_4 ON W_LOCALITY.PARENT_ID=T9_4.ID, T9_6 WHERE ((LOWER(W_LOCALITY_TYPE.NAME)=LOWER(TRIM(T9_6.PART[1])) AND    LOWER(W_LOCALITY.NAME)=LOWER(TRIM(T9_6.PART[2]))) OR (LOWER(W_LOCALITY_TYPE.NAME)=LOWER(TRIM(T9_6.PART[2])) AND    LOWER(W_LOCALITY.NAME)=LOWER(TRIM(T9_6.PART[1]))))),

T9_8 AS (

SELECT T9_5.ROOT_ID AS ROOT_ID, T9_5.ID AS ID, T9_5.PARENT_ID AS PARENT_ID, T9_5.LEVEL AS LEVEL FROM T9_5 UNION ALL SELECT T9_7.ROOT_ID AS ROOT_ID, T9_7.ID AS ID, T9_7.PARENT_ID AS PARENT_ID, T9_7.LEVEL AS LEVEL FROM T9_7),

T9_9 AS (

SELECT ROOT_ID, MAX(LEVEL) AS LEVEL FROM T9_8 GROUP BY ROOT_ID

), T9_10(ROOT_ID, ID, PARENT_ID, LEVEL) AS ( SELECT

T9_8.ROOT_ID AS ROOT_ID,

T9_8.ID AS ID,

T9_8.PARENT_ID AS PARENT_ID,

T9_8.LEVEL AS LEVEL

FROM

T9_8 INNER JOIN

T9_9 ON (T9_9.ROOT_ID=T9_8.ROOT_ID AND T9_9.LEVEL=T9_8.LEVEL)

UNION ALL
SELECT

T9_10.ROOT_ID AS ROOT_ID,

T9_8.ID AS ID,

T9_8.PARENT_ID AS PARENT_ID,

T9_8.LEVEL AS LEVEL

FROM

T9_10 INNER JOIN

T9_8 ON (T9_10.PARENT_ID=T9_8.ID AND T9_10.ROOT_ID=T9_8.ROOT_ID)),

T10 AS (

SELECT 1 AS ID FROM T6 WHERE (SELECT COUNT(*) FROM T9_10)=(SELECT COUNT(*) FROM T1))

SELECT

T9_10.ROOT_ID as ROOT_ID,

T9_10.ID AS ID,

T9_10.PARENT_ID AS PARENT_ID,

T9_10.LEVEL AS LEVEL

W_LOCALITY_VIEW.NAME AS NAME,

W_LOCALITY_VIEW.PREFIX AS PREFIX

FROM

T9_10 INNER JOIN W_LOCALITY_VIEW ON W_LOCALITY_VIEW.ID=T9_10.ID, T10

WHERE

T10.ID=1;

Скорость выполнения - 33.126 мсек

Размер таблиц:

W_LOCALITY		23,244,680
W_LOCALITY_TYPE		114

Вывод данных:

ROOT_ID	ID	PARENT_ID	LEVEL	NAME	PREFIX
65487	14696415	1037965	3	10	ДОМ
65487	1037965	65487	2	Дугина	ул
65487	65487	195836	1	Жуковский	г
65487	195836	NULL	0	Московская	обл

Скрипты для импорта КЛАДР (github)

Поиск подчиненных адресов в КЛАДР

Для поиска будем использовать заданную структуру.

Создадим представление:

CREATE OR REPLACE VIEW W_LOCALITY_VIEW(ID, PREFIX, NAME, PARENT_ID) AS

SELECT W_LOCALITY.ID AS ID, W_LOCALITY_TYPE.NAME AS PREFIX, W_LOCALITY.NAME AS NAME, W_LOCALITY.PARENT_ID AS PARENT_ID

FROM

W_LOCALITY INNER JOIN W_LOCALITY_TYPE ON W_LOCALITY_TYPE.ID=W_LOCALITY.W_LOCALITY_TYPE_ID;

Поисковые поля:

1. PARENT_ID

Возможные значения:

1. PARENT_ID IS NULL - корневые объекты.
2. PARENT_ID = 'value' - заданный объект.

Создадим индексы:

1. CREATE UNIQUE INDEX W_LOCALITY_PARENT_ID_IDX on W_LOCALITY(ID, PARENT_ID);
2. CREATE UNIQUE INDEX W_LOCALITY_FPARENT_ID_IDX on W_LOCALITY(PARENT_ID, ID);
3. CREATE UNIQUE INDEX W_LOCALITY_NULL_PARENT_ID_IDX on W_LOCALITY(ID) WHERE PARENT_ID IS NULL;

Поисковые запросы:

1. Поиск корневых объектов:
   set enable_seqscan = off;
   SELECT * FROM W_LOCALITY_VIEW WHERE PARENT_ID IS NULL;
2. Поиск дочерних объектов заданного объекта:
   set enable_seqscan = off;
   SELECT * FROM W_LOCALITY_VIEW WHERE PARENT_ID=112562;

Скорость обработки:

Поиск корневых объектов	30.248 мсек
Поиск дочерних объектов заданного объекта	15.166 мсек

Размер таблиц:

W_LOCALITY	23,244,680
W_LOCALITY_TYPE	114

Вывод данных

1. SELECT * FROM W_LOCALITY_VIEW WHERE PARENT_ID IS NULL;

   ID	PREFIX	NAME	PARENT_ID
   1331	Респ	Калмыкия	NULL
   1770	Респ	Марий Эл	NULL
   2992	Респ	Удмуртская	NULL
   3775	край	Алтайский	NULL
   ....	....	....	....
   208565	г	Севастополь	NULL

2. SELECT * FROM W_LOCALITY_VIEW WHERE PARENT_ID=112562;

   ID	PREFIX	NAME	PARENT_ID
   112563	г	Воронеж	112562
   164621	г	Воронеж-45	112562
   34380	г	Лиски	112562
   8262	г	Нововоронеж	112562
   ....	....	....	....
   8314	р-н	Калачеевский	112562

3. SELECT * FROM W_LOCALITY_VIEW WHERE ID=112562

   ID	PREFIX	NAME	PARENT_ID
   112562	обл	Воронежская	NULL

Преобразование номеров домов КЛАДР в множество

В КЛАДР номера домов представлены пятью способами.

1. Непосредственно номер дома (1А)
2. Последовательность номеров домов (1А,1В)
3. Диапазоном номеров домов (1-10)
4. Последовательность домов с четными номера (Ч(1-10))
5. Последовательность домов с нечетными номерами (Н(1-10))

Преобразуем 1 и 2 представление в множество.

SELECT

CODE,

TYPE,

HOUSE

FROM

(SELECT unnest(string_to_array(HOUSE, ',')) as HOUSE, (SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE, CODE as CODE FROM kladr_base.w_doma_tbl) T1

WHERE

HOUSE!~'^[0-9]+-[0-9]+$' AND

HOUSE!~'^(Н|Ч)\([0-9]+-[0-9]+\)$';

Преобразуем 3 представление в множество.

WITH RECURSIVE T1 as (

SELECT row_number() over() as RN, string_to_array(HOUSE, '-') as HOUSE, (SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE, CODE FROM (SELECT unnest(string_to_array(HOUSE, ',')) as HOUSE, TYPE AS TYPE, CODE as CODE FROM kladr_base.w_doma_tbl) T10 WHERE HOUSE~'^[0-9]+-[0-9]+$'),

T2(RN, CODE, TYPE, HOUSE) AS (

SELECT RN, CODE, TYPE, HOUSE[1]::INT AS HOUSE FROM T1 UNION ALL SELECT T2.RN, T2.CODE, T2.TYPE, T2.HOUSE + 1 AS HOUSE FROM T1 INNER JOIN T2 ON T2.RN=T1.RN WHERE T2.HOUSE >= T1.HOUSE[1]::INT AND (T2.HOUSE + 1) <= T1.HOUSE[2]::INT)

SELECT

CODE,

TYPE,

HOUSE

FROM

T2;

Преобразуем 4 и 5 представление в множество.

WITH RECURSIVE T3 as (

SELECT row_number() over() as RN, string_to_array(substring(HOUSE from '[0-9]+-[0-9]+'), '-') as HOUSE, (SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE, CODE FROM (SELECT unnest(string_to_array(HOUSE, ',')) as HOUSE, TYPE as TYPE, CODE as CODE FROM kladr_base.w_doma_tbl) T10 WHERE HOUSE~'^(Н|Ч)\([0-9]+-[0-9]+\)$'),

T4(RN, CODE, TYPE, HOUSE) AS (

SELECT RN, CODE, TYPE, HOUSE[1]::INT AS HOUSE FROM T3 UNION ALL SELECT T4.RN, T4.CODE, T4.TYPE, T4.HOUSE + 2 AS HOUSE FROM T3 INNER JOIN T4 ON T4.RN=T3.RN WHERE T4.HOUSE >= T3.HOUSE[1]::INT AND (T4.HOUSE + 2) <= T3.HOUSE[2]::INT)

SELECT

CODE,

TYPE,

HOUSE

FROM

T4;

Объединим получившиеся решения.

WITH RECURSIVE T1 as (

SELECT row_number() over() as RN, string_to_array(HOUSE, '-') as HOUSE, (SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE, CODE FROM (SELECT unnest(string_to_array(HOUSE, ',')) as HOUSE, TYPE AS TYPE, CODE as CODE FROM kladr_base.w_doma_tbl) T10 WHERE HOUSE~'^[0-9]+-[0-9]+$'),

T2(RN, CODE, TYPE, HOUSE) AS (

SELECT RN, CODE, TYPE, HOUSE[1]::INT AS HOUSE FROM T1 UNION ALL SELECT T2.RN, T2.CODE, T2.TYPE, T2.HOUSE + 1 AS HOUSE FROM T1 INNER JOIN T2 ON T2.RN=T1.RN WHERE T2.HOUSE >= T1.HOUSE[1]::INT AND (T2.HOUSE + 1) <= T1.HOUSE[2]::INT),

T3 as (

SELECT row_number() over() as RN, string_to_array(substring(HOUSE from '[0-9]+-[0-9]+'), '-') as HOUSE, (SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE, CODE FROM (SELECT unnest(string_to_array(HOUSE, ',')) as HOUSE, TYPE as TYPE, CODE as CODE FROM kladr_base.w_doma_tbl) T10 WHERE HOUSE~'^(Н|Ч)\([0-9]+-[0-9]+\)$'),

T4(RN, CODE, TYPE, HOUSE) AS (

SELECT RN, CODE, TYPE, HOUSE[1]::INT AS HOUSE FROM T3 UNION ALL SELECT T4.RN, T4.CODE, T4.TYPE, T4.HOUSE + 2 AS HOUSE FROM T3 INNER JOIN T4 ON T4.RN=T3.RN WHERE T4.HOUSE >= T3.HOUSE[1]::INT AND (T4.HOUSE + 2) <= T3.HOUSE[2]::INT)

SELECT

CODE,

TYPE,

to_char(HOUSE, '99999')

FROM

T2

UNION ALL
SELECT

CODE,

TYPE,

to_char(HOUSE, '99999')

FROM

T4

UNION ALL
SELECT

CODE,

TYPE,

HOUSE

FROM

(SELECT unnest(string_to_array(HOUSE, ',')) as HOUSE, (SELECT ID FROM W_LOCALITY_TYPE WHERE NAME=TYPE) as TYPE, CODE as CODE FROM kladr_base.w_doma_tbl) T1

WHERE

HOUSE!~'^[0-9]+-[0-9]+$' AND

HOUSE!~'^(Н|Ч)\([0-9]+-[0-9]+\)$';

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

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

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)

Run Windows WDM Driver

The first method is to use the CreateService API.

The second method is to manually create these values in the following registry location:

• HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\<driver name>

Registry Value		Description		Example
DisplayName		Name of your driver for service list		my_driver
ImagePath		Full NT-style path to the driver (or just the filename if it lives in system32\drivers).		\??\C:\drv\drv.sys
Start		How the driver can be started. For testing the recommended value is Demand (3), which indicates that the driver can only be started manually. The other start values - Boot (0), System (1) and Auto (2) instruct Windows to load the driver at various points during the system startup. The last value - Disabled (4) - prevents the driver from loading at all.		3
Type		The type of service. Basic kernel drivers must have a value of "1" here.		1

Starting a Driver

Once a driver has been registered as a system-service, it can be loaded (and unloaded) using the Service Control Manager.

1. programmatically:
   StartService API call.
2. command-prompt:
   net start my_driver

Unload driver

1. programmatically:
   ControlService API call.
2. command-prompt:
   net stop my_driver