Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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