Map of Water-to_Land Ratio by Zip Code (within 50km of Seattle) - BigQuery, R, choroplethr

bq-r-map.R

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

library(tidyverse)

# Use your project ID here
project <- "bqbbq-235416" # put your project ID here

# 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 < 50000 # 50 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)

# Information about choroplethr package is at https://arilamstein.com/packages/
# I had to run 'brew install gdal udunits' to get the following to work
library("choroplethr")
# One Time install
# devtools::install_github('arilamstein/choroplethrZip@v1.5.0')

water_land_ratio %>% 
  mutate(region = neighbor, value = log1p(water_land_ratio)) %>%
  choroplethrZip::zip_choropleth(num_colors = 1, zip_zoom = water_land_ratio$neighbor,
                                 legend = "Log(1+Water-to-Land Ratio)")