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;

• Inactivated Account: Did you mean to put a left join in there?
• Mike T: oh yeah, I did mean to do that!
• Inactivated Account: ah well it happens!
• boshek: That's it! Questions - 1) why did you rename things? 2) Any idea what type of resource demand ORDER BY has? I don't really need it and I am curious why you inserted it. 3) In SELECT how would I also return the geometry from poly?
• boshek: Disregard #3 pls