big_query_spatial_join.sql

WITH
  buildings AS (
  SELECT
    id,
    latitude,
    longitude,
    username,
    osm_timestamp,
  FROM
    `bigquery-public-data.geo_openstreetmap.planet_nodes`
  WHERE
    'building' IN (
    SELECT
      key
    FROM
      UNNEST(all_tags)) )
SELECT
  COUNT(b.id),
  a.geoid
FROM
  `carto-do-public-data.carto.geography_usa_blockgroup_2019` a
JOIN
  buildings b
ON
  st_intersects(st_geogpoint(b.longitude,
      b.latitude),
    a.geom)
GROUP BY
  a.geoid