INTRODUCTION

Why would we need another another database ?

  • WGEEL, WGBAST and WGNAS (+WGTRUTTA) have each developed their own databases.
  • These need to be maintained in the long term.

  • Integrating each database in ICES databases would take a lot of resources.

  • FAIR (Findability, Accessibility, Interoperability, Reusability).

  • Use of TAF (Transparent assessment Framework).

  • Use of DATSU (quality insurance and checking procedures for data call).

OBJECTIVES

We intend to build a common database structure (same referentials several DB to store)

  • Data from models at the scale of the management unit (WGEEL, WGNAS, WGBAST)

  • Continental habitat in support of ICES (e.g. landings in continental habitats) (WGEEL, WGNAS, WGBAST, WGTRUTTA)

  • Individual and group metrics, series of data

  • DB for dams/migration obstacles

  • DB of Electrofishing

General structure of the DB

DatabaseRelations ref ref RDBES RDBES RDBES->ref Model_DB Model_DB Model_DB->ref Metrics_Series Metrics_Series Metrics_Series->ref Metrics_Series->Model_DB TAF E_fishing E_fishing Regional_models Regional_models E_fishing->Regional_models model Dam_DB Dam_DB Dam_DB->Regional_models model Regional_models->Model_DB TAF

DATA AND REFERENTIALS

schema ref ref        tr_species_spe        tr_country_cou        tr_icworkinggroup_wkg        tr_version_ver        tr_metric_mtr        tr_category_cat        tr_destination_dest        tr_metadata_met        tr_area_are refeel refeel                               refeel->ref dateel dateel                               dateel->ref dateel->refeel refbast refbast                               refbast->ref datbast datbast                               datbast->ref datbast->refbast refnas refnas                               refnas->ref datnas datnas                               datnas->ref datnas->refnas reftrutta reftrutta                               reftrutta->ref dattrutta dattrutta                               dattrutta->ref dattrutta->reftrutta refwgeel refwgeel refwgeel->ref refwgeel->refeel salmoglob salmoglob salmoglob->ref salmoglob->refnas
Figure 1: Structure of the schema in diaspara. Dashed arrow indicate an import of data from existing

MODEL DB

The database structure could be simplified to the following structure

Report on DB build

Group and individual metric DB

  • DIASPARA WP2 data collection.

  • Based on our analysis of the three DB’s: a common database structure for all species is possible

  • This structure will work on the same referential structure.

Excel file for the DB

GENERAL STRUCTURE OF THE HABITAT DB

clusterA Hydroshed, EU-Hydro clusterB Country, regions, EMU, ICES divisions, Stock Units clusterC A Celtic Seas B Greater North Sea E ES B->E C Baltic D FR G Hydroshed | Topo | Habitat | Eel | Salmon | Trout E->G H EU-Hydro | Topo | Habitat | Eel | Salmon | Trout E->H F SE L National | Topo | Habitat | Eel | Salmon | Trout I Salmon River / Stock units J Fishing areas K EMU (Eel Managment Unit) K->A

Splitting per ICES Ecoregion

All Catchments corresponding to ICES ecoregions, source of the catchments hydrosheds.

WGNAS

We’ll need help to populate this.

Database structure

clusterA Panpopulation clusterB Complex clusterZ Major clusterY Subareas clusterX Division clusterW Sudivision clusterV Unit clusterC Stock clusterD Country clusterE Assessment_unit clusterF Regional clusterG River clusterH River_section
Figure 2: The nested structure layed out in table tr_area_area, dotted box indicate that the level is optional. This table will be created specifically for each group.

Major

Figure 3: Map of ICES fishing areas at Major level, source NAFO, FAO, ICES, GFCM.

Division

Figure 4: Map of ICES fishing areas at division level, source NAFO, FAO, ICES, GFCM.

Subdivision

Figure 5: Map of ICES fishing areas at subdivision level, source NAFO, FAO, ICES, GFCM.

Habitat DB (EU-Hydro)

  • We will integrate the work by Andrew French
  • Work in progress
  • Also used as a reference for WGTRUTTA (tools developed by ICES)

Habitat DB

  • Format : Geoparquet, Postgis schema
  • Hydroatlas. Version 2 is coming!
  • EU-Hydro different information but smaller coverage
  • The idea is to have access to both
  • We intend to deliver several releases (ZENODO) first one coming in april

Example from WGBAST

Salmon assessment units in the Baltic sea.
  • Assessment units, genetic origin, available habitat
  • Connection to marine: (ICES) fishing areas
  • River names (ICES), river coding

Example from WGBAST : Whole stock level

Figure 6: Whole stock level WGBAST, souce hydrosheds for the continental habitat

Example from WGBAST : Subdivision level

Figure 7: Subdivision level WGBAST, souce hydrosheds for the continental habitat

Example from WGBAST : Unit level

Figure 8: Units level WGBAST

Examples of use

Screenshot

Examples of use

Screenshot

Summary - Benefits for the WG

  • Work with ICES tools (DATSU, DB, data calls)
  • Data calls using Shiny / Datsu
  • DB manager (at ICES?) can handle all migratory species
  • One shiny instead of two / three
  • People moving between WGs know the data & Inter-WG Collaboration
  • Direct access to WGNAS -> modeling
  • Potential for spatial models: available habitat, scenarios, etc.
  • Please contact us, read our reports and let us know. We’ll be giving a presentation of the WP3 package work soon. https://projets_eabx.pages.mia.inra.fr/diaspara/workpackage3.html

Thank you!