Query time series 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.

SELECT datavalue, res.resultid, var.variablenamecv, units.unitsname, valuedatetime, sf.samplingfeaturecode, meth.methodcode, acts.begindatetime, acts.enddatetime
FROM odm2.timeseriesresultvalues tsrv
INNER JOIN odm2.results res ON tsrv.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

See a summary of time series results in the database

ts_results %>%
  group_by(resultid, variablenamecv, samplingfeaturecode, 
           methodcode, begindatetime, enddatetime) %>%
  dplyr::summarise() %>%
  knitr::kable()

resultid	variablenamecv	samplingfeaturecode	methodcode	begindatetime	enddatetime
4431	Wind direction	QB	SonicAnemometer	2018-06-27 13:55:00	2018-06-30 16:55:00
4432	Wind speed	QB	SonicAnemometer	2018-06-27 13:55:00	2018-06-30 16:55:00
4433	Wind gust speed	QB	SonicAnemometer	2018-06-27 13:55:00	2018-06-30 16:55:00
4449	Wind direction	QB	SonicAnemometer	2018-06-30 16:55:00	2018-07-22 15:15:00
4450	Wind speed	QB	SonicAnemometer	2018-06-30 16:55:00	2018-07-22 15:15:00
4451	Wind gust speed	QB	SonicAnemometer	2018-06-30 16:55:00	2018-07-22 15:15:00

Plot

ggplot(ts_results, aes(x = valuedatetime, y = datavalue, color = variablenamecv)) +
  geom_line() +
  facet_wrap(facets = vars(variablenamecv, samplingfeaturecode), scales = "free_y", ncol = 1) +
  theme_bw() +
  theme(legend.position = "none")