Sunday, November 30, 2014

Устанавливаем 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_LOCALITY23,244,680
W_LOCALITY_TYPE114

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

ROOT_IDIDPARENT_IDLEVELNAMEPREFIX
65487146964151037965310ДОМ
654871037965654872Дугинаул
65487654871958361Жуковскийг
65487195836NULL0Московскаяобл

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

Friday, November 28, 2014

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

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

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

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_LOCALITY23,244,680
W_LOCALITY_TYPE114

Вывод данных
  1. SELECT * FROM W_LOCALITY_VIEW WHERE PARENT_ID IS NULL;
    IDPREFIXNAMEPARENT_ID
    1331РеспКалмыкияNULL
    1770РеспМарий ЭлNULL
    2992РеспУдмуртскаяNULL
    3775крайАлтайскийNULL
    ................
    208565гСевастопольNULL
  2. SELECT * FROM W_LOCALITY_VIEW WHERE PARENT_ID=112562;
    IDPREFIXNAMEPARENT_ID
    112563гВоронеж112562
    164621гВоронеж-45112562
    34380гЛиски112562
    8262гНововоронеж112562
    ................
    8314р-нКалачеевский112562
  3. SELECT * FROM W_LOCALITY_VIEW WHERE ID=112562
    IDPREFIXNAMEPARENT_ID
    112562облВоронежскаяNULL

Sunday, November 23, 2014

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

В КЛАДР номера домов представлены пятью способами.
  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]+\)$';

Wednesday, November 12, 2014

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

Для хранения информации из КЛАДР необходимо создать три таблицы.
  1. CREATE TABLE W_LOCALITY_TYPE(
    IDbigserial NOT NULL PRIMARY KEY,
    NAMEcharacter varying(64) DEFAULT NULL UNIQUE,
    CREATE_DATETIMESTAMP with time zone NOT NULL DEFAULT NOW());
  2. CREATE TABLE W_LOCALITY(
    IDbigserial NOT NULL PRIMARY KEY,
    PARENT_IDbigint,
    NAME character varying(64) DEFAULT NULL,
    W_LOCALITY_TYPE_IDbigint NOT NULL references W_LOCALITY_TYPE(ID),
    KLADR_IDVARCHAR(19),
    CREATE_DATETIMESTAMP with time zone NOT NULL DEFAULT NOW())
  3. CREATE TABLE W_LOCALITY_ALIAS(
    IDbigserial NOT NULL PRIMARY KEY,
    W_LOCALITY_IDbigint NOT NULL references W_LOCALITY(ID),
    KLADR_IDVARCHAR(19),
    NAMEcharacter varying(64) NOT NULL,
    CREATE_DATETIMESTAMP 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)

Sunday, November 9, 2014

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 ValueDescriptionExample
DisplayNameName of your driver for service listmy_driver
ImagePathFull NT-style path to the driver
(or just the filename if it lives in system32\drivers).
\??\C:\drv\drv.sys
StartHow 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
TypeThe 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

Saturday, November 8, 2014

DBLink PostgreSQL To MySQL

  1. Устанавливаем git
    sudo yum install git
  2. Скачиваем модуль sqlite_fdw
    wget git clone git://github.com/atris/JDBC_FDW.git
  3. Создаем ссылку libjvm.so
    sudo ln -s /usr/lib/jvm/java-1.7.0/jre/lib/i386/server/libjvm.so /usr/lib/libjvm.so
  4. Заходим в каталог JDBC_FDW
    cd JDBC_FDW
  5. Устанавливаем модуль
    sudo PATH=/usr/pgsql-9.3/bin/:$PATH make USE_PGXS=1 install
  6. Входим в систему из под пользователя postgres
    sudo su - postgres
  7. Входим в postgresql
    psql
  8. Выбираем базу данных
    \c YourDatabase
  9. Создаем расширение
    CREATE EXTENSION jdbc_fdw;
  10. Создаем сервер
    CREATE SERVER jdbc_mysql FOREIGN DATA WRAPPER jdbc_fdw
        OPTIONS(
            drivername 'com.mysql.jdbc.Driver',
            url 'jdbc:mysql://192.168.1.2:3306/your_data_base',
            querytimeout '15',
            jarfile 'path_to/mysql-connector-java-5.1.22-bin.jar',
            maxheapsize '600'
        );
  11. Создаем мапинг пользователя
    CREATE USER MAPPING FOR PUBLIC SERVER jdbc_mysql
        OPTIONS(
            username 'root',
            password '123'
        );
  12. Создаем внешнюю таблицу
    CREATE FOREIGN TABLE test_tbl (
            a integer,
            b text)
        SERVER jdbc_mysql OPTIONS(table 'm_test');

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

Конвертация КЛАДР в формат sqlite.
  1. Скачиваем кладр с официального сайта
    wget www.gnivc.ru/html/gnivcsoft/KLADR/Base.7z
  2. Устанавливаем архиватор 7z
    sudo yum install p7zip
  3. Распаковываем архив
    7za e Base.7z
  4. Устанавливаем sqlite
    sudo yum install sqlite
  5. Устанавливаем sqlite3-dbf
    sudo yum install sqlite3-dbf
  6. Запускаем sqlite3
    sqlite3 my_kladr.db
  7. В sqlite загружаем модуль libspatialite
    .load libspatialite.so.2
  8. Импорт данных из КЛАДР в sqlite
    CREATE VIRTUAL TABLE virt_street_tbl USING VirtualDbf('/home/developer/kladr/STREET.DBF', 'CP866');
    CREATE VIRTUAL TABLE virt_socrbase_tbl USING VirtualDbf('/home/developer/kladr/SOCRBASE.DBF', 'CP866');
    CREATE VIRTUAL TABLE virt_kladr_tbl USING VirtualDbf('/home/developer/kladr/KLADR.DBF', 'CP866');
    CREATE VIRTUAL TABLE virt_flat_tbl USING VirtualDbf('/home/developer/kladr/FLAT.DBF', 'CP866');
    CREATE VIRTUAL TABLE virt_doma_tbl USING VirtualDbf('/home/developer/kladr/DOMA.DBF', 'CP866');
    CREATE VIRTUAL TABLE virt_altnames_tbl USING VirtualDbf('/home/developer/kladr/ALTNAMES.DBF', 'CP866');

    create table street_tbl as select * from virt_street_tbl;
    create table socrbase_tbl as select * from virt_socrbase_tbl;
    create table kladr_tbl as select * from virt_kladr_tbl;
    create table flat_tbl as select * from virt_flat_tbl;
    create table doma_tbl as select * from virt_doma_tbl;
    create table altnames_tbl as select * from virt_altnames_tbl;

    drop table virt_street_tbl;
    drop table virt_socrbase_tbl;
    drop table virt_kladr_tbl;
    drop table virt_flat_tbl;
    drop table virt_doma_tbl;
    drop table virt_altnames_tbl;
  9. Выходим из sqlite
    .exit
Резлуьтат: файл my_kladr.db содержит КЛАДР в формате sqlite.

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

Конвертация КЛАДР в формат sqlite.
  1. Скачиваем кладр с официального сайта
    wget www.gnivc.ru/html/gnivcsoft/KLADR/Base.7z
  2. Устанавливаем архиватор 7z
    sudo yum install p7zip
  3. Распаковываем архив
    7za e Base.7z
  4. Устанавливаем sqlite
    sudo yum install sqlite
  5. Устанавливаем sqlite3-dbf
    sudo yum install sqlite3-dbf
  6. Запускаем sqlite3
    sqlite3 my_kladr.db
  7. В sqlite загружаем модуль libspatialite
    .load libspatialite.so.2
  8. Импорт данных из КЛАДР в sqlite
    CREATE VIRTUAL TABLE virt_street_tbl USING VirtualDbf('/home/developer/kladr/STREET.DBF', 'CP866');
    CREATE VIRTUAL TABLE virt_socrbase_tbl USING VirtualDbf('/home/developer/kladr/SOCRBASE.DBF', 'CP866');
    CREATE VIRTUAL TABLE virt_kladr_tbl USING VirtualDbf('/home/developer/kladr/KLADR.DBF', 'CP866');
    CREATE VIRTUAL TABLE virt_flat_tbl USING VirtualDbf('/home/developer/kladr/FLAT.DBF', 'CP866');
    CREATE VIRTUAL TABLE virt_doma_tbl USING VirtualDbf('/home/developer/kladr/DOMA.DBF', 'CP866');
    CREATE VIRTUAL TABLE virt_altnames_tbl USING VirtualDbf('/home/developer/kladr/ALTNAMES.DBF', 'CP866');

    create table street_tbl as select * from virt_street_tbl;
    create table socrbase_tbl as select * from virt_socrbase_tbl;
    create table kladr_tbl as select * from virt_kladr_tbl;
    create table flat_tbl as select * from virt_flat_tbl;
    create table doma_tbl as select * from virt_doma_tbl;
    create table altnames_tbl as select * from virt_altnames_tbl;

    drop table virt_street_tbl;
    drop table virt_socrbase_tbl;
    drop table virt_kladr_tbl;
    drop table virt_flat_tbl;
    drop table virt_doma_tbl;
    drop table virt_altnames_tbl;
  9. Выходим из sqlite
    .exit
Резлуьтат: файл my_kladr.db содержит КЛАДР в формате sqlite.

Подключение файла my_kladr.db к базе данных PostgreSQL
  1. Скачиваем модуль sqlite_fdw
    wget https://github.com/gleu/sqlite_fdw/archive/master.zip
  2. Устанавливаем unzip
    sudo yum install unzip
  3. Распаковываем архив
    unzip master
  4. Заходим в каталог sqlite_fdw-master
    cd sqlite_fdw-master
  5. Устанавливаем модуль
    sudo PATH=/usr/pgsql-9.3/bin/:$PATH make USE_PGXS=1 install
  6. Входим в систему из под пользователя postgres
    sudo su - postgres
  7. Входим в postgresql
    psql
  8. Выбираем базу данных
    \c YourDatabase
  9. Создаем расширение
    CREATE EXTENSION sqlite_fdw;
  10. Создаем сервер
    CREATE SERVER sqlite_kladr_server
        FOREIGN DATA WRAPPER sqlite_fdw
        OPTIONS (database 'path_to_my_kladr.db');
  11. Создаем схему
    create schema kladr;
  12. Создаем внешние таблицы
    CREATE FOREIGN TABLE kladr.street_tbl(
      id bigint,
      name varchar,
      type varchar,
      code varchar,
      c2 varchar,
      c3 varchar,
      c4 varchar,
      c5 varchar)
        SERVER sqlite_kladr_server
         OPTIONS (table 'street_tbl');

    CREATE FOREIGN TABLE kladr.socrbase_tbl(
      id bigint,
      id1 bigint,
      short_name varchar,
      full_name varchar,
      id3 bigint)
        SERVER sqlite_kladr_server
         OPTIONS (table 'socrbase_tbl');

    CREATE FOREIGN TABLE kladr.kladr_tbl(
      id bigint,
      name varchar,
      type varchar,
      code varchar,
      c4 varchar,
      c5 varchar,
      c6 varchar,
      c7 bigint)
        SERVER sqlite_kladr_server
         OPTIONS (table 'kladr_tbl');

    CREATE FOREIGN TABLE kladr.doma_tbl(
      id bigint,
      house varchar,
      c1 varchar,
      c2 varchar,
      c3 varchar,
      c4 varchar,
      c5 varchar,
      c6 varchar,
      c7 varchar)
        SERVER sqlite_kladr_server
         OPTIONS (table 'doma_tbl');

    CREATE FOREIGN TABLE kladr.altnames_tbl(
      id bigint,
      code1 varchar,
      code2 varchar,
      c1 varchar)
        SERVER sqlite_kladr_server
         OPTIONS (table 'altnames_tbl');
  13. Проверяем работу
    select * from kladr.kladr_tbl limit 10;
Результат: Кладр подключен к базе данных PostgreSQL.