I track my running workouts using GPS and I have the coordinates for each of them

I create a table to save each workout

                       userId integer,
                       activityId integer,
                       geom geometry(LINESTRING,4326) 

I inserted two similar workouts (workout 1 with red, workout 2 with green)

enter image description here

I want to find a way to query workouts that are similar to each other. As you can see, the two workouts (red, green) haven't exact points but they are on the same road and has the same direction. I want to find the matching workouts and do further analysis on these matched running workouts.

I did some tests with the ST_HausdorffDistance function. The task is to scan the table and find similar workouts using the userId.

Is there any better way than the following query?

It gets 40 seconds to finish. Now the table has only 70 rows in total. I have created a spatial index for the geometry column.

SELECT a.activityId, b.activityId, ST_HausdorffDistance(a.geom, b.geom) as dist
FROM public.runnings a, public.runnings b
WHERE a.id < b.id
AND a.userId = '123'
AND b.userId = '123'
AND ST_DWithin(a.geom, b.geom, 1.0)
AND ST_DWithin(ST_StartPoint(a.geom)::geography, ST_StartPoint(b.geom)::geography, 200.0)
AND ST_DWithin(ST_EndPoint(a.geom)::geography, ST_EndPoint(b.geom)::geography, 200.0)
AND ST_HausdorffDistance(a.geom, b.geom) < 0.001

2 Answers 2


One possibility is to use a distance function between two LineString geometries such as ST_HausdorffDistance (or the more accurate ST_FrechetDistance if you have the new GEOS 3.7 support in your PostGIS).

You can select line strings that are close enough to a target track with respect to one of these distances.


This is a workaround to spot similar workout trajectories. You can add a WHERE clause in the different solutions here to set a particular id you want to focus on.

Idea 1: same start/end

  • Create a buffer around a workout line (w1) with a value of 1.5 time the average width of your streets (width) (you can tweak this value afterward to get better results)
  • Use ST_Contains to compare each workout with the create buffer.

Something like this :

WITH buffer AS (
    SELECT r.id, ST_Buffer(r.geom, width) as geom --determinate width
    FROM runnnings r
SELECT b.id, r.id as id_similar, b.geom
FROM buffer b
INNER JOIN runnings r
    ON ST_Contains(b.geom, r.geom)

The problem is: both workouts need to have the same start and end.

Idea 2: different start/end handling

  • Create the buffer as explain in idea 1

  • Use ST_Intersection to get the intersection between a workout and the buffer as a line

  • Use ST_Length to calculate a length percentage threshold to determinate whether or not two workout are similar.

     WITH buffer AS (
         SELECT r.id, ST_Buffer(r.geom, width) as geom --determinate width
         FROM runnnings r
       intersecting AS (
         SELECT r.id, b.id as id_similar,
                r.geom, ST_Intersection(r.geom, b.geom) as geominter, 
                ST_Lentgh(r.geom) as length_initial
         FROM runnings r
         INNER JOIN buffer b
             ON ST_Intersects(r.geom, b.geom)
     SELECT id, id_similar, geom
     FROM intersecting
     WHERE (ST_Length(geominter)*100)/length_initial > threshold_value --to define

Finally, you can set a threshold value as a percentage. It's the percentage of the initial workout length that must be inside the buffer of the other workout.


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.