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:

   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?


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.

  • 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 Apr 22 '20 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 Apr 22 '20 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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