Atlas of European Eel Distribution (Anguilla anguilla) in Portugal, Spain and France

DESCRIPTION

----------------

VERSIONS

version1.0.1 fixes problem with functions

version1.0.2 added table dbeel_rivers.rn_rivermouth with GEREM basin, distance to Gibraltar and link to CCM.

version1.0.3 fixes problem with functions

----------------

The SUDOANG project aims at providing common tools to managers to support eel conservation in the SUDOE area (Spain, France and Portugal). VISUANG is the SUDOANG Interactive Web Application that host all these tools . The application consists of an eel distribution atlas (GT1), assessments of mortalities caused by turbines and an atlas showing obstacles to migration
(GT2), estimates of recruitment and exploitation rate (GT3) and escapement (chosen as a target by the EC for the Eel Management Plans) (GT4). In addition, it includes an interactive map showing sampling results from the pilot basin network produced by GT6.

The eel abundance for the eel atlas and escapement has been obtained using the Eel Density Analysis model (EDA, GT4's product). EDA extrapolates the abundance of eel in sampled river segments to other segments taking into account how the abundance, sex and size of the eels change depending on different parameters. Thus, EDA requires two main data sources: those related to the river
characteristics and those related to eel abundance and characteristics.

However, in both cases, data availability was uneven in the SUDOE area. In addition, this information was dispersed among several managers and in different formats due to different sampling sources: Water Framework Directive (WFD), Community Framework for the Collection, Management and Use of Data in the Fisheries Sector (EUMAP), Eel Management Plans, research groups, scientific
papers and technical reports. Therefore, the first step towards having eel abundance estimations including the whole SUDOE area, was to have a joint river and eel database. In this report we will describe the database corresponding to the river’s characteristics in the SUDOE area and the eel abundances and their characteristics.

In the case of rivers, two types of information has been collected: 

• River topology (RN table): a compilation of data on rivers and their topological and hydrographic characteristics in the three countries.
• River attributes (RNA table): contains physical attributes that have fed the SUDOANG models.

The estimation of eel abundance and characteristic (size, biomass, sex-ratio and silver) distribution at different scales (river segment, basin, Eel Management Unit (EMU), and country) in the SUDOE area obtained with the implementation of the EDA2.3 model has been compiled in the RNE table (eel predictions).

CURRENT ACTIVE PROJECT

The project is currently active here : gitlab forgemia

TECHNICAL DESCRIPTION TO BUILD THE POSTGRES DATABASE

1. Build the database in postgres.

All tables are in ESPG:3035 (European LAEA). The format is postgreSQL database. You can download other formats (shapefiles, csv), here SUDOANG gt1 database.

Initial command

# 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      
createdb -U postgres eda2.3 
psql -U postgres eda2.3 
# this will open a command with # where you can launch the commands in the next box     

Within the psql command

 create extension "postgis";
 create extension "dblink"; 
 create extension "ltree"; 
 create extension "tablefunc";
 create schema dbeel_rivers;
 create schema france;
 create schema spain;
 create schema portugal;
 -- type \q to quit the psql shell

Now the database is ready to receive the differents dumps. The dump file are large. You might not need the part including unit basins or waterbodies. All the tables except waterbodies and unit basins are described in the Atlas. You might need to understand what is inheritance in a database. https://www.postgresql.org/docs/12/tutorial-inheritance.html

2. RN (riversegments)

These layers contain the topology (see Atlas for detail)

• dbeel_rivers.rn
• france.rn
• spain.rn
• portugal.rn

Columns (see Atlas)

dbeel_rivers.rn_rivermouth

# dbeel_rivers.rn ! mandatory => table at the international level from which 
# the other table inherit 
# even if you don't want to use other countries 
# (In many cases you should ... there are transboundary catchments) download this first.
# the rn network must be restored firt ! 
#table rne and rna refer to it by foreign keys.
pg_restore -U postgres -d eda2.3 "dbeel_rivers.rn.backup"
#france
pg_restore -U postgres -d eda2.3 "france.rn.backup"
# spain
pg_restore -U postgres -d eda2.3 "spain.rn.backup"      
# portugal
pg_restore -U postgres -d eda2.3 "portugal.rn.backup"  
# rivermouth and basins, this file contains GEREM basins, distance to Gibraltar, the link to CCM id 
 for each basin flowing to the sea.
pg_restore -U postgres -d eda2.3 "dbeel_rivers.rn_rivermouth.backup"
# with the schema you will probably want to be able to use the functions, but launch this only after 
# restoring rna in the next step
psql -U postgres -d eda2.3 -f "function_dbeel_rivers.sql"

3. RNA (Attributes)

This corresponds to tables

• dbeel_rivers.rna
• france.rna
• spain.rna
• portugal.rna

Columns (See Atlas)

Code :

pg_restore -U postgres -d eda2.3 "dbeel_rivers.rna.backup"
pg_restore -U postgres -d eda2.3 "france.rna.backup"
pg_restore -U postgres -d eda2.3 "spain.rna.backup"      
pg_restore -U postgres -d eda2.3 "portugal.rna.backup"   

4. RNE (eel predictions)

These layers contain eel data (see Atlas for detail)

• dbeel_rivers.rne
• france.rne
• spain.rne
• portugal.rne

Columns (see Atlas)

Code for restauration

pg_restore -U postgres -d eda2.3 "dbeel_rivers.rne.backup"
pg_restore -U postgres -d eda2.3 "france.rne.backup"
pg_restore -U postgres -d eda2.3 "spain.rne.backup"      
pg_restore -U postgres -d eda2.3 "portugal.rne.backup"

5. Unit basins

Units basins are not described in the Altas. They correspond to the following tables :

• dbeel_rivers.basinunit_bu
• france.basinunit_bu
• spain.basinunit_bu
• portugal.basinunit_bu
• france.basinunitout_buo
• spain.basinunitout_buo
• portugal.basinunitout_buo

The unit basins is the simple basin that surrounds a segment. It correspond to the topography unit from which unit segment have been calculated. ESPG 3035. Tables bu_unitbv, and bu_unitbvout inherit from dbeel_rivers.unit_bv. The first table intersects with a segment, the second table does not, it corresponds to basin polygons which do not have a riversegment.

Source :

• Portugal

https://sniambgeoviewer.apambiente.pt/Geodocs/gml/inspire/HY_PhysicalWaters_DrainageBasinGeoCod.ziphttps://sniambgeoviewer.apambiente.pt/Geodocs/gml/inspire/HY_PhysicalWaters_DrainageBasinGeoCod.zip

• France

In france unit bv corresponds to the RHT (Pella et al., 2012)

• Spain

http://www.mapama.gob.es/ide/metadatos/index.html?srv=metadata.show&uuid=898f0ff8-f06c-4c14-88f7-43ea90e48233

pg_restore -U postgres -d eda2.3 'dbeel_rivers.basinunit_bu.backup'
# france
pg_restore -U postgres -d eda2.3 "france.basinunit_bu.backup"
pg_restore -U postgres -d eda2.3 "france.basinunitout_buo.backup"
# spain
pg_restore -U postgres -d eda2.3 "spain.basinunit_bu.backup"
pg_restore -U postgres -d eda2.3 "spain.basinunit_bu.backup"  
# portugal
pg_restore -U postgres -d eda2.3 "portugal.basinunit_bu.backup" 
pg_restore -U postgres -d eda2.3 "portugal.basinunitout_buo.backup"  

6- Waterbodies

In these tables we have have kept the structure from the source table in WISE or from the bd_topage.

• dbeel_rivers.waterbody_unitbv 
• portugal.waterbody_unitbv
• france.waterbody_unitbv
• spain.waterbody_unitbv

In France, corresponds to the hydrographic surface from bd_topage. 

https://bdtopage.eaufrance.fr/page/documents-ressources

In spain it corresponds to . Cuencas hidrográficas de los principales ríos definidos en el artículo 3 de la Directiva Marco del Agua (DMA), in Portugal to HY_PhysicalWaters_DrainageBasinMAgua.

pg_restore -U postgres -d eda2.3 "dbeel_rivers.waterbody_unitbv.backup" 
pg_restore -U postgres -d eda2.3 "portugal.waterbody_unitbv.backup" 
pg_restore -U postgres -d eda2.3 "france.waterbody_unitbv.backup" 

7- functions

The functions can be found in dbeel_rivers_functions.sql you can read the examples there and description of the functions, here is a quick example showing the functionalities.

dbeel_rivers.get_path provides the path between two idsegments of the same basin :

select dbeel_rivers.get_path (113670,114115,'FR')
-- FR113618.FR114065.FR114053.FR114042

dbeel_rivers.get_distance calculates the distance between two rivers segments (including the distance of the idsegments themselves)

select  dbeel_rivers.get_distance (113670,114115,'FR'); 
--12669

dbeel_rivers.upstream_segments_rn(TEXT) takes an upstream segment and returns a vector of idsegments attention this function is slower than national counterparts, check in schema spain portugal and france for quicker functions. It is use for instance to calculate all eels coming from the upstream basin.

SELECT  dbeel_rivers.upstream_segments_rn('FR114042'); 
/*
FR114042
FR114053
FR113982
FR114034
...
*/

dbeel_rivers.upstream_segments_rn_sti(TEXT) takes an upstream segment and a TABLE with idsegment, target, source, this in more convenient for later use of routing functions (like get path) which require source and target
dbeel_rivers.downstream_segments_rn(TEXT) takes a segment and returns the path to the sea.

SELECT dbeel_rivers.downstream_segments_rn('SP227795');