An example of querying geospatial data from BigQuery using R

bigquery-r-example.R

# Do this first time
# install.packages('devtools') 
# devtools::install_github("rstats-db/bigrquery", ref = "master")

# Use your project ID here
project <- "your project id here" # put your Google Cloud project ID here, it will be something like project-123456

# Your sql here
# This use public data sets to get water-land ratio by zip code within
# 10 km of Seattle (zip code 98104)
sql <- "
SELECT *
  FROM (
    SELECT b.zip_code neighbor, b.city, b.county, ST_DISTANCE(a.zcta_geom, b.zcta_geom) distance
    , b.area_water_meters / b.area_land_meters water_land_ratio 
    
    FROM `bigquery-public-data.geo_us_boundaries.us_zip_codes` a,
    `bigquery-public-data.geo_us_boundaries.us_zip_codes` b
    WHERE a.zip_code != b.zip_code
    AND a.zip_code = '98104'
  )
WHERE distance < 10000 # 10 KM
ORDER BY water_land_ratio DESC"

# Execute the query and store the result
water_land_ratio <- bigrquery::query_exec(sql, project = project, use_legacy_sql = FALSE)