Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm playing around with creating some basic Strava-like functionality for a project and I've ran into a problem.

I have a database of existing trails, the user uploads their gps track of a ride they did and the site tells them the trails they rode and some stats.

I have identifying the trails working fairly well. I have segmenting out the riders gps track data for the portion matching each trail and running some stats like time and speed working. The problem is with the way i'm segmenting the gpstrack, it can sometimes produce incorrect results if a gps track does stuff like multiple laps past trails.

First I will explain my process.

  1. I create a bounding box around the users uploaded gpstrack/linestring.
  2. I find all the trails that are within that bounding box, these are the potential matches.
  3. I get the start middle and end points for the trails in the bounding box.
  4. Using these points I run a query for each trail against the users gpstrack, determining if its within 33m of the start, middle & end points. If it is I assume the user rode this trail.
  5. At the same time I get the start and end point of the corresponding segment of the users gpstrack matching the trail. This is where the problem can happen, sometimes the closest point is not the actual start to the trail, if the user rode by the trailhead multiple times.

To Illustrate the problem I created a screenshot: http://i.imgur.com/fZaImLi.png The thin cyan line is the trail in the database its trying to match too. The yellow line is the user-uploaded gpstrack, which is also under all the blue portions. The code correctly identifies that the cyan trail was ridden. But its getting the wrong point for the start point for the users gpstrack segment of that trail, because the user first rides past the trailhead then does a loop down another trail before coming back down the trail. The red dot is the startpoint my query is returning, where it should be the purple dot. But the red dot is indeed the closest point.

I'm not sure how I can go about solving this.

Here are my main 3 queries.

  1. Get the bounding box from the users gpstrack is stored in the database

    SELECT ST_AsText(ST_Expand(track, 0.01)) AS box, ST_AsText(track) AS track, altitude, time FROM rides WHERE id='32'

  2. Find the trails in the database within that bounding box

    SELECT id, title,
    ST_AsText(ST_PointN(track, 10)) AS start,
    ST_AsText(ST_PointN(track, ST_NPoints(track)-10)) AS end,
    ST_AsText(ST_PointN(track, ST_NPoints(track)/2)) AS middle
    FROM trails
    WHERE ST_Contains('POLYGON((49.340652 -123.050959,49.340652 -123.019354,49.373064 -123.019354,49.373064 -123.050959,49.340652 -123.050959))',track)

  3. Check which of the trails returned matchup with the users gpstrack and return the start and end point of the segment of that gpstrack corresponding to the trail. It finds the start and end point by comparing the trails start point to the closest point on the users gpstrack.

    SELECT
    ST_AsText(
    ST_Line_Substring(
    rides.track,
    ST_Line_Locate_Point(
    rides.track,
    r1.point
    ),
    ST_Line_Locate_Point(
    rides.track,
    r2.point
    )
    )
    ) AS track
    FROM
    rides rides,
    (
    SELECT (r.gdump).geom AS point
    FROM
    (SELECT ST_DumpPoints(track) AS gdump,
    id
    FROM rides) AS r
    WHERE id = 32
    ORDER BY ST_Distance(ST_GeomFromText('POINT(49.358069 -123.031903)', 4326),
    (r.gdump).geom) LIMIT 1
    ) AS r1,
    (
    SELECT (r.gdump).geom AS point
    FROM
    (SELECT ST_DumpPoints(track) AS gdump,
    id
    FROM rides) AS r
    WHERE id = 32
    ORDER BY ST_Distance(ST_GeomFromText('POINT(49.357256 -123.02928)', 4326),
    (r.gdump).geom) LIMIT 1
    ) AS r2
    WHERE rides.id='32' AND
    ST_DWithin(rides.track, ST_GeomFromText('POINT(49.358069 -123.031903)',4326), 0.0003) AND
    ST_DWithin(rides.track, ST_GeomFromText('POINT(49.357256 -123.02928)',4326), 0.0003) AND
    ST_DWithin(rides.track, ST_GeomFromText('POINT(49.358087 -123.030832)',4326), 0.0005) AND
    ST_Line_Locate_Point(rides.track,r1.point) < ST_Line_Locate_Point(rides.track,r2.point)

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.