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