Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Eatery density in London
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
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.