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

[Chris English]

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
 		 	   		  
                                    
                                
                            
                    
                
            
 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111121/abcafd2c/attachment.html>