Query soil moisture data

First, load the necessary libraries

library(RPostgreSQL)
library(dplyr)
library(ggplot2)

And make sure you are connected to the database

Read 1 item

Query database

Make a data frame of all time series data in database. This is an SQL chunk and the name of the output data frame is defined in the chunk option.

Soil data from site group “soil chambers” with the related feature code (wetland site code)

SELECT datavalue, res.resultid, var.variablenamecv, units.unitsname, valuedatetime, sf.samplingfeaturecode, meth.methodcode, rfsf.samplingfeaturecode AS rfcode, rf.relationshiptypecv
FROM odm2.measurementresultvalues mrv
INNER JOIN odm2.results res ON mrv.resultid = res.resultid
INNER JOIN odm2.variables var ON res.variableid = var.variableid
INNER JOIN odm2.featureactions fa ON res.featureactionid = fa.featureactionid
INNER JOIN odm2.samplingfeatures sf ON sf.samplingfeatureid = fa.samplingfeatureid
LEFT JOIN odm2.relatedfeatures rf ON rf.samplingfeatureid = sf.samplingfeatureid
LEFT JOIN odm2.samplingfeatures rfsf ON rfsf.samplingfeatureid = rf.relatedfeatureid
INNER JOIN odm2.actions acts ON acts.actionid = fa.actionid
INNER JOIN odm2.units units ON units.unitsid = res.unitsid
INNER JOIN odm2.methods meth ON meth.methodid = acts.methodid
LEFT JOIN odm2.samplingfeatureannotations sfa ON sfa.samplingfeatureid = sf.samplingfeatureid
LEFT JOIN odm2.annotations ann ON ann.annotationid = sfa.annotationid
WHERE res.sampledmediumcv = 'Soil' 
AND ann.annotationtypecv = 'Site group' AND ann.annotationtext = 'soil chambers'

All soil data

SELECT datavalue, res.resultid, var.variablenamecv, units.unitsname, valuedatetime, sf.samplingfeaturecode, meth.methodcode
FROM odm2.measurementresultvalues mrv
INNER JOIN odm2.results res ON mrv.resultid = res.resultid
INNER JOIN odm2.variables var ON res.variableid = var.variableid
INNER JOIN odm2.featureactions fa ON res.featureactionid = fa.featureactionid
INNER JOIN odm2.samplingfeatures sf ON sf.samplingfeatureid = fa.samplingfeatureid
INNER JOIN odm2.actions acts ON acts.actionid = fa.actionid
INNER JOIN odm2.units units ON units.unitsid = res.unitsid
INNER JOIN odm2.methods meth ON meth.methodid = acts.methodid
WHERE res.sampledmediumcv = 'Soil' 

Plot

scsm <- soil_results_wRF_sc
library(ggplot2)
library(dplyr)
library(forcats)
# pdf(file = "soil_moisture_transects.pdf", width = 6, height = 10)
scsm %>%
  dplyr::filter(variablenamecv == 'Volumetric water content') %>%
  mutate(samplingday = factor(lubridate::as_date(valuedatetime))) %>%
  mutate(samplingday = fct_collapse(samplingday, "2018-02-08" = c("2018-02-08", "2018-02-09"))) %>%
  group_by(samplingday, samplingfeaturecode, rfcode) %>%
  tidyr::replace_na(list(sd_value = 0)) %>%
  summarise(mean_value = mean(datavalue),
            sd_value = sd(datavalue, na.rm = TRUE)) %>%
ggplot(aes(x = samplingfeaturecode, y = mean_value, group = samplingday)) +
  geom_line(alpha = 0.5, aes(color = samplingday)) +
  geom_errorbar(aes(ymin = mean_value - sd_value, ymax = mean_value + sd_value), width = 0.1, alpha = 0.8) +
  geom_point(pch = 21, color = "black", aes(fill = samplingday), size = 3) +
  facet_wrap(facets = vars(rfcode), scales = "free_x") +
  theme_bw() +
  scale_fill_viridis_d() +
  scale_color_viridis_d()

# dev.off()

scsm <- soil_results_wRF_sc
library(ggplot2)
library(dplyr)
library(forcats)
# pdf(file = "soil_moisture_transects_v3.pdf", width = 5, height = 9)
scsm %>%
  dplyr::filter(variablenamecv == 'Volumetric water content') %>%
  mutate(samplingday = factor(lubridate::as_date(valuedatetime))) %>%
  mutate(samplingday = fct_collapse(samplingday, "2018-02-08" = c("2018-02-08", "2018-02-09"))) %>%
  group_by(samplingday, samplingfeaturecode, rfcode) %>%
  summarise(mean_value = mean(datavalue),
            sd_value = sd(datavalue, na.rm = TRUE)) %>%
  tidyr::replace_na(list(sd_value = 0)) %>%
  mutate(chamber_id = substr(samplingfeaturecode, 7,8)) %>%
ggplot(aes(x = samplingday, y = mean_value, group = chamber_id)) +
  # geom_line(aes(color = chamber_id)) +
  geom_errorbar(aes(ymin = mean_value - sd_value, ymax = mean_value + sd_value), width = 0.1, alpha = 0.8) +
  geom_point(pch = 21, color = "black", aes(fill = chamber_id), size = 3) +
  # facet_grid(rows = vars(rfcode), scales = "free_x") +
  facet_wrap(vars(rfcode), scales = "free_x") +
  theme_bw() +
  scale_fill_viridis_d() +
  scale_color_viridis_d() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1, size = 5)) +
  xlab("") + 
  ylab("Volumetric water content (%)")

# dev.off()