Creating database for migratory fishes

To support a holistic and transparent assessment

Cédric Briand, Jules Oliviéro, Jani Helminen

What is WP3?

And why?

These species are found all over Europe
To conduct large-scale modeling, a significant amount of data is required
Such data must be easily accessible

FAIR data bases – Findability, Accessibility, Interoperability, Reusability
Transparent Assessment Framework (TAF)
Spatialised and climate-enhanced SAM

Similar needs in diadromous assessment groups

Different levels of advancement but common “deadlocks”
Some things will require lots of work
- If we start to work together now, it will be more efficient

- +Shad, lamprey…?

Current status of databases

Available databases are for marine habitats and are designed for fisheries data (e.g., ICES RDBES)
Working groups have created their own databases:
- Stored in different database formats
- Different level of detail
- Sometimes large Excel files and/or external databases
- Within each group, individuals are dedicated to database work

Our Aim

Develop four database templates to facilitate more advanced and transparent assessment of migratory fish species.
These databases will be tailored for freshwater stages, incorporating a hydrographic network and migration barrier data.

Subtasks
- Identify the needs of different working groups
- Discuss interoperability and storage with ICES and GFCM
- Propose a template suitable for different species
- Validate the structure by testing the integration of data provided by the project partners

Identifying WG needs, discuss with ICES & GFCM

Several discussions with different organisations, people, groups
Presenting our main idea, identifying the needs of others
What we propose in this workshop, we want to discuss with the WGs
- Finding common ground and differences
- Continuing discussions at the WGs

Databases should be designed to be interoperable, capable of handling nested spatial scales and able to address a variety of stressors.
Our goal is to deliver databases / referentials that can be effectively utilized by working groups (WGBAST, WGNAS, WGEEL, WGTRUTTA) in the long term.

What is needed?

Creating the base, not (necessarily) the data

Creating common dictionaries

Create separate tables for each working group
- Some common information for all WGs, some separate
Common “simple” layer for international assessment needed - open data
- All data does not (necessarily) have to be common or open

Potential for updates in the future
- At some point there will be better international data available
Possibility to have better data for some areas / WGs
- National data will (always) be better

Producing common dictionaries together with ICES

Will be used for both:
- Raw data in RDBES for catch and landings
- Stock assessment database
Examples: age and lifestage
Referencials for habitat
Hierarchical so that spatial units can be integrated separately for different WGs

And therefore, the Deliverables

A quick look - details will be discussed on Tuesday

Design four (now five) international databases

Electrofishing
Habitat
Migration obstacles
Model parameters
Life history strategies, biological data
Templates will be created

Electrofishing data

Currently it is just different templates for data call
We plan produce something together with the WGs
- Ask all WGs what it should be - then propose a common structure
If it looks good, we will migrate to a common database
No need to have a separate EF database for different species(?)

Habitat and migration obstacles

Migration obstacles
Habitat availability, for models
Extend the work from e.g., AMBER and Sudoang
Use habitat work from Philip McGinnity and Andrew French

Model parameters, life history parameters

Biological and model data that are not centrally stored
Moving towards Transparent Assessment Framework
Link with ICES working groups
Reuse WGEEL, WGNAS structures
New parameters in DIASPARA project

Our feeling is that a common format can be found for salmon, trout and eel!

(without large compromises)

Biological data, biometry

GFCM - RDBFIS:
- This information could be used in the RDBFIS database as well. Collaboration where possible.
DCF data:
- Potential for RDBES?

RDBES proposal - priorities

Commercial landings
Recreational landings
Store data that we currently store in sampling that are related to DCF sampling (raw data)
Once this is done, talk about storing electrofishing data
SAM model parameters - start with postgres and see whether it fits somewhere in ICES
DAM and hydro db - Maybe JRC?
Scripts to produce data from the RDBES will be needed, it has to be TAF

Finally, a bit about our principles

Everything is online

Progress can be followed throughout
Several releases: beta versions privately in Zenodo and then publicly as soon as the data are validated
Can be commented during the project
Postgis and parquet formats using R.

DATSU

DATSU is not one of project deliverables, but
As common dictionaries are used, DATSU can streamline future data calls

Principles

Dictionaries
- Follow ICES vocab, create when not existing (search for all vocab)
- Allow for the hierarchichal spatial scale to be integrated in queries. Separate the input of different WGs in different schema but the same db (postgres).
- Some common dictionaries - some separate dictionaries
Model
- Separate parameters for model and aggregated data built at regional / national/ emu level
- Model parameters should be described in a separate metadata table (like WGNAS).
Keep changes minimal, don’t add work to working group
Use ICES existing db whenever possible
Use DATSU to check for consistency / trace data.
Raw data should go to RDBES (starting with landings), we’ll need a timeline for WG work.

Things to be agreed on

Electrofishing data
Get the needs from WGs and propose a common format, maybe within RDBES
SAM db common to all groups, stored somewhere in ICES
Development of common dictionaries for things like Age, lifestages, all GIS geographical units
Should all DCF data go to RDBES?
GFCM - RDBFIS?
More discussion tomorrow!