Hey Remi, Thanks for your reply. So in your mind you think we should have a database of say 300 polygons, and then we run a command like this right?
SELECT ST_Collect(st_setsrid(ST_POINT(x,y),27700)) FROM generate_series(53320::int, 667380::int,20) as x, generate_series(7780::int, 1226580::int,20) as y, road_polygons_table WHERE st_intersects(road_polygons_table.the_geom, st_setsrid(ST_POINT(x,y),27700)) What do you think? Thanks James On 11 November 2013 14:51, Rémi Cura <[email protected]> wrote: > 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 _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
