In BWAM/stayCALM: Stay Calm when Applying a Consolidated Assessment Listing Methodology (CALM)

Introduction

This documentation describes how tables from the New York State Department of Environmental Conservation's (NYSDEC's) database are prepared for infroming assessments using the stayCALM R-package. Some of this work will not be necessary once the database is finalized. There will still be some amount of data preparation, but it should not be as extensive.

Preprocessing

Prepare the R Environment

Load the necessary R packages into the global environment.

library(tidyverse)
library(odbc)
library(DBI)

Upload Assessment Database Files

Establish connection to the Microsoft Access Assessment database maintained by Sarah Rickard. This requires that an ODBC connection be establish the machine executing this script (independent of R) and the use of the 32-bit version of R (check global settings).

con <- dbConnect(
        drv = odbc(),
        .connection_string = "Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=S:/!123/WIPWL_Main_2_6_20.accdb;"
      )

DBI::dbListTables(con)

tbl.vec <- c("tblWIPWL", "tlkCOUNTY", "tlkDRAIN_BASIN",
             "tlkTYPE", "tlk305B", "tblTMDL")

wipwl.df <- map(tbl.vec, function(tbl.i) {
  # print(tbl.i)
  temp.df <- dbReadTable(conn = con,
              name = tbl.i)
  if (any(names(temp.df) %in% "X305B")) {
   temp.df <- rename(temp.df, X305B_CODE = X305B) 
  }

  return(temp.df)
}) %>% 
  reduce(left_join) %>% 
  rename_all(tolower) %>% 
  rename(lgth_area_units = units,
         water_type = type) %>% 
  mutate_if(is.character, na_if, "") %>% 
  mutate(class = str_replace(class, "-S", "-Special"),
         class = tolower(class),
         class = case_when(
           class %in% "fp" ~ "a",
           class %in% "see blw" ~ NA_character_,
           TRUE ~ class
         ),
         # gl_basin = substr(huc_10, 1, 2) %in% "04"
         spatial_extent = ifelse(substr(huc_10, 1, 2) %in% "04",
                                 "great_lakes",
                                 "nys"),
         water_type = case_when(
           water_type %in% c("R", "E") ~ "flow",
           water_type %in% c("L", "G") ~ "pond",
           water_type %in% c("O") ~ "ocean",
           TRUE ~ "ERROR"
         )
         )

if (any(wipwl.df$water_type %in% "ERROR")) stop("water_type error")

Export WIPWL Data

With the usethis package, the Assessment data is exported as a .rda file making it easily accessible during the development and testing of the stayCALM package.

usethis::use_data(wipwl.df, overwrite = TRUE)

BWAM/stayCALM documentation built on May 21, 2020, 3:24 p.m.