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.
- 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:
CREATE TABLE grid_australia_1gr AS
SELECT * FROM st_createfishnet (43,69,1.0,1,0,111.0, -51.0);
UPDATE grid_australia_1gr SET geom = ST_SetSRID (geom, 4326);
CREATE INDEX grid_australia_1gr_geom ON grid_australia_1gr USING gist (geom);
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
count (*) as cnt
FROM grid_australia_mpt_dump, grid_australia_1gr
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.