Problem with shortest distance calculation in PostGIS

While working with PostGIS `pgrouting`, for calculating the distance between two roads (lines) I'm using the `shortest_path` function.

The logic is based on `start_point(Start_id)` and `end_point(end_id)` but in my data the line string contains many internal points like (`linestring(1 1,2 2,3 3,4 4,5 5)` for example.)

So it is taking the start point as `(1 1)` and the endpoint as `(5 5)`. If another line starts with `(5 5)` it shows as route like `linestring(5 5,6 6)`.

But a line which has a crossing point inside the linestring like `(2 2,3 3,4 4)` is not considered connected.

For example, using this roads table:

``````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 apply the `shortest_path` function from point `(1 1)` to `(6 6)`, it does determine a path. But for `(1 1)` to `(2 3)` it doesn't find anything. It does not find the `(1 1,2 2,2 3)` route.

What do I need to do so that the function considers each segments internal points rather than only the start and end points of the lines?

-

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.

-