/top-osm-features.sql
Last active Oct 29, 2019
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