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/
.
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)
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)
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)
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)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.