Hi Rob... here is what you can do:<br><br><div style="margin-left: 40px;"><span style="font-style: italic;">SELECT AsText(line_substring(road2.the_geom,<br>LEAST(line_locate_point(road2.the_geom,Centroid(Intersection(road1.the_geom
,road2.the_geom))),line_locate_point(LineMerge(road2.the_geom),Centroid(Intersection(road3.the_geom,road2.the_geom)))),<br>GREATEST(line_locate_point(road2.the_geom,Centroid(Intersection(road1.the_geom,road2.the_geom))),line_locate_point(LineMerge(
road2.the_geom),Centroid(Intersection(road3.the_geom,road2.the_geom))))<br>)) AS substring<br>FROM<br>(SELECT * FROM roads WHERE name LIKE 'Rge 20') AS road1<br>JOIN<br>(SELECT * FROM roads WHERE name LIKE '</span>
<span style="font-style: italic;">Twp 480</span><span style="font-style: italic;">') AS road2<br>ON<br>road1.the_geom && road2.the_geom AND Intersects(road1.the_geom,road2.the_geom)<br>JOIN (SELECT * FROM roads WHERE name LIKE 'Rge 30') AS road3
<br>ON<br>road2.the_geom && road3.the_geom AND Intersects(road2.the_geom,road3.the_geom)<span style="font-style: italic;"><br></span></span></div><br>Now, how does it works? First I add a second JOIN in the FORM clause to include the third road. The road2 subquery will have the result for the Twp 480 road so it must intersect with the road1 and with the road3. All this is achieved through JOINs with the Intersection condition in the ON clause. Now you want to get the substring, but first you need to know the start and end values so you can then call to the line_substring function. So what you need to do is call the line_locate_point with the road2 geometry and the Intersection point with each one of the others geometries (road1 and road3). Then with this values you can call the line_substring function, but not yet... to call the line_substring function, the start value must be smaller than the end value and this is done with the functions LEAST and GREATEST. This two function will return the smaller value for the 2 argument of the line_substring function and the greater for the 3rd argument. So I think this is it. I don't know how fast is this query in your server... obviously as I told you in the previous mail there may be several things to do to get this query go faster like changing the LIKE comparision for = if you dont need to use the LIKE capabilities and also in this case, building an index in your table for the name column can make a difference. I hope it work for you... in my case I had to use the LineMerge() function in the geom columns cause my geometries are 'MULTILINESTRING' even when actually all of them have just one LINESTRING... If you get an error like:
<br><br><div style="margin-left: 40px;"><span style="font-style: italic;">line_interpolate_point: 1st arg isnt a line</span><br></div><span style="font-style: italic;"><span style="font-style: italic;"></span></span><div style="margin-left: 40px;">
<span style="font-style: italic;"><span style="font-style: italic;"></span></span><span style="font-style: italic;"></span></div><br>or similar, you can try with this version of the query:<br><br><div style="margin-left: 40px;">
<span style="font-style: italic;">SELECT AsText(line_substring(LineMerge(road2.the_geom),</span><br style="font-style: italic;"><span style="font-style: italic;">LEAST(line_locate_point(LineMerge(road2.the_geom),Centroid(Intersection(
road1.the_geom,road2.the_geom))),line_locate_point(LineMerge(road2.the_geom),Centroid(Intersection(road3.the_geom,road2.the_geom)))),</span><br style="font-style: italic;"><span style="font-style: italic;">GREATEST(line_locate_point(LineMerge(
road2.the_geom),Centroid(Intersection(road1.the_geom,road2.the_geom))),line_locate_point(LineMerge(road2.the_geom),Centroid(Intersection(road3.the_geom,road2.the_geom))))</span><br style="font-style: italic;"><span style="font-style: italic;">
)) AS substring</span><br style="font-style: italic;"><span style="font-style: italic;">FROM</span><br style="font-style: italic;"><span style="font-style: italic;">(SELECT * FROM roads WHERE name LIKE 'Rge 20') 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 'Twp 480') 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;"><span style="font-style: italic;">JOIN (SELECT * FROM roads WHERE name LIKE 'Rge 30') AS road3</span><br style="font-style: italic;"><span style="font-style: italic;">ON</span>
<br style="font-style: italic;"><span style="font-style: italic;">road2.the_geom && road3.the_geom AND Intersects(road2.the_geom,road3.the_geom)</span><br></div><br>I hope it works. Again, if you have any question, ask me again.
<br><br>Rodrigo.<br><br><div><span class="gmail_quote">On 8/10/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="blue" lang="EN-US">
<div>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;">Hi Rodrigo, </span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;">That worked perfectly. Now I've
tried to take it a step further and I'm stuck. The ultimate reason I'm
trying to get the intersection is to select a segment of road using
line_substring given three road names. For example, a user specifies Twp 480
from Rge 20 to Rge 30. I want to extract that segment of road as a geometry.</span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;">Once you showed me how to select the
intersection, it seemed like it should be easy to add that to a line_locate_point
query and save another step, but I'm stuck.</span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;">This is the query I tried:</span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font></p>
<p style="margin-left: 36pt;"><i><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy; font-style: italic;">SELECT line_locate_point(road_geom,IntersectionPoint) AS
start FROM (SELECT road1.geom FROM (SELECT * FROM roads WHERE name = 'Twp 480')
AS road1 JOIN (SELECT * FROM roads WHERE name = 'Rge 20') AS road2 ON
road1.geom && road2.geom AND Intersects(road1.geom,road2.geom)) AS
road_geom, (SELECT Centroid(Intersection(road1.geom,road2.geom)) FROM (SELECT *
FROM roads WHERE name = 'Twp 480') AS road1 JOIN (SELECT * FROM roads WHERE
name = 'Rge 20') AS road2 ON road1.geom && road2.geom AND Intersects(road1.geom,road2.geom))
AS IntersectionPoint;</span></font></i></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;">The error message I get is:</span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font></p>
<p style="margin-left: 36pt;"><i><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy; font-style: italic;">ERROR: function line_locate_point(record, record) does not
exist</span></font></i></p>
<p style="margin-left: 36pt;"><i><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy; font-style: italic;">SQL state: 42883</span></font></i></p>
<p style="margin-left: 36pt;"><i><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy; font-style: italic;">Hint: No function matches the given name and argument types.
You may need to add explicit type casts.</span></font></i></p>
<p style="margin-left: 36pt;"><i><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy; font-style: italic;">Character: 8</span></font></i></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;">I'm assuming the error is related to
how I am selected the road geometry for the line_locate_point function. If I
break it into three stages, and select the road geometry, and then select the
point using the sql you showed me, and then select the line_locate_point
function, it works fine.</span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;">If I can get this working, I'm
hoping to go another step further and wind up with a sql statement something
like:</span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font></p>
<p style="margin-left: 36pt;"><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;">SELECT
line_substring(road_geom, start_measure, end_measure) FROM ( … sql to
select road geometry …, sql to select line_locate_point for start point as
above, sql to select line_locate_point for end point as above).</span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;">My other question is, is this the best way
to go about this?</span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;">Thanks in advance,</span></font></p>
<p><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;">Rob</span></font></p>
<div>
<div style="text-align: center;" align="center"><font face="Times New Roman" size="3"><span style="font-size: 12pt;">
<hr align="center" size="2" width="100%">
</span></font></div>
<p><b><font face="Tahoma" size="2"><span style="font-size: 10pt; font-family: Tahoma; font-weight: bold;">From:</span></font></b><font face="Tahoma" size="2"><span style="font-size: 10pt; font-family: Tahoma;">
<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">postgis-users-bounces@postgis.refractions.net</a>
[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">postgis-users-bounces@postgis.refractions.net</a>] <b><span style="font-weight: bold;">
On Behalf Of </span></b>Rodrigo Martín LÓPEZ GREGORIO<br>
<b><span style="font-weight: bold;">Sent:</span></b> Wednesday, August 08, 2007
1:00 PM<br>
<b><span style="font-weight: bold;">To:</span></b> PostGIS Users Discussion<br>
<b><span style="font-weight: bold;">Subject:</span></b> Re: [postgis-users] SQL
Help selecting intersections</span></font></p>
</div><div><span class="e" id="q_11450bf8f250a0b5_1">
<p><font face="Times New Roman" size="3"><span style="font-size: 12pt;"> </span></font></p>
<p style="margin-bottom: 12pt;"><font face="Times New Roman" size="3"><span style="font-size: 12pt;">Hi Rob.<br>
<br>
You can make something like this:</span></font></p>
<div style="margin-left: 30pt;">
<p><i><font face="Times New Roman" size="3"><span style="font-size: 12pt; font-style: italic;">SELECT *,
Centroid(Intersection(road1.the_geom,road2.the_geom)) AS intersectionPoint FROM
<br>
(SELECT * FROM roads WHERE name LIKE 'Township Road 524') AS road1<br>
JOIN <br>
(SELECT * FROM roads WHERE name LIKE 'Range Road 20') AS road2<br>
ON</span></font></i> <i><span style="font-style: italic;"><br>
road1.the_geom && road2.the_geom AND
Intersects(road1.the_geom,road2.the_geom)</span></i></p>
</div>
<p style="margin-bottom: 12pt;"><font face="Times New Roman" size="3"><span style="font-size: 12pt;"><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: </span></font></p>
<div style="margin-left: 30pt;">
<p><i><font face="Times New Roman" size="3"><span style="font-size: 12pt; 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 <br>
(SELECT * FROM roads WHERE name LIKE 'Township Road 524') AS road1<br>
JOIN <br>
(SELECT * FROM roads WHERE name LIKE 'Range Road 20') AS road2<br>
ON</span></font></i> <i><span style="font-style: italic;"><br>
road1.the_geom && road2.the_geom AND
Intersects(road1.the_geom,road2.the_geom)</span></i></p>
</div>
<p style="margin-bottom: 12pt;"><font face="Times New Roman" size="3"><span style="font-size: 12pt;"><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.</span></font></p>
<div>
<p><span><font face="Times New Roman" size="3"><span style="font-size: 12pt;">On 8/8/07, <b><span style="font-weight: bold;">Rob
McCulley</span></b> <<a href="mailto:RMcCulley@county24.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">RMcCulley@county24.com
</a>> wrote:</span></font></span></p>
<div link="blue" vlink="purple">
<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>
<p style="margin-bottom: 12pt;"><font face="Times New Roman" size="3"><span style="font-size: 12pt;"><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">postgis-users@postgis.refractions.net
</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://postgis.refractions.net/mailman/listinfo/postgis-users</a></span></font>
</p>
</div>
<p><font face="Times New Roman" size="3"><span style="font-size: 12pt;"> </span></font></p>
</span></div></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>