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

<strong>DESCRIPTION</strong> <strong>----------------</strong> 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<br> (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<br> 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<br> 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: <strong>River topology</strong> (RN table): a compilation of data on rivers and their topological and hydrographic characteristics in the three countries. <strong>River attributes</strong> (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 <strong>RNE table (eel predictions)</strong>. <strong>CURRENT ACTIVE PROJECT</strong> The project is currently active here : gitlab forgemia <strong>TECHNICAL DESCRIPTION TO BUILD THE POSTGRES DATABASE</strong> <strong>1. Build the database in postgres.</strong> 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 <pre><code class="language-bash"># 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 </code></pre> Within the psql command <pre><code class="language-sql"> 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</code></pre> 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 <strong>2. RN (riversegments)</strong> These layers contain the topology (see Atlas for detail) dbeel_rivers.rn france.rn spain.rn portugal.rn Columns (see Atlas) gid idsegment source target lengthm nextdownidsegment path isfrontier issource seaidsegment issea geom isendoreic isinternational country dbeel_rivers.rn_rivermouth seaidsegment geom (polygon) gerem_zone_3 gerem_zone_4 (used in EDA) gerem_zone_5 ccm_wso_id country emu_name_short geom_outlet (point) name_basin dist_from_gibraltar_km name_coast basin_name <pre><code class="language-bash"># dbeel_rivers.rn ! mandatory =&gt; 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"</code></pre> <strong>3. RNA (Attributes)</strong> This corresponds to tables dbeel_rivers.rna france.rna spain.rna portugal.rna Columns (See Atlas) idsegment altitudem distanceseam distancesourcem cumnbdam medianflowm3ps surfaceunitbvm2 surfacebvm2 strahler shreeve codesea name pfafriver pfafsegment basin riverwidthm temperature temperaturejan temperaturejul wettedsurfacem2 wettedsurfaceotherm2 lengthriverm emu cumheightdam riverwidthmsource slope dis_m3_pyr_riveratlas dis_m3_pmn_riveratlas dis_m3_pmx_riveratlas drought drought_type_calc Code : <pre><code class="language-bash">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" </code></pre> <strong>4. RNE (eel predictions)</strong> These layers contain eel data (see Atlas for detail) dbeel_rivers.rne france.rne spain.rne portugal.rne Columns (see Atlas) idsegment surfaceunitbvm2 surfacebvm2 delta gamma density neel beel peel150 peel150300 peel300450 peel450600 peel600750 peel750 nsilver bsilver psilver150300 psilver300450 psilver450600 psilver600750 psilver750 psilver pmale150300 pmale300450 pmale450600 pfemale300450 pfemale450600 pfemale600750 pfemale750 pmale pfemale sex_ratio cnfemale300450 cnfemale450600 cnfemale600750 cnfemale750 cnmale150300 cnmale300450 cnmale450600 cnsilver150300 cnsilver300450 cnsilver450600 cnsilver600750 cnsilver750 cnsilver delta_tr gamma_tr type_fit_delta_tr type_fit_gamma_tr density_tr density_pmax_tr neel_pmax_tr nsilver_pmax_tr density_wd neel_wd beel_wd nsilver_wd bsilver_wd sector_tr year_tr is_current_distribution_area is_pristine_distribution_area_1985 Code for restauration <pre><code class="language-bash">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"</code></pre> <strong>5. Unit basins</strong> 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&amp;uuid=898f0ff8-f06c-4c14-88f7-43ea90e48233 <pre><code class="language-bash">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" </code></pre> <strong>6- Waterbodies</strong> 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. <br> <br> 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. <pre><code class="language-bash">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" </code></pre> <br> 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 : <pre><code class="language-sql">select dbeel_rivers.get_path (113670,114115,'FR') -- FR113618.FR114065.FR114053.FR114042</code></pre> dbeel_rivers.get_distance calculates the distance between two rivers segments (including the distance of the idsegments themselves) <pre><code class="language-sql">select dbeel_rivers.get_distance (113670,114115,'FR'); --12669</code></pre> 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. <pre><code class="language-sql">SELECT dbeel_rivers.upstream_segments_rn('FR114042'); /* FR114042 FR114053 FR113982 FR114034 ... */</code></pre> 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<br> dbeel_rivers.downstream_segments_rn(TEXT) takes a segment and returns the path to the sea. <pre><code class="language-sql">SELECT dbeel_rivers.downstream_segments_rn('SP227795');</code></pre><strong>数据集说明</strong> <strong>----------------</strong> 版本1.0.1：修复函数相关问题 版本1.0.2：新增表`dbeel_rivers.rn_rivermouth`，包含GEREM流域、至直布罗陀的距离以及与CCM的关联链接 版本1.0.3：修复函数相关问题 ---------------- <strong>SUDOANG项目概况</strong> SUDOANG项目旨在为管理者提供通用工具，以支持南欧区域（SUDOE，涵盖西班牙、法国与葡萄牙）内的鳗鲡保护工作。VISUANG是SUDOANG的交互式Web应用程序，集成了所有上述工具。该应用包含以下模块： 1. 鳗鲡分布图集（GT1）、涡轮机致死评估及迁徙障碍图集（GT2）、鳗鲡补充量与开发率评估（GT3），以及逃逸量评估（GT4，该指标已被欧盟委员会定为鳗鲡管理计划的目标指标）； 2. 此外还包含一张交互式地图，展示由GT6生成的试点流域网络的采样结果。 鳗鲡图集与逃逸量评估所需的鳗鲡丰度数据，通过<strong>鳗鲡密度分析模型（Eel Density Analysis model，简称EDA，为GT4的产出成果）</strong>获取。EDA可将采样河段的鳗鲡丰度外推至其他河段，同时考虑鳗鲡丰度、性别与体型随不同参数的变化规律。因此，EDA需两类主要数据源：与河流特征相关的数据，以及与鳗鲡丰度及自身特征相关的数据。 然而，在SUDOE区域内，两类数据的可获得性并不均衡；此外，由于采样来源各异（包括《水框架指令（Water Framework Directive，简称WFD）》、渔业领域数据收集、管理与使用社区框架（EUMAP）、鳗鲡管理计划、研究团队、学术论文与技术报告），相关信息分散于多位管理者手中，且格式不统一。因此，要实现覆盖整个SUDOE区域的鳗鲡丰度估算，第一步需构建一套统一的河流与鳗鲡数据库。本报告将对SUDOE区域内的河流特征数据库，以及鳗鲡丰度与相关特征数据库进行详细说明。 针对河流数据，共收集两类信息： - <strong>河流拓扑结构（River topology，即RN表）</strong>：整合了三个国家的河流及其拓扑与水文特征数据； - <strong>河流属性（River attributes，即RNA表）</strong>：包含用于SUDOANG模型的物理属性数据。 通过部署EDA2.3模型得到的SUDOE区域不同尺度（河段、流域、鳗鲡管理单元（Eel Management Unit，简称EMU）及国家尺度）的鳗鲡丰度与特征（体型、生物量、性别比与银化鳗比例）分布数据，已汇总至<strong>RNE表（鳗鲡预测数据，即eel predictions）</strong>。 <strong>当前活跃项目</strong> 本项目目前可在以下地址访问：GitLab仓库forgemia <strong>PostgreSQL数据库构建技术说明</strong> <strong>1. 构建PostgreSQL数据库</strong> 所有图层均采用EPSG:3035坐标系（欧洲LAEA坐标系），数据库格式为PostgreSQL数据库。你可在此处下载其他格式（Shapefile、CSV）的数据：SUDOANG gt1数据库。 初始命令如下： <pre><code class="language-bash"># 打开CMD命令行 # 使用以下命令切换至已下载文件所在目录 cd c:/path/to/my/folder # 注意需确保psql可被正常调用，Windows系统可将postgres的bin目录添加至系统路径，否则需完整指定postgres bin目录的路径，具体说明可参见下文链接 createdb -U postgres eda2.3 psql -U postgres eda2.3 # 此命令将打开psql命令行界面，你可在其中执行后续命令 </code></pre> 在psql命令行中执行以下命令： <pre><code class="language-sql">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; -- 输入q可退出psql命令行 </code></pre> 此时数据库已准备就绪，可导入各类备份文件。请注意备份文件体积较大，你可能无需导入包含单元流域或水体的部分。除水体与单元流域外，其余所有表的详细说明均可参见图集。你可参考PostgreSQL官方文档了解数据库继承相关知识：https://www.postgresql.org/docs/12/tutorial-inheritance.html <strong>2. RN（河段，riversegments）表</strong> 此类图层包含河流拓扑结构信息（详细说明参见图集），对应表包括：`dbeel_rivers.rn`、`france.rn`、`spain.rn`、`portugal.rn`。 字段说明（详见图集）：gid、idsegment、source、target、lengthm、nextdownidsegment、path、isfrontier、issource、seaidsegment、issea、geom、isendoreic、isinternational、country。 表`dbeel_rivers.rn_rivermouth`的字段：seaidsegment、geom（多边形）、gerem_zone_3、gerem_zone_4（用于EDA模型）、gerem_zone_5、ccm_wso_id、country、emu_name_short、geom_outlet（点）、name_basin、dist_from_gibraltar_km、name_coast、basin_name。 > 注：`dbeel_rivers.rn`为国际级基础表，其余国家级表均继承自该表。即便你仅需使用单个国家的数据，仍建议导入所有国家的表——因为存在跨界流域的情况。需优先导入该表，其余表（rne与rna）均通过外键关联至此表。 <pre><code class="language-bash"># 优先导入dbeel_rivers.rn pg_restore -U postgres -d eda2.3 "dbeel_rivers.rn.backup" # 导入法国数据 pg_restore -U postgres -d eda2.3 "france.rn.backup" # 导入西班牙数据 pg_restore -U postgres -d eda2.3 "spain.rn.backup" # 导入葡萄牙数据 pg_restore -U postgres -d eda2.3 "portugal.rn.backup" # 导入河口与流域数据，该文件包含GEREM流域、到直布罗陀的距离以及各入海流域的CCM关联ID pg_restore -U postgres -d eda2.3 "dbeel_rivers.rn_rivermouth.backup" # 如需使用相关函数，可在完成rna表导入后执行以下命令 psql -U postgres -d eda2.3 -f "function_dbeel_rivers.sql" </code></pre> <strong>3. RNA（属性）表</strong> 此类表对应：`dbeel_rivers.rna`、`france.rna`、`spain.rna`、`portugal.rna`。 字段说明（详见图集）：idsegment、altitudem、distanceseam、distancesourcem、cumnbdam、medianflowm3ps、surfaceunitbvm2、surfacebvm2、strahler、shreeve、codesea、name、pfafriver、pfafsegment、basin、riverwidthm、temperature、temperaturejan、temperaturejul、wettedsurfacem2、wettedsurfaceotherm2、lengthriverm、emu、cumheightdam、riverwidthmsource、slope、dis_m3_pyr_riveratlas、dis_m3_pmn_riveratlas、dis_m3_pmx_riveratlas、drought、drought_type_calc。 导入命令如下： <pre><code class="language-bash">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" </code></pre> <strong>4. RNE（鳗鲡预测数据，eel predictions）表</strong> 此类图层包含鳗鲡相关数据（详细说明参见图集），对应表包括：`dbeel_rivers.rne`、`france.rne`、`spain.rne`、`portugal.rne`。 字段说明（详见图集）：idsegment、surfaceunitbvm2、surfacebvm2、delta、gamma、density、neel、beel、peel150、peel150300、peel300450、peel450600、peel600750、peel750、nsilver、bsilver、psilver150300、psilver300450、psilver450600、psilver600750、psilver750、psilver、pmale150300、pmale300450、pmale450600、pfemale300450、pfemale450600、pfemale600750、pfemale750、pmale、pfemale、sex_ratio、cnfemale300450、cnfemale450600、cnfemale600750、cnfemale750、cnmale150300、cnmale300450、cnmale450600、cnsilver150300、cnsilver300450、cnsilver450600、cnsilver600750、cnsilver750、cnsilver、delta_tr、gamma_tr、type_fit_delta_tr、type_fit_gamma_tr、density_tr、density_pmax_tr、neel_pmax_tr、nsilver_pmax_tr、density_wd、neel_wd、beel_wd、nsilver_wd、bsilver_wd、sector_tr、year_tr、is_current_distribution_area、is_pristine_distribution_area_1985。 导入命令如下： <pre><code class="language-bash">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" </code></pre> <strong>5. 单元流域表</strong> 单元流域表未在图集中详细说明，对应表包括：`dbeel_rivers.basinunit_bu`、`france.basinunit_bu`、`spain.basinunit_bu`、`portugal.basinunit_bu`、`france.basinunitout_buo`、`spain.basinunitout_buo`、`portugal.basinunitout_buo`。 单元流域指环绕单个河段的简单流域，对应计算河段所用的地形单元，坐标系为EPSG:3035。表`bu_unitbv`与`bu_unitbvout`继承自`dbeel_rivers.unit_bv`：前者与河段相交，后者则对应未关联河段的流域多边形。 数据来源： - 葡萄牙：https://sniambgeoviewer.apambiente.pt/Geodocs/gml/inspire/HY_PhysicalWaters_DrainageBasinGeoCod.zip - 法国：法国单元流域对应RHT系统（Pella et al., 2012） - 西班牙：http://www.mapama.gob.es/ide/metadatos/index.html?srv=metadata.show&uuid=898f0ff8-f06c-4c14-88f7-43ea90e48233 导入命令如下： <pre><code class="language-bash">pg_restore -U postgres -d eda2.3 'dbeel_rivers.basinunit_bu.backup' # 导入法国数据 pg_restore -U postgres -d eda2.3 "france.basinunit_bu.backup" pg_restore -U postgres -d eda2.3 "france.basinunitout_buo.backup" # 导入西班牙数据 pg_restore -U postgres -d eda2.3 "spain.basinunit_bu.backup" pg_restore -U postgres -d eda2.3 "spain.basinunitout_buo.backup" # 导入葡萄牙数据 pg_restore -U postgres -d eda2.3 "portugal.basinunit_bu.backup" pg_restore -U postgres -d eda2.3 "portugal.basinunitout_buo.backup" </code></pre> <strong>6. 水体表</strong> 此类表沿用了WISE或bd_topage数据源的原始表结构，对应表包括：`dbeel_rivers.waterbody_unitbv`、`portugal.waterbody_unitbv`、`france.waterbody_unitbv`、`spain.waterbody_unitbv`。 在法国，该表对应bd_topage中的水文表面数据：https://bdtopage.eaufrance.fr/page/documents-ressources 在西班牙，该表对应《水框架指令（DMA，即Directiva Marco del Agua）》第3条定义的主要河流流域；在葡萄牙，该表对应HY_PhysicalWaters_DrainageBasinMAgua。 导入命令如下： <pre><code class="language-bash">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" </code></pre> <br><br> <strong>7. 函数库</strong> 相关函数可在`dbeel_rivers_functions.sql`文件中查看，其中包含函数示例与详细说明。以下为部分功能的快速示例： - `dbeel_rivers.get_path`：获取同一流域内两个河段ID之间的连通路径： <pre><code class="language-sql">select dbeel_rivers.get_path (113670,114115,'FR') -- 返回结果：FR113618.FR114065.FR114053.FR114042</code></pre> - `dbeel_rivers.get_distance`：计算两个河流河段之间的距离（包含河段自身的长度）： <pre><code class="language-sql">select dbeel_rivers.get_distance (113670,114115,'FR'); -- 返回结果：12669</code></pre> - `dbeel_rivers.upstream_segments_rn(TEXT)`：输入上游河段ID，返回所有上游河段的ID数组。注意该函数的运行速度慢于各国专属函数，可在spain、portugal、france schema下查找更高效的对应函数。该函数常用于计算上游流域的鳗鲡总量： <pre><code class="language-sql">SELECT dbeel_rivers.upstream_segments_rn('FR114042'); /* 返回结果：FR114042 FR114053 FR113982 FR114034 ... */</code></pre> - `dbeel_rivers.upstream_segments_rn_sti(TEXT)`：输入上游河段ID与包含idsegment、target、source字段的表，该格式更便于后续路径规划类函数（如get_path）的调用，此类函数需用到source与target字段。 - `dbeel_rivers.downstream_segments_rn(TEXT)`：输入河段ID，返回该河段至入海口的连通路径： <pre><code class="language-sql">SELECT dbeel_rivers.downstream_segments_rn('SP227795');</code></pre>