1

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

I create a table to save each workout

CREATE TABLE runnings (id SERIAL PRIMARY KEY,
                       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.

UPDATE

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
1

I put that as an answer as I don't have enough space in the comments. This is a work-around 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.

You'll probably get more answers but maybe this can be a good start to seek what you are looking for.

1

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.

  • Thank you for the suggestion. I want to scan all rows of the table in order to find similar workouts for a specific user. I tried an SQL statement that is shown above at my first post. Do you have any recommendation for better optimisation or please suggest me any better sql query statement. – Bill Dani Nov 22 '17 at 19:25

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.