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?

Example of linestring and points A and B on it

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.

(ST_LineLocatePoint(l,p2)-ST_LineLocatePoint(l,p1) )*st_length(l) dist_along_line, 
st_distance(p1, p2) direct_distance
  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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.