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

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:

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. The first and second parameters of the function developed by the programmer under the "nickname" Kaveh (URL of the resource - https: gist.github.com/ka7eh), take this opportunity to thank him for this, 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.