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
- 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.
points AS (
-- there are a couple of ways to handle set-returning functions; this one is just another with equal results
(dp).geom AS geom,
-- get row count to easily identify the last point
count(*) OVER() AS mx
-- (why not geography type for meter as unit?)
) AS dp
-- always return minimum length of all duplicates
-- 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
) OVER(PARTITION BY seg.xdsegid) AS len,
FROM points AS pts
JOIN LATERAL (
FROM alabama AS st
ORDER BY st.geom <-> pts.geom
) AS seg
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.
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).