OSM POI w/ details from BigQuery

osm_pois_from_bq.sql

WITH osm AS (
  SELECT 
  layer_code, 
  layer_class, 
  layer_name, 
  gdal_type as geography_type, 
  osm_id, 
  CASE 
    WHEN (tags.key = 'name') THEN tags.value
    ELSE ''
  END as name, 
  CASE 
    WHEN (tags.key = 'name:en') THEN tags.value
    ELSE ''
  END as name_en,
  CASE 
    WHEN (tags.key = 'capacity') THEN tags.value
    ELSE ''
  END as capacity,
  CASE 
    WHEN (tags.key = 'brand') THEN tags.value
    ELSE ''
  END as brand,
  geometry

FROM `bigquery-public-data.geo_openstreetmap.planet_layers`, 
      unnest(all_tags) as tags
WHERE layer_code BETWEEN 2000 AND 2999 AND osm_id	IS NOT NULL
AND layer_name NOT IN 
('pitch', 'bench', 'fire_hydrant', 'graveyard', 'waste_basket', 'tourist_guidepost', 'shelter', 'toilet', 
'drinking_water', 'memorial','hunting_stand', 'archaeological', 'archaeological_site', 'wayside_cross', 'picnic_site',
'fountain', 'camera_surveillance', 'ruins', 'camp_site', 'vending_machine','wayside_shrine','tourist_map','recycling_paper',
'wastewater_plant','recycling_glass','monument', 'chalet', 'recycling_clothes', 'emergency_phone', 'vending_parking', 
'emergency_access', 'water_works', 'vending_cigarette', 'tower_observation', 'alpine_hut', 'lighthouse', 'windmill', 
'fort', 'battlefield','recycling_metal', 'water_well')
)

SELECT osm.*, map.GEOUNIT as country, map.ISO_A2 as country_short 
FROM osm
JOIN `uc-atlas.maps_international.polygons_ne_10m_admin_0_countries` map ON ST_INTERSECTS(geometry, polygon)