Устанавливаем 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.