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;
As for your first problem, what would you want to happen with a point that falls exactly on the boundary between two grid cells?
As for the area calculation:
geodata=# SELECT ST_Area(ST_GeogFromText('POLYGON((-10 0,-10 5,-5 5,-5
0,-10 0))'));
st_area
308911036269.806 (1 row)
geodata=# select postgis_full_version();
postgis_full_version
-------------------------------------------------------------------------------------------------------------------------------------------
POSTGIS="1.5.3" GEOS="3.3.2-CAPI-1.7.2" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.6" USE_STATS (procs from 1.5 r5385 need upgrade)
(1 row)
Seems to work for me. What version are you using??
Searching in Google I found this maillist exchange regarding AT_Area and polys touching the equator:
postgis-users
Perhaps, to better understand, could you run the same query but crossing the equator. Such as:
geodata=# SELECT ST_Area(ST_GeographyFromText('POLYGON((-2 -1,-2 1,-1 1,-1 -1,-2 -1))'));
st_area
------------------
24728063597.0354
(1 row)
Best Answer
Here is a set returning function
ST_CreateFishnet
that creates a 2D grid of polygon geometries:where
nrow
andncol
are the number of rows and columns,xsize
andysize
are the lengths of the cell size, and optionalx0
andy0
are coordinates for the bottom-left corner.The result is
row
andcol
numbers, starting from 1 at the bottom-left corner, andgeom
rectangular polygons for each cell. So for example:Or to make a single geometry collection for the full grid:
You can add the
x0
/y0
origin offsets (these defaulted to zero).