[postgis-users] Grid of points inside Polygon

[James David Smith]

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 <remi.cura at gmail.com> 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 <james.david.smith at gmail.com>
>>
>> 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
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users