Counting Polygon Centroids in Polygons

Question

I have two tables in PostgreSQL 13:

• hexbin table with geom field (red)
• sites table with geom field (blue)

I want to return a table with the hexbin geometries and a total count of where the sites fall within the hexbin (counting site centers in hexbins).

The following code does appear to return me what I require:

SELECT hexbin.gid, hexbin.geom, count(*) AS site_total
    FROM sites, hexbin
    WHERE ST_intersects(hexbin.geom, (ST_PointOnSurface(sites.geom)))
GROUP BY hexbin.gid

Can this approach be improved?

From looking at other samples and my previous experience with SQL Server Spatial, should I be incorporating a join to improve performance?

Answer 1

You have not provided test data to check the different options for optimizing your query and yet, splitting your query into simple subqueries can lead to a 2-n times optimization, so check my assumption on your data and for this run the query:

WITH
    tbla AS (SELECT ST_PointOnSurface(geom) geom FROM sites),
    tblb AS (SELECT a.gid, (a.geom) geom, count(*) AS sites_total FROM hexbin a
        WHERE EXISTS (SELECT 1 FROM tbla b WHERE ST_Intersects(a.geom, b.geom)) GROUP BY a.gid)
    SELECT a.gid, a.geom, SUM(a.sites_total) AS sum_sites FROM tbla b, tblb a 
    WHERE ST_Intersects(a.geom, (b.geom)) GROUP BY a.gid, a.geom

Check your result.

There's always room for optimization as long as the query execution speed doesn't turn to "0".