PostGIS – Creating a Grid on a Polygon and Finding Number of Points in Each Grid

grids-graticulespolygonpostgis

How can I create a grid on top of a polygon and then insert the points onto the grid?

As far as i know, i am using How to create a regular polygon grid in PostGIS? guide to created the grid. However, I do not know how to move the grid around the map.

Must I set the SRID?

I am planning to create the grid on the australia and new zealand map.

Best Answer

So, Derek, let's try to swim together in the children's pool PostgreSQL / PostGIS and QGIS. If I understood your question correctly, then you sailed with me.

Initial data:

These are the polygons of the states Australia and New Zealand (they say very beautiful nature here);
These are points (populated areas - grid_australia_mpt)

See Figure 1 1) To create a grid, you run the following queries in pgAdmin:

1.1) CREATE TABLE grid_australia_1gr AS SELECT * FROM st_createfishnet(43,69,1.0,1.0,111.0,-51.0);

1.2) UPDATE grid_australia_1gr SET geom = ST_SetSRID (geom, 4326); 1.3) CREATE INDEX grid_australia_1gr_geom ON grid_australia_1gr USING gist (geom); 1.4) ANALYZE grid_australia_1gr;

The explanation of item 1.1. as important for us: In my example, I decided to build a map grid in 1 degree increments in latitude and longitude, for this I determined the coordinates of the lower left (south-western) corner of the grid (latitude, so the states are in the southern hemisphere they have negative values) B = -51.0 the degree is exactly and (longitude) L = 111.0 degrees exactly, then I defined the boundaries to which 2 states fall and determined their longitude values of 69 degrees and latitude 43 degrees. Okay, so I've defined the parameters for this function (https://trac.osgeo.org/postgis/wiki/UsersWikiCreateFishnet).

The first and second parameters of the function the number of rows and columns,

the second and third is the grid cell's grid size in degrees,

the fifth and sixth are our origin.

See Figure 2 2) In the grid_australia_1gr table, you need to delete two fields named "col" and "row", create a primary key, for example a gid and set a limit on it. Next, I created a table

create table grid_australia_mpt_dump as SELECT (ST_Dumppoints (geom)). geom FROM grid_australia_mpt; To convert points of type MultiPoint to Point;

3) In pgAdmin, run the following query

create table grid_australia_mpt_select as SELECT grid_australia_1gr.gid, grid_australia_1gr.geom, count (*) as cnt FROM grid_australia_mpt_dump, grid_australia_1gr WHERE st_intersects (grid_australia_mpt_dump.geom, grid_australia_1gr.geom) GROUP BY grid_australia_1gr.gid ORDER BY grid_australia_1gr.gid;

I hope that we swam across the pool together, good luck.

Related Solutions

PostGIS Point Grid – 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;

Creating Regular Polygon Grid in PostGIS – Comprehensive Guide

Here is a set returning function ST_CreateFishnet that creates a 2D grid of polygon geometries:

CREATE OR REPLACE FUNCTION ST_CreateFishnet(
        nrow integer, ncol integer,
        xsize float8, ysize float8,
        x0 float8 DEFAULT 0, y0 float8 DEFAULT 0,
        OUT "row" integer, OUT col integer,
        OUT geom geometry)
    RETURNS SETOF record AS
$$
SELECT i + 1 AS row, j + 1 AS col, ST_Translate(cell, j * $3 + $5, i * $4 + $6) AS geom
FROM generate_series(0, $1 - 1) AS i,
     generate_series(0, $2 - 1) AS j,
(
SELECT ('POLYGON((0 0, 0 '||$4||', '||$3||' '||$4||', '||$3||' 0,0 0))')::geometry AS cell
) AS foo;
$$ LANGUAGE sql IMMUTABLE STRICT;

where nrow and ncol are the number of rows and columns, xsize and ysize are the lengths of the cell size, and optional x0 and y0 are coordinates for the bottom-left corner.

The result is row and col numbers, starting from 1 at the bottom-left corner, and geom rectangular polygons for each cell. So for example:

SELECT *
FROM ST_CreateFishnet(4, 6, 10, 10) AS cells;
 row | col |         geom
-----+-----+--------------------------------
   1 |   1 | 0103000000010000000500000000...
   2 |   1 | 0103000000010000000500000000...
   3 |   1 | 0103000000010000000500000000...
   4 |   1 | 0103000000010000000500000000...
   1 |   2 | 0103000000010000000500000000...
   2 |   2 | 0103000000010000000500000000...
   ...
   3 |   6 | 0103000000010000000500000000...
   4 |   6 | 0103000000010000000500000000...
(24 rows)

Or to make a single geometry collection for the full grid:

SELECT ST_Collect(cells.geom)
FROM ST_CreateFishnet(4, 6, 10, 10) AS cells;

4x6 grid

You can add the x0 / y0 origin offsets (these defaulted to zero).

Best Answer

Related Solutions

PostGIS Point Grid – Creating Regular Point Grid Inside Polygon in PostGIS

Creating Regular Polygon Grid in PostGIS – Comprehensive Guide

Related Question