Eatery density in London

OSM-LON-eatery-density.sql

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 `openstreetmap-public-data-dev.osm_planet.osm_layers_partitions` AS layers
	WHERE layers.partnum = `openstreetmap-public-data-dev.osm_planet.name2partnum`('boundary-administrative')
		AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'name' and tags.value='London')
		AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'place' and tags.value='city')
),
city_eateries AS (
	SELECT
		ST_GEOHASH(ST_CENTROID(layers.geometry)) AS eatery_geohash,
		layers.*
	FROM `openstreetmap-public-data-dev.osm_planet.osm_layers_partitions` AS layers JOIN UNNEST(all_tags) AS tags, city
	WHERE layers.partnum IN (
      `openstreetmap-public-data-dev.osm_planet.name2partnum`('amenity-biergarten'),
      `openstreetmap-public-data-dev.osm_planet.name2partnum`('amenity-cafe'),
      `openstreetmap-public-data-dev.osm_planet.name2partnum`('amenity-fast_food'),
      `openstreetmap-public-data-dev.osm_planet.name2partnum`('amenity-food_court'),
      `openstreetmap-public-data-dev.osm_planet.name2partnum`('amenity-market_place'),
      `openstreetmap-public-data-dev.osm_planet.name2partnum`('amenity-pub'),
      `openstreetmap-public-data-dev.osm_planet.name2partnum`('amenity-restaurant'),
      `openstreetmap-public-data-dev.osm_planet.name2partnum`('amenity-vending_machine')
    )
    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_eateries_agg AS (
        SELECT
                count(1) as count_eateries,
                -- grid cell size is equal to 0.01 x 0.01 degree
                ST_GEOHASH(ST_SNAPTOGRID(ST_GEOGPOINTFROMGEOHASH(eatery_geohash), 0.01)) AS geohash
        FROM city_eateries
        GROUP BY 2
),
city_eateries_agg_points AS (
        SELECT
          *,
          ST_GEOGPOINTFROMGEOHASH(geohash) AS geometry
        FROM city_eateries_agg
),
city_eateries_agg_cells AS (
        SELECT
                geohash,
                count_eateries,
                (SELECT
                        ST_MAKEPOLYGON(ST_MAKELINE(ARRAY_AGG(geom)))
                        FROM UNNEST(ARRAY[
                                ST_GEOGPOINT(ST_X(geometry)-0.25/50, ST_Y(geometry)-0.25/50),
                                ST_GEOGPOINT(ST_X(geometry)-0.25/50, ST_Y(geometry)+0.25/50),
                                ST_GEOGPOINT(ST_X(geometry)+0.25/50, ST_Y(geometry)+0.25/50),
                                ST_GEOGPOINT(ST_X(geometry)+0.25/50, ST_Y(geometry)-0.25/50)
                        ]) as geom
                ) as geometry
        FROM city_eateries_agg_points
)
SELECT * FROM city_eateries_agg_cells