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: [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 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 [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
