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?