Return rows after a spatial join where the count is zero

Question

I am trying to determine the number points in a table of polygons AND include a row of zero for those instances where a polygon has no points in it. This query works well except that no zero rows are outputted:

SELECT COUNT(h.points) AS "num_points", ws.polygons
                 FROM points_table as h
                 JOIN polygon_table as ws 
                 ON ST_Intersects(ws.geom, h.geometry)
                 GROUP BY ws.grouping_variable;

I tried using COALESCE like so:

SELECT COALESCE (COUNT(h.points), 0) AS "num_points", ws.polygons
                 FROM points_table as h
                 JOIN polygon_table as ws 
                 ON ST_Intersects(ws.geom, h.geometry)
                 GROUP BY ws.grouping_variable;

This successfully ran but didn't return any instances where the row equal zero. I think I am getting a little confused about the order of SQL. I am quite certain that one route is to take the "non-zero" output and LEFT JOIN it with the original polygon_table but I am not clear where that should actually happen.

In summary - how do I return rows after a spatial join where the count is zero as well as all the other counts?

Answer 1

If you want the point count for each polygon, then use polygon for the first table, then a LEFT JOIN the points for zero or more matches. I've renamed a few things here:

SELECT poly.gid, COUNT(point.*) AS num_points
FROM polygon_table AS poly
LEFT JOIN points_table AS point ON ST_Intersects(poly.geom, point.geom)
GROUP BY poly.gid, poly.geom
ORDER BY poly.gid;