Skip to content

Instantly share code, notes, and snippets.

@kpennell

kpennell/bigquerybuildings.sql

Created February 3, 2022 19:10
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kpennell/dc7e52d46d32e19a7cfb7c2a01fb1a37 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kpennell/dc7e52d46d32e19a7cfb7c2a01fb1a37 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. kpennell created this gist Feb 3, 2022.
    43 changes: 43 additions & 0 deletions bigquerybuildings.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,43 @@
    -- 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