Skip to content

Instantly share code, notes, and snippets.

@allenday

allenday/changesets-in-singapore.sql

Created December 27, 2019 05:09
Show Gist options
  • Save allenday/a92a9249a236e33c8186da925ef519d7 to your computer and use it in GitHub Desktop.
Save allenday/a92a9249a236e33c8186da925ef519d7 to your computer and use it in GitHub Desktop.
Download ZIP
Find all OpenStreetMap changeset IDs that contain points in Singapore's geographical extent
Raw
changesets-in-singapore.sql
WITH singapore AS (
SELECT ST_MAKEPOLYGON(ST_MAKELINE(
[ST_GEOGPOINT(103.6920359,1.1304753),ST_GEOGPOINT(104.0120359,1.1304753),
ST_GEOGPOINT(104.0120359,1.4504753),ST_GEOGPOINT(103.6920359,1.4504753)
]
)) AS boundingbox
)
SELECT DISTINCT changeset.id
FROM
`bigquery-public-data.geo_openstreetmap.history_changesets` AS changeset JOIN UNNEST(nodes) AS cnode,
`bigquery-public-data.geo_openstreetmap.history_nodes` AS nodes,
singapore
WHERE cnode = nodes.id
AND ST_INTERSECTS(singapore.boundingbox, nodes.geometry)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment