30

How to create, inside a polygon, a regular grid of point spaced x,y in postgis? Like the example:

alt text

  • I tried to make clipping polygons merging this code with "postGIS in action" dicing code but only one polygon is created... Did I forget something? CREATE OR REPLACE FUNCTION makegrid(geometry, integer, integer) RETURNS geometry AS 'SELECT st_intersection(g1.geom1, g2.geom2) AS geom FROM (SELECT $1 AS geom1) AS g1 INNER JOIN (Select st_setsrid(CAST(ST_MakeBox2d( st_setsrid(ST_Point(x,y),$3), st_setsrid(ST_Point(x + $2 ,y +$2),$3)) as geometry),$3) as geom2 FROM 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( – aurel_nc May 2 '13 at 14:30
  • look at my detailed answer. – Muhammad Imran Siddique Mar 27 at 12:31
29

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;

HTH

Nicklas

  • 1
    You may need to add st_setSRID() to the st_point functions, otherwise st_intersects does not work. – JaakL Oct 31 '12 at 9:25
  • Added my tested version as separate answer. – JaakL Oct 31 '12 at 16:43
12

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

  • The accepted answer doesn't work with my data due to SRID Errors. This modification works better. – Vitaly Isaev Oct 20 '14 at 19:47
  • You might want to add something when polygon is crossing the antimeridian? I can imagine it would cause a problem with xmin/xmax. – Thomas Jul 8 '16 at 10:23
  • 2
    It didn't work for me. Using Postgres 9.6 and PostGIS 2.3.3. Inside the generate_series call, I had to put this as the second parameter "ceiling(st_xmax($1))::int" instead of "ceiling(st_xmax($1)-st_xmin($1))::int", and "ceiling(st_ymax($1))::int" instead of "ceiling(st_ymax($1)-st_ymin($1))::int" – Vitor Sapucaia Mar 27 '18 at 15:09
  • I approve the previous comment; the upper bound of generate_series should be the ceiling of the max, and not the ceiling of the difference (max - min). – R. Bourgeon Jun 11 '18 at 16:18
10

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. ++

  • That's a smart workaround. Have you checked the results? Are they consistent? – Pablo May 29 '13 at 13:57
  • Hi. Yes pablo. I'm happy with the results so far. I needed it to build some polygon with relative altitude above the ground. (I use SRTM to calculate the altitude I want for every grid point). I'm now only missing a way to include the points that are laying on the perimeter of the polygon as well. Currently the rendered shape is somewhat truncated at the edge. – Julien Garcia Jun 12 '13 at 11:34
  • worked, when all others' solutions failed, thanks! – Jordan Arseno Aug 7 '14 at 17:38
7

This algorithm should be fine:

createGridInPolygon(polygon, resolution) {
    for(x=polygon.xmin; x<polygon.xmax; x+=resolution) {
       for(y=polygon.ymin; y<polygon.ymax; y+=resolution) {
          if(polygon.contains(x,y)) createPoint(x,y);
       }
    }
}

where 'polygon' is the polygon and 'resolution' is the required grid resolution.

To implement it in PostGIS, the following functions may be needed:

Good luck!

  • 1
    Note that if you have large complex polygons areas (e.g. I have coastline buffer), then this approach is not quite optimal. – JaakL Oct 31 '12 at 8:34
  • Then, what do you propose instead? – julien Oct 31 '12 at 8:56
3

Three algorithms using different methods.

Github Repo Link

  1. The simple and best approach, using the actual earth distance of coordinates from x and y direction. The algorithm works with any SRID, internally it works with WGS 1984(EPSG:4326) and result transform back to input SRID.

Function ===================================================================

CREATE OR REPLACE FUNCTION public.I_Grid_Point_Distance(geom public.geometry, x_side decimal, y_side decimal)
RETURNS public.geometry AS $BODY$
DECLARE
x_min decimal;
x_max decimal;
y_max decimal;
x decimal;
y decimal;
returnGeom public.geometry[];
i integer := -1;
srid integer := 4326;
input_srid integer;
BEGIN
CASE st_srid(geom) WHEN 0 THEN
    geom := ST_SetSRID(geom, srid);
        ----RAISE NOTICE 'No SRID Found.';
    ELSE
        ----RAISE NOTICE 'SRID Found.';
END CASE;
    input_srid:=st_srid(geom);
    geom := st_transform(geom, srid);
    x_min := ST_XMin(geom);
    x_max := ST_XMax(geom);
    y_max := ST_YMax(geom);
    y := ST_YMin(geom);
    x := x_min;
    i := i + 1;
    returnGeom[i] := st_setsrid(ST_MakePoint(x, y), srid);
<<yloop>>
LOOP
IF (y > y_max) THEN
    EXIT;
END IF;

CASE i WHEN 0 THEN 
    y := ST_Y(returnGeom[0]);
ELSE 
    y := ST_Y(ST_Project(st_setsrid(ST_MakePoint(x, y), srid), y_side, radians(0))::geometry);
END CASE;

x := x_min;
<<xloop>>
LOOP
  IF (x > x_max) THEN
      EXIT;
  END IF;
    i := i + 1;
    returnGeom[i] := st_setsrid(ST_MakePoint(x, y), srid);
    x := ST_X(ST_Project(st_setsrid(ST_MakePoint(x, y), srid), x_side, radians(90))::geometry);
END LOOP xloop;
END LOOP yloop;
RETURN
ST_CollectionExtract(st_transform(ST_Intersection(st_collect(returnGeom), geom), input_srid), 1);
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;

Use the function with a simple query, geometry must be valid and polygon, multi-polygons or envelope type

SELECT I_Grid_Point_Distance(geom, 50, 61) from polygons limit 1;

Result======================================================================

enter image description here

  1. Second function based on Nicklas Avén algorithm. I enhanced it to handle any SRID.

    have upgrade the following changes in the algorithm.

    1. Separate variable for x and y direction for pixel size,
    2. New variable to calculate the distance in spheroid or ellipsoid.
    3. Input any SRID, function transform Geom to the working environment of Spheroid or Ellipsoid Datum, then apply the distance to each side, get the result and transform to input SRID.

Function ===================================================================

CREATE OR REPLACE FUNCTION I_Grid_Point(geom geometry, x_side decimal, y_side decimal, spheroid boolean default false)
RETURNS SETOF geometry AS $BODY$ 
DECLARE
x_max decimal; 
y_max decimal;
x_min decimal;
y_min decimal;
srid integer := 4326;
input_srid integer; 
BEGIN
CASE st_srid(geom) WHEN 0 THEN
  geom := ST_SetSRID(geom, srid);
  RAISE NOTICE 'SRID Not Found.';
    ELSE
        RAISE NOTICE 'SRID Found.';
    END CASE;

    CASE spheroid WHEN false THEN
        RAISE NOTICE 'Spheroid False';
        srid := 4326;
        x_side := x_side / 100000;
        y_side := y_side / 100000;
    else
        srid := 900913;
        RAISE NOTICE 'Spheroid True';
    END CASE;
    input_srid:=st_srid(geom);
    geom := st_transform(geom, srid);
    x_max := ST_XMax(geom);
    y_max := ST_YMax(geom);
    x_min := ST_XMin(geom);
    y_min := ST_YMin(geom);
RETURN QUERY
WITH res as (SELECT ST_SetSRID(ST_MakePoint(x, y), srid) point FROM
generate_series(x_min, x_max, x_side) as x,
generate_series(y_min, y_max, y_side) as y
WHERE st_intersects(geom, ST_SetSRID(ST_MakePoint(x, y), srid))
) select ST_TRANSFORM(ST_COLLECT(point), input_srid) from res;
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE STRICT;

Use it with a simple query.

SELECT I_Grid_Point(geom, 22, 15, false) from polygons;

Result===================================================================enter image description here

  1. Function based on series generator.

Function==================================================================

CREATE OR REPLACE FUNCTION I_Grid_Point_Series(geom geometry, x_side decimal, y_side decimal, spheroid boolean default false)
RETURNS SETOF geometry AS $BODY$
DECLARE
x_max decimal;
y_max decimal;
x_min decimal;
y_min decimal;
srid integer := 4326;
input_srid integer;
x_series DECIMAL;
y_series DECIMAL;
BEGIN
CASE st_srid(geom) WHEN 0 THEN
  geom := ST_SetSRID(geom, srid);
  RAISE NOTICE 'SRID Not Found.';
    ELSE
        RAISE NOTICE 'SRID Found.';
    END CASE;

    CASE spheroid WHEN false THEN
        RAISE NOTICE 'Spheroid False';
    else
        srid := 900913;
        RAISE NOTICE 'Spheroid True';
    END CASE;
    input_srid:=st_srid(geom);
    geom := st_transform(geom, srid);
    x_max := ST_XMax(geom);
    y_max := ST_YMax(geom);
    x_min := ST_XMin(geom);
    y_min := ST_YMin(geom);

    x_series := CEIL ( @( x_max - x_min ) / x_side);
    y_series := CEIL ( @( y_max - y_min ) / y_side );
RETURN QUERY
SELECT st_collect(st_setsrid(ST_MakePoint(x * x_side + x_min, y*y_side + y_min), srid)) FROM
generate_series(0, x_series) as x,
generate_series(0, y_series) as y
WHERE st_intersects(st_setsrid(ST_MakePoint(x*x_side + x_min, y*y_side + y_min), srid), geom);
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE STRICT;

Use it with a simple query.

SELECT I_Grid_Point_Series(geom, 22, 15, false) from polygons; Result==========================================================================

enter image description here

2

So my fixed version:

CREATE OR REPLACE FUNCTION makegrid(geometry, integer, integer)
RETURNS geometry AS
'SELECT ST_Collect(st_setsrid(ST_POINT(x,y),$3)) FROM 
  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 
where st_intersects($1,st_setsrid(ST_POINT(x,y),$3))'
LANGUAGE sql

Usage:

SELECT (ST_Dump(makegrid(the_geom, 1000, 3857))).geom as the_geom from my_polygon_table
  • 1
    Hi, I get empty result with makegrid function. The shapefile was imported to PostGIS using shp2pgsql. Have no idea what could be causing trouble, the srs is set to wgs84. – Michal Zimmermann Mar 15 '13 at 12:15
2

Here is another approach which is certainly faster and easier to understand.

For example for a 1000m by 1000m grid:

SELECT (ST_PixelAsCentroids(ST_AsRaster(the_geom,1000.0,1000.0))).geom 
FROM the_polygon

Also the original SRID is preserved.

This snippet convert the polygon's geometry to an empty raster, then convert each pixel into a point. Advantage: we do not have to check again if the original polygon intersects the points.

Optional:

You can also add the grid alignement with the parameter gridx and gridy. But since we use the centroid of each pixel (and not a corner) we need to use a modulo to compute the right value:

SELECT (ST_PixelAsCentroids(ST_AsRaster(the_geom,1000.0,1000.0,mod(1000/2,100),mod(1000/2,100)))).geom 
FROM the_polygon

With mod(grid_size::integer/2,grid_precision)

Here is the postgres function:

CREATE OR REPLACE FUNCTION st_makegrid(geometry, float, integer)
RETURNS SETOF geometry AS
'SELECT (ST_PixelAsCentroids(ST_AsRaster($1,$2::float,$2::float,mod($2::int/2,$3),mod($2::int/2,$3)))).geom'
LANGUAGE sql;

Canbe used with:

SELECT makegrid(the_geom,1000.0,100) as geom from the_polygon  
-- makegrid(the_geom,grid_size,alignement)
1

A small potential update to the previous answers - third argument as scale for wgs84 (or use 1 for normal ones), and also rounding inside the code so that the scaled points on multiple shapes are aligned.

Hope this helps, Martin

CREATE OR REPLACE FUNCTION makegrid(geometry, integer, integer)
RETURNS geometry AS



/*geometry column , integer: distance between points, integer: scale factor for distance (useful for wgs84, e.g. use there 50000 as distance and 1000000 as scale factor*/

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

LANGUAGE sql
  • Wouldn't transforming the geometry to a specific SRID (like EPSG:3857) be better than just multiplying by a scale factor? – Nikolaus Krismer Apr 25 '17 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.