Postgresql – Aggregating data by group and time gaps

aggregatedatetimegroup bypostgispostgresql

I've got a postgresql+postgis database filled with GPS data: 

CREATE TABLE "my_table" (
    "id" serial NOT NULL PRIMARY KEY, 
    "gps_id" text NOT NULL, 
    "lat" numeric NOT NULL, 
    "lon" numeric NOT NULL, 
    "speed" numeric NOT NULL,
    "geom" geometry,
    "timestamp" timestamp with time zone NOT NULL,
);

My data comes in regularly but there are some gaps. I'd like to find the average speed and build up a linestring from the data grouped by "gps_id" AND by gaps greater than X hours:

SELECT 
   gps_id,
   avg(speed) as avg_speed,
   ST_MakeLine(my_table.geom ORDER BY timestamp) as line
FROM my_table
GROUP BY gps_id

It's pretty easy to do find the basic aggregate but grouping by time-gap has me stumped. I've tried to group by time buckets (using date_trunc) and then counting the number of gps messages within the time bucket and tossing if < X but that's not really the behaviour I want. I'd like to be able to get something like:

Data Output:

gps_id,       avg_speed,     line,        gap
ABC,          12.01          01002...     0
ABC,          20,20          01002...     1
DEF,           0,2           01002...     0

A bonus would be to find the start and end times of each group so that I could eyeball where/when the gaps are happening.

Any ideas?

Best Answer

As far as I understood the task you want to calculate average speed for every 4 hours as example. Means 0:00-3:59 o'clock, then 4:00-7:59 etc. So, probably it'll help you if you will use date part and floor functions as

floor(date_part('hour', timestamp) / 4)

then you'll get a rank of time gap.

Related Question