Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
top OSM features
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 '%"%'
AND LOWER(value) NOT IN ('yes','no')
AND value NOT LIKE '%:%'
AND SAFE_CAST(value AS NUMERIC) IS NULL
GROUP BY
key
,value
ORDER BY c DESC
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.