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)
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