# How to calculate information from overlapping cars paths on map

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? Nov 23, 2016 at 22:37

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
WHERE EXISTS (SELECT p2.car_id
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.... Nov 23, 2016 at 22:05