[postgis-users] SQL Help selecting intersections

[Rob Tester]

This is what I do using tiger roads from the census. It is a simple self
join on the completechain table. The actual function is more detailed to
resolve names of the street but this should get you going in the right
direction to do one select on the table to get the intersection point.

 

 

SELECT DISTINCT
intersection(cc1.wkb_geometry,cc2.wkb_geometry),cc1.fedirp,cc1.fename,cc1.fe
type,cc1.fedirs,

 
cc2.fedirp,cc2.fename,cc2.fetype,cc2.fedirs,cc1.zip 

 
FROM completechain cc1,completechain cc2 WHERE cc1.zip= 12345 AND
cc2.zip=12345

 
AND upper(cc1.fename)=upper('street1') AND
upper(cc2.fename)=upper('street1');

 

Rob.

 

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Rob
McCulley
Sent: Wednesday, August 08, 2007 10:50 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] SQL Help selecting intersections

 

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/48fd5e73/attachment.html>