Использование PostGIS для измерения расстояний - История изменений

SSSRebelious в 18:55, 3 июня 2012

2012-06-03T18:55:19Z

← Предыдущая версия		Версия от 20:55, 3 июня 2012
Строка 131:		Строка 131:
	SET water_dist = -- устанавливаем значения поля water_dist равными:		SET water_dist = -- устанавливаем значения поля water_dist равными:
	(SELECT CAST(buil_dist AS int) -- выбираем значения из таблицы "a" и задаём им тип int.		(SELECT CAST(buil_dist AS int) -- выбираем значения из таблицы "a" и задаём им тип int.
	FROM a) --		FROM a)
	FROM a		FROM a
	WHERE dump_dist.gid = a.gid;		WHERE dump_dist.gid = a.gid;

SSSRebelious: вроде закончил статью, осталось пройти модерацию)))

2012-06-03T18:40:53Z

вроде закончил статью, осталось пройти модерацию)))

← Предыдущая версия		Версия от 20:40, 3 июня 2012
Строка 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 для расчёта расстояний между объектами.

SSSRebelious: создал статью "измерение расстояний с помощью постгис"

2012-06-03T13:27:06Z

создал статью "измерение расстояний с помощью постгис"

Новая страница

{{Статья|Черновик}}
При работе с пространственными данными часто возникает необходимость вычислить расстояния от одних объектов до других. Для решения этой задачи как нельзя лучше подходит [http://www.postgis.org/ PostGIS].

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

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

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

{| style="border-spacing:0;"
| style="border-top:0.05pt solid #000000;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| <center>'''Название таблицы'''</center>
| style="border-top:0.05pt solid #000000;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| <center>'''Тип геометрии'''</center>
| style="border-top:0.05pt solid #000000;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| <center>'''Проекция'''</center>
| style="border-top:0.05pt solid #000000;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| <center>'''Первичный ключ'''</center>
| style="border:0.05pt solid #000000;padding:0.097cm;"| <center>'''Примечания'''</center>

|-
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| water_line
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| полилинии
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| EPSG: 3395
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| gid
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:0.05pt solid #000000;padding:0.097cm;"| очень много объектов

|-
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| water_poly
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| мультиполигоны
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| не задана

(EPSG: 3395)
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| gid
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:0.05pt solid #000000;padding:0.097cm;"| очень много объектов

|-
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| illegal_dump
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| мультиполигоны
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| не задана

(EPSG: 3395)
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| gid
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:0.05pt solid #000000;padding:0.097cm;"| мало объектов

|-
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| dump_dist
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| без поля геометрии
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| без проекции
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:none;padding:0.097cm;"| gid
| style="border-top:none;border-bottom:0.05pt solid #000000;border-left:0.05pt solid #000000;border-right:0.05pt solid #000000;padding:0.097cm;"| предназначена для хранения расстояний от свалок до различных объектов

|}

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

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

Разобьём планируемый запрос на несколько логических этапов:
# Объединение и перепроецирование геометрий водных объектов.
# Создание пространственного индекса для объединённых и перепроецированных геометрий водных объектов (для последующего ускорения запроса для вычисления расстояний).
# Непосредственно вычисление расстояний от определённых объектов таблицы illegal_dumps до ближайшего водного объекта.
# Запись полученных значений в таблицу расстояний (уточним, что в таблице dump_dist поля имеют тип данных [http://ru.wikipedia.org/wiki/%D0%A6%D0%B5%D0%BB%D0%BE%D0%B5_%28%D1%82%D0%B8%D0%BF_%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85%29 int], чтобы хранить расстояния в целых метрах в то время, как функция определения расстояния возвращает тип [http://ru.wikipedia.org/wiki/Float float8]).
# Удаление временных файлов.

Нам понадобятся следующие функции PostGIS/PostgreSQL:
* [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-droptable.html DROP TABLE]
* [http://www.postgresql.org/docs/9.1/static/sql-createindex.html CREATE INDEX]
* [http://www.postgresql.org/docs/9.1/static/queries-union.html UNION ALL]
* [http://www.postgresql.org/docs/9.1/static/tutorial-join.html LEFT JOIN]
* [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.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]

<syntaxhighlight lang="sql">
CREATE TEMPORARY TABLE w AS
SELECT ST_Transfrom(l.geom, 28403) AS combine
FROM water_line AS l
UNION ALL SELECT ST_Transfrom(ST_SetSRID(p.the_geom, 3395), 28403)
FROM water_poly AS p;
</syntaxhighlight>

<syntaxhighlight lang="sql">
CREATE INDEX temp_water_geom
ON w
USING gist
(combine);
</syntaxhighlight>

<syntaxhighlight lang="sql">
CREATE TEMPORARY TABLE a AS
SELECT DISTINCT ON (dump.gid) dump.gid,
ST_Distance(ST_Transform(ST_SetSRID(dump.the_geom, 3395), 28403), w.combine)
AS buil_dist
FROM illegal_dumps AS dump
LEFT JOIN w
FROM ST_Dwithin(ST_Transform(ST_SetSRID(dump.the_geom, 3395), 28403), w.combine), 10000)
WHERE dump."NAME"='illegal dump'
ORDER BY dump.gid, ST_Distance(ST_Transform(ST_SetSRID(dump.the_geom, 3395), 28403), w.combine);
</syntaxhighlight>

<syntaxhighlight lang="sql">
UPDATE dump_dist
SET water_dist = (SELECT CAST(buil_dist AS int) FROM a WHERE dump_dist.gid = a.gid)
FROM a
WHERE dump_dist.gid = a.gid;
</syntaxhighlight>

<syntaxhighlight lang="sql">
DROP TABLE a, w;
</syntaxhighlight>