Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement