bigquerybuildings.sql

  -- Define bounding area of London
WITH
  bounding_area AS (
  SELECT
    geometry
  FROM
    `bigquery-public-data.geo_openstreetmap.planet_features`
  WHERE
    ('name',
      'Los Angeles County') IN (
    SELECT
      (key,
        value)
    FROM
      UNNEST(all_tags) ) -- los angeles
    AND ('boundary',
      'administrative') IN (
    SELECT
      (key,
        value)
    FROM
      UNNEST(all_tags) ) -- los angeles
  ORDER BY
    st_area(geometry) DESC
  LIMIT
    1 )
SELECT
  planet_features.osm_id,
  planet_features.geometry,
FROM
  `bigquery-public-data.geo_openstreetmap.planet_features` planet_features,
  bounding_area
WHERE
  ( ('building',
      'yes') IN (
    SELECT
      (key,
        value)
    FROM
      UNNEST(all_tags) ))
  AND ST_DWithin(bounding_area.geometry,
    planet_features.geometry,
    0)  -- Filter only features within bounding_area