How to recalculate the the length of a geography linestring from nearby geography point in POSTGIS?

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?

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 Jan 12 '12 at 20:16

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 Jan 12 '12 at 20:26