Getting the distance traveled across road segments

Question

so I have a query that takes an encoded polyline, segmentizes that polyline into smaller pieces, and matches each point on that segmentized line to a "road segment" from a separate table in my database. This query works well and efficiently, matching each point to its closest road segment. What I want to be able to do is then find the total distance traveled within each returned matched segment, but I'm struggling with that part.

Here is the query:

WITH
  points AS (
  SELECT (ST_DumpPoints(
         ST_Segmentize(ml, .000032))
       ).*
  FROM ST_LineFromEncodedPolyline('xxx') AS ml
)
SELECT *
FROM (
  SELECT
     seg.xdsegid,
     pts.path,
     ST_Distance(pts.geom::geography, seg.geom::geography) AS distance
FROM points AS pts
JOIN LATERAL (
  SELECT st.xdsegid,
         st.geom
  FROM alabama AS st
  ORDER BY pts.geom <-> st.geom
  LIMIT 1
) AS seg
  ON true
  ORDER BY seg.xdsegid, path
) q
ORDER BY path;

And it currently returns rows that look like this:

I know I probably need to turn each segment into a line and get the distance that way, but I can't for the life of me figure out how to best go about grouping and measuring each one of the returned segments. Also, sometimes the segments will return in such a way that a small portion of the points will momentarily be matched to another segment before returning to the original. Not sure if this is problematic.

Answer 1

Instead of approximating (see comments), let's try to be as precise as possible; the solution to this is even simpler than what you had in mind.

I'm adding just a bit of logic to your query to be able to determine if a segment is matched by the first or last point in the path (i.e. start & end point of your polyline), and

• if so, get the fraction of line length of that segment where the closest possible projection of the point lies at, times the total segment length
• if not, simply get the segments total length

Also, since there will be a bunch of duplicate segments returned by the KNN query, the query needs to return only the minimum value of all calculated lengths and DISTINCT the returned rows (this is mainly necessary to get the fractional length of the start/end point segments instead of the full length returned by other points matching these same segments). For best precision the length is calculated using the geography type.

WITH
  points AS (
    -- there are a couple of ways to handle set-returning functions; this one is just another with equal results
    SELECT (dp).path[1],
           (dp).geom AS geom,
           -- get row count to easily identify the last point
           count(*) OVER() AS mx
    FROM ST_DumpPoints(
           ST_Segmentize(
             ST_LineFromEncodedPolyline('xxx'),     
             -- (why not geography type for meter as unit?)                
             .000032
           ) 
         ) AS dp
  ),

SELECT DISTINCT
       seg.xdsegid,
       -- always return minimum length of all duplicates
       MIN(
         CASE
           -- if start point, get inverse fraction of line length
           WHEN pts.path = 1
             THEN ST_Length(seg.geom::geography) * (1 - ST_LineLocatePoint(seg.geom, pts.geom))
           -- if end point, get fraction of line length
           WHEN pts.path = pts.mx
             THEN ST_Length(seg.geom::geography) * ST_LineLocatePoint(seg.geom, pts.geom)
           -- else, get full line length
           ELSE ST_Length(seg.geom::geography)
         END
       ) OVER(PARTITION BY seg.xdsegid) AS len,
       seg.geom
FROM points AS pts
JOIN LATERAL (
    SELECT xdsegid,
           geom
    FROM alabama AS st
    ORDER BY st.geom <-> pts.geom
    LIMIT 1
) AS seg
ON true
ORDER BY ln.id;

This sould return all found segments with their traversed length in meter (use ROUND() or a cast to INTEGER if you are not interested in sub-atomic fractional lengths...); for all inner segments this will be the total segment length, for the two outer segments this will be the fractional length based on the closest projected point to the start/end point of your polyline.

Note: ST_LineLocatePoint, similar to the other functions using or returning fractions of line length, calculates the fraction from the start point of the given line. For the above query, and your usecase in general, to make sense, your road network should be directed correctly, i.e consistent with the direction of the passed in polyline (or vice versa), in order to find the correct (and not inversed) partial distance along the outer segments.

---

Another useful function btw. is ST_ClosestPoint; in conjunction with your KNN search, you could e.g. simply project the vertices of your polyline onto the road segments if you need.

See e.g. my answer here (albeit in an UPDATE you can see the similar concept).