I have multiple geometry points that identify a car path in a time interval. Some cars can make a path that overlap another car path.

I want to calculate the car speed to have different color linestring on a map and I want to calculate the average speed for the overlapping path.

Does anyone have any idea how to select the overlapping paths and calculate the average speed?

In my Postgres database table I have these columns:
car_id , timestamp(of rilevations), latitude, longitude, geometry.

This is an example of the situation:

Green stands for 40mph.

Red stands for 80mph.

How can I calculate an average speed for the overlapping path?

The goal is to create an json file that represents the traffic state.

  • Sure wouldn't want to be a pedestrian on Pine or California streets. Was the red car totaled in a bus collision at Sacramento?
    – Vince
    Nov 23, 2016 at 22:37

1 Answer 1


You would first form linestrings from the gps points.

  WITH paths AS ( SELECT car_id, 
ST_MakeLine(geometry ORDER BY timestamp) AS path, 
  tstzrange(MIN(timestamp) , MAX(end_time)) As trip_period
  FROM gps
  GROUP BY car_id, (you might also want to group by some time range or trip_id) )
SELECT p.car_id, p.path, (ST_Length(p.path::geography)* 0.000621371) / ( EXTRACT(epoch FROM (upper(trip_period) - lower(trip_period)) ) / 3600 )  As speed_mph
INTO car_paths
FROM paths;

CREATE INDEX idx_car_paths_path USING gist(path);
CREATE INDEX idx_car_paths_trip_period USING gist(trip_period);

Now to get list of paths that cross other paths

SELECT p.car_id, p.path
FROM car_paths As p 
    FROM car_paths AS p2 
        WHERE p2.car_id <> p.car_id AND 
 ST_Intersects(p2.path,  p.path)     
  AND p2.trip_period && p.trip_period );
  • I'm not sure, but considering this green line you have to use some routing machine to drive this kind of line using this specific 3 points... unless there is enough points in set....
    – Jendrusk
    Nov 23, 2016 at 22:05

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.