Пространственные запросы в PostgreSQL с помощью встроенных функций PostGIS

Ответить
Анна Горева
Интересующийся
Сообщения: 15
Зарегистрирован: 02 ноя 2016, 13:20
Репутация: 8
Откуда: Нижний Новгород

Пространственные запросы в PostgreSQL с помощью встроенных функций PostGIS

Сообщение Анна Горева » 22 ноя 2017, 13:03

Есть задача: найти пересечение трассы и муниципального образования, из полученного пересечения вырезать пересечение с кадастровыми земельными участками.

Я попробовала реализовать такую задачу с помощью 1 запроса:
1 вариант - Сначала из муниципального образования вырезаются кадастровые земельные участки. Потом ищется пересечение полученного результата с трассой.

Код: Выделить всё

SELECT
	ST_PolygonFromText(ST_AsText(st_intersection(st_difference(bb.geom_local, cc.geom_local), aa.geom_buff_local)))
FROM
	obj_contour as aa,
	munic_obr as bb,
	rosreestr_obj as cc;
2 вариант - Сначала ищется пересечение трассы и муниципального образования. Потом из результата вырезаются кадастровые земельные участки.

Код: Выделить всё

SELECT
	ST_PolygonFromText(ST_AsText(st_difference(st_intersection(aa.geom_buff_local, bb.geom_local), cc.geom_local)))
FROM
	obj_contour as aa,
	munic_obr as bb,
	rosreestr_obj as cc;
Ошибки в синтаксисе отсутствуют. Запускаю запрос.... и жду больше часа, когда запрос выполниться. В результате не дождалась и принудительно прекратила работу (даже если запрос бы сработал рано или поздно, такая скорость его обработки меня не устраивает). Возможность создания временной/промежуточной таблицы не рассматривается.

Продвинутые юзеры помогите решить такую, казалось бы, не сложную задачу!!!

Александр Мурый
Гуру
Сообщения: 5173
Зарегистрирован: 26 сен 2009, 16:26
Репутация: 793
Ваше звание: званий не имею
Откуда: Москва

Re: Пространственные запросы в PostgreSQL с помощью встроенных функций PostGIS

Сообщение Александр Мурый » 22 ноя 2017, 13:06

Индексы для таблиц с полигонами построены?
Редактор материалов, модератор форума

trir
Гуру
Сообщения: 5271
Зарегистрирован: 09 апр 2010, 19:30
Репутация: 1013
Ваше звание: просто мимо прохожу
Откуда: Ё-бург

Re: Пространственные запросы в PostgreSQL с помощью встроенных функций PostGIS

Сообщение trir » 22 ноя 2017, 13:16

а сколько выполняются запросы по отдельности?
может стоит сделать вложеные запросы?

Анна Горева
Интересующийся
Сообщения: 15
Зарегистрирован: 02 ноя 2016, 13:20
Репутация: 8
Откуда: Нижний Новгород

Re: Пространственные запросы в PostgreSQL с помощью встроенных функций PostGIS

Сообщение Анна Горева » 22 ноя 2017, 13:20

Индексы для таблиц с полигонами построены?
"Точно не знаю. Не моя БД. Уточню" - Уточнила! у полей geom нет индексов
а сколько выполняются запросы по отдельности?
По отдельности можно сказать мгновенно (6 секунд).
может стоит сделать вложеные запросы?
Подскажите как? Именно поэтому и задала вопрос на форуме

trir
Гуру
Сообщения: 5271
Зарегистрирован: 09 апр 2010, 19:30
Репутация: 1013
Ваше звание: просто мимо прохожу
Откуда: Ё-бург

Re: Пространственные запросы в PostgreSQL с помощью встроенных функций PostGIS

Сообщение trir » 22 ноя 2017, 13:33

как то так

Код: Выделить всё

SELECT st_intersection(tbl1.geom, aa.geom_buff_local)
from 
(SELECT st_difference(bb.geom_local, cc.geom_local) as geom from munic_obr as bb, rosreestr_obj as cc) as tbl1,
obj_contour as aa
ST_PolygonFromText(ST_AsText - это зачем?

Анна Горева
Интересующийся
Сообщения: 15
Зарегистрирован: 02 ноя 2016, 13:20
Репутация: 8
Откуда: Нижний Новгород

Re: Пространственные запросы в PostgreSQL с помощью встроенных функций PostGIS

Сообщение Анна Горева » 22 ноя 2017, 13:47

ST_PolygonFromText(ST_AsText - это зачем?
И правда незачем)) чтобы "наверняка" написала

Ariki
Гуру
Сообщения: 731
Зарегистрирован: 12 янв 2011, 22:40
Репутация: 304
Ваше звание:

Re: Пространственные запросы в PostgreSQL с помощью встроенных функций PostGIS

Сообщение Ariki » 22 ноя 2017, 14:03

А сколько строк в каждой из таблиц? CROSS JOIN трёх таблиц без дополнительных условий выглядит очень подозрительно, и мало шансов, что он выполнится за разумное время. Тут нужно ограничивать выборки объектов, участвующих в геометрических операциях, а не пытаться пересечь объекты во всех комбинациях.

Анна Горева
Интересующийся
Сообщения: 15
Зарегистрирован: 02 ноя 2016, 13:20
Репутация: 8
Откуда: Нижний Новгород

Re: Пространственные запросы в PostgreSQL с помощью встроенных функций PostGIS

Сообщение Анна Горева » 22 ноя 2017, 14:13

munic_obr - 2 контура
rosreestr_obj - порядка 100 тысяч контуров
obj_contour - 1 контур (пока один контур хранится. Если будет два и больше контуров, то WHERE obj_contour.id=1)

Анна Горева
Интересующийся
Сообщения: 15
Зарегистрирован: 02 ноя 2016, 13:20
Репутация: 8
Откуда: Нижний Новгород

Re: Пространственные запросы в PostgreSQL с помощью встроенных функций PostGIS

Сообщение Анна Горева » 22 ноя 2017, 16:29

Александр Мурый писал(а):
22 ноя 2017, 13:06
Индексы для таблиц с полигонами построены?
Пытаюсь создать индекс для геометрических полей...

Код: Выделить всё

CREATE INDEX rosreestr_GEOM_INDEX ON rosreestr_obj USING GIST ( geom_local );
Выдает ошибку "[Err] ОШИБКА: ошибка синтаксиса (примерное положение: "INDEX")"

Я ошибку проигнорировала и вроде индексы создались. Но быстрее от этого не стало..

Ariki
Гуру
Сообщения: 731
Зарегистрирован: 12 янв 2011, 22:40
Репутация: 304
Ваше звание:

Re: Пространственные запросы в PostgreSQL с помощью встроенных функций PostGIS

Сообщение Ariki » 23 ноя 2017, 01:18

Если выдал синтаксическую ошибку, то запрос не был выполнен. Хотя синтаксис вроде верный. Попытайтесь ещё раз: может, от предыдущей команды в консоли мусор остался?

Для решения вашей задачи попробуйте такой запрос:

Код: Выделить всё

SELECT ST_Difference(
    ST_Intersection(
        m.geom_local,
        (
            SELECT ST_Union(c.geom_buff_local)
            FROM obj_contour c
            WHERE ST_Intersects(c.geom_buff_local, m.geom_local)
        )
    ),
    (
        SELECT ST_Union(r.geom_local)
        FROM rosreestr_obj r
        WHERE ST_Intersects(r.geom_local, m.geom_local)
    )
) geom_diff
FROM munic_obr m;

Анна Горева
Интересующийся
Сообщения: 15
Зарегистрирован: 02 ноя 2016, 13:20
Репутация: 8
Откуда: Нижний Новгород

Re: Пространственные запросы в PostgreSQL с помощью встроенных функций PostGIS

Сообщение Анна Горева » 23 ноя 2017, 10:56

:D Получила адекватный результат!
Перелагаемые вами выше запросы скорее всего рабочие, но скорость та же... не дождалась результата.
Но вот за идею использования ST_UNION спасибо!

В итоге реализация такая:
1. Найти пересечение трассы и кадастровых земельных участков. Записать результат в БД в таблицу "intersection_obj".
2. Найти пересечение трассы и МО, и уже из этого вырезать контура из таблицы "intersection_obj" (предварительно их объединить) - скорость запроса 0.2 с!!! Записать результат в БД в таблицу "intersection_obj".

Код: Выделить всё

SELECT 
	st_intersection(aa.geom_buff_local, st_difference(bb.geom_local, tbl1.geom_inter)) as geom 
FROM 
	obj_contour as aa,
	munic_obr as bb, 
	(SELECT
		st_union(cc.geom_local) as geom_inter
	FROM 
		intersection_obj as cc
	WHERE 
		cc.obj_contour_id='1') as tbl1
WHERE 
	aa."id" = '1';

Ответить

Вернуться в «PostGIS/PostgreSQL»

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и 2 гостя