Creating regular point grid inside polygon in PostGIS

You do that with generate_series.

If you don't want to manually write where the grid is to start and stop, the easiest is to create a function.

I have not tested the below properly, but I think it should work:

CREATE OR REPLACE FUNCTION makegrid(geometry, integer)
RETURNS geometry AS
'SELECT ST_Collect(ST_POINT(x, y)) FROM 
generate_series(floor(ST_XMIN($1))::int, ceiling(ST_XMAX($1)-ST_XMIN($1))::int, $2) AS x,
generate_series(floor(ST_YMIN($1))::int, ceiling(ST_YMAX($1)-ST_YMIN($1))::int, $2) AS y 
WHERE st_intersects($1, ST_POINT(x, y))'
LANGUAGE sql

To use it you can do:

SELECT makegrid(the_geom, 1000) from mytable;

where the first argument is the polygon you want the grid in, and the second argument is the distance between the points in the grid.

If you want one point per row you just use ST_Dump like:

SELECT (ST_Dump(makegrid(the_geom, 1000))).geom as the_geom from mytable;

I have picked up Nicklas Avén makegrid function code and made it a bit more generic by reading and using the srid from the polygon geometry. Otherwise using a polygon with a defined srid, would give an error.

The function:

CREATE OR REPLACE FUNCTION makegrid(geometry, integer)
RETURNS geometry AS
'SELECT ST_Collect(ST_SetSRID(ST_POINT(x, y), ST_SRID($1))) FROM
generate_series(floor(ST_XMIN($1))::int, ceiling(ST_XMAX($1) - ST_XMIN($1))::int, $2) AS x,
generate_series(floor(ST_YMIN($1))::int, ceiling(ST_YMAX($1) - ST_YMIN($1))::int, $2) AS y
WHERE st_intersects($1, ST_SetSRID(ST_POINT(x,y), ST_SRID($1)))'
LANGUAGE sql

To use the function is done exactly as Nicklas Avén wrote:

SELECT makegrid(the_geom, 1000) from mytable;

or if you want one point per row:

SELECT (ST_Dump(makegrid(the_geom, 1000))).geom as the_geom from mytable;

Hope this will be usefull for someone.

Alex

People using a wgs84 geometry will probably have trouble with this function since

generate_series(floor(st_xmin($1))::int, ceiling(st_xmax($1))::int,$2) as x
,generate_series(floor(st_ymin($1))::int, ceiling(st_ymax($1))::int,$2) as y 

only return integers. Except for very big geometries such as countries (that are laying on multiple lat, lng degrees), this will cause to collect only 1 point which is most of the time not even intersecting the geometry itself... => empty result !

My trouble was I can not seem to use generate_series() with a decimal distance on floating numbers such as those WSG84... This is why I tweaked the function to get it working anyway :

SELECT ST_Collect(st_setsrid(ST_POINT(x/1000000::float,y/1000000::float),st_srid($1))) FROM 
  generate_series(floor(st_xmin($1)*1000000)::int, ceiling(st_xmax($1)*1000000)::int,$2) as x ,
  generate_series(floor(st_ymin($1)*1000000)::int, ceiling(st_ymax($1)*1000000)::int,$2) as y 
WHERE st_intersects($1,ST_SetSRID(ST_POINT(x/1000000::float,y/1000000::float),ST_SRID($1)))

Basically exactly the same. Just multiplying and dividing by 1000000 to get the decimals into the game when I need it.

There is surely a better solution to achieve that. ++