In walkerjeffd/myrwaR: Tools for the Mystic River Watershed Association

knitr::opts_chunk$set(collapse = FALSE, comment = "#>")

This document demonstrates how to use the myrwaR package to export data from the MyRWA Water Quality Database to csv files that can be imported into WQX/STORET.

There are three types of datasets that can be imported to WQX: Projects, Locations, and Results. For each of these three dataset types, myrwaR provides a corresponding wqx_<type>() function that extracts the data from the database, convert it to the format accepted by WQX, and validates the dataset against WQX criteria and allowed values (aka WQX domain values). Note that the WQX domain values are provided in the package source code in the folder inst/extdata/wqx_domain/.

Open Database Connection

In order to extract data from the water quality database, first we need to create a connection to the database:

library(myrwaR)
base_dir <- 'D:/Dropbox/Work/mystic/db'
db_path <- file.path(base_dir, "MysticDB_20160208.accdb")
ch <- db_connect(db_path)

Projects Table

The WQX Projects table is generated using the wqx_projects() function, which requires the database channel (ch) and a list of one or more project IDs (projects). For example, to export the baseline (Project.ID="BASE") project to WQX format.

projects <- wqx_projects(ch, projects='BASE')
str(projects)

This data frame can be saved to a csv file, which can then be imported to WQX:

write.csv(projects, file='projects.csv', row.names=FALSE)

Results Table

The WQX Results table is generated using the wqx_results() function, which requires the database channel (ch) and a list of one or more project IDs (projects). For example, to get the results for the baseline sampling program:

results <- wqx_results(ch, projects='BASE')
str(results)

The results table can then be validated using wqx_validate_results(). This function will check for missing columns, missing values (only for columns where missing values are not allowed), and whether any values are not part of the WQX domain dictionary.

wqx_validate_results(results)

If any errors occur, they will be reported as a Warning, and the message Validation Failed will be shown at the end. Let's set an invalid value to one of the columns and re-run the validation to see what an error looks like.

results_invalid <- results
results_invalid[1, "CharacteristicName"] <- "Moose"
wqx_validate_results(results_invalid)

The valid results table can be saved to a csv file and then imported to WQX (note that the na="" is important, otherwise R will insert "NA" into the file, which WQX will not accept):

write.csv(results, file='results.csv', na="", row.names=FALSE)

Locations Table

The WQX Locations Table is generated using the wqx_locations() function, which also requires the database channel (ch) and a list of location IDs (locations). The location IDs can be extracted using the unique() function on the MonitoringLocationID column of the results data frame.

locations <- wqx_locations(ch, locations=unique(results$MonitoringLocationID))
str(locations)

The locations table can be validated using wqx_validate_locations(), which works similarly to wqx_validate_results():

wqx_validate_locations(locations)

The locations table can then be saved to a csv file for importing to WQX (note again the na="" argument):

write.csv(locations, file='locations.csv', na="", row.names=FALSE)

walkerjeffd/myrwaR documentation built on May 3, 2019, 10:46 p.m.