Использование PostGIS для измерения расстояний: различия между версиями

Материал из GIS-Lab
Перейти к навигации Перейти к поиску
(создал статью "измерение расстояний с помощью постгис")
 
(вроде закончил статью, осталось пройти модерацию))))
Строка 58: Строка 58:


Разобьём планируемый запрос на несколько логических этапов:
Разобьём планируемый запрос на несколько логических этапов:
# Объединение и перепроецирование геометрий водных объектов.
# Объединение и перепроецирование геометрий водных объектов из разных таблиц.
# Создание пространственного индекса для объединённых и перепроецированных геометрий водных объектов (для последующего ускорения запроса для вычисления расстояний).
# Создание пространственного индекса для объединённых и перепроецированных геометрий водных объектов (для последующего ускорения запроса для вычисления расстояний).
# Непосредственно вычисление расстояний от определённых объектов таблицы illegal_dumps до ближайшего водного объекта.
# Непосредственно вычисление расстояний от определённых объектов таблицы illegal_dumps до ближайшего водного объекта.
Строка 65: Строка 65:


Нам понадобятся следующие функции PostGIS/PostgreSQL:
Нам понадобятся следующие функции PostGIS/PostgreSQL:
* [http://postgis.refractions.net/docs/ST_Distance.html ST_Distance]
* [http://www.postgis.org/docs/ST_Transform.html ST_Transfrom]
* [http://www.postgis.org/docs/ST_SetSRID.html ST_SetSRID]
* [http://www.postgis.org/docs/ST_DWithin.html ST_DWithin]
* [http://www.postgresql.org/docs/9.1/static/sql-select.html SELECT]
* [http://www.postgresql.org/docs/9.1/static/sql-select.html SELECT]
* [http://www.postgresql.org/docs/9.1/static/sql-createtable.html CREATE TABLE]
* [http://www.postgresql.org/docs/9.1/static/sql-createtable.html CREATE TABLE]
Строка 73: Строка 77:
* [http://www.postgresql.org/docs/9.1/interactive/sql-select.html#SQL-ORDERBY ORDER BY]
* [http://www.postgresql.org/docs/9.1/interactive/sql-select.html#SQL-ORDERBY ORDER BY]
* [http://www.postgresql.org/docs/9.1/interactive/sql-update.html UPDATE]
* [http://www.postgresql.org/docs/9.1/interactive/sql-update.html UPDATE]
* [http://www.postgis.org/docs/ST_Transform.html ST_Transfrom]
* [http://www.postgresql.org/docs/9.1/static/sql-createcast.html CAST]
* [http://www.postgis.org/docs/ST_SetSRID.html ST_SetSRID]
 
* [http://www.postgis.org/docs/ST_DWithin.html ST_DWithin]
==== Составление запроса ====
Для начала создадим временную таблицу, в которую поместим перепроецированные геометрии из таблиц, содержащих водные объекты:
<syntaxhighlight lang="sql">
CREATE TEMPORARY TABLE w AS  -- создаём временную таблицу
SELECT ST_Transfrom(l.geom, 28403) -- выбираем все записи поля геометрии таблицы water_line и перепроецируем их в EPSG:28403
  AS combine -- задаём имя результирующего поля геометрии
FROM water_line AS l -- задаём псевдоним таблицы для данного запроса
UNION ALL -- объединяем полученный результат со следующим запросом:
  SELECT ST_Transfrom(ST_SetSRID(p.geom, 3395), 28403) -- выбираем все записи поля геометрии из water_poly,
                                                      -- задаём им проекцию EPSG:3395 и перепроецируем в EPSG:28403
FROM water_poly AS p; -- задаём псевдоним таблицы для данного запроса
</syntaxhighlight>


Для того, чтобы существенно ускорить последующий запрос для вычисления расстояний, создадим пространственный индекс для нашей временной таблицы w:
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
CREATE TEMPORARY TABLE w AS
CREATE INDEX temp_water_geom -- создаём индекс и даём ему имя
SELECT ST_Transfrom(l.geom, 28403) AS combine  
  ON w -- определяем индексируемую таблицу
FROM water_line AS l
  USING gist -- указываем тип индекса
UNION ALL SELECT ST_Transfrom(ST_SetSRID(p.the_geom, 3395), 28403)  
  (combine); -- указываем индексируемое поле
FROM water_poly AS p;
</syntaxhighlight>
</syntaxhighlight>


Опционально можно создать перманентный пространственный индекс для геометрии таблицы illegal_dump в проекции 28403:
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
CREATE INDEX temp_water_geom
CREATE INDEX pulkovo_dumps
   ON w
   ON illegal_dumps
   USING gist
   USING gist
   (combine);
   (ST_Transform(ST_SetSRID(dump.geom, 3395), 28403));
</syntaxhighlight>
</syntaxhighlight>


Теперь вычислим расстояния. Нас интересуют те объекты таблицы dump_dist, которые в поле NAME имеют значение "illegal dump". Чтобы существенно ускорить процесс (PostGIS будет вычислять расстояния от каждого объекта до каждого объекта, что может занять очень много времени) отсеем те водные объекты, которые, находятся на расстоянии, скажем 10000 метров от свалок (мы уверены, что расстояние от свалок до водных объектов не превышает 10 км). Для этого применим функцию ST_DWithin, которая и воспользуется пространственным индексом, который мы создали для временной таблицы w.
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
CREATE TEMPORARY TABLE a AS
CREATE TEMPORARY TABLE a AS
SELECT DISTINCT ON (dump.gid) dump.gid,
SELECT DISTINCT ON (dump.gid) dump.gid, -- задаём условие, чтобы одной записи в таблице illegal_dumps
   ST_Distance(ST_Transform(ST_SetSRID(dump.the_geom, 3395), 28403), w.combine)  
                                        -- соответствовало только одно значение расстояния
   AS buil_dist
   ST_Distance(ST_Transform(ST_SetSRID(dump.geom, 3395), 28403), w.combine) -- вычисляем расстояния между объектами двух таблиц
FROM illegal_dumps AS dump   
   AS wat_dist
  FROM illegal_dumps AS dump   
LEFT JOIN w
LEFT JOIN w
FROM ST_Dwithin(ST_Transform(ST_SetSRID(dump.the_geom, 3395), 28403), w.combine), 10000)
  FROM ST_Dwithin(ST_Transform(ST_SetSRID(dump.geom, 3395), 28403), w.combine), 10000) -- убираем из расчётов
WHERE dump."NAME"='illegal dump'
                          -- все объекты таблицы w, которые находятся дальше 10 км от объектов illegal_dumps
ORDER BY dump.gid, ST_Distance(ST_Transform(ST_SetSRID(dump.the_geom, 3395), 28403), w.combine);
  WHERE dump."NAME"='illegal dump'
ORDER BY dump.gid, -- сортируем результата сначала по значениям gid,
        ST_Distance(ST_Transform(ST_SetSRID(dump.geom, 3395), 28403), w.combine); --а затем - по значениям расстояний
                                                                                  -- между объектами (таким образом
                                                -- в результирующей таблице остаются только самые короткие расстояния)
</syntaxhighlight>
</syntaxhighlight>


Теперь занесём полученные значения в таблицу dump_dist (поле water_dist). Для того, чтобы преобразовать значения типа float8 (нам ведь не нужна нанометровая точность), которые нам вернула функция ST_Distance, в int (тип данных поля water_dist - int) используем функцию CAST.
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
UPDATE dump_dist
UPDATE dump_dist -- обновляем таблицу dump_dist
SET water_dist = (SELECT CAST(buil_dist AS int) FROM a WHERE dump_dist.gid = a.gid)
SET water_dist = -- устанавливаем значения поля water_dist равными:
              (SELECT CAST(buil_dist AS int) -- выбираем значения из таблицы "a" и задаём им тип int.
                FROM a) --
FROM a
FROM a
WHERE dump_dist.gid = a.gid;
WHERE dump_dist.gid = a.gid;
</syntaxhighlight>
</syntaxhighlight>


Удаляем ставшие более ненужными временные таблицы a и w.
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
DROP TABLE a, w;
DROP TABLE a, w;
</syntaxhighlight>
</syntaxhighlight>
==== Заключение ====
На простом примере из реальной практики было продемонстрировано использование комбинирование различных функций PostgreSQL/PostGIS для расчёта расстояний между объектами.

Версия от 19:40, 3 июня 2012

Эта страница является черновиком статьи.


При работе с пространственными данными часто возникает необходимость вычислить расстояния от одних объектов до других. Для решения этой задачи как нельзя лучше подходит PostGIS.

В данной статье мы рассмотрим вычисление расстояний с помощью PostGIS и продемонстрируем некоторые полезные функции, которые сделают этот процесс более удобным.

Задача

Вычислить расстояния от некоторых объектов данного слоя до ближайших водоёмов или рек.

Данные

В нашей базе данных содержатся следующие таблицы:

Название таблицы
Тип геометрии
Проекция
Первичный ключ
Примечания
water_line полилинии EPSG: 3395 gid очень много объектов
water_poly мультиполигоны не задана

(EPSG: 3395)

gid очень много объектов
illegal_dump мультиполигоны не задана

(EPSG: 3395)

gid мало объектов
dump_dist без поля геометрии без проекции gid предназначена для хранения расстояний от свалок до различных объектов


Как мы видим, все используемые таблицы, содержащие поле геометрии, находятся в одной проекции (точнее это мы знаем, что они в одной проекции), однако, в самой базе данных, проекция для water_poly и illegal_dump не задана. Кроме того, используемая проекция не подходит для измерения расстояний. Таблицы с водными объектами содержат десятки и даже сотни тысяч записей, что делает вычисление расстояний до них весьма продолжительным. Ну и напоследок самое прекрасное: нас интересует расстояние до любого ближайшего водного объекта и неважно, находится ли он в таблице water_line или water_poly.

Планирование запроса

Очевидно, что при расчёте расстояний, нам понадобиться задать проекцию таблицам, где она не задана; осуществить перепроецирование в проекцию более подходящую для расчёта расстояний (соответствующую зону Гаусса-Крюгера); совместить геометрии полилиний и мультиполигонов в одном поле геометрии (да, в отличие от .shp, PostGIS позволяет хранить различные типы геометрий в одном поле); ну и наконец, заполонить таблицу расстояний искомыми значениями.

Разобьём планируемый запрос на несколько логических этапов:

  1. Объединение и перепроецирование геометрий водных объектов из разных таблиц.
  2. Создание пространственного индекса для объединённых и перепроецированных геометрий водных объектов (для последующего ускорения запроса для вычисления расстояний).
  3. Непосредственно вычисление расстояний от определённых объектов таблицы illegal_dumps до ближайшего водного объекта.
  4. Запись полученных значений в таблицу расстояний (уточним, что в таблице dump_dist поля имеют тип данных int, чтобы хранить расстояния в целых метрах в то время, как функция определения расстояния возвращает тип float8).
  5. Удаление временных файлов.

Нам понадобятся следующие функции PostGIS/PostgreSQL:

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

Для начала создадим временную таблицу, в которую поместим перепроецированные геометрии из таблиц, содержащих водные объекты: 

CREATE TEMPORARY TABLE w AS  -- создаём временную таблицу
SELECT ST_Transfrom(l.geom, 28403) -- выбираем все записи поля геометрии таблицы water_line и перепроецируем их в EPSG:28403
  AS combine -- задаём имя результирующего поля геометрии 
FROM water_line AS l -- задаём псевдоним таблицы для данного запроса
UNION ALL -- объединяем полученный результат со следующим запросом:
  SELECT ST_Transfrom(ST_SetSRID(p.geom, 3395), 28403) -- выбираем все записи поля геометрии из water_poly,
                                                       -- задаём им проекцию EPSG:3395 и перепроецируем в EPSG:28403
FROM water_poly AS p; -- задаём псевдоним таблицы для данного запроса

Для того, чтобы существенно ускорить последующий запрос для вычисления расстояний, создадим пространственный индекс для нашей временной таблицы w: 

CREATE INDEX temp_water_geom -- создаём индекс и даём ему имя
  ON w -- определяем индексируемую таблицу
  USING gist -- указываем тип индекса
  (combine); -- указываем индексируемое поле

Опционально можно создать перманентный пространственный индекс для геометрии таблицы illegal_dump в проекции 28403: 

CREATE INDEX pulkovo_dumps
  ON illegal_dumps
  USING gist
  (ST_Transform(ST_SetSRID(dump.geom, 3395), 28403));

Теперь вычислим расстояния. Нас интересуют те объекты таблицы dump_dist, которые в поле NAME имеют значение "illegal dump". Чтобы существенно ускорить процесс (PostGIS будет вычислять расстояния от каждого объекта до каждого объекта, что может занять очень много времени) отсеем те водные объекты, которые, находятся на расстоянии, скажем 10000 метров от свалок (мы уверены, что расстояние от свалок до водных объектов не превышает 10 км). Для этого применим функцию ST_DWithin, которая и воспользуется пространственным индексом, который мы создали для временной таблицы w. 

CREATE TEMPORARY TABLE a AS
SELECT DISTINCT ON (dump.gid) dump.gid, -- задаём условие, чтобы одной записи в таблице illegal_dumps 
                                        -- соответствовало только одно значение расстояния
  ST_Distance(ST_Transform(ST_SetSRID(dump.geom, 3395), 28403), w.combine) -- вычисляем расстояния между объектами двух таблиц
  AS wat_dist
  FROM illegal_dumps AS dump  
LEFT JOIN w
  FROM ST_Dwithin(ST_Transform(ST_SetSRID(dump.geom, 3395), 28403), w.combine), 10000) -- убираем из расчётов 
                           -- все объекты таблицы w, которые находятся дальше 10 км от объектов illegal_dumps
  WHERE dump."NAME"='illegal dump'
ORDER BY dump.gid, -- сортируем результата сначала по значениям gid, 
         ST_Distance(ST_Transform(ST_SetSRID(dump.geom, 3395), 28403), w.combine); --а затем - по значениям расстояний 
                                                                                   -- между объектами (таким образом 
                                                 -- в результирующей таблице остаются только самые короткие расстояния)

Теперь занесём полученные значения в таблицу dump_dist (поле water_dist). Для того, чтобы преобразовать значения типа float8 (нам ведь не нужна нанометровая точность), которые нам вернула функция ST_Distance, в int (тип данных поля water_dist - int) используем функцию CAST. 

UPDATE dump_dist -- обновляем таблицу dump_dist
SET water_dist =  -- устанавливаем значения поля water_dist равными:
               (SELECT CAST(buil_dist AS int) -- выбираем значения из таблицы "a" и задаём им тип int.
                FROM a) -- 
FROM a
WHERE dump_dist.gid = a.gid;

Удаляем ставшие более ненужными временные таблицы a и w. 

DROP TABLE a, w;

Заключение

На простом примере из реальной практики было продемонстрировано использование комбинирование различных функций PostgreSQL/PostGIS для расчёта расстояний между объектами.

Источник — https://wiki.gis-lab.info/index.php?title=Использование_PostGIS_для_измерения_расстояний&oldid=5476