postgis problem with shortest distance calculation

Question

while working with POSTGIS pgrouting, for calculateing the distance between two roads(lines) i got the shortest_path function.

But the logic is based on Start_point(Start_id) and end_point(end_id) but in my data the linestring contains so many internal points like ('linestring(1 1,2 2,3 3,4 4,5 5)' just for example..)

it is taking start point (1 1) endpoint(5 5)

if other line starting with (5 5) it is showing as route...like ('linestring(5 5,6 6)')

But line which crossing the point inside the linestring like(2 2,3 3,4 4) which is not telling as connected.. example

table roads: id name way 1 A linestring(1 1,2 2,3 3,4 4,5 5) 2 B linestring(5 5,6 6) 3 c linestring(2 1,2 2,2 3)

if i am applying shortest_path function from point(1 1) to (6 6) its showing the way but for (1 1) to (2 3) it is not showing anything...but there is a route for this (1 1,2 2,2 3)

can anyone please help me out for finding the solution..

Regards Deepak M

Answer 1

Maybe this will help start you out but here is how I am thinking through this.

I can't think of any case, re spherical trig where two straight lines would be closer in the middle than at the edges, unless they cross so I think you can narrow your search down to the vertices of each road plus the nearest point to that vertex on the other road. I am assuming that your roads are modelled as line segments along great circles.

So I don't think that this is a complete solution but, what I would be looking at doing is:

1. Do these lines intersect? If you assume that all intersections are listed as such in your db, you can assume no.
2. If so that's the closest point.
3. If not, then grab the vertices on both roads, draw any lines perpendicular to another line segment on the other, and calculate distance to intersection. Your shortest point will be the minimum of these lines.

That seems the best answer. You have to calculate points. I don;t know of any magic way to do this in PostGIS but it is something where spherical trig may come to the rescue. If the distances are sufficiently small, plane trig may be sufficient as an approximation, of course.