Find all OpenStreetMap changeset IDs that contain points in Singapore's geographical extent
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