Insert soil moisture data, v2

This is how to insert new soil moisture data from the TDR probe into the database. Click “Code” at the top of this page to download an rmarkdown version of this file!

Prepare data

Make one data frame with the data to upload where each row is data from one point without shifts in the x, y, or z location. Necessary columns are:

• Timestamp column with datetime formatted as YYYY-MM-DD HH:MM:SS
• One column for each of the variables measured
• A column with site code names

files <- list.files("../../Delmarva/data/sm", full.names = TRUE)

files

[1] "../../Delmarva/data/sm/Field_measurements - SOIL_field_measurements.csv"
[2] "../../Delmarva/data/sm/Soil_Moisture - July2018.csv"                    
[3] "../../Delmarva/data/sm/SoilMoisture - June2018.csv"                     

sm <- readr::read_csv(file = files[2])

Parsed with column specification:
cols(
  Date = col_character(),
  Time = col_time(format = ""),
  Site = col_character(),
  Location = col_character(),
  VWC_percent = col_double(),
  soil_EC_mscm = col_double(),
  soil_temp_C = col_double()
)

Make a Timestamp column with properly formatted dates (YYYY-MM-DD HH:MM:SS). This can be a character column or use POSIXct if you need to specify a timezone other than your computer’s current time.

sm$Timestamp <- strptime(paste(sm$Date, sm$Time), 
                         format = "%B %d, %Y %H:%M:%S", tz = "")
sm$Timestamp <- as.POSIXct(sm$Timestamp)

Make a site column with site code names

sm$site <- paste(sm$Site, sm$Location)
sm$site <- sub(pattern = "SC ", "SC-", sm$site)

Any new site names will get added to the sampling features table. If you expect that all your sites should be in the database already, use check_samplingfeaturecodes() to make sure. This function will return any site names that are not in the database. You will have to be connected to the database (see below) for it to work.

check_samplingfeaturecodes(new_codes = sm$site, db)

Date	Time	Site	Location	VWC_percent	soil_EC_mscm	soil_temp_C	Timestamp	site
July 23, 2018	08:05:00	QB	SC-E	15.8	0.08	26.0	2018-07-23 08:05:00	QB SC-E
July 23, 2018	08:05:00	QB	SC-E	16.9	0.03	25.7	2018-07-23 08:05:00	QB SC-E
July 23, 2018	08:05:00	QB	SC-E	18.0	0.08	25.7	2018-07-23 08:05:00	QB SC-E
July 23, 2018	08:08:00	QB	SC-D	38.0	0.00	25.6	2018-07-23 08:08:00	QB SC-D
July 23, 2018	08:08:00	QB	SC-D	31.1	0.02	25.6	2018-07-23 08:08:00	QB SC-D
July 23, 2018	08:08:00	QB	SC-D	24.9	0.00	25.6	2018-07-23 08:08:00	QB SC-D

Make sure there are no NA data values. Your data is not ready to upload if it contains NAs! If you have missing values, convert them to a numeric value (such as -9999) and specify a ‘nodatavalue’ when making the vars_list.

names(sm)[-1] %>%
  purrr::map_lgl(function(x) any(is.na(sm[[x]])))

[1] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE

Upload

Then load rodm2 and connect to the database

devtools::install_github("khondula/rodm2")

* installing *source* package ‘rodm2’ ...
** R
** inst
** byte-compile and prepare package for lazy loading
** help
*** installing help indices
*** copying figures
** building package indices
** installing vignettes
** testing if installed package can be loaded
* DONE (rodm2)

library(rodm2)

Read 1 item

vars list

Make a list with the necessary information about each variable with the following format:

list("Variable1 Name" = list(column = "ColumnName", units = "Units Name"),
     "Variable2 Name" = list(column = "ColumnName", units = "Units Name"))

If the column name is the exact name of the variable from the controlled vocabulary (i.e. with spaces and correct capitalization), column does not need to be specified. If there are columns in your data that have data quality codes or censor codes, those should be specified here as qualitycodecol and censorcodecol respectively. You can also specify a variabletypecv, variabledefinition and nodatavalue for variables that haven’t been entered to the database before.

Variable names and units names need to be from the controlled vocabularies. To see these options, use the get_cv_terms() function.

get_cv_terms("variablename")
get_cv_terms("units")

vars_list <- list("Volumetric water content" = 
                    list(column ="VWC_percent", units = "Percent"),
                   "Bulk electrical conductivity" = 
                    list(column = "soil_EC_mscm", units = "Millisiemen per Centimeter"),
                   "Temperature" = 
                    list(column = "soil_temp_C", units = "Degree Celsius"))

upload measurements function

Use db_insert_results_m() to upload the data. The arguments actionby and equipment_name are optional. For processed data, specify a value for processinglevel (the default is “Raw data”).

db_insert_results_m(db = db,
                     datavalues = sm,
                     method = "soilmoistureTDR",
                     site_code_col = "site",
                     variables = vars_list,
                     actionby = "Kelly",
                     sampledmedium = "Soil")

Helper functions

The method and site code will be added to the database if they do not already exist. If the values supplied for actionby or equipment_name do not already exist in the database, you will also need to supply the required arguments to db_descrbie_person() or db_describe_equipment().

If you want to see if a site code exists in the database already, use check_samplingfeaturecodes to returns the names of any sites NOT in the database compared to a given vector, or get_site_names_like() for approximate string matching for sites that ARE in the database.

check_samplingfeaturecodes(new_codes = "ND", db = db)

get_site_names_like(x = "QB well", db = db)

To see the current methods, people, or equipment in the database, use db_get_%() functions:

db_get_equipment(db)
db_get_people(db)
db_get_methods(db)