Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How may buildings are within 100m of Central Park, New York?
WITH city AS (
SELECT
layers.name as osm_name,
layers.all_tags AS osm_tags,
(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'admin_level') as admin_level,
layers.geometry AS geometry
FROM `bigquery-public-data.geo_openstreetmap.layers` AS layers
WHERE layers.partnum = `bigquery-public-data.geo_openstreetmap.name2partnum`('boundary-administrative')
AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'name' and tags.value='New York')
AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'place' and tags.value='city')
),
city_buildings AS (
SELECT
layers.*
FROM `bigquery-public-data.geo_openstreetmap.layers` AS layers, city
WHERE layers.partnum = `bigquery-public-data.geo_openstreetmap.name2partnum`('building')
AND ST_DWITHIN(city.geometry, layers.geometry, 0)
-- ignore incorrect geometries with wrong orientation (see GeoJSON RFC 7946)
AND ST_AREA(layers.geometry) <= 1E10
),
city_parks AS (
SELECT
layers.*
FROM `bigquery-public-data.geo_openstreetmap.layers` AS layers JOIN UNNEST(all_tags) AS tags, city
WHERE layers.partnum = `bigquery-public-data.geo_openstreetmap.name2partnum`('leisure-park')
AND tags.value = 'Central Park'
AND ST_DWITHIN(city.geometry, layers.geometry, 0)
-- ignore incorrect geometries with wrong orientation (see GeoJSON RFC 7946)
AND ST_AREA(layers.geometry) <= 1E10
),
city_buildings_parks AS (
SELECT
-- distance histogram bin size is equal to 30 meters
30*round(min(ST_DISTANCE(city_buildings.geometry, city_parks.geometry))/30) as distance_park,
ST_GEOHASH(ST_CENTROID(city_buildings.geometry)) AS building_geohash
FROM city_buildings, city_parks
GROUP BY 2
)
SELECT COUNT(building_geohash) FROM city_buildings_parks WHERE distance_park <= 100
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.