top OSM features

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 '%"%'
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