Hi Rob.<br><br>You can make something like this:<br><br style="font-style: italic;"><div style="margin-left: 40px;"><span style="font-style: italic;">SELECT *, Centroid(Intersection(road1.the_geom,road2.the_geom)) AS intersectionPoint FROM
</span><br style="font-style: italic;"><span style="font-style: italic;">(SELECT * FROM roads WHERE name LIKE 'Township Road 524') AS road1</span><br style="font-style: italic;"><span style="font-style: italic;">JOIN
</span><br style="font-style: italic;"><span style="font-style: italic;">(SELECT * FROM roads WHERE name LIKE 'Range Road 20') AS road2</span><br style="font-style: italic;"><span style="font-style: italic;">ON</span>
<br style="font-style: italic;"><span style="font-style: italic;">road1.the_geom && road2.the_geom AND Intersects(road1.the_geom,road2.the_geom)</span><br></div><br>Obviously you can select just the columns you want o get extra info like X and Y coordinates of the intersectionPoint doing something like:
<br><br><div style="margin-left: 40px;"><span style="font-style: italic;">SELECT *, X(Centroid(Intersection(road1.the_geom,road2.the_geom))) AS xcoord, Y(Centroid(Intersection(road1.the_geom,road2.the_geom))) AS Ycoord FROM
</span><br style="font-style: italic;"><span style="font-style: italic;">(SELECT * FROM roads WHERE name LIKE 'Township Road 524') AS road1</span><br style="font-style: italic;"><span style="font-style: italic;">JOIN
</span><br style="font-style: italic;"><span style="font-style: italic;">(SELECT * FROM roads WHERE name LIKE 'Range Road 20') AS road2</span><br style="font-style: italic;"><span style="font-style: italic;">ON</span>
<br style="font-style: italic;"><span style="font-style: italic;">road1.the_geom && road2.the_geom AND Intersects(road1.the_geom,road2.the_geom)</span><br style="font-style: italic;"></div><br>You can also modify the WHERE condition in the search of each individual road like using ILIKE instead of LIKE to make the search case insensitive, use % in the LIKE condition to search with just part of the road name, etc.
<br><br>(This is just a simple solution, you can get better results doing more complex things like having a separated table with road names and road id, making the search in this table and getting the ids of the matched roads and then, having a index on your road table by road_id, accessing just the roads that have matched the previous search, etc.)
<br><br>If you have any question, ask me again.<br><br>Rodrigo.<br><br><div><span class="gmail_quote">On 8/8/07, <b class="gmail_sendername">Rob McCulley</b> <<a href="mailto:RMcCulley@county24.com">RMcCulley@county24.com
</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div link="blue" vlink="purple" lang="EN-US">
<div>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">Hi there, </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">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.</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">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:</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">Step 1: Select all segments of Twp Rd 524.</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">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.</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">Step 3: Select the intersection of the two geometries
to get a point geometry.</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">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?</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">Thank You,</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">Rob McCulley</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">County of Vermilion
River</span></font></p>
</div>
</div>
<br>_______________________________________________<br>postgis-users mailing list<br><a onclick="return top.js.OpenExtLink(window,event,this)" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a><br><a onclick="return top.js.OpenExtLink(window,event,this)" href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br>
</blockquote></div><br>