Top 5 Recent Articles

ARTICLES CATEGORIES

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.