vignettes/aa-data-exploration-and-preparation.Rmd
aa-data-exploration-and-preparation.Rmd<!DOCTYPE html>
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("POSTGIS_HOST")
postgis_user <- Sys.getenv("POSTGIS_USER")
postgis_password <- Sys.getenv("POSTGIS_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 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
# Display the head of all
for (i in all_schemas$table_name) {
print(dplyr::tbl(con, dbplyr::in_schema("diadesatlas", i)))
}
#> # Source: table<"diadesatlas"."abundance_level"> [??
#> # x 4]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> abundance_level_id abundance_level_… abundance_level_…
#> <int> <chr> <chr>
#> 1 1 absent The species was …
#> 2 2 rare Occasional vagra…
#> 3 3 common Functional popul…
#> 4 4 abundant Functional popul…
#> # … with 1 more variable:
#> # abundance_level_interpretation_short <chr>
#> # Source: table<"diadesatlas"."basin"> [?? x 12]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> basin_id basin_name country ecoregion_code longitude
#> <int> <chr> <chr> <int> <dbl>
#> 1 15 Authie France 20025 1.63
#> 2 228 Vosso Norway 20022 5.72
#> 3 229 Laerdalselva Norway 20022 7.46
#> 4 230 Aroelva Norway 20022 7.16
#> 5 235 Figgjo Norway 20022 5.83
#> 6 236 Etneelv Norway 20022 5.92
#> 7 237 Opo Norway 20022 6.54
#> 8 238 Loneelv Norway 20022 5.05
#> 9 231 Eira Norway 20022 8.14
#> 10 239 Bondalselv Norway 20022 6.38
#> # … with more rows, and 7 more variables:
#> # latitude <dbl>, surface_area_drainage_basin <dbl>,
#> # length_main_watercourse <dbl>,
#> # altitude_source <dbl>, bibliography <chr>,
#> # administrator <chr>, link_pictures <chr>
#> # Source: table<"diadesatlas"."basin_outlet"> [?? x
#> # 9]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> basin_id wso_id ccm_name ccm_area ccm_window geom
#> <int> <int> <chr> <dbl> <int> <pq_gm>
#> 1 147 83758 Spey 3061. 2001 010600…
#> 2 155 83745 Tay 5902. 2001 010600…
#> 3 137 291745 <NA> 1014. 2003 010600…
#> 4 65 1034751 <NA> 51464. 2008 010600…
#> 5 232 1034818 <NA> 3182. 2008 010600…
#> 6 242 1034790 Vefsna 4218. 2008 010600…
#> 7 66 83751 <NA> 8443. 2001 010600…
#> 8 177 291146 Vilaine 10490. 2003 010600…
#> 9 233 1034734 <NA> 6163. 2008 010600…
#> 10 271 83811 Thames 13514. 2001 010600…
#> # … with more rows, and 3 more variables:
#> # seaoutlet_geom <pq_gmtry>,
#> # simplified_geom <pq_gmtry>,
#> # verysimplified_geom <pq_gmtry>
#> # Source: table<"diadesatlas"."casestudy_basin"> [??
#> # x 2]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> 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"."category"> [?? x 3]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> 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"."cices"> [?? x 4]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> cices_id mea cices expert_knowledge
#> <int> <chr> <chr> <chr>
#> 1 1 Provisioning services "Bio… Food provision
#> 2 2 Provisioning services "Bio… Option value (L…
#> 3 3 Provisioning services "Bio… Option value (M…
#> 4 4 Cultural services "Phy… Recreation spor…
#> 5 5 Cultural services "Phy… Sport fishing c…
#> 6 6 Cultural services "Phy… Option value (f…
#> 7 7 Cultural services "Spi… Spiritual exper…
#> 8 8 Cultural services "Int… Gastronomy arou…
#> 9 9 Cultural services "Int… Gastronomic fes…
#> 10 10 Cultural services "Int… Art and folklore
#> # … with more rows
#> # Source: table<"diadesatlas"."climatic_model"> [?? x
#> # 5]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> climatic_model_id climatic_model_code oceanic_model
#> <int> <chr> <chr>
#> 1 1 canesm2 CanESM2
#> 2 2 cnrmcm5 CNRM-CM5
#> 3 3 csiromk360 CSIRO-Mk3-6-0
#> 4 4 gfdlesm2m GFDL-ESM2M
#> 5 5 hadgem2es HadGEM2-ES
#> 6 6 ipslcm5amr IPSL-CM5A-MR
#> 7 7 miroc5 MIROC5
#> 8 8 mpiesmmr MPI-ESM_MR
#> 9 9 noresm1me NorESM1-ME
#> 10 10 access10 ACCESS1-0
#> # … with more rows, and 2 more variables:
#> # atmospheric_model <chr>, climatic_model_hm <chr>
#> # Source: table<"diadesatlas"."hydiadparameter"> [??
#> # x 16]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> species_id Dmax lambda r AgeFirstMat nbCohorts
#> <int> <dbl> <dbl> <dbl> <dbl> <int>
#> 1 6 7.11 0.0931 1.10 5 3
#> 2 8 4.07 0.442 1.09 3 3
#> 3 18 0.714 0.193 1.16 5 3
#> 4 21 1.17 0.136 1.31 8 3
#> 5 25 0.496 0.270 1.03 3 3
#> 6 26 11.5 0.00851 1.04 3.5 3
#> 7 4 0.106 0.0472 1.02 11 3
#> 8 20 46.2 0.00143 1.07 2 3
#> 9 13 35.9 0 1.15 12 3
#> 10 19 50.3 0.000432 1.07 3.5 3
#> # … with more rows, and 10 more variables: gamma <dbl>,
#> # DistMean <dbl>, alpha <dbl>, beta <dbl>,
#> # Mdisp <dbl>, DistMax <dbl>, withAllee <lgl>,
#> # withNatalStray <lgl>, usePresence <lgl>,
#> # Sdisp <dbl>
#> # Source: table<"diadesatlas"."ices_area"> [?? x 11]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> gid major_fa subarea division subdivisio unit
#> <int> <chr> <chr> <chr> <chr> <chr>
#> 1 83 27 8 e <NA> <NA>
#> 2 10 27 4 a <NA> <NA>
#> 3 7 27 4 c <NA> <NA>
#> 4 72 27 3 a <NA> <NA>
#> 5 27 27 6 a <NA> <NA>
#> 6 8 27 4 b <NA> <NA>
#> 7 84 27 9 b <NA> <NA>
#> 8 82 27 8 d <NA> <NA>
#> 9 36 27 9 a <NA> <NA>
#> 10 79 27 7 c <NA> <NA>
#> # … with more rows, and 5 more variables:
#> # area_full <chr>, area_27 <chr>, area_km2 <dbl>,
#> # geom <pq_gmtry>, simplified_geom <pq_gmtry>
#> # Source: table<"diadesatlas"."ices_grass"> [?? x 14]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> id geom fid cat gid major_fa subarea
#> <int> <pq_gmtry> <int> <int> <int> <chr> <chr>
#> 1 1 01030000201… 1 4 10 27 4
#> 2 2 01030000201… 2 4 10 27 4
#> 3 3 01030000201… 3 1 8 27 4
#> 4 4 01030000201… 4 4 10 27 4
#> 5 5 01030000201… 5 5 72 27 3
#> 6 6 01030000201… 6 7 40 27 8
#> 7 7 01030000201… 7 7 40 27 8
#> 8 8 01030000201… 8 7 40 27 8
#> 9 9 01030000201… 9 10 46 27 7
#> 10 10 01030000201… 10 8 42 27 7
#> # … with more rows, and 7 more variables:
#> # division <chr>, subdivisio <chr>, unit <chr>,
#> # area_full <chr>, area_27 <chr>, area_km2 <dbl>,
#> # simplified <chr>
#> # Source: table<"diadesatlas"."ices_rectangle"> [?? x
#> # 15]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> gid id icesname south west north east area_km2
#> <int> <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <int>
#> 1 1846 2549 01D2 36 -18 36.5 -17 4987
#> 2 1847 2550 02D2 36.5 -18 37 -17 4955
#> 3 1848 2551 03D2 37 -18 37.5 -17 4923
#> 4 1849 2552 04D2 37.5 -18 38 -17 4891
#> 5 1850 2553 05D2 38 -18 38.5 -17 4858
#> 6 1851 2554 06D2 38.5 -18 39 -17 4825
#> 7 1852 2555 07D2 39 -18 39.5 -17 4792
#> 8 1853 2556 08D2 39.5 -18 40 -17 4758
#> 9 1854 2557 09D2 40 -18 40.5 -17 4724
#> 10 1855 2558 10D2 40.5 -18 41 -17 4689
#> # … with more rows, and 7 more variables:
#> # objectid <int64>, ecoregion <chr>, area <dbl>,
#> # percentage <dbl>, shape_star <dbl>,
#> # shape_stle <dbl>, geom <pq_gmtry>
#> # Source: table<"diadesatlas"."iucn_classification">
#> # [?? x 3]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> iucn_classificat… iucn_classificat… bibliography
#> <int> <chr> <chr>
#> 1 3 IUCN_FR UICN Comité franç…
#> 2 4 IUCN_IE King, J.L., F. Ma…
#> 3 2 IUCN_ES www.uicn.es/web/p…
#> 4 1 IUCN_Europe Brooks, Emma. Eur…
#> 5 5 IUCN_PT Martins, Rogélia,…
#> 6 6 IUCN_UK https://hub.jncc.…
#> # Source: table<"diadesatlas"."iucn_level"> [?? x 3]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> 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"."lang85_85"> [?? x 12]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> id geom fid cat cat_ basin_id wso_id
#> <int> <pq_gmtry> <int> <int> <int> <int> <int>
#> 1 1 0103000020E6… 1 2 6 65 1.03e6
#> 2 2 0103000020E6… 2 4 17 271 8.38e4
#> 3 3 0103000020E6… 3 7 22 62 1.03e6
#> 4 4 0103000020E6… 4 15 38 51 6 e0
#> 5 5 0103000020E6… 5 16 38 51 6 e0
#> 6 6 0103000020E6… 6 17 38 51 6 e0
#> 7 7 0103000020E6… 7 24 47 81 1.03e6
#> 8 8 0103000020E6… 8 28 71 40 8.38e4
#> 9 9 0103000020E6… 9 32 78 164 8.41e4
#> 10 10 0103000020E6… 10 34 83 19 8.37e4
#> # … with more rows, and 5 more variables:
#> # ccm_name <chr>, ccm_area <dbl>, ccm_window <int>,
#> # seaoutlet_ <chr>, simplified <chr>
#> # Source: table<"diadesatlas"."species"> [?? x 5]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> species_id latin_name english_name local_name active
#> <int> <chr> <chr> <chr> <lgl>
#> 1 1 Acipenser … <NA> "" FALSE
#> 2 2 Acipenser … <NA> "" FALSE
#> 3 3 Acipenser … <NA> "" FALSE
#> 4 5 Alosa alge… <NA> "" FALSE
#> 5 7 Alosa casp… <NA> "" FALSE
#> 6 9 Alosa imma… <NA> "" FALSE
#> 7 10 Alosa kess… <NA> "" FALSE
#> 8 11 Alosa tana… <NA> "" FALSE
#> 9 12 Alosa volg… <NA> "" FALSE
#> 10 14 Caspiomyzo… <NA> "" FALSE
#> # … with more rows
#> # Source: table<"diadesatlas"."species_rectangle">
#> # [?? x 4]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> species_rectangle_id year species_id gid
#> <int> <int> <int> <int>
#> 1 2162 2016 4 3323
#> 2 2163 2017 4 3323
#> 3 2164 2016 4 3324
#> 4 2165 2015 4 3324
#> 5 2166 2015 4 3565
#> 6 2167 2016 6 3318
#> 7 2168 2017 6 3318
#> 8 2169 2011 6 3318
#> 9 2170 2008 6 3318
#> 10 2171 2010 6 3318
#> # … with more rows
#> # Source: table<"diadesatlas"."v_abundance"> [?? x
#> # 13]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> basin_id abundance_id abundance_level_id species_id
#> <int> <int> <int> <int>
#> 1 179 26667 3 8
#> 2 188 26668 1 8
#> 3 69 25726 1 4
#> 4 174 25727 1 4
#> 5 69 25730 1 6
#> 6 174 25731 1 6
#> 7 69 25734 2 8
#> 8 174 25735 NA 8
#> 9 69 25744 3 13
#> 10 174 25745 3 13
#> # … with more rows, and 9 more variables:
#> # year_from <int>, year_to <int>, basin_name <chr>,
#> # latin_name <chr>, simplified_name <chr>,
#> # fish_name <chr>, abundance_level_name <chr>,
#> # abundance_level_interpretation <chr>,
#> # abundance_level_interpretation_short <chr>
#> # Source: table<"diadesatlas"."v_maxvalue_es"> [?? x
#> # 4]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> category_id casestudy_id species_id esvalue_id
#> <int> <int> <int> <int>
#> 1 3 4 8 0
#> 2 1 5 6 2
#> 3 3 8 8 0
#> 4 1 5 19 0
#> 5 2 5 8 0
#> 6 3 8 20 0
#> 7 3 7 25 -1
#> 8 2 7 21 1
#> 9 2 9 4 0
#> 10 2 4 13 1
#> # … with more rows
#> # Source: table<"diadesatlas"."v_hybrid_model"> [?? x
#> # 17]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> hybrid_model_result_id basin_id species_id climatic_model_…
#> <int> <int> <int> <int>
#> 1 1395365 184 6 9
#> 2 1395366 184 6 9
#> 3 1395367 184 6 9
#> 4 1395368 184 6 9
#> 5 1395369 184 6 9
#> 6 1395370 184 6 9
#> 7 1395371 184 6 9
#> 8 1395372 184 6 9
#> 9 1395373 184 6 9
#> 10 1395374 184 6 9
#> # … 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>
#> # Source: table<"diadesatlas"."v_hybrid_model_mavg">
#> # [?? x 14]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> latin_name basin_name climatic_scenar… species_id
#> <chr> <chr> <chr> <int>
#> 1 Acipenser stu… Aa rcp45 4
#> 2 Acipenser stu… Aa rcp45 4
#> 3 Acipenser stu… Aa rcp45 4
#> 4 Acipenser stu… Aa rcp45 4
#> 5 Acipenser stu… Aa rcp45 4
#> 6 Acipenser stu… Aa rcp45 4
#> 7 Acipenser stu… Aa rcp45 4
#> 8 Acipenser stu… Aa rcp45 4
#> 9 Acipenser stu… Aa rcp45 4
#> 10 Acipenser stu… Aa rcp45 4
#> # … with more rows, and 10 more variables:
#> # basin_id <int>, year <int>, nit_min <dbl>,
#> # nit_mean <dbl>, nit_max <dbl>, hsi_min <dbl>,
#> # hsi_mean <dbl>, hsi_max <dbl>, nit_movingavg <dbl>,
#> # hsi_movingavg <dbl>
#> # Source: table<"diadesatlas"."v_ices_geom"> [?? x 4]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> ices_type gid icesname geom
#> <chr> <int> <chr> <pq_gmtry>
#> 1 rectangle 3159 22E6 0106000020E6100000010000000…
#> 2 rectangle 2247 21D6 0106000020E6100000010000000…
#> 3 rectangle 2256 30D6 0106000020E6100000010000000…
#> 4 rectangle 1966 27D3 0106000020E6100000010000000…
#> 5 rectangle 3177 44E6 0106000020E6100000010000000…
#> 6 rectangle 4058 37F7 0106000020E6100000010000000…
#> 7 rectangle 2258 32D6 0106000020E6100000010000000…
#> 8 rectangle 2727 12E1 0106000020E6100000010000000…
#> 9 rectangle 3092 40E5 0106000020E6100000010000000…
#> 10 rectangle 2052 19D4 0106000020E6100000010000000…
#> # … with more rows
#> # Source: table<"diadesatlas"."v_occurence"> [?? x 8]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> ices_type species_id gid fish_name latin_name
#> <chr> <int> <int> <chr> <chr>
#> 1 division 6 82 Allis shad Alosa alosa
#> 2 rectangle 8 3329 Twaite shad Alosa fallax
#> 3 rectangle 6 2629 Allis shad Alosa alosa
#> 4 rectangle 8 2626 Twaite shad Alosa fallax
#> 5 rectangle 8 3569 Twaite shad Alosa fallax
#> 6 rectangle 22 2996 European flo… Platichthys…
#> 7 rectangle 19 2620 Thinlip grey… Chelon rama…
#> 8 division 6 44 Allis shad Alosa alosa
#> 9 rectangle 22 3410 European flo… Platichthys…
#> 10 rectangle 19 3567 Thinlip grey… Chelon rama…
#> # … with more rows, and 3 more variables:
#> # simplified_name <chr>, icesname <chr>,
#> # nb_occurence <int64>
#> # Source:
#> # table<"diadesatlas"."v_species_ices_occurence"> [??
#> # x 8]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> ices_type gid species_id fish_name icesname
#> <chr> <int> <int> <chr> <chr>
#> 1 rectangle 3159 4 European sturgeon 22E6
#> 2 rectangle 3159 6 Allis shad 22E6
#> 3 rectangle 3159 8 Twaite shad 22E6
#> 4 rectangle 3159 13 European eel 22E6
#> 5 rectangle 3159 18 River lamprey 22E6
#> 6 rectangle 3159 20 European smelt 22E6
#> 7 rectangle 3159 21 Sea lamprey 22E6
#> 8 rectangle 3159 22 European flounder 22E6
#> 9 rectangle 3159 25 Atlantic salmon 22E6
#> 10 rectangle 3159 26 Sea trout 22E6
#> # … with more rows, and 3 more variables:
#> # latin_name <chr>, simplified_name <chr>,
#> # nb_occurence <int64>
#> # Source: table<"diadesatlas"."abundance"> [?? x 14]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> basin_id abundance_id presence_absence year_from
#> <int> <int> <int> <int>
#> 1 1 1 0 1951
#> 2 1 2 0 1851
#> 3 3 5 0 1951
#> 4 3 6 0 1851
#> 5 14 29 0 1951
#> 6 14 30 0 1851
#> 7 15 31 0 1951
#> 8 15 32 0 1851
#> 9 16 33 0 1951
#> 10 16 34 0 1851
#> # … with more rows, and 10 more variables:
#> # year_to <int>, period_comment <chr>,
#> # abundance_migratory_comment <chr>,
#> # abundance_resident_comment <chr>,
#> # bibliography <chr>, ecoregion_code <int>,
#> # ecoregion_name <chr>, abundance_migratory_id <int>,
#> # abundance_resident_id <int>, species_id <int>
#> # Source: table<"diadesatlas"."casestudy"> [?? x 4]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> casestudy_id casestudy_name publishable geom
#> <int> <chr> <lgl> <pq_gmtry>
#> 1 3 Normand-Breton … TRUE 0103000020E…
#> 2 4 Loire TRUE 0106000020E…
#> 3 6 Gipuzcoan TRUE 0106000020E…
#> 4 1 Waterford Harbo… TRUE 0106000020E…
#> 5 9 Mondego TRUE 0106000020E…
#> 6 8 Minho TRUE 0103000020E…
#> 7 2 Tamar-Taff-Frome TRUE 0106000020E…
#> 8 5 Gironde-Garonne… TRUE 0106000020E…
#> 9 7 Ulla TRUE 0103000020E…
#> # Source: table<"diadesatlas"."ecosystemic_services">
#> # [?? x 5]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> ecosystemic_services_id casestudy_id species_id
#> <int> <int> <int>
#> 1 89 1 6
#> 2 99 1 8
#> 3 95 1 18
#> 4 96 1 21
#> 5 90 1 25
#> 6 97 1 26
#> 7 93 1 20
#> 8 94 1 4
#> 9 91 1 13
#> 10 92 1 22
#> # … with more rows, and 2 more variables:
#> # subcategory_id <int>, esvalue_id <int>
#> # Source: table<"diadesatlas"."esvalue"> [?? x 3]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> 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"."hybrid_model_result">
#> # [?? x 9]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> hybrid_model_result_id basin_id species_id climatic_model_…
#> <int> <int> <int> <int>
#> 1 1395365 184 6 9
#> 2 1395366 184 6 9
#> 3 1395367 184 6 9
#> 4 1395368 184 6 9
#> 5 1395369 184 6 9
#> 6 1395370 184 6 9
#> 7 1395371 184 6 9
#> 8 1395372 184 6 9
#> 9 1395373 184 6 9
#> 10 1395374 184 6 9
#> # … with more rows, and 5 more variables:
#> # climatic_scenario <chr>, year <int>, nit <dbl>,
#> # hsi <dbl>, saturation_rate <dbl>
#> # Source: table<"diadesatlas"."locale"> [?? x 4]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> locale_id country_code english_label country_label
#> <int> <chr> <chr> <chr>
#> 1 59 fr ALL EXISTING S… TOUTES LES SO…
#> 2 60 fr Allis shad Grande alose
#> 3 61 fr Atlantic salmon Saumon atlant…
#> 4 62 fr Barrow Barrow
#> 5 63 fr Couesnon Couesnon
#> 6 64 fr Cultural servi… Services cult…
#> 7 65 fr Deba Deba
#> 8 66 fr Dordogne Dordogne
#> 9 67 fr Dorset Frome Dorset Frome
#> 10 68 fr European eel Anguille euro…
#> # … with more rows
#> # Source: table<"diadesatlas"."outlet_distance"> [??
#> # x 3]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> 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"."species_division"> [??
#> # x 4]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> species_division_id year species_id gid
#> <int> <int> <int> <int>
#> 1 1 2015 4 47
#> 2 2 2012 4 47
#> 3 3 2016 4 40
#> 4 4 2015 4 40
#> 5 5 2016 4 39
#> 6 6 2017 4 39
#> 7 7 2014 4 39
#> 8 8 2015 6 10
#> 9 9 2004 6 10
#> 10 10 2012 6 8
#> # … with more rows
#> # Source: table<"diadesatlas"."species_iucn"> [?? x
#> # 4]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> species_iucn_id species_id iucn_classification_id
#> <int> <int> <int>
#> 1 1 4 1
#> 2 2 13 1
#> 3 3 18 1
#> 4 4 21 1
#> 5 5 25 1
#> 6 6 26 1
#> 7 7 20 1
#> 8 8 19 1
#> 9 9 6 1
#> 10 10 22 1
#> # … with more rows, and 1 more variable:
#> # iucn_level_id <int>
#> # Source: table<"diadesatlas"."subcategory"> [?? x 3]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> subcategory_id subcategory_name category_id
#> <int> <chr> <int>
#> 1 1 Food provision from commer… 2
#> 2 2 Option value associated to… 2
#> 3 3 Redistribution of nutrient… 3
#> 4 4 Traditional know-how (fish… 1
#> 5 5 Traditional know-how relat… 1
#> 6 6 Gastronomy around species … 1
#> 7 7 Natural heritage and natur… 1
#> 8 8 Recreation sport fishing (… 1
#> 9 9 The potential for environm… 1
#> # Source: table<"diadesatlas"."v_basin"> [?? x 3]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> basin_id basin_name geom
#> <int> <chr> <pq_gmtry>
#> 1 199 Imsa 0103000020E610000001000000060000…
#> 2 68 Guadiana 0103000020E610000001000000B70000…
#> 3 368 Sor 0103000020E610000001000000080000…
#> 4 174 Vida 0106000020E610000002000000010300…
#> 5 103 Navia 0103000020E610000001000000210000…
#> 6 16 Auzance 0103000020E610000001000000080000…
#> 7 60 Garonne 0106000020E610000003000000010300…
#> 8 169 Tyne 0103000020E610000001000000220000…
#> 9 22 Blackwater 0106000020E610000002000000010300…
#> 10 50 Eden 0103000020E6100000010000001C0000…
#> # … with more rows
#> # Source:
#> # table<"diadesatlas"."v_ecosystemic_services"> [?? x
#> # 14]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> category_id casestudy_id species_id subcategory_id
#> <int> <int> <int> <int>
#> 1 2 9 21 99
#> 2 1 9 19 99
#> 3 3 5 20 3
#> 4 1 4 4 7
#> 5 2 7 20 1
#> 6 1 9 6 99
#> 7 1 1 19 8
#> 8 2 3 4 99
#> 9 2 5 26 2
#> 10 2 8 20 99
#> # … with more rows, and 10 more variables:
#> # esvalue_id <int>, subcategory_name <chr>,
#> # category_name <chr>, latin_name <chr>,
#> # simplified_name <chr>, fish_name <chr>,
#> # casestudy_name <chr>, esvalue_code <chr>,
#> # esvalue_name <chr>, sortorder <int>
#> # Source: table<"diadesatlas"."v_iucn"> [?? x 7]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> species_id latin_name simplified_name english_name
#> <int> <chr> <chr> <chr>
#> 1 6 Alosa alosa A. alosa Allis shad
#> 2 22 Platichthys… P. flesus European flo…
#> 3 8 Alosa fallax A. fallax Twaite shad
#> 4 13 Anguilla an… A. anguilla European eel
#> 5 18 Lampetra fl… L. fluviatilis River lamprey
#> 6 21 Petromyzon … P. marinus Sea lamprey
#> 7 25 Salmo salar S. salar Atlantic sal…
#> 8 26 Salmo trutta S. trutta Sea trout
#> 9 20 Osmerus epe… O. eperlanus European sme…
#> 10 19 Chelon rama… C. ramada Thinlip grey…
#> # … with more rows, and 3 more variables:
#> # iucn_classification_code <chr>,
#> # iucn_level_code <chr>, iucn_level_name <chr>
casestudy <- tbl(con, dbplyr::in_schema("diadesatlas", "casestudy"))
casestudy
#> # Source: table<"diadesatlas"."casestudy"> [?? x 4]
#> # Database: postgres
#> # [diadesatlas_owner@localhost:5432/diades]
#> casestudy_id casestudy_name publishable geom
#> <int> <chr> <lgl> <pq_gmtry>
#> 1 3 Normand-Breton … TRUE 0103000020E…
#> 2 4 Loire TRUE 0106000020E…
#> 3 6 Gipuzcoan TRUE 0106000020E…
#> 4 1 Waterford Harbo… TRUE 0106000020E…
#> 5 9 Mondego TRUE 0106000020E…
#> 6 8 Minho TRUE 0103000020E…
#> 7 2 Tamar-Taff-Frome TRUE 0106000020E…
#> 8 5 Gironde-Garonne… TRUE 0106000020E…
#> 9 7 Ulla TRUE 0103000020E…
# 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_outlet")) %>% 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"))
# set query and reproject to EPSG:4326 for GeoJSON creation
# (the real query is more involved with a bounding box etc)
qry <- "SELECT ST_Transform(diadesatlas.basin_outlet.simplified_geom, 4326) as geom FROM diadesatlas.basin_outlet"
# i then read as an sf object
pols <- sf::st_read(con, query = qry, geom = "geom")
# and convert to GeoJSON
pols.js <- geojsonio::geojson_json(pols)
# pols %>% geojson::as.geojson() %>% geojson::geo_write("plop.json")
# options(readr.default_locale=readr::locale(tz="Europe/Berlin"))
# df60 <- get_eurostat_geospatial(resolution = 60)
#
# CE.sf <- df60 %>%
# dplyr::filter(LEVL_CODE == 2 & CNTR_CODE %in% c("AT","CZ","DE","HU","PL","SK")) %>%
# dplyr::select(NUTS_ID)
plot(pols)
pols %>%
ggplot() +
geom_sf(color = "black", size = 0.4)
# CE = sf::as_Spatial(pols)
leaflet() %>%
addProviderTiles("CartoDB.Positron") %>%
addPolygons(data = pols, color = "green")
DBI::dbDisconnect(con)