ADO - Discharge hydrological datasets

#To set up the connection, the following two packages are required.

library(DBI)
library(RPostgres)

#Using the tidyverse in R, we can circumvent the direct usage of SQL language, and can use the more user-friendly dplyr syntax. For this we need these packages.

library(dplyr)
library(dbplyr)

#For data wrangling and visualization we will use these packages.

library(sf)
library(lubridate)
library(ggplot2)
library(mapview)

#Set up the connection
#The following sets up the connection to the SQL database. This is currently only possible within the ScientificNet. The parameters are copied from the documentation.

conn <- dbConnect(Postgres(), 
                  dbname = "climate_data", 
                  host = "10.7.18.68", 
                  port = 5432, 
                  user = "climate_user",
                  password = "meteo_data")
##################################
#List the available tables.

dbListTables(conn)

#The daily values are stored in the table stations_data.
tbl(conn, "stations_data")

#The climatologies (averages for the period 1981-2010) values are stored in the table climatologies.
tbl(conn, "climatologies")

#And finally the metadata with the station locations and more info on the processing in metadata.
tbl(conn, "metadata")

##########################
#Additional examples for create map and plots are available at: http://edp-doc.eurac.edu/cdb_doc/notebooks/Access-CDB-R-ubuntu.html
##########################