<!DOCTYPE html>
00-exploration-of-data
Installation
Install the present package and use its core functions
remotes::install_github('inrae/diades.atlas')
Packages
library(dplyr)
library(DBI)
library(ggplot2)
library(RPostgres)
library(diades.atlas)
# pkgload::load_all()
You can use this package without opening the Shiny application
Connect to the database
se <- new.env()
conn_eurodiad <- dbConnect(
RPostgres::Postgres(),
dbname = 'eurodiad',
host = '<your-host>',
port = 5432,
user = '<your-username>',
password = rstudioapi::askForPassword("Database password"))
session$userData$con <-conn_eurodiad
# For ThinkR only
se <- new.env()
conn_eurodiad <- connect(session = se)
DBI::dbListTables(conn_eurodiad)
## [1] "abundance_level"
## [2] "basin"
## [3] "basin_outlet"
## [4] "casestudy_basin"
## [5] "category"
## [6] "cices"
## [7] "climatic_model"
## [8] "hydiadparameter"
## [9] "ices_area"
## [10] "ices_grass"
## [11] "ices_rectangle"
## [12] "iucn_classification"
## [13] "iucn_level"
## [14] "lang85_85"
## [15] "species"
## [16] "species_rectangle"
## [17] "v_abundance"
## [18] "v_maxvalue_es"
## [19] "v_hybrid_model"
## [20] "v_hybrid_model_mavg"
## [21] "v_ices_geom"
## [22] "v_occurence"
## [23] "v_species_ices_occurence"
## [24] "abundance"
## [25] "casestudy"
## [26] "ecosystemic_services"
## [27] "esvalue"
## [28] "hybrid_model_result"
## [29] "locale"
## [30] "outlet_distance"
## [31] "species_division"
## [32] "species_iucn"
## [33] "subcategory"
## [34] "v_basin"
## [35] "v_ecosystemic_services"
## [36] "v_iucn"Explore data
It is recommended to use tbl() instead of dbGetQuery() to connect to a table in the database.tbl() does not download the table, it only connect to the database and let it run the query. This means that if you run a filter() or a mutate() after that, the database executes it, not R, which is more efficient.
However, at some point, some of your {tidyverse} operations can not be realised or translated in SQL, hence, you need to download the resulting dataset using collect(). Then you can continue as usual.
For instance.
-
dbGetQuery()download data in R-
filter()is executed by R in your R session
-
# Create a function
get_data_dbi <- function(conn_eurodiad, species_id, scenario) {
query <- "SELECT * FROM diadesatlas.v_hybrid_model"
dbGetQuery(conn_eurodiad, query) %>%
filter(species_id %in% !!species_id,
climatic_scenario %in% !!scenario)
}
# Use it
get_data_dbi(conn_eurodiad,
species_id = c(6),
scenario = 'rcp85')
## hybrid_model_result_id basin_id species_id
## 1 1395745 1 6
## 2 1395746 1 6
## 3 1395747 1 6
## 4 1395748 1 6
## 5 1395749 1 6
## 6 1395750 1 6
## 7 1395751 1 6
## 8 1395752 1 6
## 9 1395753 1 6
## 10 1395754 1 6
## 11 1395755 1 6
## 12 1395756 1 6
## 13 1395757 1 6
## 14 1395758 1 6
## 15 1395759 1 6
## 16 1395760 1 6
## 17 1395761 1 6
## 18 1395762 1 6
## 19 1395763 1 6
## 20 1395764 1 6
## 21 1395765 1 6
## 22 1395766 1 6
## 23 1395767 1 6
## 24 1395768 1 6
## 25 1395769 1 6
## 26 1395770 1 6
## 27 1395771 1 6
## 28 1395772 1 6
## 29 1395773 1 6
## 30 1395774 1 6
## 31 1395775 1 6
## 32 1395776 1 6
## 33 1395777 1 6
## 34 1395778 1 6
## 35 1395779 1 6
## 36 1395780 1 6
## 37 1395781 1 6
## 38 1395782 1 6
## 39 1395783 1 6
## 40 1395784 1 6
## 41 1395785 1 6
## 42 1395786 1 6
## 43 1395787 1 6
## 44 1395788 1 6
## 45 1395789 1 6
## 46 1395790 1 6
## 47 1395791 1 6
## 48 1395792 1 6
## 49 1395793 1 6
## 50 1395794 1 6
## 51 1395795 1 6
## 52 1395796 1 6
## 53 1395797 1 6
## 54 1395798 1 6
## 55 1395799 1 6
## 56 1395800 1 6
## 57 1395801 1 6
## 58 1395802 1 6
## climatic_model_id year nit hsi
## 1 2 1951 568.905820 0.24424759
## 2 2 1952 485.922085 0.32226324
## 3 2 1953 429.942259 0.19459266
## 4 2 1954 383.999570 0.24571498
## 5 2 1955 297.920901 0.08498220
## 6 2 1956 208.892845 0.23510293
## 7 2 1957 140.493131 0.32735810
## 8 2 1958 100.724190 0.41504563
## 9 2 1959 66.924378 0.24550794
## 10 2 1960 36.807523 0.37964568
## 11 2 1961 15.134462 0.27040872
## 12 2 1962 5.429765 0.24577305
## 13 2 1963 0.000000 0.22412295
## 14 2 1964 0.000000 0.25041559
## 15 2 1965 0.000000 0.32038514
## 16 2 1966 0.000000 0.25260188
## 17 2 1967 0.000000 0.36233798
## 18 2 1968 0.000000 0.26426540
## 19 2 1969 0.000000 0.32815258
## 20 2 1970 0.000000 0.07997118
## 21 2 1971 0.000000 0.32295007
## 22 2 1972 0.000000 0.26076233
## 23 2 1973 0.000000 0.32594177
## 24 2 1974 0.000000 0.32919022
## 25 2 1975 0.000000 0.33330411
## 26 2 1976 0.000000 0.07650311
## 27 2 1977 0.000000 0.12106809
## 28 2 1978 0.000000 0.24494783
## 29 2 1979 0.000000 0.26687631
## 30 2 1980 0.000000 0.24788790
## 31 2 1981 0.000000 0.25248367
## 32 2 1982 0.000000 0.05853723
## 33 2 1983 0.000000 0.43384268
## 34 2 1984 0.000000 0.24605194
## 35 2 1985 0.000000 0.32409335
## 36 2 1986 0.000000 0.30049253
## 37 2 1987 0.000000 0.31984017
## 38 2 1988 0.000000 0.24944539
## 39 2 1989 0.000000 0.26567151
## 40 2 1990 0.000000 0.23825317
## 41 2 1991 0.000000 0.32738422
## 42 2 1992 0.000000 0.32538706
## 43 2 1993 0.000000 0.31455448
## 44 2 1994 0.000000 0.25445784
## 45 2 1995 0.000000 0.32881698
## 46 2 1996 0.000000 0.40264379
## 47 2 1997 0.000000 0.32566102
## 48 2 1998 0.000000 0.35783520
## 49 2 1999 0.000000 0.37290455
## 50 2 2000 0.000000 0.33744471
## 51 2 2001 0.000000 0.26335707
## 52 2 2002 0.000000 0.17640363
## 53 2 2003 0.000000 0.35140068
## 54 2 2004 0.000000 0.29239221
## 55 2 2005 0.000000 0.18490081
## 56 2 2006 0.000000 0.25068678
## 57 2 2007 0.000000 0.25083582
## 58 2 2008 0.000000 0.12043068
## saturation_rate climatic_scenario
## 1 0.269749152 rcp85
## 2 0.174624776 rcp85
## 3 0.255878434 rcp85
## 4 0.180987713 rcp85
## 5 0.405997180 rcp85
## 6 0.102900033 rcp85
## 7 0.049702931 rcp85
## 8 0.028105281 rcp85
## 9 0.031569580 rcp85
## 10 0.011228148 rcp85
## 11 0.006481810 rcp85
## 12 0.002558567 rcp85
## 13 0.000000000 rcp85
## 14 0.000000000 rcp85
## 15 0.000000000 rcp85
## 16 0.000000000 rcp85
## 17 0.000000000 rcp85
## 18 0.000000000 rcp85
## 19 0.000000000 rcp85
## 20 0.000000000 rcp85
## 21 0.000000000 rcp85
## 22 0.000000000 rcp85
## 23 0.000000000 rcp85
## 24 0.000000000 rcp85
## 25 0.000000000 rcp85
## 26 0.000000000 rcp85
## 27 0.000000000 rcp85
## 28 0.000000000 rcp85
## 29 0.000000000 rcp85
## 30 0.000000000 rcp85
## 31 0.000000000 rcp85
## 32 0.000000000 rcp85
## 33 0.000000000 rcp85
## 34 0.000000000 rcp85
## 35 0.000000000 rcp85
## 36 0.000000000 rcp85
## 37 0.000000000 rcp85
## 38 0.000000000 rcp85
## 39 0.000000000 rcp85
## 40 0.000000000 rcp85
## 41 0.000000000 rcp85
## 42 0.000000000 rcp85
## 43 0.000000000 rcp85
## 44 0.000000000 rcp85
## 45 0.000000000 rcp85
## 46 0.000000000 rcp85
## 47 0.000000000 rcp85
## 48 0.000000000 rcp85
## 49 0.000000000 rcp85
## 50 0.000000000 rcp85
## 51 0.000000000 rcp85
## 52 0.000000000 rcp85
## 53 0.000000000 rcp85
## 54 0.000000000 rcp85
## 55 0.000000000 rcp85
## 56 0.000000000 rcp85
## 57 0.000000000 rcp85
## 58 0.000000000 rcp85
## climatic_model_code oceanic_model
## 1 cnrmcm5 CNRM-CM5
## 2 cnrmcm5 CNRM-CM5
## 3 cnrmcm5 CNRM-CM5
## 4 cnrmcm5 CNRM-CM5
## 5 cnrmcm5 CNRM-CM5
## 6 cnrmcm5 CNRM-CM5
## 7 cnrmcm5 CNRM-CM5
## 8 cnrmcm5 CNRM-CM5
## 9 cnrmcm5 CNRM-CM5
## 10 cnrmcm5 CNRM-CM5
## 11 cnrmcm5 CNRM-CM5
## 12 cnrmcm5 CNRM-CM5
## 13 cnrmcm5 CNRM-CM5
## 14 cnrmcm5 CNRM-CM5
## 15 cnrmcm5 CNRM-CM5
## 16 cnrmcm5 CNRM-CM5
## 17 cnrmcm5 CNRM-CM5
## 18 cnrmcm5 CNRM-CM5
## 19 cnrmcm5 CNRM-CM5
## 20 cnrmcm5 CNRM-CM5
## 21 cnrmcm5 CNRM-CM5
## 22 cnrmcm5 CNRM-CM5
## 23 cnrmcm5 CNRM-CM5
## 24 cnrmcm5 CNRM-CM5
## 25 cnrmcm5 CNRM-CM5
## 26 cnrmcm5 CNRM-CM5
## 27 cnrmcm5 CNRM-CM5
## 28 cnrmcm5 CNRM-CM5
## 29 cnrmcm5 CNRM-CM5
## 30 cnrmcm5 CNRM-CM5
## 31 cnrmcm5 CNRM-CM5
## 32 cnrmcm5 CNRM-CM5
## 33 cnrmcm5 CNRM-CM5
## 34 cnrmcm5 CNRM-CM5
## 35 cnrmcm5 CNRM-CM5
## 36 cnrmcm5 CNRM-CM5
## 37 cnrmcm5 CNRM-CM5
## 38 cnrmcm5 CNRM-CM5
## 39 cnrmcm5 CNRM-CM5
## 40 cnrmcm5 CNRM-CM5
## 41 cnrmcm5 CNRM-CM5
## 42 cnrmcm5 CNRM-CM5
## 43 cnrmcm5 CNRM-CM5
## 44 cnrmcm5 CNRM-CM5
## 45 cnrmcm5 CNRM-CM5
## 46 cnrmcm5 CNRM-CM5
## 47 cnrmcm5 CNRM-CM5
## 48 cnrmcm5 CNRM-CM5
## 49 cnrmcm5 CNRM-CM5
## 50 cnrmcm5 CNRM-CM5
## 51 cnrmcm5 CNRM-CM5
## 52 cnrmcm5 CNRM-CM5
## 53 cnrmcm5 CNRM-CM5
## 54 cnrmcm5 CNRM-CM5
## 55 cnrmcm5 CNRM-CM5
## 56 cnrmcm5 CNRM-CM5
## 57 cnrmcm5 CNRM-CM5
## 58 cnrmcm5 CNRM-CM5
## atmospheric_model climatic_model_hm basin_name
## 1 CNRM-CERFACS-CNRM-CM5 cn Aa
## 2 CNRM-CERFACS-CNRM-CM5 cn Aa
## 3 CNRM-CERFACS-CNRM-CM5 cn Aa
## 4 CNRM-CERFACS-CNRM-CM5 cn Aa
## 5 CNRM-CERFACS-CNRM-CM5 cn Aa
## 6 CNRM-CERFACS-CNRM-CM5 cn Aa
## 7 CNRM-CERFACS-CNRM-CM5 cn Aa
## 8 CNRM-CERFACS-CNRM-CM5 cn Aa
## 9 CNRM-CERFACS-CNRM-CM5 cn Aa
## 10 CNRM-CERFACS-CNRM-CM5 cn Aa
## 11 CNRM-CERFACS-CNRM-CM5 cn Aa
## 12 CNRM-CERFACS-CNRM-CM5 cn Aa
## 13 CNRM-CERFACS-CNRM-CM5 cn Aa
## 14 CNRM-CERFACS-CNRM-CM5 cn Aa
## 15 CNRM-CERFACS-CNRM-CM5 cn Aa
## 16 CNRM-CERFACS-CNRM-CM5 cn Aa
## 17 CNRM-CERFACS-CNRM-CM5 cn Aa
## 18 CNRM-CERFACS-CNRM-CM5 cn Aa
## 19 CNRM-CERFACS-CNRM-CM5 cn Aa
## 20 CNRM-CERFACS-CNRM-CM5 cn Aa
## 21 CNRM-CERFACS-CNRM-CM5 cn Aa
## 22 CNRM-CERFACS-CNRM-CM5 cn Aa
## 23 CNRM-CERFACS-CNRM-CM5 cn Aa
## 24 CNRM-CERFACS-CNRM-CM5 cn Aa
## 25 CNRM-CERFACS-CNRM-CM5 cn Aa
## 26 CNRM-CERFACS-CNRM-CM5 cn Aa
## 27 CNRM-CERFACS-CNRM-CM5 cn Aa
## 28 CNRM-CERFACS-CNRM-CM5 cn Aa
## 29 CNRM-CERFACS-CNRM-CM5 cn Aa
## 30 CNRM-CERFACS-CNRM-CM5 cn Aa
## 31 CNRM-CERFACS-CNRM-CM5 cn Aa
## 32 CNRM-CERFACS-CNRM-CM5 cn Aa
## 33 CNRM-CERFACS-CNRM-CM5 cn Aa
## 34 CNRM-CERFACS-CNRM-CM5 cn Aa
## 35 CNRM-CERFACS-CNRM-CM5 cn Aa
## 36 CNRM-CERFACS-CNRM-CM5 cn Aa
## 37 CNRM-CERFACS-CNRM-CM5 cn Aa
## 38 CNRM-CERFACS-CNRM-CM5 cn Aa
## 39 CNRM-CERFACS-CNRM-CM5 cn Aa
## 40 CNRM-CERFACS-CNRM-CM5 cn Aa
## 41 CNRM-CERFACS-CNRM-CM5 cn Aa
## 42 CNRM-CERFACS-CNRM-CM5 cn Aa
## 43 CNRM-CERFACS-CNRM-CM5 cn Aa
## 44 CNRM-CERFACS-CNRM-CM5 cn Aa
## 45 CNRM-CERFACS-CNRM-CM5 cn Aa
## 46 CNRM-CERFACS-CNRM-CM5 cn Aa
## 47 CNRM-CERFACS-CNRM-CM5 cn Aa
## 48 CNRM-CERFACS-CNRM-CM5 cn Aa
## 49 CNRM-CERFACS-CNRM-CM5 cn Aa
## 50 CNRM-CERFACS-CNRM-CM5 cn Aa
## 51 CNRM-CERFACS-CNRM-CM5 cn Aa
## 52 CNRM-CERFACS-CNRM-CM5 cn Aa
## 53 CNRM-CERFACS-CNRM-CM5 cn Aa
## 54 CNRM-CERFACS-CNRM-CM5 cn Aa
## 55 CNRM-CERFACS-CNRM-CM5 cn Aa
## 56 CNRM-CERFACS-CNRM-CM5 cn Aa
## 57 CNRM-CERFACS-CNRM-CM5 cn Aa
## 58 CNRM-CERFACS-CNRM-CM5 cn Aa
## fish_name latin_name simplified_name
## 1 Allis shad Alosa alosa A. alosa
## 2 Allis shad Alosa alosa A. alosa
## 3 Allis shad Alosa alosa A. alosa
## 4 Allis shad Alosa alosa A. alosa
## 5 Allis shad Alosa alosa A. alosa
## 6 Allis shad Alosa alosa A. alosa
## 7 Allis shad Alosa alosa A. alosa
## 8 Allis shad Alosa alosa A. alosa
## 9 Allis shad Alosa alosa A. alosa
## 10 Allis shad Alosa alosa A. alosa
## 11 Allis shad Alosa alosa A. alosa
## 12 Allis shad Alosa alosa A. alosa
## 13 Allis shad Alosa alosa A. alosa
## 14 Allis shad Alosa alosa A. alosa
## 15 Allis shad Alosa alosa A. alosa
## 16 Allis shad Alosa alosa A. alosa
## 17 Allis shad Alosa alosa A. alosa
## 18 Allis shad Alosa alosa A. alosa
## 19 Allis shad Alosa alosa A. alosa
## 20 Allis shad Alosa alosa A. alosa
## 21 Allis shad Alosa alosa A. alosa
## 22 Allis shad Alosa alosa A. alosa
## 23 Allis shad Alosa alosa A. alosa
## 24 Allis shad Alosa alosa A. alosa
## 25 Allis shad Alosa alosa A. alosa
## 26 Allis shad Alosa alosa A. alosa
## 27 Allis shad Alosa alosa A. alosa
## 28 Allis shad Alosa alosa A. alosa
## 29 Allis shad Alosa alosa A. alosa
## 30 Allis shad Alosa alosa A. alosa
## 31 Allis shad Alosa alosa A. alosa
## 32 Allis shad Alosa alosa A. alosa
## 33 Allis shad Alosa alosa A. alosa
## 34 Allis shad Alosa alosa A. alosa
## 35 Allis shad Alosa alosa A. alosa
## 36 Allis shad Alosa alosa A. alosa
## 37 Allis shad Alosa alosa A. alosa
## 38 Allis shad Alosa alosa A. alosa
## 39 Allis shad Alosa alosa A. alosa
## 40 Allis shad Alosa alosa A. alosa
## 41 Allis shad Alosa alosa A. alosa
## 42 Allis shad Alosa alosa A. alosa
## 43 Allis shad Alosa alosa A. alosa
## 44 Allis shad Alosa alosa A. alosa
## 45 Allis shad Alosa alosa A. alosa
## 46 Allis shad Alosa alosa A. alosa
## 47 Allis shad Alosa alosa A. alosa
## 48 Allis shad Alosa alosa A. alosa
## 49 Allis shad Alosa alosa A. alosa
## 50 Allis shad Alosa alosa A. alosa
## 51 Allis shad Alosa alosa A. alosa
## 52 Allis shad Alosa alosa A. alosa
## 53 Allis shad Alosa alosa A. alosa
## 54 Allis shad Alosa alosa A. alosa
## 55 Allis shad Alosa alosa A. alosa
## 56 Allis shad Alosa alosa A. alosa
## 57 Allis shad Alosa alosa A. alosa
## 58 Allis shad Alosa alosa A. alosa
## [ reached 'max' / getOption("max.print") -- omitted 120542 rows ]-
tbl()only connects to the table, only a glimpse of the data is presented-
filter()is run by the SQL database
-
get_data_tbl <- function(conn_eurodiad, species_id, scenario) {
tbl(conn_eurodiad, "v_hybrid_model") %>%
filter(species_id %in% !!species_id,
climatic_scenario %in% !!scenario)
}
# Use it
get_data_tbl(conn_eurodiad,
species_id = c(6),
scenario = 'rcp85')
## # Source: lazy query [?? x 17]
## # Database: postgres
## # [diadesatlas_owner@localhost:5432/diades]
## hybrid_model_result_id basin_id species_id climatic_model_…
## <int> <int> <int> <int>
## 1 1395745 1 6 2
## 2 1395746 1 6 2
## 3 1395747 1 6 2
## 4 1395748 1 6 2
## 5 1395749 1 6 2
## 6 1395750 1 6 2
## 7 1395751 1 6 2
## 8 1395752 1 6 2
## 9 1395753 1 6 2
## 10 1395754 1 6 2
## # … with more rows, and 13 more variables: year <int>,
## # nit <dbl>, hsi <dbl>, saturation_rate <dbl>,
## # climatic_scenario <chr>, climatic_model_code <chr>,
## # oceanic_model <chr>, atmospheric_model <chr>,
## # climatic_model_hm <chr>, basin_name <chr>,
## # fish_name <chr>, latin_name <chr>,
## # simplified_name <chr>- If you need to create some {ggplot2} figures after that, you can collect
get_data_tbl_collect <- function(conn_eurodiad, species_id, scenario) {
tbl(conn_eurodiad, "v_hybrid_model") %>%
filter(species_id %in% !!species_id,
climatic_scenario %in% !!scenario) %>%
# From collect(), the full data is in the R session
collect()
}
# Use it
get_data_tbl_collect(conn_eurodiad,
species_id = c(6),
scenario = 'rcp85') %>%
filter(basin_id == 1) %>%
ggplot(aes(x = year)) +
geom_line(aes(y = hsi))
-
Note that you can use a full SQL query with
tbl()
get_data_tbl_query_collect <- function(conn_eurodiad, species_id, scenario) {
query <- "SELECT * FROM diadesatlas.v_hybrid_model"
tbl(conn_eurodiad, sql(query)) %>%
# Adds a filter to be run as SQL in the database
filter(species_id %in% !!species_id,
climatic_scenario %in% !!scenario) %>%
# From collect(), the full data is in the R session
collect()
}
# Use it
get_data_tbl_query_collect(conn_eurodiad,
species_id = c(6),
scenario = 'rcp85') %>%
filter(basin_id == 1) %>%
ggplot(aes(x = year)) +
geom_line(aes(y = hsi))
Disconnect from the database
DBI::dbDisconnect(conn_eurodiad)