Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Eatery density difference between two datasets
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='New York')
AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'place' and tags.value='city')
),
eateries AS (
SELECT
'osm' AS source,
ST_GEOHASH(ST_CENTROID(layers.geometry)) AS geohash
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
UNION ALL
SELECT
'foam' AS source,
geohash
FROM `blocklytics-data.foam_public.points_on_map` AS points
LEFT JOIN `blocklytics-data.foam_public.poi` AS data
USING (ipfs_hash)
JOIN UNNEST(tags) AS tags, city
WHERE tags = 'Food'
AND ST_DWITHIN(city.geometry, ST_GEOGPOINTFROMGEOHASH(geohash), 0)
),
eateries_agg AS (
SELECT
source,
ST_GEOHASH(ST_SNAPTOGRID(ST_GEOGPOINTFROMGEOHASH(geohash), 0.01)) AS geohash,
COUNT(1) as count_eateries
-- grid cell size is equal to 0.01 x 0.01 degree
FROM eateries
GROUP BY 1,2
),
eateries_agg_points AS (
SELECT
*,
ST_GEOGPOINTFROMGEOHASH(geohash) AS geometry
FROM eateries_agg
),
eateries_agg_cells AS (
SELECT
source,
geohash,
count_eateries AS c,
(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 eateries_agg_points
),
ratio AS (
SELECT o.geohash, o.c AS oc, f.c AS fc, o.geometry
FROM eateries_agg_cells AS o, eateries_agg_cells AS f
WHERE o.geohash = f.geohash AND o.source = 'osm' AND f.source = 'foam'
UNION ALL
SELECT o.geohash, o.c AS oc, 0 AS fc, o.geometry
FROM eateries_agg_cells AS o
WHERE o.geohash NOT IN (SELECT geohash FROM eateries_agg_cells WHERE source = 'foam')
UNION ALL
SELECT f.geohash, 0 AS oc, f.c AS fc, f.geometry
FROM eateries_agg_cells AS f
WHERE f.geohash NOT IN (SELECT geohash FROM eateries_agg_cells WHERE source = 'osm')
)
SELECT oc,fc,((fc+1)/(oc+1)) AS ratio, log10((fc+1)/(oc+1)) AS logratio, geohash, geometry FROM ratio
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.