If I have one linestring and coordinates of two points on that linestring, what is the easiest way to calculate not shortest distance from those two points, but distance over the given road (given linestring) in PostGIS?
So in the given example given linestrin is whole line and I want to calculate length of red line between points A and B. Any help with this?
This is one way of doing it
Find out how big fraction of the line is between the 2 points with ST_LineLocatePoint and multiply that fraction with the whole length.
SELECT
(ST_LineLocatePoint(l,p2)-ST_LineLocatePoint(l,p1) )*st_length(l) dist_along_line,
st_distance(p1, p2) direct_distance
FROM
(
SELECT 'linestring(1 1, 1 2, 2 2, 5 0, 10 5)'::geometry l,
'point(1 2)'::geometry p1, 'point(5 3)'::geometry p2
) a
