CODEWITHSUNDEEP
sqlpostgresqlgpsgaps-and-islands
Vitalii Honta
Vitalii Honta

Reputation: 317

Continous trips based on descrete GPS messages

Assume we have a table which contains data periodically sent by GPS tracker.

CREATE TABLE unit_messages
(datetime timestamp, speed numeric, trip_distance numeric);

It contains timestamp when message is sent, speed, etc. and trip_distance growing when car is driving and reset to 0 when it been stopped.

('2017-10-26T13:41:41', 0.0, 0.0),
('2017-10-26T13:41:57', 23.0, 0.1),
('2017-10-26T13:42:01', 11.0, 0.1),
('2017-10-26T13:42:18', 20.0, 0.2),
('2017-10-26T13:42:33', 56.0, 0.4),
('2017-10-26T13:42:41', 58.0, 0.5),
('2017-10-26T13:43:13', 0.0, 0.6),
...
('2017-10-26T14:03:38', 12.0, 13.5),
('2017-10-26T15:21:52', 0.0, 0.0)

The goal is to make SQL query producing a TRIP whit such signature:

from_date, to_date, trip_distance

where from_date is datetime when trip_distance=0
(2017-10-26T13:41:41 in first row)
and to_date is datetime of last row before trip_distance became 0
(like in row ('2017-10-26T14:03:38', 12.0, 13.5) before the last)

The only solution i have is to sequentially iterate over result of SELECT in a loop. Does somebody have any ide how to do this by SQL?

Upvotes: 1

Views: 38

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

WITH cte as (
    SELECT *, 
           COUNT(CASE WHEN trip_distance = 0 THEN 1 END) 
           OVER (ORDER BY datetime) as grp
    FROM unit_messages 
)
SELECT MIN(datetime), MAX(datetime), MAX(trip_distance)
FROM cte
GROUP BY grp

Upvotes: 2

Related Questions