[postgis-users] SQL Help selecting intersections

[Rob McCulley]

Hi there, 

 

I'm looking for a little help with some SQL.  I've got a table of roads
with names.  I need to be able to get the intersection of two roads
given the two names of the roads.  The problem I have is, most of my
roads have multiple segments with the same name.

 

Currently I'm doing this in essentially three steps.  For example, lets
say I'm looking for the intersection of Township Road 524 and Range Road
20.  I just took a look at my database, and there are two segments for
Twp Rd 524 and there are 5 segments for Rge Rd 20.  My current solution
is:

 

Step 1:  Select all segments of Twp Rd 524.

Step 2: Iterate through each segment of Twp Rd 524 and select any
segment of Rge Rd 20 that intersects it.  After I'm finished the
iteration, I have two geometries that intersect, one from Twp Rd 524 and
one from Rge Rd 20.

Step 3:  Select the intersection of the two geometries to get a point
geometry.

 

This seems like a bit of a brute force method of doing things.  In the
example case, I end up making four separate queries to the database.  In
some cases it could be quite a few more.  I'm sure there is a more
elegant solution, but I can't quite get my head around how to do it.  Is
it possible to get the intersection of the roads given the names in a
single SQL statement?

 

Thank You,

Rob McCulley

County of Vermilion River

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070808/bd1dcf86/attachment.html>