Hey, the whole point on using a sgbds like postgis is using index. If you have one line you don't use indexes...
So in short, don't make one polygon with a buffer of all the road, but a table with a line for the buffer for every road, then do you computation to create grid of points inside of polygons, then union the result of points! And it s always a bad idea to run a function on big data when you have not tested it fully (including scaling behavior) on small data. Cheers Rémi-C 2013/11/11 James David Smith <[email protected]> > Hi all, > > Would appreciate some advice on the best way to accomplish this please. > > Our situation is that we have a single polygon which has been created > by buffering all of the major roads in the UK. Projection is OSGB36 > (27700). Obviously it's quite a big polygon. > > --> SELECT st_area(geom) FROM roadbufferunion; > st_area > ------------------ > 77228753220.8271 > > What we now want to do is create a regular grid of 20 metre x 20 metre > points instead the polygon area. So we wrote this function (based on > some googling, apologies for not being able to recall the exact person > who originally wrote it): > > CREATE OR REPLACE FUNCTION makegrid(geometry, integer, integer) > RETURNS geometry AS > 'SELECT ST_Collect(st_setsrid(ST_POINT(x,y),$3)) FROM > generate_series(53320::int, 667380::int,$2) as x > ,generate_series(7780::int, 1226580::int,$2) as y > where st_intersects($1,st_setsrid(ST_POINT(x,y),$3))' > LANGUAGE sql > > and we then run this by doing the following: > > SELECT st_x((ST_Dump(makegrid(geom, 20, 27700))).geom) as x, > st_y((ST_Dump(makegrid(geom, 20, 27700))).geom) as y INTO grid_points > from roadbufferunion; > > However after over 2 days of the query running on a pretty powerful > linux cluster, we still have no result. I'm not sure if it is > actually running or not to be honest. > > Does the query look right? > Any ideas how we can make it run quicker? > > Thanks > > James > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
