Generating a Grid (fishnet) of points or polygons for PostGIS
I wrote an article on Gridding a PostGIS Geometry object a while back.
I had cause, recently, to use a variation on this for the generation of an array of point objects for a geometry polygon, so I wrote a version that also allows for the generation of a grid of polygons or points depending on a boolean parameter.
-- Create required type DROP TYPE IF EXISTS T_Grid CASCADE; CREATE TYPE T_Grid AS ( gcol int4, grow int4, geom geometry ); -- Drop function is exists DROP FUNCTION IF EXISTS ST_RegularGrid(geometry, NUMERIC, NUMERIC, BOOLEAN); -- Now create the function CREATE OR REPLACE FUNCTION ST_RegularGrid(p_geometry geometry, p_TileSizeX NUMERIC, p_TileSizeY NUMERIC, p_point BOOLEAN DEFAULT TRUE) RETURNS SETOF T_Grid AS $BODY$ DECLARE v_mbr geometry; v_srid int4; v_halfX NUMERIC := p_TileSizeX / 2.0; v_halfY NUMERIC := p_TileSizeY / 2.0; v_loCol int4; v_hiCol int4; v_loRow int4; v_hiRow int4; v_grid T_Grid; BEGIN IF ( p_geometry IS NULL ) THEN RETURN; END IF; v_srid := ST_SRID(p_geometry); v_mbr := ST_Envelope(p_geometry); v_loCol := trunc((ST_XMIN(v_mbr) / p_TileSizeX)::NUMERIC ); v_hiCol := CEIL( (ST_XMAX(v_mbr) / p_TileSizeX)::NUMERIC ) - 1; v_loRow := trunc((ST_YMIN(v_mbr) / p_TileSizeY)::NUMERIC ); v_hiRow := CEIL( (ST_YMAX(v_mbr) / p_TileSizeY)::NUMERIC ) - 1; FOR v_col IN v_loCol..v_hiCol Loop FOR v_row IN v_loRow..v_hiRow Loop v_grid.gcol := v_col; v_grid.grow := v_row; IF ( p_point ) THEN v_grid.geom := ST_SetSRID( ST_MakePoint((v_col * p_TileSizeX) + v_halfX, (v_row * p_TileSizeY) + V_HalfY), v_srid); ELSE v_grid.geom := ST_SetSRID( ST_MakeEnvelope((v_col * p_TileSizeX), (v_row * p_TileSizeY), (v_col * p_TileSizeX) + p_TileSizeX, (v_row * p_TileSizeY) + p_TileSizeY), v_srid); END IF; RETURN NEXT v_grid; END Loop; END Loop; END; $BODY$ LANGUAGE plpgsql IMMUTABLE COST 100 ROWS 1000; -- Assign ownership ALTER FUNCTION ss.st_regulargrid(geometry, NUMERIC, NUMERIC, BOOLEAN) OWNER TO postgres;
Now, let’s do some testing:
First, generate grid of points over a geometry’s MBR.
SELECT gcol, grow,ST_AsText(geom) AS geomWKT
FROM ss.ST_RegularGrid(ST_GeomFromText('LINESTRING(0 0, 100 100)',0),20,20);
Result:
| gcol | grow | geomWKT |
|---|---|---|
| 0 | 0 | POINT (10 10) |
| 0 | 1 | POINT (10 30) |
| 0 | 2 | POINT (10 50) |
| 0 | 3 | POINT (10 70) |
| 0 | 4 | POINT (10 90) |
| 1 | 0 | POINT (30 10) |
| 1 | 1 | POINT (30 30) |
| 1 | 2 | POINT (30 50) |
| 1 | 3 | POINT (30 70) |
| 1 | 4 | POINT (30 90) |
| 2 | 0 | POINT (50 10) |
| 2 | 1 | POINT (50 30) |
| 2 | 2 | POINT (50 50) |
| 2 | 3 | POINT (50 70) |
| 2 | 4 | POINT (50 90) |
| 3 | 0 | POINT (70 10) |
| 3 | 1 | POINT (70 30) |
| 3 | 2 | POINT (70 50) |
| 3 | 3 | POINT (70 70) |
| 3 | 4 | POINT (70 90) |
| 4 | 0 | POINT (90 10) |
| 4 | 1 | POINT (90 30) |
| 4 | 2 | POINT (90 50) |
| 4 | 3 | POINT (90 70) |
| 4 | 4 | POINT (90 90) |
Secondly, generate grid of polygons over the MBR of the same object.
SELECT gcol, grow,ST_AsText(geom) AS geomWKT
FROM ss.ST_RegularGrid(ST_GeomFromText('LINESTRING(0 0, 100 100)',0),20,20,FALSE);
Result:
| gcol | grow | geomWKT |
|---|---|---|
| 0 | 0 | POLYGON ((0 0,0 20,20 20,20 0,0 0)) |
| 0 | 1 | POLYGON ((0 20,0 40,20 40,20 20,0 20)) |
| 0 | 2 | POLYGON ((0 40,0 60,20 60,20 40,0 40)) |
| 0 | 3 | POLYGON ((0 60,0 80,20 80,20 60,0 60)) |
| 0 | 4 | POLYGON ((0 80,0 100,20 100,20 80,0 80)) |
| 1 | 0 | POLYGON ((20 0,20 20,40 20,40 0,20 0)) |
| 1 | 1 | POLYGON ((20 20,20 40,40 40,40 20,20 20)) |
| 1 | 2 | POLYGON ((20 40,20 60,40 60,40 40,20 40)) |
| 1 | 3 | POLYGON ((20 60,20 80,40 80,40 60,20 60)) |
| 1 | 4 | POLYGON ((20 80,20 100,40 100,40 80,20 80)) |
| 2 | 0 | POLYGON ((40 0,40 20,60 20,60 0,40 0)) |
| 2 | 1 | POLYGON ((40 20,40 40,60 40,60 20,40 20)) |
| 2 | 2 | POLYGON ((40 40,40 60,60 60,60 40,40 40)) |
| 2 | 3 | POLYGON ((40 60,40 80,60 80,60 60,40 60)) |
| 2 | 4 | POLYGON ((40 80,40 100,60 100,60 80,40 80)) |
| 3 | 0 | POLYGON ((60 0,60 20,80 20,80 0,60 0)) |
| 3 | 1 | POLYGON ((60 20,60 40,80 40,80 20,60 20)) |
| 3 | 2 | POLYGON ((60 40,60 60,80 60,80 40,60 40)) |
| 3 | 3 | POLYGON ((60 60,60 80,80 80,80 60,60 60)) |
| 3 | 4 | POLYGON ((60 80,60 100,80 100,80 80,60 80)) |
| 4 | 0 | POLYGON ((80 0,80 20,100 20,100 0,80 0)) |
| 4 | 1 | POLYGON ((80 20,80 40,100 40,100 20,80 20)) |
| 4 | 2 | POLYGON ((80 40,80 60,100 60,100 40,80 40)) |
| 4 | 3 | POLYGON ((80 60,80 80,100 80,100 60,80 60)) |
| 4 | 4 | POLYGON ((80 80,80 100,100 100,100 80,80 80)) |
Visually.
I hope this is useful for someone.
One thought on “Generating a Grid (fishnet) of points or polygons for PostGIS”