Skip to content

Instantly share code, notes, and snippets.

@hankroark

hankroark/bigquery-r-example.R

Created March 23, 2019 19:48
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save hankroark/4537ef85f4b3cafe99f6a499b7a549ef to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save hankroark/4537ef85f4b3cafe99f6a499b7a549ef to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. hankroark renamed this gist Mar 23, 2019. 1 changed file with 0 additions and 0 deletions.
    0 bigquery-r-example → bigquery-r-example.R
    View file
    File renamed without changes.
  2. hankroark created this gist Mar 23, 2019.
    26 changes: 26 additions & 0 deletions bigquery-r-example
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,26 @@
    # 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)