I have a geography type linestring *route_geography geography(LineString,4326)*, its a route from point A to point B. Now I have a point C near that line. I want to find the nearest point on that line, lets call it D. And then calculate the distance between D and B.

Should I use *ST_Line_Locate_Point()* to find the point D?

And if I do find the point D should I then use *ST_Line_Substring()* to split up the AB linestring to make a new DB linestring?

These two functions are for geometry type not geography that my data is in, so what should I do?

2 Answers 2


Yes you need a combination of ST_Line_Interpolate_Point and ST_Line_Locate_Point, using the terms of your example, and asuming that you already have a linestring of your path from A to B,

ST_Distance_Sphere( ST_Line_Interpolate_Point(lineAtoB, ST_Line_Locate_Point(lineAtoB,pointC) ), pointB)
  • I tested your solution with real data and results were off. Haven't yet analyzed why.
    – Tamm
    Commented Jan 12, 2012 at 20:26

You just need to multiply the return value of ST_Line_Locate_Point by the route length to get the length from A to D, since

ST_Line_Locate_Point — Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point, as a fraction of total 2d line length.

For D to B use

(1 - ST_Line_Locate_Point) * lenghtA-B
  • My own solution to get length from D to B was: ST_Length( ST_Line_SubString( linestringAB, ST_Line_Locate_Point(linestringAB, pointC), 1 )::geography) - but your solution gives almost same results. Thanks
    – Tamm
    Commented Jan 12, 2012 at 20:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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