Working with geospatial data  |  BigQuery  |  Google Cloud

PHOTO EMBED

Tue Feb 08 2022 23:19:04 GMT+0000 (UTC)

Saved by @qbui #sql

-- how many stations within 1 mile range of each zip code?
SELECT
    zip_code AS zip,
    ANY_VALUE(zip_code_geom) AS polygon,
    COUNT(*) AS bike_stations
FROM
    `bigquery-public-data.new_york.citibike_stations` AS bike_stations,
    `bigquery-public-data.geo_us_boundaries.zip_codes` AS zip_codes
WHERE ST_DWithin(
         zip_codes.zip_code_geom,
         ST_GeogPoint(bike_stations.longitude, bike_stations.latitude),
         1609.34)
GROUP BY zip
ORDER BY bike_stations DESC
content_copyCOPY

https://cloud.google.com/bigquery/docs/geospatial-data