<html>
<head>
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Tahoma
}
--></style>
</head>
<body class='hmmessage'><div dir='ltr'>
<div>create table summit_final_drop_8 as</div><div>select Distinct ST_ShortestLine(a.intersection_geom, b.geom) </div><div>from summit_parcels_centroid as b, summit_roads as a </div><div>where ST_DWithin(a.intersection_geom,b.geom,150) ;</div><div><br></div><div>This got me nearly there and preserves the start/end of the line segment needed for</div><div>_pg_routing, though throws up additional or excess final connections, probably</div><div>a limit would help. At least as viewed in UDig, the segment lengths looked right . </div><div>I'll look into nearest neighbor as an alternative solution.</div><div><br></div><br><div><hr id="stopSpelling">Date: Tue, 22 Nov 2011 06:19:48 +0000<br>From: borntopedal@yahoo.co.uk<br>To: postgis-users@postgis.refractions.net<br>Subject: Re: [postgis-users] ST_ShortestLine(r.geom, m.geom) confusion - better limiting point to nearest line results<br><br><div style="color:#000;background-color:#fff;font-family:Courier New, courier, monaco, monospace, sans-serif;font-size:12pt"><div><span>Yes you will as it finds the shortest distance each time between points and lines, sorry - you need the inner query to give you the nearest neighbour and then generate the line between that and the point. Have a quick search for "nearest neighbour postgis" for how to do this. That should give you the results you are after.</span></div><div><span><br></span></div><div>I think I have something somewhere if you can't find it online.</div><div><span><br></span></div><div><span>Phil</span></div><div><br><blockquote style="margin-left:5px;padding-left:5px"> <div style="font-size:12pt;font-family:'Courier New', courier, monaco, monospace, sans-serif"> <div style="font-size:12pt;font-family:'times new roman', 'new york', times,\00000d\00000a serif"> <font size="2" face="Arial"> <hr size="1"> <b><span style="font-weight:bold">From</span></b>ris English <sglish@hotmail.com><br> <b><span style="font-weight:bold">To:</span></b> postgis_users <postgis-users@postgis.refractions.net> <br> <b><span style="font-weight:bold">Sent:</span></b> Tuesday, 22 November 2011, 1:09<br> <b><span style="font-weight:bold">Subject:</span></b> Re: [postgis-users] ST_ShortestLine(r.geom, m.geom) confusion - better limiting point to nearest line results<br> </font> <br><div id="ecxyiv65513775">
<style>
.ExternalClass #ecxyiv65513775 .ecxyiv65513775hmmessage P
{padding:0px;}
.ExternalClass #ecxyiv65513775 body.ecxyiv65513775hmmessage
{font-size:10pt;font-family:Tahoma;}
</style>
<div><div dir="ltr">
<style>
.ExternalClass #ecxyiv65513775 .ecxyiv65513775hmmessage P
{padding:0px;}
.ExternalClass #ecxyiv65513775 body.ecxyiv65513775hmmessage
{font-size:10pt;font-family:Tahoma;}
</style>
<div dir="ltr"><div style="font-size:10pt;font-family:Tahoma"><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">Thanks Phil for your response. The below does greatly reduce number of rows created through the</font></div><div style="font-size:10pt;font-family:Tahoma"><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">inner join.</font></div><div style="font-size:10pt;font-family:Tahoma"><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">The "shortestlines' unfortunately seem to radiate from the center of town. </font></div><div style="font-size:10pt;font-family:Tahoma"><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2"><br></font></div><div style="font-size:10pt;font-family:Tahoma"><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">create table summit_final_drop as</font></div><div style="font-size:10pt;font-family:Tahoma"><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">select ST_ShortestLine(nearestgeom.intersection_geom, b.geom) </font></div><div style="font-size:10pt;font-family:Tahoma"><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">from summit_parcels_centroid as b, (Select a.*, </font></div><div style="font-size:10pt;font-family:Tahoma"><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2"><br></font></div><div style="font-size:10pt;font-family:Tahoma"><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">ST_Distance(a.intersection_geom, b.geom) as dist from summit_roads as a, summit_parcels_centroid as b order by dist asc limit 1) as nearestgeom;</font></div><div style="font-size:10pt;font-family:Tahoma"><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">Query returned successfully with no result in 12281 ms.</font></div><div style="font-size:10pt;font-family:Tahoma"><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2"><br></font></div><div style="font-size:10pt;font-family:Tahoma"><div><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">Changing asc limit 1) to desc limit 1) and all segments </font><span class="ecxyiv65513775Apple-style-span" style="font-size:10pt;font-family:Tahoma">come from the edge of as if a fan.</span></div></div><div style="font-size:10pt;font-family:Tahoma"><span class="ecxyiv65513775Apple-style-span" style="font-size:10pt;font-family:Tahoma"><br></span></div><div><div><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">I return to your suggested order ST_Shortestline(point, line) - though I was concerned that this will flip my start/end points for the segment with an eye</font></div><div><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">toward the final part, pg_routing</font></div><div><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2"><br></font></div><div><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">create table summit_final_drop_4 as</font></div><div><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">select ST_ShortestLine(b.geom, nearestgeom.geom) </font></div><div><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">from summit_parcels_centroid as b, (Select a.*, </font></div><div><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2"><br></font></div><div><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">ST_Distance(a.intersection_geom, b.geom) as dist from summit_roads as a, summit_parcels_centroid as b order by dist asc limit 1) as nearestgeom;</font></div></div><div style="font-size:10pt;font-family:Tahoma"><span class="ecxyiv65513775Apple-style-span" style="font-size:10pt"><br></span></div><div style="font-size:10pt;font-family:Tahoma"><span class="ecxyiv65513775Apple-style-span" style="font-size:10pt">And result is same radiation from the center.</span></div><div style="font-size:10pt;font-family:Tahoma"><span class="ecxyiv65513775Apple-style-span" style="font-size:10pt"><br></span></div><div style="font-size:10pt;font-family:Tahoma"><span class="ecxyiv65513775Apple-style-span" style="font-size:10pt">Running St_ShortestLine twice:</span></div><div style="font-size:10pt;font-family:Tahoma"><span class="ecxyiv65513775Apple-style-span" style="font-size:10pt"><br></span></div><div><div><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">create table summit_final_drop_7 as</font></div><div><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">select ST_ShortestLine(nearestgeom.geom, b.geom) </font></div><div><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">from summit_parcels_centroid as b, (Select
a.*, </font></div><div><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2"><br></font></div><div><font class="ecxyiv65513775Apple-style-span" face="Tahoma" size="2">ST_Shortestline(a.intersection_geom, b.geom) as dist from summit_roads as a, summit_parcels_centroid as b order by dist asc limit 1 ) as nearestgeom;</font></div><div style="font-size:10pt;font-family:Tahoma"><br></div></div><div style="font-size:10pt;font-family:Tahoma">Fan again. So, something about the order that roads are compared to points. I'll keep poking around.</div><div style="font-size:10pt;font-family:Tahoma">Chris</div><div style="font-size:10pt;font-family:Tahoma"><span class="ecxyiv65513775Apple-style-span" style="font-size:10pt"><br></span></div><br><div style="font-size:10pt;font-family:Tahoma"><hr id="ecxyiv65513775stopSpelling">Date: Mon, 21 Nov 2011 22:13:33 +0000<br>From: borntopedal@yahoo.co.uk<br>Subject: Re:
[postgis-users] ST_ShortestLine(r.geom, m.geom) confusion - limiting point to nearest line results<br>To: postgis-users@postgis.refractions.net; sglish@hotmail.com<br><br><table cellspacing="0" cellpadding="0" border="0"><tbody><tr><td valign="top" style="font:inherit">Hi Chris<br>
You need an inner query that selects the closest line segment and use that geometry in the shortest line query <br>
Select shrtest line(p.the_geom,nearestgeom.thegeom) from<br>
Points p,<br>
Select a.*, st_distance(a.the_geom, b.the_geom) as dist from roads a,points b order by dist asc limit 1) as nearestgeom<br>
Hope this is readable as sending from phone.<br>
</td></tr></tbody></table> <div id="ecxyiv65513775ecx_origMsg_">
<div style="font-family:arial, helvetica,">
<br>
<div style="font-size:12pt;font-family:'times new roman', 'new york', times, serif">
<font size="2" face="Tahoma">
<hr size="1">
<b>
<span style="font-weight:bold">From:</span>
</b>
Chris English <sglish@hotmail.com>; <br>
<b>
<span style="">To:</span>
</b>
<postgis-users@postgis.refractions.net>; <br>
<b>
<span style="">Subject:</span>
</b>
[postgis-users] ST_ShortestLine(r.geom, m.geom) confusion - limiting point to nearest line results <br>
<b>
<span style="font-weight:bold">Sent:</span>
</b>
Mon, Nov 21, 2011 9:00:47 PM <br>
</font>
<br>
<table cellspacing="0" cellpadding="0" border="0">
<tbody>
<tr>
<td valign="top" style="font:inherit"><div dir="ltr">
<style>
.ExternalClass #ecxyiv65513775 .ecxyiv65513775ExternalClass .ecxyiv65513775ecxhmmessage P
{padding:0px;}
.ExternalClass #ecxyiv65513775 .ecxyiv65513775ExternalClass body.ecxyiv65513775ecxhmmessage
{font-size:10pt;font-family:Tahoma;}
</style>
<div dir="ltr">Hi all,<div><br></div><div>My goal is to model electric distribution in a municipality with 8.4, PostGis 1.5 and pgrouting.</div><div><br></div><div>I clipped roads and parcels from state level (roads) and county level (parcels) to get roads and parcels for</div><div>a municipality resulting in two tables:</div><div><br></div><div>summit_roads (306 rows) and summit_parcels (6874 rows) . </div><div>The roads table generally conforms to the layout of electric distribution.</div><div>I then extracted a centroids table from parcels to try to develop the 'final drop' line segment, the last bit of wire from</div><div>distribution wire (roads) to a home or business with the final goal to then join to final drop to roads for end to end distribution.</div><div><br></div><div><div><br></div><div>explain </div><div>create table summit_final_drop as</div><div>select Distinct ST_ShortestLine(r.intersection_geom, m.geom) as
final_drop_geom,</div><div>r.sld_name,</div><div>r.measured_l,</div><div>m.pams_pin,</div><div>m.mun,</div><div>m.block,</div><div>m.lot,</div><div>m.qcode</div><div>from summit_roads as r,</div><div>summit_parcels_centroid as m;</div></div><div><br></div><div>This code resulted in 2,109,000 rows , essentially centroid to every line point as against what</div><div>I was expecting.</div><div><br></div><div>Suggestions appreciated.</div><div><br></div><div>Thanks,</div><div>Chris</div></div>
</div></td>
</tr>
</tbody>
</table>
</div>
</div>
</div></div></div>
</div></div>
</div><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br><br> </div> </div> </blockquote></div> </div><br>_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users</div> </div></body>
</html>