0

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?

2 Answers 2

2

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;
3
  • I forgot about this question! Thanks for the answer. I've built up something similar for my own use and wrote about it here
    – RedM
    Commented Jun 2, 2023 at 13:24
  • I attempting to do the same thing with vehicle GPS data. @RedM your link is now broken, is there an updated link?
    – Scott
    Commented Apr 24 at 15:39
  • Huh, that's weird. Here's an updated link; almost identical except for caps
    – RedM
    Commented Apr 24 at 21:53
0

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.

2
  • I don't want to calculate average speed every 4 hours. I want to group a table of GPS points by the GPS ID and separate by any gaps in the time stamp.
    – RedM
    Commented Apr 22, 2020 at 6:24
  • I want GPS loggers that have good reception and are feeding consistent data to the database to be grouped together. The end goal being grouping trips together rather than having large time or space jumps within a group, whether the duration is several days or 15 minutes.
    – RedM
    Commented Apr 22, 2020 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.