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 <[email protected]>
>To: postgis_users <[email protected]>
>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: [email protected]
>Subject: Re: [postgis-users] ST_ShortestLine(r.geom, m.geom) confusion -
>limiting point to nearest line results
>To: [email protected]; [email protected]
>
>
>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 <[email protected]>;
>To: <[email protected]>;
>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
>[email protected]
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
