How to do an OUTER join with a spatial join in BigQuery to be able to get a count value of 0 where there is no intersection?

I'm doing a fairly conventional point/polygon spatial join in order to determine the number of points that fall in each polygon. The result (from the left join) includes all of the polygons + the count where there is an intersection. But it doesn't return the polygon + 0 or null where there is no intersection. I'd like to still have the polygon geometry as it looks better on a map (vs. just a missing area).

 WITH    geoidandcount AS(    SELECT      a.geo_id,      COUNT(b.latitude) AS count    FROM      `polygon_dataset` a    LEFT OUTER JOIN -- ERROR      `point_dataset` b    ON      st_contains(a.the_geom,        ST_GEOGPOINT(b.longitude,          b.latitude))    GROUP BY      a.geo_id )  SELECT    a.*,    b.the_geom,    a.count,  FROM    geoidandcount a  JOIN    `polygon_dataset` b  USING    (geo_id)  

I had to do the CTE as you can't group by geography.

https://stackoverflow.com/questions/67391073/how-to-do-an-outer-join-with-a-spatial-join-in-bigquery-to-be-able-to-get-a-coun May 05, 2021 at 03:34AM