I have two data sets with a large number of points that I’m organizing into a hexbin grid.
I'm associating these points with the hexbin polygons by using PostGIS to determine if a point falls within a hexbin polygon. The problem with my initial query is that if
st_intersects returned false, then I didn’t get the polygon geometry at all. It was simply a hexagon shaped hole in my map. What I wanted to return, instead, was the geometry and then a
count of 0. So, when there is an intersection, return the polygon + the count (this is a common operation and many previous questions cover how to do this). But I also want to return the rows where
true and have the count (of points in the polygon) be 0. I can do this with these CTEs and a union:
with disjoint_table as ( select a.the_geom_webmercator, a.cartodb_id, 0 as count from hexbin_polygons_table a LEFT join points_table b on st_intersects(a.the_geom_webmercator, b.the_geom_webmercator) where b.the_geom_webmercator IS NULL ), intersect_table as ( select a.the_geom_webmercator, a.cartodb_id, count(b.the_geom_webmercator) from hexbin_polygons_table a join points_table b on st_contains(a.the_geom_webmercator, b.the_geom_webmercator) group by a.the_geom_webmercator, a.cartodb_id ) select * from disjoint_table union select * from intersect_table
disjoint_table part (left join where is NULL...etc.) comes from Paul Ramsey's answer here
This CTE +
union works but is very slow. Is there a simpler or better way to get this sort of thing?