Aggregating data by group and time gaps

Question

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?

Answer 1

I know this is an old question, but maybe somebody comes here for an answer. I just had to do the thing RedM asked. I used CTE with some window functions, following the Gaps and Islands article:

with points as (
  select car_id, tts, accuracy, geom,
  -- First we identify time gaps and set a flag on these.
  -- Default should be long ago to register.
  case when extract(epoch from tts - lag(tts, 1, '1990-01-01'::timestamp)
    -- 1200 is the number of seconds for a gap.
    over (partition by car_id order by tts)) > 1200 then 1
    else 0 end as has_gap
  from all_tracks_may_30
),
clustered as (
  select car_id, tts, accuracy, geom,
  -- Now we convert boundary flags to cluster ids to group on.
  sum(has_gap) over (partition by car_id order by tts) as cluster_id
  from points
)
-- And aggregating into a line becomes easy.
select car_id, ST_MakeLine(array_agg(geom order by tts)) as route,
  -- If needed, avg speed is ST_Length of the above
  -- divided by extract (epoch from max(tts) - min(tts))
  min(tts) as start_ts, max(tts) as end_ts
from clustered group by car_id, cluster_id
having count(*) >= 3;

Answer 2

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.