1

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?

2

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;
  • Did you mean to put a left join in there? – DPSSpatial Sep 14 '18 at 21:15
  • 1
    oh yeah, I did mean to do that! – Mike T Sep 14 '18 at 21:21
  • ah well it happens! – DPSSpatial Sep 14 '18 at 21:25
  • 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 Sep 14 '18 at 21:42
  • Disregard #3 pls – boshek Sep 14 '18 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.