# Creating a Grid on a polygon and find number of points in each grid

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.

• Are you trying to create a vector grid dataset or to display a grid/graticule on a map?
– PolyGeo
Commented Feb 13, 2018 at 4:48
• You should pretty much always set the SRID, yes. It avoids lots of accidental pain. You always convert from one SRID to another, later, with ST_Transform. Commented Feb 13, 2018 at 12:37
• You mean a single polygon or a table of many polygons? If you mean a single polygons then I would understand why you want "to move the grid around the map". And what do you mean by "insert the points onto the grid"? Please clarify... Commented Feb 14, 2018 at 15:03
• What does it mean, however, I do not know how to move the grid around the map? 5 and 6, the function variable fishnet is the origin of the left-south-west corner of your grid, define its coordinates so that all your polygons are in it and determine the size of the cells of your grid ... Commented May 31, 2018 at 12:18

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:

1. These are the polygons of the states Australia and New Zealand (they say very beautiful nature here);
2. 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.

• И особая благодарность всем разработчикам PostgreSQL / PostGIS и QGIS :-). Commented Jun 1, 2018 at 10:07
• hey! thanks a lot for your comment. I am glad to hear from you and this really helps me a lot. It is almost same as what I want as what we want to know is the which grid does the point laid on.cheers! Commented Jun 2, 2018 at 22:29