<html><body><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="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; margin-top: 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,
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="yiv65513775">
<style><!--
#yiv65513775 .yiv65513775hmmessage P
{
margin:0px;padding:0px;}
#yiv65513775 body.yiv65513775hmmessage
{
font-size:10pt;font-family:Tahoma;}
--></style>
<div><div dir="ltr">
<style><!--
#yiv65513775 .yiv65513775hmmessage P
{
margin:0px;padding:0px;}
#yiv65513775 body.yiv65513775hmmessage
{
font-size:10pt;font-family:Tahoma;}
--></style>
<div dir="ltr"><div style="font-size: 10pt; font-family: Tahoma; "><font class="yiv65513775Apple-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="yiv65513775Apple-style-span" face="Tahoma" size="2">inner join.</font></div><div style="font-size: 10pt; font-family: Tahoma; "><font class="yiv65513775Apple-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="yiv65513775Apple-style-span" face="Tahoma" size="2"><br></font></div><div style="font-size: 10pt; font-family: Tahoma; "><font class="yiv65513775Apple-style-span" face="Tahoma" size="2">create table summit_final_drop as</font></div><div style="font-size: 10pt; font-family: Tahoma; "><font
class="yiv65513775Apple-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="yiv65513775Apple-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="yiv65513775Apple-style-span" face="Tahoma" size="2"><br></font></div><div style="font-size: 10pt; font-family: Tahoma; "><font class="yiv65513775Apple-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="yiv65513775Apple-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="yiv65513775Apple-style-span" face="Tahoma" size="2"><br></font></div><div style="font-size: 10pt; font-family: Tahoma; "><div><font class="yiv65513775Apple-style-span" face="Tahoma" size="2">Changing asc limit 1) to desc limit 1) and all segments </font><span class="yiv65513775Apple-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="yiv65513775Apple-style-span" style="font-size: 10pt; font-family: Tahoma; "><br></span></div><div><div><font class="yiv65513775Apple-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="yiv65513775Apple-style-span" face="Tahoma" size="2">toward the final part, pg_routing</font></div><div><font
class="yiv65513775Apple-style-span" face="Tahoma" size="2"><br></font></div><div><font class="yiv65513775Apple-style-span" face="Tahoma" size="2">create table summit_final_drop_4 as</font></div><div><font class="yiv65513775Apple-style-span" face="Tahoma" size="2">select ST_ShortestLine(b.geom, nearestgeom.geom) </font></div><div><font class="yiv65513775Apple-style-span" face="Tahoma" size="2">from summit_parcels_centroid as b, (Select a.*, </font></div><div><font class="yiv65513775Apple-style-span" face="Tahoma" size="2"><br></font></div><div><font class="yiv65513775Apple-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="yiv65513775Apple-style-span" style="font-size:10pt;"><br></span></div><div style="font-size: 10pt; font-family:
Tahoma; "><span class="yiv65513775Apple-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="yiv65513775Apple-style-span" style="font-size:10pt;"><br></span></div><div style="font-size: 10pt; font-family: Tahoma; "><span class="yiv65513775Apple-style-span" style="font-size:10pt;">Running St_ShortestLine twice:</span></div><div style="font-size: 10pt; font-family: Tahoma; "><span class="yiv65513775Apple-style-span" style="font-size:10pt;"><br></span></div><div><div><font class="yiv65513775Apple-style-span" face="Tahoma" size="2">create table summit_final_drop_7 as</font></div><div><font class="yiv65513775Apple-style-span" face="Tahoma" size="2">select ST_ShortestLine(nearestgeom.geom, b.geom) </font></div><div><font class="yiv65513775Apple-style-span" face="Tahoma" size="2">from summit_parcels_centroid as b, (Select
a.*, </font></div><div><font class="yiv65513775Apple-style-span" face="Tahoma" size="2"><br></font></div><div><font class="yiv65513775Apple-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="yiv65513775Apple-style-span" style="font-size:10pt;"><br></span></div><br><div style="font-size: 10pt; font-family: Tahoma; "><hr id="yiv65513775stopSpelling">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="yiv65513775ecx_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>
#yiv65513775 .yiv65513775ExternalClass .yiv65513775ecxhmmessage P
{padding:0px;}
#yiv65513775 .yiv65513775ExternalClass body.yiv65513775ecxhmmessage
{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 ymailto="mailto:postgis-users@postgis.refractions.net" 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></body></html>