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.