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

Tue Nov 22 07:36:49 PST 2011

create table summit_final_drop_8 asselect  Distinct ST_ShortestLine(a.intersection_geom, b.geom) from summit_parcels_centroid as b,  summit_roads as a where ST_DWithin(a.intersection_geom,b.geom,150) ;
This got me nearly there and preserves the start/end of the line segment needed for_pg_routing, though throws up additional or excess final connections, probablya limit would help.  At least as viewed in UDig, the segment lengths looked right . I'll look into nearest neighbor as an alternative solution.

Date: Tue, 22 Nov 2011 06:19:48 +0000
From: borntopedal at yahoo.co.uk
To: postgis-users at postgis.refractions.net
Subject: Re: [postgis-users] ST_ShortestLine(r.geom,	m.geom) confusion - better limiting  point to nearest line results

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 theinner join.The "shortestlines' unfortunately seem to radiate from the center of town.  
create table summit_final_drop asselect 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 eyetoward the final part, pg_routing
create table summit_final_drop_4 asselect 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 asselect 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 fora 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 fromdistribution 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 asselect 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.qcodefrom 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 whatI was expecting.
Suggestions appreciated.
Thanks,Chris

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
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/fcf2b040/attachment.html>