Skip to content

Instantly share code, notes, and snippets.

@wriglz

wriglz/osm_lighthouses.sql

Created November 7, 2023 12:37
Show Gist options
  • Save wriglz/396e2f0dfcbcac6a4a079903c9fc2ef2 to your computer and use it in GitHub Desktop.
Save wriglz/396e2f0dfcbcac6a4a079903c9fc2ef2 to your computer and use it in GitHub Desktop.
Download ZIP
SQL to extract lighthouses from the BigQuery OSM dataset
Raw
osm_lighthouses.sql
SELECT
osm_id,
ST_CENTROID(geometry) AS geom,
(SELECT value FROM UNNEST(all_tags) WHERE key = 'name' ) AS name,
FROM
bigquery-public-data.geo_openstreetmap.planet_features
WHERE
('man_made', 'lighthouse') IN (
SELECT
(key, value)
FROM
UNNEST(all_tags))
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment