<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Arial","sans-serif";
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></b></p>
<p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></b></p>
<p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></b></p>
<p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>SELECT DISTINCT
intersection(cc1.wkb_geometry,cc2.wkb_geometry),cc1.fedirp,cc1.fename,cc1.fetype,cc1.fedirs,<o:p></o:p></span></b></p>
<p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> cc2.fedirp,cc2.fename,cc2.fetype,cc2.fedirs,cc1.zip
<o:p></o:p></span></b></p>
<p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'> FROM
completechain cc1,completechain cc2 WHERE cc1.zip= 12345 AND cc2.zip=12345<o:p></o:p></span></b></p>
<p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>
AND
upper(cc1.fename)=upper('street1') AND upper(cc2.fename)=upper('street1’);<o:p></o:p></span></b></p>
<p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></b></p>
<p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Rob.<o:p></o:p></span></b></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b>On Behalf Of </b>Rob
McCulley<br>
<b>Sent:</b> Wednesday, August 08, 2007 10:50 AM<br>
<b>To:</b> postgis-users@postgis.refractions.net<br>
<b>Subject:</b> [postgis-users] SQL Help selecting intersections<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Hi
there, <o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>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:<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Step
1: Select all segments of Twp Rd 524.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Step
3: Select the intersection of the two geometries to get a point geometry.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>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?<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Thank
You,<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Rob
McCulley<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>County
of Vermilion River<o:p></o:p></span></p>
</div>
</body>
</html>