3

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?

Improve this question

2 Answers 2

Reset to default
1

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)
Improve this answer
1
  • I tested your solution with real data and results were off. Haven't yet analyzed why.
    – Tamm
    Jan 12, 2012 at 20:26
0

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
Improve this answer
1
  • 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, 2012 at 20:16

Your Answer

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

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