Continental and marine habitat for diadromous Fishes in Europe, United States and Canadian East coast. These files contain the referentials for river, lakes and catchments from hydrosheds, plus referentials based on hydroshed and ICES and GFCM referentials in the marine area to describe a hierarchy of habitats, for ICES diadromous fish working groups WGNAS, WGBAST and WGEEL. Versions Version 0.0.4 (march 2026) (THIS RELEASE) Added excel file with named rivers (WGNAS, WGBAST) Version 0.0.3 (march 2026) fixed issue of corrupted format when trying to restore files (for gpc files) tested on server postgres 17.0 Version 0.0.2 (January 2026) fixed geometry format in parquet for lakes Version 0.0.1 (December 2025) Eventual structural modifications depending on feedback from WGNAS and WGBAST Adding referential table for WGEEL Version 0.0.1-beta (July 2025) Beta release for view and test by DIASPARA and WORKING GROUPS. Integration with migdb vocabulary. Starting the integration of specific tables. Adding names to rivers corresponding to ICES vocabularies Version 0.0.1-pre-beta (mid June 2025) Adding referential tables for hierarchical structure Structure for WGBAST and WGNAS Version 0.0.1-alpha (mid February 2025) Alpha release for view and test by DIASPARA work here : Git issue using the hydrosheds Download postgres database If you don't have postgres, download it, make sure to use the stackbuilder program to download postgis. open a shell with command CMD Move to the place where you have downloaded the file using the following command cd c:/path/to/my/folder Note psql must be accessible, in windows you can add the path to the postgres bin folder, otherwise you need to add the full path to the postgres bin folder see link to instructions below below we assume that the user you are using to postgres createdb -U postgres diaspara psql -U postgres diaspara This will open a command with # where you can launch the following SQL command CREATE EXTENSION postgis; You will need this because we only restore part of the database : the schema corresponding to different locations.You will probably want to create roles, if not it's not a problem you'll get some warnings that grants were not created for this user CREATE ROLE diaspara_admin; CREATE ROLE diaspara_read; You need to have these schema ready to restore the content of some tables CREATE SCHEMA ref; CREATE SCHEMA refeel; CREATE SCHEMA refnas; CREATE SCHEMA refbast; Now you will have saved the files in the c:/path/to/my/folder location where you are currently working. You need to use pg_restore to restore the files If you want to restore everything in one go use : pg_restore -U postgres -f all_habitat.pgc This will restore all schemas for different ICES areas plus the habitat tables in schema ref, refbast, refnas and refeel If you are just interested in a limited set of areas : The first file to restore is habitat. This is necessary as all tables inherit from habitat. pg_restore -U postgres -f habitat.pgc Then if for instance if you want to use h_med_central.pgc use : pg_restore -U postgres -f h_med_central.pgc all_ref.gpc contains the code for schema ref, refnas, refeel, refbast (habitats for the three working groups) : Download Parquet format You can use the files straight from R or Qgis. To use with R you will need Arrow and DuckDB packages. Tutorials If you need any help to use those, please see the webinar videos and presentation documents that can be found here. Project page https://diaspara.bordeaux-aquitaine.inrae.fr Specific work done by WP3 can be found here. Description and details about the creation of the Habitat Database can be found here. Acknowlegment hydrosheds : Linke, S., Lehner, B., Ouellet Dallaire, C., Ariwi, J., Grill, G., Anand, M., Beames, P., Burchard-Levine, V., Maxwell, S., Moidu, H., Tan, F., Thieme, M. (2019). Global hydro-environmental sub-basin and river reach characteristics at high spatial resolution. Scientific Data 6: 283. doi: https://doi.org/10.1038/s41597-019-0300-6

Example use of Arrow data from Zenodo and R # install packages (uncomment to use) #install.packages("zen4R") #install.packages("purrr") #install.packages("arrow") #install.packages("sfarrow") library(zen4R) library(arrow) library(sfarrow) library(purrr) library(ggplot2) # this will create a directory, use setwd() if you want to # change the directory to put the files dir.create("download_zenodo") # you can list the files in Zenodo using the following script # z purrr::map(\(x)unzip(zipfile=x, exdir ="download_zenodo")) # Open with arrow ds select(are_lev_code) |> unique() |> collect() mymap filter(are_lev_code == "Assessment_unit") |> collect() mymap$geometry req_user_agent("R (httr2) - ICES ecoregions download") |> req_timeout(120) resp <- req_perform(req, path = zip_path) dir_create(unzip_dir, recurse = TRUE) unzipped_files <- unzip(zipfile = zip_path, exdir = unzip_dir) shp <- unzipped_files[grepl("\\.shp$", unzipped_files, ignore.case = TRUE)] # Read with sf ecoregions_sf <- sf::st_read(shp, quiet = TRUE) plot(ecoregions_sf["Ecoregion"], main = "ICES Ecoregions") R function to get parents areas library(DBI) library(RPostgres) library(dplyr) con <- DBI::dbConnect(drv=RPostgres::Postgres(), dbname = "diaspara", host = "myhost", user = "diaspara_read", password = "************" ) area <- dbGetQuery(con, "SELECT are_id, are_are_id, are_code, are_lev_code, are_ismarine, are_name FROM refbast.tr_area_are;") save(area, file = "area.Rdata") get_area <- function(are_id, area, tab=data.frame()) { if (is.na(are_id)) { return(tab) } else { if (nrow(tab)==0){ tab <- area[area$are_id == are_id,] } else { tab <- rbind(tab, area[area$are_id == are_id,]) } are_id <- tab[nrow(tab), "are_are_id"] get_area(are_id, area, tab) } } # So we can use a recursive from R hierar <- get_area(area,are_id = 1275) # or from postgres get_area_postgres <- function( con, are_id){ return(dbGetQuery(con, "SELECT * FROM ref.get_parent_area(1275, 'WGBAST')")) } Get parents in Postgres : recursive function DROP FUNCTION ref.get_parent_area(integer, TEXT); CREATE OR REPLACE FUNCTION ref.get_parent_area(_are_id integer, _are_wkg_code TEXT) RETURNS TABLE(are_id integer, are_are_id INTEGER, are_code TEXT,are_lev_code TEXT, are_ismarine BOOLEAN, are_rivername TEXT) LANGUAGE plpgsql AS $function$ BEGIN RETURN QUERY WITH RECURSIVE get_parents AS( -- anchor member SELECT r0.are_id, r0.are_are_id, r0.are_code, r0.are_lev_code, r0.are_ismarine, r0.are_name FROM ref.tr_area_are r0 WHERE r0.are_id = $1 and r0.are_wkg_code = $2 UNION -- recursive term SELECT r1.are_id, r1.are_are_id, r1.are_code, r1.are_lev_code, r1.are_ismarine, r1.are_name FROM get_parents JOIN ref.tr_area_are r1 ON r1.are_id = get_parents.are_are_id WHERE r1.are_wkg_code = $2) SELECT * FROM get_parents; END $function$ ; GRANT ALL ON FUNCTION ref.get_parent_area(integer, text) TO diaspara_read; hierar <- get_area_postgres(con, are_id = 1275) # Lets say you have the main_river code as a vector from your points (you know this by spatial join between points and the river segment) main_rivers <- c("20056363", "20056030") selected_areas <- dplyr::left_join(data.frame("main_riv"= main_rivers), area, by = join_by("main_riv" == "are_code")) # to work with more than one argument to the function I would use mapply ll <- mapply(get_area, selected_areas$are_id, MoreArgs = list(area=area),SIMPLIFY = FALSE) bind_rows(ll)