vignettes/aa-data-exploration-and-preparation.Rmd
aa-data-exploration-and-preparation.Rmd<!DOCTYPE html>
This vignette explores the tables in the database and ensures that R has access to the tables. It also transforms the data in json.
library(diades.atlas)
# renv::install("dm")
# library(dm)
library(dplyr)
library(leaflet)
library(ggplot2)
Do not forget to set environment variables in .Renviron
postgis_host <- Sys.getenv("POSTGRES_HOST")
postgis_user <- Sys.getenv("POSTGRES_USER")
postgis_password <- Sys.getenv("POSTGRES_PASSWORD")
con <- DBI::dbConnect(
RPostgres::Postgres(),
host = Sys.getenv("POSTGRES_HOST", diades.atlas:::get_golem_config("POSTGRES_HOST")),
dbname = Sys.getenv("POSTGRES_DBNAME", diades.atlas:::get_golem_config("POSTGRES_DBNAME")),
port = Sys.getenv("POSTGRES_PORT", diades.atlas:::get_golem_config("POSTGRES_PORT")),
user = Sys.getenv("POSTGRES_USER", "diadesatlas_r"),
password = Sys.getenv("POSTGRES_PASS", "diadesPassword"),
options = "-c search_path=diadesatlas"
)
# Listtables
# DBI::dbListTables(con)
# DBI::dbListObjects(con)
# Get the names of all the tables in the schema
all_schemas <- DBI::dbGetQuery(con, "SELECT table_name FROM information_schema.tables WHERE table_schema='diadesatlas'")
all_schemas
#> table_name
#> 1 outlet_distance
#> 2 hydiadparameter
#> 3 basin_location
#> 4 ices_rectangle2
#> 5 ices_division
#> 6 cices
#> 7 abundance
#> 8 v_abundance
#> 9 dbversion
#> 10 v_iucn
#> 11 abundance_level
#> 12 v_hybrid_model_mavg
#> 13 v_ecosystemic_services
#> 14 v_occurence
#> 15 v_basin
#> 16 v_species_ices_occurence
#> 17 v_hybrid_model
#> 18 species_iucn
#> 19 ices_area
#> 20 ices_rectangle
#> 21 iucn_classification
#> 22 iucn_level
#> 23 species_division
#> 24 species_rectangle
#> 25 v_ices_geom
#> 26 esvalue
#> 27 locale
#> 28 hybrid_model_result
#> 29 casestudy
#> 30 ecosystemic_services
#> 31 basin
#> 32 basin_outlet
#> 33 casestudy_basin
#> 34 climatic_model
#> 35 species
#> 36 category
#> 37 subcategory
#> 38 v_maxvalue_es
#> 39 lang85_85
#> 40 ices_grass
# Display the head of all
for (i in all_schemas$table_name) {
print(dplyr::tbl(con, dbplyr::in_schema("diadesatlas", i)))
}
#> # Source: table<"diadesatlas"."outlet_distance"> [?? x 3]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> departure arrival distance
#> <int> <int> <dbl>
#> 1 1 1 0
#> 2 1 3 1279.
#> 3 1 387 2458.
#> 4 1 358 2015.
#> 5 1 359 1382.
#> 6 1 290 1491.
#> 7 1 14 662.
#> 8 1 15 173.
#> 9 1 16 965.
#> 10 1 385 2259.
#> # … with more rows
#> # Source: table<"diadesatlas"."hydiadparameter"> [?? x 16]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> species_id Dmax lambda r AgeFirstMat nbCohorts gamma DistMean alpha beta Mdisp DistMax withAllee
#> <int> <dbl> <dbl> <dbl> <dbl> <int> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <lgl>
#> 1 6 7.11 0.0931 1.3 5 3 0.173 40.5 0.444 0.417 1.90e-2 256. TRUE
#> 2 8 4.07 0.442 1.3 3 3 0.145 24.0 0.549 0.418 3.69e-2 151. TRUE
#> 3 18 0.714 0.193 1.3 5 3 0.366 10 0.216 0.706 3.81e-2 41.2 TRUE
#> 4 21 1.17 0.136 1.3 8 3 0.628 83.2 0.0180 0.869 4.88e-3 300. TRUE
#> 5 25 0.496 0.270 1.3 3 3 0.0605 20.5 0.380 0.496 3.57e-2 111. TRUE
#> 6 26 11.5 0.00851 1.3 3.5 3 0.0828 25.4 0.0376 0.931 2.92e-2 88.0 TRUE
#> 7 4 0.106 0.0472 1.3 11 3 0.0865 464. 0.000110 1.35 4.28e-4 1323. TRUE
#> 8 20 46.2 0.00143 1.3 2 3 0.109 2.42 0.904 0.556 3.98e-1 11.9 TRUE
#> 9 13 35.9 0 1.3 12 3 0.253 170. 0.0000204 1.85 4.67e-3 413. TRUE
#> 10 19 50.3 0.000432 1.3 3.5 3 0.210 39.8 0.000301 1.84 8.32e-3 100 TRUE
#> # … with more rows, and 3 more variables: withNatalStray <lgl>, usePresence <lgl>, Sdisp <dbl>
#> # Source: table<"diadesatlas"."basin_location"> [?? x 6]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> basin_id basin_name surface_area basin_geom basin_geom_simplified outlet_geom
#> <dbl> <chr> <dbl> <pq_gmtry> <pq_gmtry> <pq_gmtry>
#> 1 1 Aa 1287. 0106000020E6100000010000… 0103000020E610000001000000670… 0101000020E6100…
#> 2 2 Adige 12417. 0106000020E6100000010000… 0103000020E610000001000000440… 0101000020E6100…
#> 3 5 Aksu 18175. 0106000020E6100000020000… 0103000020E6100000010000006D0… 0101000020E6100…
#> 4 8 Angermanalven 31815. 0106000020E6100000080000… 0103000020E610000001000000710… 0101000020E6100…
#> 5 11 Asi 57018. 0106000020E6100000010000… 0103000020E610000001000000AB0… 0101000020E6100…
#> 6 18 Axios_Vardar 24397. 0106000020E6100000040000… 0103000020E610000001000000080… 0101000020E6100…
#> 7 303 Arachthos 2441. 0106000020E6100000010000… 0103000020E610000001000000AD0… 0101000020E6100…
#> 8 306 Evrotas 1770. 0106000020E6100000010000… 0103000020E610000001000000700… 0101000020E6100…
#> 9 366 Oka 143. 0106000020E6100000010000… 0103000020E6100000010000001C0… 0101000020E6100…
#> 10 44 Don 429400. 0106000020E6100000020000… 0103000020E610000001000000E70… 0101000020E6100…
#> # … with more rows
#> # Source: table<"diadesatlas"."ices_rectangle2"> [?? x 5]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> ices_type gid icesname geom exterior_geom
#> <chr> <int> <chr> <pq_gmtry> <pq_gmtry>
#> 1 rectangle 1846 01D2 0106000020E610000001000000010300000001… 0103000020E610000001000000050000001092FFFFF…
#> 2 rectangle 1847 02D2 0106000020E610000001000000010300000001… 0103000020E610000001000000050000001092FFFFF…
#> 3 rectangle 1848 03D2 0106000020E610000001000000010300000001… 0103000020E610000001000000050000001092FFFFF…
#> 4 rectangle 1849 04D2 0106000020E610000001000000010300000001… 0103000020E610000001000000050000001092FFFFF…
#> 5 rectangle 1850 05D2 0106000020E610000001000000010300000001… 0103000020E610000001000000050000001092FFFFF…
#> 6 rectangle 1851 06D2 0106000020E610000001000000010300000001… 0103000020E610000001000000050000001092FFFFF…
#> 7 rectangle 1852 07D2 0106000020E610000001000000010300000001… 0103000020E610000001000000050000001092FFFFF…
#> 8 rectangle 1853 08D2 0106000020E610000001000000010300000001… 0103000020E610000001000000050000001092FFFFF…
#> 9 rectangle 1854 09D2 0106000020E610000001000000010300000001… 0103000020E610000001000000050000001092FFFFF…
#> 10 rectangle 1855 10D2 0106000020E610000001000000010300000001… 0103000020E610000001000000050000001092FFFFF…
#> # … with more rows
#> # Source: table<"diadesatlas"."ices_division"> [?? x 5]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> ices_type gid icesname simplified_geom exterior_geom
#> <chr> <int> <chr> <pq_gmtry> <pq_gmtry>
#> 1 division 72 27.3.a 0103000020E610000052040000A8220000146DB504… 0103000020E610000001000000A8220000146DB…
#> 2 division 10 27.4.a 0106000020E6100000030000000103000000800A00… 0103000020E6100000010000001C30000075308…
#> 3 division 8 27.4.b 0103000020E610000068000000DA160000948116A9… 0103000020E610000001000000DA16000094811…
#> 4 division 7 27.4.c 0103000020E61000006A00000055100000473D2BA7… 0103000020E61000000100000055100000473D2…
#> 5 division 27 27.6.a 0103000020E61000009C0200000B240000F7260400… 0103000020E6100000010000000B240000F7260…
#> 6 division 78 27.6.b 0103000020E610000002000000050000002BD00000… 0103000020E610000001000000050000002BD00…
#> 7 division 48 27.7.a 0106000020E6100000040000000103000000010000… 0103000020E6100000010000007D6300008C7B6…
#> 8 division 42 27.7.b 0103000020E61000000F01000081130000A17EB9EA… 0103000020E61000000100000081130000A17EB…
#> 9 division 79 27.7.c 0103000020E610000001000000050000002BD00000… 0103000020E610000001000000050000002BD00…
#> 10 division 47 27.7.d 0106000020E6100000020000000103000000150000… 0103000020E61000000100000034070000FCF03…
#> # … with more rows
#> # Source: table<"diadesatlas"."cices"> [?? x 4]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> cices_id mea cices expert_knowledge
#> <int> <chr> <chr> <chr>
#> 1 1 Provisioning services "Biomass (wild animals and their outputs)" Food provision
#> 2 2 Provisioning services "Biomass (wild animals and their outputs)" Option value (Leather provisi…
#> 3 3 Provisioning services "Biomass (wild animals and their outputs)" Option value (Molecule provis…
#> 4 4 Cultural services "Physical and experiential interactions with n… Recreation sport fishing
#> 5 5 Cultural services "Physical and experiential interactions with n… Sport fishing competitions
#> 6 6 Cultural services "Physical and experiential interactions with n… Option value (fishing competi…
#> 7 7 Cultural services "Spiritual, symbolic, and other interactions w… Spiritual experience (includi…
#> 8 8 Cultural services "Intellectual and representative interactions … Gastronomy around species and…
#> 9 9 Cultural services "Intellectual and representative interactions … Gastronomic festival or events
#> 10 10 Cultural services "Intellectual and representative interactions … Art and folklore
#> # … with more rows
#> # Source: table<"diadesatlas"."abundance"> [?? x 14]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> basin_id abundance_id presence_absence year_from year_to period_comment abundance_migrator… abundance_reside…
#> <int> <int> <int> <int> <int> <chr> <chr> <chr>
#> 1 1 1 0 1951 2010 2000 <NA> <NA>
#> 2 1 2 0 1851 1950 1900 <NA> <NA>
#> 3 3 5 0 1951 2010 2000 <NA> <NA>
#> 4 3 6 0 1851 1950 1900 <NA> <NA>
#> 5 14 29 0 1951 2010 2000 <NA> <NA>
#> 6 14 30 0 1851 1950 1900 <NA> <NA>
#> 7 15 31 0 1951 2010 2000 <NA> <NA>
#> 8 15 32 0 1851 1950 1900 <NA> <NA>
#> 9 16 33 0 1951 2010 2000 <NA> <NA>
#> 10 16 34 0 1851 1950 1900 <NA> <NA>
#> # … with more rows, and 6 more variables: bibliography <chr>, ecoregion_code <int>, ecoregion_name <chr>,
#> # abundance_migratory_id <int>, abundance_resident_id <int>, species_id <int>
#> # Source: table<"diadesatlas"."v_abundance"> [?? x 13]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> basin_id abundance_id abundance_level_id species_id year_from year_to basin_name latin_name simplified_name
#> <int> <int> <int> <int> <int> <int> <chr> <chr> <chr>
#> 1 179 26667 3 8 2011 NA Vire Alosa fallax A. fallax
#> 2 188 26668 1 8 2011 NA Yser Alosa fallax A. fallax
#> 3 69 25726 1 4 2011 NA Gudenaa Acipenser sturio A. sturio
#> 4 174 25727 1 4 2011 NA Vida Acipenser sturio A. sturio
#> 5 69 25730 1 6 2011 NA Gudenaa Alosa alosa A. alosa
#> 6 174 25731 1 6 2011 NA Vida Alosa alosa A. alosa
#> 7 69 25734 2 8 2011 NA Gudenaa Alosa fallax A. fallax
#> 8 174 25735 NA 8 2011 NA Vida Alosa fallax A. fallax
#> 9 69 25744 3 13 2011 NA Gudenaa Anguilla anguilla A. anguilla
#> 10 174 25745 3 13 2011 NA Vida Anguilla anguilla A. anguilla
#> # … with more rows, and 4 more variables: fish_name <chr>, abundance_level_name <chr>,
#> # abundance_level_interpretation <chr>, abundance_level_interpretation_short <chr>
#> # Source: table<"diadesatlas"."dbversion"> [?? x 3]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> dbversion_id dbversion_number dbversion_date
#> <int> <chr> <dttm>
#> 1 1 1.0.0 2022-05-16 00:00:00
#> 2 2 1.0.1 2022-06-09 17:56:44
#> 3 3 1.0.2 2023-02-07 15:08:54
#> # Source: table<"diadesatlas"."v_iucn"> [?? x 7]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> species_id latin_name simplified_name english_name iucn_classifica… iucn_level_code iucn_level_name
#> <int> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 6 Alosa alosa A. alosa Allis shad IUCN_Europe LC Least Concern
#> 2 22 Platichthys flesus P. flesus European fl… IUCN_Europe LC Least Concern
#> 3 8 Alosa fallax A. fallax Twaite shad IUCN_Europe LC Least Concern
#> 4 13 Anguilla anguilla A. anguilla European eel IUCN_Europe CR Critically End…
#> 5 18 Lampetra fluviatilis L. fluviatilis River lampr… IUCN_Europe LC Least Concern
#> 6 21 Petromyzon marinus P. marinus Sea lamprey IUCN_Europe LC Least Concern
#> 7 25 Salmo salar S. salar Atlantic sa… IUCN_Europe LC Least Concern
#> 8 26 Salmo trutta S. trutta Sea trout IUCN_Europe LC Least Concern
#> 9 20 Osmerus eperlanus O. eperlanus European sm… IUCN_Europe LC Least Concern
#> 10 19 Chelon ramada C. ramada Thinlip gre… IUCN_Europe LC Least Concern
#> # … with more rows
#> # Source: table<"diadesatlas"."abundance_level"> [?? x 4]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> abundance_level_id abundance_level_name abundance_level_interpretation abundance_level_interp…
#> <int> <chr> <chr> <chr>
#> 1 1 absent The species was never recorded in the catchme… Not recorded in the pe…
#> 2 2 rare Occasional vagrants were recorded in the catc… Occasional vagrants
#> 3 3 common Functional populations were present in the ca… Functional populations
#> 4 4 abundant Functional populations were present in and nu… Abundant functional po…
#> # Source: table<"diadesatlas"."v_hybrid_model_mavg"> [?? x 14]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> latin_name basin_name climatic_scenar… species_id basin_id year nit_min nit_mean nit_max hsi_min hsi_mean
#> <chr> <chr> <chr> <int> <int> <int> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 Acipenser st… Aa rcp45 4 1 1951 8.71 15.2 27.5 0.0677 0.118
#> 2 Acipenser st… Aa rcp45 4 1 1952 9.48 11.4 14.9 0.0737 0.0889
#> 3 Acipenser st… Aa rcp45 4 1 1953 13.2 14.1 14.9 0.103 0.110
#> 4 Acipenser st… Aa rcp45 4 1 1954 12.3 13.9 15.6 0.0958 0.108
#> 5 Acipenser st… Aa rcp45 4 1 1955 8.12 17.4 27.5 0.0631 0.135
#> 6 Acipenser st… Aa rcp45 4 1 1956 8.75 11.2 15.7 0.0680 0.0869
#> 7 Acipenser st… Aa rcp45 4 1 1957 8.75 13.1 17.3 0.0680 0.102
#> 8 Acipenser st… Aa rcp45 4 1 1958 9.10 10.6 13.3 0.0707 0.0826
#> 9 Acipenser st… Aa rcp45 4 1 1959 4.52 7.57 9.10 0.0352 0.0589
#> 10 Acipenser st… Aa rcp45 4 1 1960 9.93 15.5 20.5 0.0772 0.120
#> # … with more rows, and 3 more variables: hsi_max <dbl>, nit_movingavg <dbl>, hsi_movingavg <dbl>
#> # Source: table<"diadesatlas"."v_ecosystemic_services"> [?? x 14]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> category_id casestudy_id species_id subcategory_id esvalue_id subcategory_name category_name latin_name
#> <int> <int> <int> <int> <int> <chr> <chr> <chr>
#> 1 2 9 21 99 0 ALL EXISTING SUBCATE… Provisionning… Petromyzo…
#> 2 1 9 19 99 0 ALL EXISTING SUBCATE… Cultural serv… Chelon ra…
#> 3 3 5 20 3 -1 Redistribution of nu… Regulating se… Osmerus e…
#> 4 1 4 4 7 0 Natural heritage and… Cultural serv… Acipenser…
#> 5 2 7 20 1 0 Food provision from … Provisionning… Osmerus e…
#> 6 1 9 6 99 1 ALL EXISTING SUBCATE… Cultural serv… Alosa alo…
#> 7 1 1 19 8 2 Recreation sport fis… Cultural serv… Chelon ra…
#> 8 2 3 4 99 0 ALL EXISTING SUBCATE… Provisionning… Acipenser…
#> 9 2 5 26 2 -1 Option value associa… Provisionning… Salmo tru…
#> 10 2 8 20 99 0 ALL EXISTING SUBCATE… Provisionning… Osmerus e…
#> # … with more rows, and 6 more variables: simplified_name <chr>, fish_name <chr>, casestudy_name <chr>,
#> # esvalue_code <chr>, esvalue_name <chr>, sortorder <int>
#> # Source: table<"diadesatlas"."v_occurence"> [?? x 8]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> ices_type species_id gid fish_name latin_name simplified_name icesname nb_occurence
#> <chr> <int> <int> <chr> <chr> <chr> <chr> <int64>
#> 1 rectangle 8 3329 Twaite shad Alosa fallax A. fallax 28E8 7
#> 2 rectangle 6 2629 Allis shad Alosa alosa A. alosa 11E0 15
#> 3 rectangle 22 3003 European flounder Platichthys flesus P. flesus 37E4 3
#> 4 rectangle 26 4135 Sea trout Salmo trutta S. trutta 38F8 1
#> 5 division 6 44 Allis shad Alosa alosa A. alosa 27.7.g 12
#> 6 division 13 47 European eel Anguilla anguilla A. anguilla 27.7.d 15
#> 7 rectangle 25 3656 Atlantic salmon Salmo salar S. salar 39F2 1
#> 8 rectangle 13 3081 European eel Anguilla anguilla A. anguilla 29E5 2
#> 9 rectangle 19 3076 Thinlip grey mullet Chelon ramada C. ramada 24E5 5
#> 10 rectangle 20 2158 European smelt Osmerus eperlanus O. eperlanus 30D5 1
#> # … with more rows
#> # Source: table<"diadesatlas"."v_basin"> [?? x 3]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> basin_name basin_id geom
#> <chr> <int> <pq_gmtry>
#> 1 Aa 1 0103000020E61000000100000067000000B952D9131D8801401BBC172A2B5649408D0C014E4885014000E325…
#> 2 Adour 3 0103000020E610000001000000640100003233CE8E170AE0BF1F91FFE9FC694540E2EB095DA9D0E0BFBCE4A2…
#> 3 Alcoa 387 0103000020E61000000100000033000000ED59C15EDBBB21C0C7B0A13118C64340364D1B3C77BB21C0D8934B…
#> 4 Anllons 358 0103000020E6100000010000004E000000B693D402240F21C0C2A361BB369A454051BC20BAD90B21C0EEAAAB…
#> 5 Artibai 359 0103000020E6100000010000001A00000050DD05F9C08103C02E478DC051A045400788A472099703C08C6A78…
#> 6 Ason 290 0103000020E61000000100000038000000315B7B490DE20BC0E120DA39AE92454050BEFE9623160CC0FCD576…
#> 7 Aulne 14 0103000020E6100000010000005D000000D85741AAE4E60BC00A3519C6FB1A48409BF318828CDD0BC008C323…
#> 8 Authie 15 0103000020E6100000010000007C000000234C696EBAA7044062A2A7E6470C49405AFD67A2D9810440EE7E4C…
#> 9 Auzance 16 0103000020E61000000100000034000000F6C13D5D7E32F8BF28CAD72761454740E79CF4DA6972F8BFC84B2D…
#> 10 Ave 385 0103000020E6100000010000006B0000009EE0BE53973420C09E8644EE81C544404F8DBB263F2F20C06C85FA…
#> # … with more rows
#> # Source: table<"diadesatlas"."v_species_ices_occurence"> [?? x 8]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> ices_type gid species_id fish_name icesname latin_name simplified_name nb_occurence
#> <chr> <int> <int> <chr> <chr> <chr> <chr> <int64>
#> 1 rectangle 3159 4 European sturgeon 22E6 Acipenser sturio A. sturio 0
#> 2 rectangle 3159 6 Allis shad 22E6 Alosa alosa A. alosa 5
#> 3 rectangle 3159 8 Twaite shad 22E6 Alosa fallax A. fallax 1
#> 4 rectangle 3159 13 European eel 22E6 Anguilla anguilla A. anguilla 0
#> 5 rectangle 3159 18 River lamprey 22E6 Lampetra fluviatilis L. fluviatilis 0
#> 6 rectangle 3159 20 European smelt 22E6 Osmerus eperlanus O. eperlanus 0
#> 7 rectangle 3159 21 Sea lamprey 22E6 Petromyzon marinus P. marinus 0
#> 8 rectangle 3159 22 European flounder 22E6 Platichthys flesus P. flesus 0
#> 9 rectangle 3159 25 Atlantic salmon 22E6 Salmo salar S. salar 0
#> 10 rectangle 3159 26 Sea trout 22E6 Salmo trutta S. trutta 0
#> # … with more rows
#> # Source: table<"diadesatlas"."v_hybrid_model"> [?? x 17]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> hybrid_model_result_id basin_id species_id climatic_model_id year nit hsi saturation_rate climatic_scenar…
#> <int> <int> <int> <int> <int> <dbl> <dbl> <dbl> <chr>
#> 1 3 1 6 2 1951 1176. 0.244 0.558 rcp45
#> 2 4 1 6 2 1952 1144. 0.322 0.411 rcp45
#> 3 5 1 6 2 1953 1120. 0.195 0.666 rcp45
#> 4 6 1 6 2 1954 1097. 0.246 0.517 rcp45
#> 5 7 1 6 2 1955 734. 0.0850 1 rcp45
#> 6 8 1 6 2 1956 998. 0.235 0.491 rcp45
#> 7 9 1 6 2 1957 949. 0.327 0.336 rcp45
#> 8 10 1 6 2 1958 912. 0.415 0.254 rcp45
#> 9 11 1 6 2 1959 724. 0.246 0.342 rcp45
#> 10 12 1 6 2 1960 670. 0.380 0.204 rcp45
#> # … with more rows, and 8 more variables: 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>
#> # Source: table<"diadesatlas"."species_iucn"> [?? x 4]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> species_iucn_id species_id iucn_classification_id iucn_level_id
#> <int> <int> <int> <int>
#> 1 1 4 1 4
#> 2 2 13 1 4
#> 3 3 18 1 8
#> 4 4 21 1 8
#> 5 5 25 1 8
#> 6 6 26 1 8
#> 7 7 20 1 8
#> 8 8 19 1 8
#> 9 9 6 1 8
#> 10 10 22 1 8
#> # … with more rows
#> # Source: table<"diadesatlas"."ices_area"> [?? x 11]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> gid major_fa subarea division subdivisio unit area_full area_27 area_km2 geom simplified_geom
#> <int> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <dbl> <pq_gmtry> <pq_gmtry>
#> 1 83 27 8 e <NA> <NA> 27.8.e 8.e NA 01030000201… 0103000020E61000000…
#> 2 10 27 4 a <NA> <NA> 27.4.a 4.a 267336. 01060000201… 0106000020E61000000…
#> 3 7 27 4 c <NA> <NA> 27.4.c 4.c 63634. 01060000201… 0103000020E61000006…
#> 4 72 27 3 a <NA> <NA> 27.3.a 3.a NA 01030000201… 0103000020E61000005…
#> 5 27 27 6 a <NA> <NA> 27.6.a 6.a 236759. 01060000201… 0103000020E61000009…
#> 6 8 27 4 b <NA> <NA> 27.4.b 4.b 280957. 01060000201… 0103000020E61000006…
#> 7 84 27 9 b <NA> <NA> 27.9.b 9.b NA 01030000201… 0103000020E61000000…
#> 8 82 27 8 d <NA> <NA> 27.8.d 8.d NA 01030000201… 0103000020E61000000…
#> 9 36 27 9 a <NA> <NA> 27.9.a 9.a 167855. 01060000201… 0106000020E61000000…
#> 10 78 27 6 b <NA> <NA> 27.6.b 6.b NA 01030000201… 0103000020E61000000…
#> # … with more rows
#> # Source: table<"diadesatlas"."ices_rectangle"> [?? x 15]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> gid id icesname south west north east area_km2 objectid ecoregion area percentage shape_star
#> <int> <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <int> <int64> <chr> <dbl> <dbl> <dbl>
#> 1 1846 2549 01D2 36 -18 36.5 -17 4987 30 Oceanic Northeast… 0.5 100 0.500
#> 2 1847 2550 02D2 36.5 -18 37 -17 4955 121 Oceanic Northeast… 0.5 100 0.500
#> 3 1848 2551 03D2 37 -18 37.5 -17 4923 209 Oceanic Northeast… 0.5 100 0.500
#> 4 1849 2552 04D2 37.5 -18 38 -17 4891 296 Oceanic Northeast… 0.5 100 0.500
#> 5 1850 2553 05D2 38 -18 38.5 -17 4858 385 Oceanic Northeast… 0.5 100 0.500
#> 6 1851 2554 06D2 38.5 -18 39 -17 4825 463 Oceanic Northeast… 0.5 100 0.500
#> 7 1852 2555 07D2 39 -18 39.5 -17 4792 538 Oceanic Northeast… 0.5 100 0.500
#> 8 1853 2556 08D2 39.5 -18 40 -17 4758 624 Oceanic Northeast… 0.5 100 0.500
#> 9 1854 2557 09D2 40 -18 40.5 -17 4724 715 Oceanic Northeast… 0.5 100 0.500
#> 10 1855 2558 10D2 40.5 -18 41 -17 4689 806 Oceanic Northeast… 0.5 100 0.500
#> # … with more rows, and 2 more variables: shape_stle <dbl>, geom <pq_gmtry>
#> # Source: table<"diadesatlas"."iucn_classification"> [?? x 3]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> iucn_classification_id iucn_classification_code bibliography
#> <int> <chr> <chr>
#> 1 3 IUCN_FR UICN Comité français, MNHN, SFI & AFB. (2019) La Liste rouge …
#> 2 4 IUCN_IE King, J.L., F. Marnell, N. Kingston, R. Rosell, P. Boylan, J.…
#> 3 2 IUCN_ES www.uicn.es/web/pdf/Tablas_especies_informe_RedList_Spain.xlsx
#> 4 1 IUCN_Europe Brooks, Emma. European Red List of Freshwater Fishes. Luxembo…
#> 5 6 IUCN_UK https://hub.jncc.gov.uk/assets/478f7160-967b-4366-acdf-8941fd…
#> 6 5 IUCN_PT Cabral, M.J. (coord), Almeida, J., Almeida, P.R., Dellinger T…
#> # Source: table<"diadesatlas"."iucn_level"> [?? x 3]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> iucn_level_id iucn_level_code iucn_level_name
#> <int> <chr> <chr>
#> 1 1 EX Extinct
#> 2 2 EW Extinct in the Wild
#> 3 3 RE Regionally Extinct
#> 4 4 CR Critically Endangered
#> 5 5 EN Endangered
#> 6 6 VU Vulnerable
#> 7 7 NT Near Threatened
#> 8 8 LC Least Concern
#> 9 9 DD Data Deficient
#> 10 10 NE Not Evaluated
#> # Source: table<"diadesatlas"."species_division"> [?? x 4]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> species_division_id year species_id gid
#> <int> <int> <int> <int>
#> 1 1 2013 4 37
#> 2 2 2015 4 47
#> 3 3 2015 4 40
#> 4 4 2016 4 40
#> 5 5 2016 4 39
#> 6 6 2016 4 47
#> 7 7 2017 4 39
#> 8 8 2003 6 7
#> 9 9 2003 6 40
#> 10 10 2003 6 39
#> # … with more rows
#> # Source: table<"diadesatlas"."species_rectangle"> [?? x 4]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> species_rectangle_id year species_id gid
#> <int> <int> <int> <int>
#> 1 1 2013 4 3234
#> 2 2 2015 4 3324
#> 3 3 2015 4 3565
#> 4 4 2016 4 3323
#> 5 5 2016 4 3324
#> 6 6 2016 4 3485
#> 7 7 2017 4 3323
#> 8 8 2003 6 3323
#> 9 9 2003 6 3324
#> 10 10 2003 6 3160
#> # … with more rows
#> # Source: table<"diadesatlas"."v_ices_geom"> [?? x 4]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> ices_type gid icesname geom
#> <chr> <int> <chr> <pq_gmtry>
#> 1 rectangle 3159 22E6 0106000020E6100000010000000103000000010000000500000080FFFFFFFFFF0FC02892FFFFFF3F474…
#> 2 rectangle 2247 21D6 0106000020E610000001000000010300000001000000050000006048FEFFFFFF2BC018C9FFFFFFFF464…
#> 3 rectangle 2256 30D6 0106000020E610000001000000010300000001000000050000006048FEFFFFFF2BC018C9FFFFFF3F494…
#> 4 rectangle 1966 27D3 0106000020E61000000100000001030000000100000005000000D06D0000000031C0E86D00000080484…
#> 5 rectangle 3177 44E6 0106000020E6100000010000000103000000010000000500000080FFFFFFFFFF0FC0D8A4000000C04C4…
#> 6 rectangle 4058 37F7 0106000020E610000001000000010300000001000000050000004022F9FFFFFF1B40F836000000004B4…
#> 7 rectangle 2258 32D6 0106000020E610000001000000010300000001000000050000006048FEFFFFFF2BC0385BFFFFFFBF494…
#> 8 rectangle 2727 12E1 0106000020E610000001000000010300000001000000050000002024FFFFFFFF21C018C9FFFFFFBF444…
#> 9 rectangle 3092 40E5 0106000020E61000000100000001030000000100000005000000C090FCFFFFFF13C02892FFFFFFBF4B4…
#> 10 rectangle 2052 19D4 0106000020E6100000010000000103000000010000000500000090490100000030C0F83600000080464…
#> # … with more rows
#> # Source: table<"diadesatlas"."esvalue"> [?? x 3]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> esvalue_id esvalue_code esvalue_name
#> <int> <chr> <chr>
#> 1 -1 ? Unknown
#> 2 0 0 Not present
#> 3 1 € Low value
#> 4 2 €€ Median value
#> 5 3 €€€ High value
#> # Source: table<"diadesatlas"."locale"> [?? x 4]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> locale_id country_code english_label country_label
#> <int> <chr> <chr> <chr>
#> 1 59 fr ALL EXISTING SUBCATEGORIES TOUTES LES SOUS-CATEGORIES EXISTANTES
#> 2 60 fr Allis shad Grande alose
#> 3 61 fr Atlantic salmon Saumon atlantique
#> 4 62 fr Barrow Barrow
#> 5 63 fr Couesnon Couesnon
#> 6 64 fr Cultural services Services culturels
#> 7 65 fr Deba Deba
#> 8 66 fr Dordogne Dordogne
#> 9 67 fr Dorset Frome Dorset Frome
#> 10 68 fr European eel Anguille européenne
#> # … with more rows
#> # Source: table<"diadesatlas"."hybrid_model_result"> [?? x 9]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> hybrid_model_result_id basin_id species_id climatic_model_id climatic_scenario year nit hsi saturation_rate
#> <int> <int> <int> <int> <chr> <int> <dbl> <dbl> <dbl>
#> 1 2 1 6 2 rcp45 0 1451. 0.168 1
#> 2 3 1 6 2 rcp45 1951 1176. 0.244 0.558
#> 3 4 1 6 2 rcp45 1952 1144. 0.322 0.411
#> 4 5 1 6 2 rcp45 1953 1120. 0.195 0.666
#> 5 6 1 6 2 rcp45 1954 1097. 0.246 0.517
#> 6 7 1 6 2 rcp45 1955 734. 0.0850 1
#> 7 8 1 6 2 rcp45 1956 998. 0.235 0.491
#> 8 9 1 6 2 rcp45 1957 949. 0.327 0.336
#> 9 10 1 6 2 rcp45 1958 912. 0.415 0.254
#> 10 11 1 6 2 rcp45 1959 724. 0.246 0.342
#> # … with more rows
#> # Source: table<"diadesatlas"."casestudy"> [?? x 4]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> casestudy_id casestudy_name publishable geom
#> <int> <chr> <lgl> <pq_gmtry>
#> 1 3 Normand-Breton Gulf TRUE 0103000020E6100000010000003A010000D08FF885F7A4F0BFCA66C74A9…
#> 2 4 Loire TRUE 0106000020E61000000400000001030000000100000005000000E71D48D…
#> 3 6 Gipuzcoan TRUE 0106000020E610000002000000010300000001000000FA000000F5690FD…
#> 4 1 Waterford Harbour TRUE 0106000020E610000002000000010300000003000000840200004472DEF…
#> 5 9 Mondego TRUE 0106000020E6100000020000000103000000010000006502000090455BF…
#> 6 8 Minho TRUE 0103000020E610000001000000B7040000295C2F83AAE61BC0CC7D996F0…
#> 7 2 Tamar-Taff-Frome TRUE 0106000020E610000003000000010300000001000000CA00000019C5EAD…
#> 8 5 Gironde-Garonne-Dordogne TRUE 0106000020E61000000200000001030000000400000065080000ABC3407…
#> 9 7 Ulla TRUE 0103000020E61000000100000066010000AF11015F00D71FC093CDADAA1…
#> # Source: table<"diadesatlas"."ecosystemic_services"> [?? x 5]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> ecosystemic_services_id casestudy_id species_id subcategory_id esvalue_id
#> <int> <int> <int> <int> <int>
#> 1 89 1 6 1 0
#> 2 99 1 8 1 0
#> 3 95 1 18 1 0
#> 4 96 1 21 1 0
#> 5 90 1 25 1 0
#> 6 97 1 26 1 -1
#> 7 93 1 20 1 -1
#> 8 94 1 4 1 0
#> 9 91 1 13 1 -1
#> 10 92 1 22 1 1
#> # … with more rows
#> # Source: table<"diadesatlas"."basin"> [?? x 12]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> basin_id basin_name country ecoregion_code longitude latitude surface_area_drainage_basin length_main_wate…
#> <int> <chr> <chr> <int> <dbl> <dbl> <dbl> <dbl>
#> 1 15 Authie France 20025 1.63 50.4 1305 100
#> 2 228 Vosso Norway 20022 5.72 60.5 NA NA
#> 3 229 Laerdalselva Norway 20022 7.46 61.1 NA NA
#> 4 230 Aroelva Norway 20022 7.16 61.3 NA NA
#> 5 235 Figgjo Norway 20022 5.83 58.8 NA NA
#> 6 236 Etneelv Norway 20022 5.92 59.7 NA NA
#> 7 237 Opo Norway 20022 6.54 60.1 NA NA
#> 8 238 Loneelv Norway 20022 5.05 60.8 NA NA
#> 9 231 Eira Norway 20022 8.14 62.7 NA NA
#> 10 239 Bondalselv Norway 20022 6.38 62.3 NA NA
#> # … with more rows, and 4 more variables: altitude_source <dbl>, bibliography <chr>, administrator <chr>,
#> # link_pictures <chr>
#> # Source: table<"diadesatlas"."basin_outlet"> [?? x 9]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> basin_id wso_id ccm_name ccm_area ccm_window geom seaoutlet_geom simplified_geom verysimplified_ge…
#> <int> <int> <chr> <dbl> <int> <pq_gmtr> <pq_gmtry> <pq_gmtry> <pq_gmtry>
#> 1 147 83758 Spey 3061. 2001 01060000… 0101000020E6100… 0103000020E61000… 0103000020E610000…
#> 2 155 83745 Tay 5902. 2001 01060000… 0101000020E6100… 0103000020E61000… 0103000020E610000…
#> 3 137 291745 <NA> 1014. 2003 01060000… 0101000020E6100… 0103000020E61000… 0103000020E610000…
#> 4 65 1034751 <NA> 51464. 2008 01060000… 0101000020E6100… 0106000020E61000… 0106000020E610000…
#> 5 232 1034818 <NA> 3182. 2008 01060000… 0101000020E6100… 0106000020E61000… 0106000020E610000…
#> 6 242 1034790 Vefsna 4218. 2008 01060000… 0101000020E6100… 0103000020E61000… 0103000020E610000…
#> 7 177 291146 Vilaine 10490. 2003 01060000… 0101000020E6100… 0103000020E61000… 0103000020E610000…
#> 8 233 1034734 <NA> 6163. 2008 01060000… 0101000020E6100… 0106000020E61000… 0106000020E610000…
#> 9 271 83811 Thames 13514. 2001 01060000… 0101000020E6100… 0106000020E61000… 0106000020E610000…
#> 10 62 1034724 Glomma 41911 2008 01060000… 0101000020E6100… 0106000020E61000… 0106000020E610000…
#> # … with more rows
#> # Source: table<"diadesatlas"."casestudy_basin"> [?? x 2]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> casestudy_id basin_id
#> <int> <int>
#> 1 1 20
#> 2 2 330
#> 3 2 331
#> 4 2 329
#> 5 3 36
#> 6 3 137
#> 7 4 89
#> 8 5 382
#> 9 5 60
#> 10 6 362
#> # … with more rows
#> # Source: table<"diadesatlas"."climatic_model"> [?? x 5]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> climatic_model_id climatic_model_code oceanic_model atmospheric_model climatic_model_hm
#> <int> <chr> <chr> <chr> <chr>
#> 1 1 canesm2 CanESM2 "CCCma-CanESM2" ""
#> 2 2 cnrmcm5 CNRM-CM5 "CNRM-CERFACS-CNRM-CM5" "cn"
#> 3 3 csiromk360 CSIRO-Mk3-6-0 "CSIRO-QCCCE-CSIRO-Mk3-6-0" "cs"
#> 4 4 gfdlesm2m GFDL-ESM2M "NOAA-GFDL-GFDL-ESM2M" ""
#> 5 5 hadgem2es HadGEM2-ES "MOHC-HadGEM2-ES" ""
#> 6 6 ipslcm5amr IPSL-CM5A-MR "IPSL-IPSL-CM5A-MR" ""
#> 7 7 miroc5 MIROC5 "MIROC-MIROC5" ""
#> 8 8 mpiesmmr MPI-ESM_MR "MPI-M-MPI-ESM-LR" ""
#> 9 9 noresm1me NorESM1-ME "NCC-NorESM1-M" "no"
#> 10 10 access10 ACCESS1-0 "" ""
#> # … with more rows
#> # Source: table<"diadesatlas"."species"> [?? x 5]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> species_id latin_name english_name local_name active
#> <int> <chr> <chr> <chr> <lgl>
#> 1 1 Acipenser gueldenstaedtii <NA> "" FALSE
#> 2 2 Acipenser naccarii <NA> "" FALSE
#> 3 3 Acipenser stellatus <NA> "" FALSE
#> 4 5 Alosa algeriensis <NA> "" FALSE
#> 5 7 Alosa caspia <NA> "" FALSE
#> 6 9 Alosa immaculata <NA> "" FALSE
#> 7 10 Alosa kessleri <NA> "" FALSE
#> 8 11 Alosa tanaica <NA> "" FALSE
#> 9 12 Alosa volgensis <NA> "" FALSE
#> 10 14 Caspiomyzon wagneri <NA> "" FALSE
#> # … with more rows
#> # Source: table<"diadesatlas"."category"> [?? x 3]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> category_id category_name sortorder
#> <int> <chr> <int>
#> 1 2 Provisionning services 1
#> 2 3 Regulating services 2
#> 3 1 Cultural services 3
#> # Source: table<"diadesatlas"."subcategory"> [?? x 3]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> subcategory_id subcategory_name category_id
#> <int> <chr> <int>
#> 1 1 Food provision from commercial fisheries 2
#> 2 2 Option value associated to the unknown production of molecules 2
#> 3 3 Redistribution of nutrients - nutrients transportation 3
#> 4 4 Traditional know-how (fishing techniques used) related to the commercial fisheries 1
#> 5 5 Traditional know-how related to the recreational fisheries 1
#> 6 6 Gastronomy around species including emotional brotherhood (festival, event, and fo… 1
#> 7 7 Natural heritage and natural diversity - the existence value 1
#> 8 8 Recreation sport fishing (incl. competition) 1
#> 9 9 The potential for environmental education and research 1
#> # Source: table<"diadesatlas"."v_maxvalue_es"> [?? x 4]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> category_id casestudy_id species_id esvalue_id
#> <int> <int> <int> <int>
#> 1 2 8 13 1
#> 2 3 3 8 0
#> 3 2 8 20 0
#> 4 1 8 19 0
#> 5 3 8 19 0
#> 6 3 9 21 0
#> 7 3 6 6 1
#> 8 3 4 8 0
#> 9 1 5 6 2
#> 10 3 8 8 0
#> # … with more rows
#> # Source: table<"diadesatlas"."lang85_85"> [?? x 12]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> id geom fid cat cat_ basin_id wso_id ccm_name ccm_area ccm_window seaoutlet_ simplified
#> <int> <pq_gmtry> <int> <int> <int> <int> <int> <chr> <dbl> <int> <chr> <chr>
#> 1 1 0103000020… 1 2 6 65 1.03e6 <NA> 51464. 2008 SRID=4326;P… SRID=4326;MULT…
#> 2 2 0103000020… 2 4 17 271 8.38e4 Thames 13514. 2001 SRID=4326;P… SRID=4326;MULT…
#> 3 3 0103000020… 3 7 22 62 1.03e6 Glomma 41911 2008 SRID=4326;P… SRID=4326;MULT…
#> 4 4 0103000020… 4 15 38 51 6 e0 Elbe 143656. 2000 SRID=4326;P… SRID=4326;MULT…
#> 5 5 0103000020… 5 16 38 51 6 e0 Elbe 143656. 2000 SRID=4326;P… SRID=4326;MULT…
#> 6 6 0103000020… 6 17 38 51 6 e0 Elbe 143656. 2000 SRID=4326;P… SRID=4326;MULT…
#> 7 7 0103000020… 7 24 47 81 1.03e6 <NA> 8356. 2008 SRID=4326;P… SRID=4326;MULT…
#> 8 8 0103000020… 8 28 71 40 8.38e4 <NA> 1928. 2001 SRID=4326;P… SRID=4326;MULT…
#> 9 9 0103000020… 9 32 78 164 8.41e4 <NA> 1327. 2001 SRID=4326;P… SRID=4326;MULT…
#> 10 10 0103000020… 10 34 83 19 8.37e4 <NA> 5811. 2001 SRID=4326;P… SRID=4326;MULT…
#> # … with more rows
#> # Source: table<"diadesatlas"."ices_grass"> [?? x 14]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> id geom fid cat gid major_fa subarea division subdivisio unit area_full area_27 area_km2
#> <int> <pq_gmtry> <int> <int> <int> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <dbl>
#> 1 1 0103000020110F… 1 4 10 27 4 a <NA> <NA> 27.4.a 4.a 267336.
#> 2 2 0103000020110F… 2 4 10 27 4 a <NA> <NA> 27.4.a 4.a 267336.
#> 3 3 0103000020110F… 3 1 8 27 4 b <NA> <NA> 27.4.b 4.b 280957.
#> 4 4 0103000020110F… 4 4 10 27 4 a <NA> <NA> 27.4.a 4.a 267336.
#> 5 5 0103000020110F… 5 5 72 27 3 a <NA> <NA> 27.3.a 3.a NA
#> 6 6 0103000020110F… 6 7 40 27 8 a <NA> <NA> 27.8.a 8.a 62145.
#> 7 7 0103000020110F… 7 7 40 27 8 a <NA> <NA> 27.8.a 8.a 62145.
#> 8 8 0103000020110F… 8 7 40 27 8 a <NA> <NA> 27.8.a 8.a 62145.
#> 9 9 0103000020110F… 9 10 46 27 7 e <NA> <NA> 27.7.e 7.e 56370.
#> 10 10 0103000020110F… 10 8 42 27 7 b <NA> <NA> 27.7.b 7.b 35555.
#> # … with more rows, and 1 more variable: simplified <chr>
casestudy <- tbl(con, dbplyr::in_schema("diadesatlas", "casestudy"))
casestudy
#> # Source: table<"diadesatlas"."casestudy"> [?? x 4]
#> # Database: postgres [ptlambert@citerne.bordeaux.irstea.priv:5432/eurodiad]
#> casestudy_id casestudy_name publishable geom
#> <int> <chr> <lgl> <pq_gmtry>
#> 1 3 Normand-Breton Gulf TRUE 0103000020E6100000010000003A010000D08FF885F7A4F0BFCA66C74A9…
#> 2 4 Loire TRUE 0106000020E61000000400000001030000000100000005000000E71D48D…
#> 3 6 Gipuzcoan TRUE 0106000020E610000002000000010300000001000000FA000000F5690FD…
#> 4 1 Waterford Harbour TRUE 0106000020E610000002000000010300000003000000840200004472DEF…
#> 5 9 Mondego TRUE 0106000020E6100000020000000103000000010000006502000090455BF…
#> 6 8 Minho TRUE 0103000020E610000001000000B7040000295C2F83AAE61BC0CC7D996F0…
#> 7 2 Tamar-Taff-Frome TRUE 0106000020E610000003000000010300000001000000CA00000019C5EAD…
#> 8 5 Gironde-Garonne-Dordogne TRUE 0106000020E61000000200000001030000000400000065080000ABC3407…
#> 9 7 Ulla TRUE 0103000020E61000000100000066010000AF11015F00D71FC093CDADAA1…
# empty
# dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "cices51")) %>% collect()
# services ?
# dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "cices")) %>% collect()
# services new
dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "es_diades")) %>% collect()
# geometry of all basin
dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "basin_location")) %>% collect()
# Model results over the years
dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "hybrid_model_result")) %>% collect()
# Abundance of species in a given basin ove the years
dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "abundance")) %>% collect()
# ?
dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "climatic_model")) %>% collect()
# List of all basins
dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "basin")) %>% collect()
# List of basin for case_study
dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "casestudy")) %>% collect()
# List of basins id for case_study
dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "casestudy_basin")) %>% collect()
# List of ecosystem services
dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "ecosystem_service")) %>% collect()
dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "ecosystem_service_new")) %>% collect()
# List of species
dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "species")) %>% collect()
Create all json data used on the home page of the app
# List of case studies
casestudy <- dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "casestudy")) %>% collect()
# List of the corresponding basins
casestudy_basin <- dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "casestudy_basin")) %>% collect()
# Put them two together
cstd <- full_join(casestudy, casestudy_basin) %>% filter(publishable)
# Get the features for drawing the polygons on the map
qry <- "SELECT ST_Transform(diadesatlas.basin_outlet.simplified_geom, 4326) as geom, basin_id as basin_id FROM diadesatlas.basin_outlet"
# i then read as an sf object
pols <- sf::st_read(con, query = qry, geom = "geom") %>%
filter(basin_id %in% casestudy_basin$basin_id) %>%
left_join(cstd)
pols %>%
geojson::as.geojson() %>%
geojson::geo_write(here::here("inst/casestudy.json"))
# List of ecosystem services
ecosystems <- dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "ecosystem_service")) %>% collect()
ecosystems %>%
filter(presence == 1) %>%
jsonlite::write_json(here::here("inst/ecosystems.json"))
# Services
services <- dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "v_ecosystemic_services")) %>% collect()
services %>%
distinct(category_id, subcategory_id, category_name, subcategory_name) %>%
mutate(
subcategory_id = gsub("99", "0", subcategory_id)
) %>%
arrange(
category_id, subcategory_id
) %>%
mutate(
subcategory_id = gsub("0", "99", subcategory_id)
) %>%
jsonlite::write_json(here::here("inst/services.json"))
eco_and_services <- full_join(ecosystems, services)
# Species
species <- dplyr::tbl(con, dbplyr::in_schema("diadesatlas", "species")) %>% collect()
species %>%
filter(species_id %in% ecosystems$species_id) %>%
jsonlite::write_json(here::here("inst/species.json"))
DBI::dbDisconnect(con)