Как реализовать контроль прохождения маршрута через PostGIS?

Question

Кратко:

Необходимо проверить, соотвествует ли пройденный машрут транспортом заложенному для него маршруту.

Подробно:

У меня есть две таблицы:

Таблица с контрольными точками машрута:

route_points:

id: идентификатор точки

route_id: индентификатор маршрута

point: координата контрольной точки

order_id: сортировка точек

Таблица с историей перемещения транспорта:

transort_logs:

id: идентификатор лога

datetime: дата-время лога

point: координаты местоположения транспорта

transport_id: идентификатор транспорта

Я хочу выполнить запрос и получить примерно следующий ответ:


route_point_id (точка маршрута) | match_found (пройдена ли) | time (если пройдена, то во сколько)
                              1 |                      true |                    2020-11-01 13:05
                              2 |                     false |                    null

Ниже я прикладываю мое представление о том, как это должно работать:

На картинке вы можете видеть:

CP (check point) - точки маршрута из таблицы route_points

L (log) - реальное местоположение транспорта

Для каждого CP нужно сделать Buffer (зону вокруг точки с радиусом, допустим, 30 метров)

Проверить, есть ли точки L, которые входят в эту область

Если есть - сохранить время прохождения точки (если точек в области несколько, то самое первое время)

Я бы хотел иметь один SQL запрос для этого.

Answer 1

Запрос будет типа такого:

SELECT 
   r.id route_point_id,
   min(l.id) NOT NULL AS match_found,
   min(t.datetime) "time"
FROM route_points AS r
   LEFT JOIN transport_logs l ON (ST_DWithin(r.point, l.point, 30))
WHERE r.route_id = <route_id> AND l.id = <transport_log_id>
GROUP BY r.id

Если есть несколько точек в радиусе от точки маршрута, то будет выбрана самая первая.

Нужно создать индексы для столбцов point в обеих таблицах:

CREATE INDEX route_points__point_idx ON route_points USING GIST ( point ); 
CREATE INDEX transport_logs__point_idx ON transport_logs USING GIST ( point );

БЛОГ НА HUSL

Как реализовать контроль прохождения маршрута через PostGIS?

Ответы (1 шт):