Skip to content

Instantly share code, notes, and snippets.

@kpennell
Created February 3, 2022 19:10
Show Gist options
  • Save kpennell/dc7e52d46d32e19a7cfb7c2a01fb1a37 to your computer and use it in GitHub Desktop.
Save kpennell/dc7e52d46d32e19a7cfb7c2a01fb1a37 to your computer and use it in GitHub Desktop.
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment