Skip to content

Instantly share code, notes, and snippets.

@allenday
allenday / prolific-users.sql
Created Dec 27, 2019
Which users have contributed the most relations to the current version of OpenStreetMap?
View prolific-users.sql
SELECT username, COUNT(*) AS c
FROM
`bigquery-public-data.geo_openstreetmap.planet_relations` AS relations
GROUP BY username
ORDER BY c DESC
@allenday
allenday / fire-hydrants-globally-obsolete.sql
Last active Dec 27, 2019
Find all obsolete fire hydrants on the planet
View fire-hydrants-globally-obsolete.sql
SELECT *
FROM
`bigquery-public-data.geo_openstreetmap.history_nodes` AS node JOIN UNNEST(all_tags) AS tags
WHERE
(tags.key = 'emergency' AND tags.value = 'fire_hydrant')
AND id NOT IN (
SELECT id
FROM
`bigquery-public-data.geo_openstreetmap.planet_nodes` AS node JOIN UNNEST(all_tags) AS tags
WHERE
@allenday
allenday / changesets-in-singapore.sql
Created Dec 27, 2019
Find all OpenStreetMap changeset IDs that contain points in Singapore's geographical extent
View changesets-in-singapore.sql
WITH singapore AS (
SELECT ST_MAKEPOLYGON(ST_MAKELINE(
[ST_GEOGPOINT(103.6920359,1.1304753),ST_GEOGPOINT(104.0120359,1.1304753),
ST_GEOGPOINT(104.0120359,1.4504753),ST_GEOGPOINT(103.6920359,1.4504753)
]
)) AS boundingbox
)
SELECT DISTINCT changeset.id
FROM
View get_partitions.sql
CREATE OR REPLACE FUNCTION
`osm_planet.get_partitions`(name STRING)
RETURNS ARRAY<DATE> AS (
CASE
WHEN name="place-city" THEN ARRAY<DATE>["1970-01-01"]
WHEN name="place-town" THEN ARRAY<DATE>["1970-01-02"]
WHEN name="place-village" THEN ARRAY<DATE>["1970-01-03"]
WHEN name="place-hamlet" THEN ARRAY<DATE>["1970-01-04"]
WHEN name="place-national_capital" THEN ARRAY<DATE>["1970-01-05"]
WHEN name="place-suburb" THEN ARRAY<DATE>["1970-01-06"]
View top-osm-features.sql
SELECT
LOWER(tags.key) AS k
,LOWER(tags.value) AS v
,COUNT(*) AS c
FROM `bigquery-public-data.geo_openstreetmap.features` JOIN UNNEST (all_tags) AS tags
WHERE LOWER(key) NOT IN (
'source','import','created_by','restriction','attribution','note','operator','mapper','province','objtype','name','ref','wikidata','alt_name','fixme','name_1','int_name','int_ref','hgv','old_ref','old_name','official_name','import_uuid','to','from','description','denotation','brand','email','opening_hours','notas','tipo','rcn_ref'
)
AND key NOT LIKE '%:%'
AND key NOT LIKE '%"%'
View junk.csv
http://tf-models.arilot.org/static-tf-models/img/Abstract-Patterned_Blouse/img_00000049.jpg blouse
http://tf-models.arilot.org/static-tf-models/img/Abstract-Stripe_Fuzzy_Sweater/img_00000011.jpg sweater
View global-cities.csv
Region City
Asean Bangkok
Asean Hanoi
Asean Ho Chi Minh City
Asean Jakarta
Asean Kuala Lumpur
Asean Manila
Asean Phnom Penh
Asean Singapore
Asia Ahmedabad
@allenday
allenday / OSM-NYC-highways-to-buildings.sql
Created Oct 9, 2019
Highway length to building count ratio for NYC
View OSM-NYC-highways-to-buildings.sql
WITH country AS (
SELECT
osm_layers.name as osm_name,
osm_layers.all_tags AS osm_tags,
(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'admin_level') as admin_level,
osm_layers.geometry AS geometry
FROM `openstreetmap-public-data-dev.osm_planet.osm_layers` AS osm_layers
WHERE osm_layers.name='boundary-administrative'
AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'name' and tags.value = 'United States')
),
@allenday
allenday / OSM-NYC-differential-eatery-density.sql
Created Sep 25, 2019
Eatery density difference between two datasets
View OSM-NYC-differential-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='New York')
AND EXISTS(SELECT tags.value FROM UNNEST(all_tags) as tags WHERE tags.key = 'place' and tags.value='city')
View 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')
You can’t perform that action at this time.