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: [email protected] To: [email protected] 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 <[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 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 _______________________________________________ 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
