[postgis-users] ST_ShortestLine(r.geom, m.geom) confusion - better limiting point to nearest line results

[Phil James]

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.

I think I have something somewhere if you can't find it online.

Phil



>________________________________
> Fromris English <sglish at hotmail.com>
>To: postgis_users <postgis-users at postgis.refractions.net> 
>Sent: Tuesday, 22 November 2011, 1:09
>Subject: Re: [postgis-users] ST_ShortestLine(r.geom, m.geom) confusion - better limiting  point to nearest line results
> 
>
> 
> 
>Thanks Phil for your response.  The below does greatly reduce number of rows created through the
>inner join.
>The "shortestlines' unfortunately seem to radiate from the center of town.  
>
>
>create table summit_final_drop as
>select ST_ShortestLine(nearestgeom.intersection_geom, b.geom) 
>from summit_parcels_centroid as b, (Select a.*, 
>
>
>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;
>Query returned successfully with no result in 12281 ms.
>
>
>Changing asc limit 1) to desc limit 1) and all segments come from the edge of as if a fan.
>
>
>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
>toward the final part, pg_routing
>
>
>create table summit_final_drop_4 as
>select ST_ShortestLine(b.geom, nearestgeom.geom) 
>from summit_parcels_centroid as b, (Select a.*, 
>
>
>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;
>
>
>And result is same radiation from the center.
>
>
>Running St_ShortestLine twice:
>
>
>create table summit_final_drop_7 as
>select ST_ShortestLine(nearestgeom.geom, b.geom) 
>from summit_parcels_centroid as b, (Select a.*, 
>
>
>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;
>
>
>Fan again.  So, something about the order that roads are compared to points.  I'll keep poking around.
>Chris
>
>
>
>
>________________________________
>Date: Mon, 21 Nov 2011 22:13:33 +0000
>From: borntopedal at yahoo.co.uk
>Subject: Re: [postgis-users] ST_ShortestLine(r.geom, m.geom) confusion - limiting  point to nearest line results
>To: postgis-users at postgis.refractions.net; sglish at hotmail.com
>
>
>Hi Chris
>You need an inner query that selects the closest line segment and use that geometry in the shortest line query 
>Select shrtest line(p.the_geom,nearestgeom.thegeom) from
>Points p,
>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
>Hope this is readable as sending from phone.
> 
>
>
>
>________________________________
> From:  Chris English <sglish at hotmail.com>; 
>To:  <postgis-users at postgis.refractions.net>; 
>Subject:  [postgis-users] ST_ShortestLine(r.geom, m.geom) confusion - limiting  point to nearest line results 
>Sent:  Mon, Nov 21, 2011 9:00:47 PM 
> 
>
> 
>Hi all,
>
>
>My goal is to model electric distribution in a municipality with 8.4, PostGis 1.5 and pgrouting.
>
>
>I clipped roads and parcels from state level (roads) and county level (parcels) to get roads and parcels for
>a municipality resulting in two tables:
>
>
>summit_roads (306 rows) and summit_parcels (6874 rows) . 
>The roads table generally conforms to the layout of electric distribution.
>I then extracted a centroids table from parcels to try to develop the 'final drop' line segment, the last bit of wire from
>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.
>
>
>
>
>explain 
>create table summit_final_drop as
>select Distinct ST_ShortestLine(r.intersection_geom, m.geom) as final_drop_geom,
>r.sld_name,
>r.measured_l,
>m.pams_pin,
>m.mun,
>m.block,
>m.lot,
>m.qcode
>from summit_roads as r,
>summit_parcels_centroid as m;
>
>
>This code resulted in 2,109,000 rows , essentially centroid to every line point as against what
>I was expecting.
>
>
>Suggestions appreciated.
>
>
>Thanks,
>Chris 
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111122/1a988790/attachment.html>