Получение стартовой/конечной точки трека

Ответить
regrunger
Новоприбывший
Сообщения: 11
Зарегистрирован: 17 сен 2016, 11:57
Репутация: 0

Получение стартовой/конечной точки трека

Сообщение regrunger » 11 июл 2018, 16:46

Исходные данные: таблица с заездами автомобилей.
Задача: получить стартовую и финишную точку для каждого автомобиля на каждый день.

Нет сомнений, что это очень просто, но мне пока не удалось получить в результате запроса и атрибуты и геометрию:

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

SELECT vehicle_id,  date_time::date AS day, min(date_time) AS time
FROM gps_table
GROUP BY vehicle_id, day
Такой запрос возвращает корректные записи с началом движения, но при попытке добавить в SELECT геометрию, начинает ругаться. Предлагает, применить к ней агрегатные функции или группировку.

Буду рад любой помощи, уверен, решение лежит на поверхности!

freeExec
Гуру
Сообщения: 1195
Зарегистрирован: 23 апр 2011, 10:32
Репутация: 205
Откуда: Ульяновск

Re: Получение стартовой/конечной точки трека

Сообщение freeExec » 11 июл 2018, 16:48

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

SELECT geom, gps.* FROM
(ваш запрос) AS gps
WHERE vehicle_id = gps.vehicle_id AND day = gps.day

Dmitry Stasev
Участник
Сообщения: 67
Зарегистрирован: 13 мар 2018, 08:59
Репутация: 22
Откуда: MO

Re: Получение стартовой/конечной точки трека

Сообщение Dmitry Stasev » 11 июл 2018, 20:17

regrunger писал(а):
11 июл 2018, 16:46
очень просто
Ваш запрос содержит агрегат MIN, а значит нужна группировка, которая не работают с геометриями, да и блобами вообще (как можно сгруппировать по геометрии ???).

Вам надо разделить запрос на 2 составляющие.
Первая вернет список по Вашему запросу
Вторая по этому списку вернет геометрии

Можно, как предложил уважаемый freeExec, через "select from select"

а можно через обобщенное выражение ПРИМЕРНО ТАК:

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

with cte as(
	SELECT vehicle_id,  date_time::date AS day, min(date_time) AS time
	FROM gps_table
	GROUP BY vehicle_id, dayy
	)
select c.*, g.geometry from cte c
join gps_table g on c.time = g.date_time
where c.vehicle_id =  g.vehicle_id

freeExec
Гуру
Сообщения: 1195
Зарегистрирован: 23 апр 2011, 10:32
Репутация: 205
Откуда: Ульяновск

Re: Получение стартовой/конечной точки трека

Сообщение freeExec » 11 июл 2018, 20:51

Dmitry Stasev писал(а):
11 июл 2018, 20:17
группировка, которая не работают с геометриями
Вообще работает, например объединить все в одну

Dmitry Stasev
Участник
Сообщения: 67
Зарегистрирован: 13 мар 2018, 08:59
Репутация: 22
Откуда: MO

Re: Получение стартовой/конечной точки трека

Сообщение Dmitry Stasev » 11 июл 2018, 21:10

Да, неудачный пример я привел )))
имелась ввиду чистая группировка, а не агрегация

regrunger
Новоприбывший
Сообщения: 11
Зарегистрирован: 17 сен 2016, 11:57
Репутация: 0

Re: Получение стартовой/конечной точки трека

Сообщение regrunger » 12 июл 2018, 14:50

freeExec, cпасибо за быстрый ответ! У меня была проблема с оповещениями - уведомления о ваших ответах не пришли.
freeExec писал(а):
11 июл 2018, 16:48
SELECT geom, gps.* FROM
(ваш запрос) AS gps
WHERE vehicle_id = gps.vehicle_id AND day = gps.day
Когда увидел ваш вариант, у меня сразу появился вопрос: как я смогу
получить поле geom, если в результате запроса из которого я делаю выборку такого поля нет. К сожалению, мои ощущения подтвердились: "ERROR: column "pos" does not exist" (pos - это мое поле с геометрией).
Последний раз редактировалось regrunger 12 июл 2018, 15:06, всего редактировалось 1 раз.

regrunger
Новоприбывший
Сообщения: 11
Зарегистрирован: 17 сен 2016, 11:57
Репутация: 0

Re: Получение стартовой/конечной точки трека

Сообщение regrunger » 12 июл 2018, 15:01

Dmitry Stasev, cпасибо за ваше предложение! Но я прежде отвечу на ваш вопрос цитатой с gis.stackexchange:
Dmitry Stasev писал(а):
11 июл 2018, 20:17
Ваш запрос содержит агрегат MIN, а значит нужна группировка, которая не работают с геометриями, да и блобами вообще (как можно сгруппировать по геометрии ???)
"It's important to know that a GROUP BY geom clause in PostGIS 2.3 and earlier actually groups rows based on bounding box equality, not geometric equality. With real, non-contrived data, this often has the same effect as grouping on geometric equality (and is much faster), but may not produce expected results when multiple distinct polygons have the same bounding box. Starting in PostGIS 2.4, GROUP BY operates on a rigid definition of geometric equality, taking into account the start point and orientation of geometries".

Относительно вашего предложения. Оно хорошее, примерно таким образом я и решил задачу (опять же вовремя не увидел ваше решение из-за несработавшего уведомления).
Dmitry Stasev писал(а):
11 июл 2018, 20:17
а можно через обобщенное выражение ПРИМЕРНО ТАК:
Изначально я пытался связать по полю со временем, но по какой-то странной причине мне возвращалось больше строк, чем требовалось (в запрос with их было 220). Вероятно, это моя вина так как время могло дублироваться для нескольких автомобилей, поэтому Джойн был некорректный. Тогда я решил все же ввести идентификатор и через обобщенное выражение получил нужный список.

freeExec
Гуру
Сообщения: 1195
Зарегистрирован: 23 апр 2011, 10:32
Репутация: 205
Откуда: Ульяновск

Re: Получение стартовой/конечной точки трека

Сообщение freeExec » 12 июл 2018, 15:18

regrunger писал(а):
12 июл 2018, 14:50
как я смогу
получить поле geom, если в результате запроса из которого я делаю выборку такого поля нет.
Да моя невнимательность, первая строка должна была иметь вид

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

SELECT geom, gps.* FROM gps_table,

Dmitry Stasev
Участник
Сообщения: 67
Зарегистрирован: 13 мар 2018, 08:59
Репутация: 22
Откуда: MO

Re: Получение стартовой/конечной точки трека

Сообщение Dmitry Stasev » 13 июл 2018, 10:41

regrunger писал(а):
12 июл 2018, 15:01
Starting in PostGIS 2.4, GROUP BY operates on a rigid definition of geometric equality, taking into account the start point and orientation of geometries".
Не знал, спасибо. Интересно, зачем (в реальных задачах) может такая группировка понадобится... Поиск дублей геометрий может.

regrunger
Новоприбывший
Сообщения: 11
Зарегистрирован: 17 сен 2016, 11:57
Репутация: 0

Re: Получение стартовой/конечной точки трека

Сообщение regrunger » 13 июл 2018, 10:47

Dmitry Stasev, Я интуитивно использовал группировку именно для таких целей)

Ответить

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

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

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