Skip to content

Instantly share code, notes, and snippets.

@janbenetka
Created October 25, 2021 07:49
Show Gist options
  • Save janbenetka/0fab433c95ebd971ad4a59ef044be260 to your computer and use it in GitHub Desktop.
Save janbenetka/0fab433c95ebd971ad4a59ef044be260 to your computer and use it in GitHub Desktop.
OSM POI w/ details from BigQuery
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment