DIASPARA habitat database creation script

Diadromous species use both Marine and continental environments. They are subjected to multiple stressor acting locally or at the global scale. Currently ICES has designed its databases for the marine environment, and we need an habitat referential to better account for stressor (dam, connectivity, pollution, fishery) acting at the very local scale in the continental environment. The objective is to propose a vocabulary for habitat based on the hydroshed database and covering all continental freshwater and transitional habitats across Europe, but also to provide a referential for units from the river, to the wider stock level, including the marine environment, and adapted to the needs of the diadromous expert groups (WGNAS, WGBAST, WGTRUTTA, WGEEL).

River network choice

To create the habitat database we decided to use a single river network. The Hydrological data and maps based on SHuttle Elevation Derivatives at multiple Scales (HydroSHEDS).

Some development was also done with the EU-hydro. This code relied on a lot of fixes made by Andrew French. These fixes could unfortunately be integrated by the time of finishing this report. However, that does not exclude the use of EU-Hydro (or other) data in the future.

The HydroSHEDS data, with a reach to the full project area, was used for creating the referential in this project.

The Hydrosheds here is just used as dictionary providing consistent names about europe. It does not mean that other GIS will not be used by the different working groups, but only that in the future the names in the continental habitat will be provided from the area table, and some of the codes for basins will be based on hydroshed.

HydroSHEDS

Data description

HydroSHEDS is a global database providing high-resolution hydrographic data derived from NASA’s Shuttle Radar Topography Mission (SRTM). Covering most land areas, it offers structured datasets for hydrological modeling, watershed analysis, and environmental research. The data is available at resolutions up to 3 arc-seconds (~90m) in raster (GeoTIFF) and vector (shapefiles,geodatabases) formats. We use the geodatabases format. It includes river networks, watershed boundaries, drainage directions, and flow accumulation. Core products include HydroBASINS for watershed boundaries, HydroRIVERS for river networks, HydroLAKES for lakes and reservoirs, and HydroATLAS, which adds environmental attributes.

Descriptions of riversegments variables

data_description <- dbGetQuery(con_diaspara, 
  "SELECT cols.column_name AS var_name, 
        pgd.description AS description
  FROM information_schema.columns cols
  LEFT JOIN pg_catalog.pg_statio_all_tables st 
      ON st.schemaname = cols.table_schema AND st.relname = cols.table_name
  LEFT JOIN pg_catalog.pg_description pgd 
      ON pgd.objoid = st.relid AND pgd.objsubid = cols.ordinal_position
  WHERE cols.table_schema = 'riveratlas'
  AND cols.table_name = 'riveratlas_v10';")

knitr::kable(data_description) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Variables description

var_name	description
NA	NA
:--------	:-----------

Importing HydroSHEDS

The first thing is to download and process the hydrological network from the HydroSHEDS website. To do so we create a PowerShell script that iterates through predefined file IDs ($files) and corresponding dataset types ($atlas), constructing download URLs and filenames dynamically. The script navigates to the source directory, downloads each dataset as a ZIP file using curl, extracts it to a specified output directory, and then connects to the PostgreSQL database (diaspara). Within the database, it ensures a clean schema by dropping any existing schema of the same name and recreating it for fresh data import.

PowerShell code to download HydroSHEDS

# launch in powershell

# Basin 20082137
# River 20087321
# Lake 35959544

[String[]]$files = "20082137","20087321","35959544"
[String[]]$atlas = "Basin","River","Lake"
$pathccmsource = "D:\eda\hydroatlas"
$pathccmout = "D:\eda\"

for ($i = 0; $i -lt $files.Length; $i++) {
    $file = $files[$i]
    $atlasName = $atlas[$i]

    Write-Output "Downloading "$atlasName""ATLAS""

    $namefile = "$atlasName" + "ATLAS_Data_v10.gdb"
    $schema = "$atlasName" + "ATLAS"

    cd $pathccmsource

    curl -o "$namefile.zip" "https://figshare.com/ndownloader/files/$file"

    Expand-Archive "$namefile.zip" -DestinationPath "$pathccmout"

    psql --dbname="postgresql://${env:userlocal}:${env:passlocal}@$env:hostdiaspara/diaspara" -c "DROP SCHEMA IF EXISTS $schema CASCADE; CREATE SCHEMA $schema;"
}

The same process is used to import a country layer and ICES divisions. ICES divisions are downloaded from the ICES website.

Inserting data

Then we insert the downloaded data to the database. To do so we use ogr2ogr, a command-line tool from the GDAL library, to import geospatial data from File Geodatabases (GDB) into a PostgreSQL database. Each command processes a different dataset—BasinATLAS, RiverATLAS, and LakeATLAS—and loads them into corresponding schemas within the DIASPARA database.

The -f "PostgreSQL" specifies the output format, while -a_srs EPSG:4326 ensures the spatial reference system is set to WGS 84 (EPSG:4326). The -lco SCHEMA="..." option places each dataset into a designated schema (basinatlas, riveratlas, lakeatlas). The -overwrite flag ensures any existing data in the schema is replaced. The -progress option provides real-time feedback during execution. Lastly, --config PG_USE_COPY YES optimizes performance by using PostgreSQL’s COPY command for faster data insertion.

Code to import data to the database

# launch in OSGEO4W console, (QGIS) line by line

ogr2ogr.exe -f "PostgreSQL" -a_srs EPSG:4326 PG:"host=localhost port=5432 dbname=diaspara user=postgres password=postgres" -lco SCHEMA="basinatlas" D:\eda\BasinATLAS_v10.gdb   -overwrite -progress --config PG_USE_COPY YES

ogr2ogr.exe -f "PostgreSQL" -a_srs EPSG:4326 PG:"host=localhost port=5432 dbname=diaspara user=postgres password=postgres" -lco SCHEMA="riveratlas" D:\eda\RiverATLAS_v10.gdb -overwrite -progress --config PG_USE_COPY YES

ogr2ogr.exe -f "PostgreSQL" -a_srs EPSG:4326 PG:"host=localhost port=5432 dbname=diaspara user=postgres password=postgres" -lco SCHEMA="lakeatlas" D:\eda\LakeATLAS_v10.gdb   -overwrite -progress --config PG_USE_COPY YES

psql --dbname=postgresql://${env:userlocal}:${env:passlocal}@$env:hostdiaspara/diaspara -c "
"ALTER TABLE riveratlas.riveratlas_v10 RENAME COLUMN shape TO geom;"

The same process is used to insert other downloaded data to the database.

Building the database

Once the data are downloaded we choose to to split the database into smaller groups following ICES Areas and Ecoregions.

Step 1 : Spliting data into smaller groups for efficiency (Europe/N.Africa)

European hydrological data are extracted by first selecting large-scale catchments from basinatlas.basinatlas_v10_lev03, a layer representing coarse-resolution catchments at level 3. We filtered these catchments based on specific hybas_id values and stored them in a temporary table tempo.hydro_large_catchments_europe. Next, we refine our selection by extracting smaller, more detailed catchments from basinatlas.basinatlas_v10_lev12, which represents the most detailed level (level 12) of the HydroBASINS hierarchy. We ensure that these smaller catchments are spatially contained within the large catchments using ST_Within, saving them in tempo.hydro_small_catchments_europe, and add a GIST index on geometries to improve spatial query efficiency. Finally, we select river segments from riveratlas.riveratlas_v10 that intersect with the chosen small catchments using ST_Intersects, storing the results in tempo.hydro_riversegments_europe.

-- Selecting european data from hydroatlas (large catchments)
DROP TABLE IF EXISTS tempo.hydro_large_catchments_europe;
CREATE TABLE tempo.hydro_large_catchments_europe AS(
SELECT shape FROM basinatlas.basinatlas_v10_lev03
WHERE hybas_id = ANY(ARRAY[2030000010,2030003440,2030005690,2030006590,
              2030006600,2030007930,2030007940,2030008490,2030008500,
                            2030009230,2030012730,2030014550,2030016230,2030018240,2030020320,2030024230,2030026030,2030028220,
                            2030030090,2030033480,2030037990,2030041390,2030045150,2030046500,2030047500,2030048590,2030048790,
                            2030054200,2030056770,2030057170,2030059370,2030059450,2030059500,2030068680,2030059510])
);--35

-- Selecting european data from hydroatlas (small catchments)
DROP TABLE IF EXISTS tempo.hydro_small_catchments_europe;
CREATE TABLE tempo.hydro_small_catchments_europe AS (
SELECT * FROM basinatlas.basinatlas_v10_lev12 ba
WHERE EXISTS (
  SELECT 1
  FROM tempo.hydro_large_catchments_europe hlce
  WHERE ST_Within(ba.shape,hlce.shape)
  )
);--78055
CREATE INDEX idx_tempo_hydro_small_catchments_europe ON tempo.hydro_small_catchments_europe USING GIST(shape);

-- Selecting european data from riveratlas
DROP TABLE IF EXISTS tempo.hydro_riversegments_europe;
CREATE TABLE tempo.hydro_riversegments_europe AS(
    SELECT * FROM riveratlas.riveratlas_v10 r
    WHERE EXISTS (
        SELECT 1
        FROM tempo.hydro_small_catchments_europe e
        WHERE ST_Intersects(r.geom,e.shape)
    )
); --589947

The same method is used to select catchments and river segments in the Southern Mediterranean area.

Figure 1: Map of all catchments present in the database.

Step 2 : Selecting the most downstream riversegment for each reach

Next we need to extract the most downstream river segments from tempo.hydro_riversegments_europe. To achieve this, we filter the table to retain only the river segments where hyriv_id is equal to main_riv, which correspond to the most downstream river segment of the river basin, ensuring that for each reach, only its most downstream segment is selected. The results are then stored in tempo.riveratlas_mds.

DROP TABLE IF EXISTS tempo.riveratlas_mds;
CREATE TABLE tempo.riveratlas_mds AS (
    SELECT *
    FROM tempo.hydro_riversegments_europe
    WHERE hydro_riversegments_europe.hyriv_id = hydro_riversegments_europe.main_riv); --17344

The same method has been used for Southern Mediterranean data.

Figure 2: Map of most downstream river segments in the Baltic area.

Step 3 : Creating a most downstream point

To identify the most downstream point for each river segment in tempo.riveratlas_mds, a new column, downstream_point, is added to store the point geometry (EPSG:4326). The last coordinate of each segment’s geometry was extracted using ST_PointN on the cut down line geometry from ST_Dump, ensuring that the most downstream point is correctly identified. The table is then updated by assigning the extracted downstream point to the corresponding hyriv_id. Finally, a GIST index on downstream_point is created to improve the efficiency of spatial queries.

ALTER TABLE tempo.riveratlas_mds
    ADD COLUMN downstream_point geometry(Point, 4326);
WITH downstream_points AS (
    SELECT 
        hyriv_id,
        ST_PointN((ST_Dump(geom)).geom, ST_NumPoints((ST_Dump(geom)).geom)) AS downstream_point
    FROM tempo.riveratlas_mds
)
UPDATE tempo.riveratlas_mds AS t
    SET downstream_point = dp.downstream_point
    FROM downstream_points AS dp
    WHERE t.hyriv_id = dp.hyriv_id; --17344

CREATE INDEX idx_tempo_riveratlas_mds_dwnstrm ON tempo.riveratlas_mds USING GIST(downstream_point);

The same method is used for Southern Mediterranean data.

Figure 3: Map of most downstream points in the Baltic area.

Step 4 : Intersecting the most downstream point with wanted ICES areas

Baltic

ICES divisions are already used in the Baltic by WGBAST thus we have kept this same structure. It is divided in three areas grouping together ICES divisions 30 & 31 ; 27,28,29 & 32 and 22,24,25 & 26.

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

A new table, tempo.ices_areas_3229_27, is created by selecting river segments from tempo.riveratlas_mds where the downstream_point of each segment is within a specified distance (0.01 units) of the geometry in ices_areas."ices_areas_20160601_cut_dense_3857". The selection is further restricted to areas with specific subdivisio values (‘32’, ‘29’, ‘28’, ‘27’). Additionally, to avoid duplicates, only segments whose downstream_point is not already present in tempo.ices_areas_3031 are included. The same method is used for the whole Baltic area.

CREATE TABLE tempo.ices_areas_3229_27 AS (
    SELECT dp.*
    FROM tempo.riveratlas_mds AS dp
    JOIN ices_areas."ices_areas_20160601_cut_dense_3857" AS ia
    ON ST_DWithin(
        dp.downstream_point,
        ia.geom,
        0.01
    )
    WHERE ia.subdivisio=ANY(ARRAY['32','29','28','27'])
    AND dp.downstream_point NOT IN (
        SELECT existing.downstream_point
        FROM tempo.ices_areas_3031 AS existing)
); --569

Figure 5: Map of most downstream points in the Northern Baltic ICES area.

Ecoregions

For the rest of the dataset, ICES ecoregions are used to group catchments and river segments together.

Figure 6: Map of ICES fishing ecoregions.

The most downstream points are gathered from tempo.riveratlas_mds, applying three filters: a country filter, an ICES ecoregion filter, and an exclusion condition.

First, we retrieve distinct downstream points that are within 0.04 degrees of the geometries in ices_ecoregions.ices_ecoregions_20171207_erase_esri, specifically selecting objectid = 11, which corresponded to the desired ICES ecoregion. Additionally, these points have to be within 0.02 degrees of the geometries in tempo.ne_10m_admin_0_countries, ensuring they are located in Norway or Sweden (in this case, when working on the northern part of the North Sea).

Similarly, we selected downstream points that are within 0.04 degrees of the geometries in ices_areas.ices_areas_20160601_cut_dense_3857, specifically for subdivisio = '23', while also ensuring they are within 0.02 degrees of Norway or Sweden. This is done because of an overlap between ICES ecoregions and ICES areas in this particular zone.

Finally, we combine both sets of points while applying an exclusion condition: any points from the ICES area selection that already exist in the ICES ecoregion selection are removed, ensuring no duplicates are kept, while maintaining priority for the ecoregion-based selection. The resulting dataset tempo.ices_ecoregions_nsea_north contains the most downstream points filtered by ICES ecoregions, ICES areas, and country boundaries for Norway and Sweden.

CREATE TABLE tempo.ices_ecoregions_nsea_north AS (
    WITH ecoregion_points AS (
        SELECT DISTINCT dp.*
        FROM tempo.riveratlas_mds AS dp
        JOIN ices_ecoregions."ices_ecoregions_20171207_erase_esri" AS er
        ON ST_DWithin(
            dp.downstream_point,
            er.geom,
            0.04
        )
        JOIN tempo.ne_10m_admin_0_countries AS cs
        ON ST_DWithin(
            dp.downstream_point,
            cs.geom,
            0.02
        )
        WHERE er.objectid = 11
          AND cs.name IN ('Norway','Sweden')
    ),
    area_points AS (
        SELECT DISTINCT dp.*
        FROM tempo.riveratlas_mds AS dp
        JOIN ices_areas."ices_areas_20160601_cut_dense_3857" AS ia
        ON ST_DWithin(
            dp.downstream_point,
            ia.geom,
            0.04
        )
        JOIN tempo.ne_10m_admin_0_countries AS cs
        ON ST_DWithin(
            dp.downstream_point,
            cs.geom,
            0.02
        )
        WHERE ia.subdivisio = '23'
          AND cs.name IN ('Norway','Sweden')
    )
    SELECT * FROM ecoregion_points
    UNION
    SELECT * FROM area_points
    WHERE downstream_point NOT IN (
        SELECT downstream_point FROM ecoregion_points
    )
);--1271

Figure 7: Map of most downstream points in the Northern Sea.

Step 4.5 : Repeating the intersection using a larger buffer to retrieve missing points

Baltic

We select the most downstream points from tempo.riveratlas_mds that are within a larger buffer of 0.1 degrees of the geometries in ices_areas.ices_areas_20160601_cut_dense_3857, specifically for subdivisio values 32, 29, and 28 (subdivision 27 being already complete).

To avoid duplication, we compile a list of excluded points by gathering all downstream points already present in several existing tables: tempo.ices_areas_26_22, tempo.ices_areas_3229_27, tempo.ices_areas_3031, tempo.ices_ecoregions_barent, tempo.ices_ecoregions_nsea_north, and tempo.ices_ecoregions_norwegian.

We then identify the missing points by filtering out any downstream points from our selection that match an already excluded point. This ensures that only new and unique points were retained.
Finally, we insert these missing points into tempo.ices_areas_3229_27.

WITH filtered_points AS (
    SELECT dp.*
    FROM tempo.riveratlas_mds AS dp
    JOIN ices_areas."ices_areas_20160601_cut_dense_3857" AS ia
    ON ST_DWithin(
        dp.downstream_point,
        ST_Transform(ia.geom, 4326),
        0.1
    )
    WHERE ia.subdivisio = ANY(ARRAY['32', '29', '28'])
),
excluded_points AS (
    SELECT downstream_point
    FROM tempo.ices_areas_26_22
    UNION ALL
    SELECT downstream_point FROM tempo.ices_areas_3229_27
    UNION ALL
    SELECT downstream_point FROM tempo.ices_areas_3031
    UNION ALL
    SELECT downstream_point FROM tempo.ices_ecoregions_barent
    UNION ALL
    SELECT downstream_point FROM tempo.ices_ecoregions_nsea_north
    UNION ALL
    SELECT downstream_point FROM tempo.ices_ecoregions_norwegian
),
missing_points AS (
    SELECT fp.*
    FROM filtered_points AS fp
    LEFT JOIN excluded_points AS ep
    ON ST_Equals(fp.downstream_point, ep.downstream_point)
    WHERE ep.downstream_point IS NULL
)
INSERT INTO tempo.ices_areas_3229_27
SELECT mp.*
FROM missing_points AS mp;--8

Ecoregions

We select distinct downstream points from tempo.riveratlas_mds using two spatial filters. The first selection includes points within 0.1 degrees of ices_ecoregions.ices_ecoregions_20171207_erase_esri, specifically for objectid = 11, and also within the larger buffer of 0.1 degrees of tempo.ne_10m_admin_0_countries, ensuring they are in Norway or Sweden.

The second selection included points within 0.1 degrees of ices_areas.ices_areas_20160601_cut_dense_3857, specifically for subdivisio = '23', while also ensuring proximity to Norway or Sweden. Both selections are merged to form the set of filtered points.

To prevent duplication, we compile a list of excluded points by gathering all downstream points already present in several tables: tempo.ices_areas_26_22, tempo.ices_areas_3229_27, tempo.ices_areas_3031, tempo.ices_ecoregions_barent, tempo.ices_ecoregions_nsea_north, tempo.ices_ecoregions_norwegian, and tempo.ices_ecoregions_nsea_south.

We then identify missing points by removing any downstream points from our selection that match an already excluded point. This ensures that only new and unique points were retained.

Finally, we insert these missing points into tempo.ices_ecoregions_nsea_north, adding new downstream points that meet the criteria while avoiding duplicates.

WITH filtered_points AS (
    SELECT DISTINCT dp.*
    FROM tempo.riveratlas_mds AS dp
    JOIN ices_ecoregions.ices_ecoregions_20171207_erase_esri AS er
    ON ST_DWithin(
        dp.downstream_point,
        ST_Transform(er.geom, 4326),
        0.1
    )
    JOIN tempo.ne_10m_admin_0_countries AS cs
    ON ST_DWithin(
        dp.downstream_point,
        ST_Transform(cs.geom, 4326),
        0.1
    )
    WHERE er.objectid = 11
      AND cs.name IN ('Norway', 'Sweden')

    UNION
    SELECT DISTINCT dp.*
    FROM tempo.riveratlas_mds AS dp
    JOIN ices_areas.ices_areas_20160601_cut_dense_3857 AS ia
    ON ST_DWithin(
        dp.downstream_point,
        ST_Transform(ia.geom, 4326),
        0.1
    )
    JOIN tempo.ne_10m_admin_0_countries AS cs
    ON ST_DWithin(
        dp.downstream_point,
        ST_Transform(cs.geom, 4326),
        0.1
    )
    WHERE ia.subdivisio = '23'
      AND cs.name IN ('Norway', 'Sweden')
),
excluded_points AS (
    SELECT downstream_point FROM tempo.ices_areas_26_22
    UNION ALL
    SELECT downstream_point FROM tempo.ices_areas_3229_27
    UNION ALL
    SELECT downstream_point FROM tempo.ices_areas_3031
    UNION ALL
    SELECT downstream_point FROM tempo.ices_ecoregions_barent
    UNION ALL
    SELECT downstream_point FROM tempo.ices_ecoregions_nsea_north
    UNION ALL
    SELECT downstream_point FROM tempo.ices_ecoregions_norwegian
    UNION ALL
    SELECT downstream_point FROM tempo.ices_ecoregions_nsea_south
),
missing_points AS (
    SELECT fp.*
    FROM filtered_points AS fp
    LEFT JOIN excluded_points AS ep
    ON ST_Equals(fp.downstream_point, ep.downstream_point)
    WHERE ep.downstream_point IS NULL
)
INSERT INTO tempo.ices_ecoregions_nsea_north
SELECT mp.*
FROM missing_points AS mp;

Step 5 : Copy all riversegments corresponding to the previously selected riversegments using the main_riv identifier

A new schema h_baltic_3229_27 is created to store the selected river segments. Within this schema, we create the riversegments table by selecting distinct river segments from tempo.hydro_riversegments_europe that match the main_riv values found in tempo.ices_areas_3229_27. This ensures that only river segments associated with the relevant ICES subdivisions are included.

To optimize the table, we add a primary key constraint on hyriv_id, ensuring data integrity and uniqueness. Additionally, two indexes are created: a B-tree index on main_riv to improve lookup efficiency and a GIST index on geom to speed up spatial queries.

CREATE SCHEMA h_baltic_3229_27;
DROP TABLE IF EXISTS h_baltic_3229_27.riversegments;
CREATE TABLE h_baltic_3229_27.riversegments AS (
    SELECT DISTINCT ON (hre.geom) hre.*
    FROM tempo.hydro_riversegments_europe AS hre
    JOIN tempo.ices_areas_3229_27 AS ia
    ON hre.main_riv = ia.main_riv
);--30869

ALTER TABLE h_baltic_3229_27.riversegments
ADD CONSTRAINT pk_hyriv_id PRIMARY KEY (hyriv_id);

CREATE INDEX idx_h_baltic_3229_27_riversegments_main_riv ON h_baltic_3229_27.riversegments USING BTREE(main_riv);
CREATE INDEX idx_h_baltic_3229_27_riversegments ON h_baltic_3229_27.riversegments USING GIST(geom);

Figure 8: Map of river segments for 27, 29-32 ICES areas.

Step 6 : Gather all corresponding catchments using an intersection function

We create the h_baltic_26_22.catchments table by selecting distinct small catchments from tempo.hydro_small_catchments_europe that intersect with the river segments stored in h_baltic_26_22.riversegments. To avoid duplication, we exclude any catchments that were already present in h_baltic_3031.catchments or h_baltic_3229_27.catchments.

To improve performance and maintain data integrity, we add a primary key constraint on hybas_id. Additionally, we created a B-tree index on main_bas to optimize lookups and a GIST index on shape to enhance spatial query efficiency.

DROP TABLE IF EXISTS h_baltic_26_22.catchments;
CREATE TABLE h_baltic_26_22.catchments AS (
    SELECT DISTINCT ON (hce.hybas_id) hce.*
    FROM tempo.hydro_small_catchments_europe AS hce
    JOIN h_baltic_26_22.riversegments AS rs
    ON ST_Intersects(hce.shape, rs.geom)
    LEFT JOIN (
        SELECT shape FROM h_baltic_3031.catchments
        UNION ALL
        SELECT shape FROM h_baltic_3229_27.catchments
    ) AS excluded
    ON hce.shape && excluded.shape
    AND ST_Equals(hce.shape, excluded.shape)
    WHERE excluded.shape IS NULL
);--3878

ALTER TABLE h_baltic_26_22.catchments
ADD CONSTRAINT pk_hybas_id PRIMARY KEY (hybas_id);

CREATE INDEX idx_h_baltic_26_22_catchments_main_bas ON h_baltic_26_22.catchments USING BTREE(main_bas);
CREATE INDEX idx_h_baltic_26_22_catchments ON h_baltic_26_22.catchments USING GIST(shape);

Figure 9: Map of catchments for 27, 29-32 ICES areas.

Step 7 : Retrieve all missing endorheic catchments using an evelope

We construct the tempo.oneendo_bisciber table by generating a concave hull around the exterior boundary of the merged catchment shapes from h_biscay_iberian.catchments. This process creates a generalized polygon representing the area covered by these catchments. To improve spatial query performance, we added a GIST index on the geometry column.

Next, we identify endorheic basins from basinatlas.basinatlas_v10_lev12 that intersect with tempo.oneendo_bisciber. To ensure only relevant basins are selected, we exclude those already present in surrounding areas (h_biscay_iberian.catchments, h_med_west.catchments, h_nsea_south.catchments), and specific basins defined by main_bas values 2120017150, 2120017480, and 2120018070 that belong to another area. The remaining filtered basins are then inserted into h_biscay_iberian.catchments.

Finally, we populate h_biscay_iberian.riversegments by selecting distinct river segments from tempo.hydro_riversegments_europe that intersected with the newly added catchments. To prevent duplicate entries, we exclude any river segments that already existed in h_biscay_iberian.riversegments.

DROP TABLE IF EXISTS tempo.oneendo_bisciber;
CREATE TABLE tempo.oneendo_bisciber AS (
    SELECT  ST_ConcaveHull(ST_MakePolygon(ST_ExteriorRing((ST_Dump(ST_Union(ha.shape))).geom)),0.1,FALSE) geom
    FROM h_biscay_iberian.catchments AS ha);--67
CREATE INDEX idx_tempo_oneendo_bisciber ON tempo.oneendo_bisciber USING GIST(geom);
    

WITH endo_basins AS (   
    SELECT ba.*
    FROM basinatlas.basinatlas_v10_lev12 AS ba
    JOIN tempo.oneendo_bisciber
    ON ba.shape && oneendo_bisciber.geom
    AND ST_Intersects(ba.shape, oneendo_bisciber.geom)
),
excluded_basins AS (
    SELECT shape 
    FROM h_biscay_iberian.catchments
    UNION ALL
    SELECT shape 
    FROM h_med_west.catchments
    UNION ALL
    SELECT shape 
    FROM h_nsea_south.catchments
    UNION ALL
    SELECT shape
    FROM basinatlas.basinatlas_v10_lev12
    WHERE main_bas = ANY(ARRAY[2120017150, 2120017480, 2120018070])
),
filtered_basin AS (
    SELECT eb.*
    FROM endo_basins eb
    LEFT JOIN excluded_basins exb
    ON eb.shape && exb.shape
    AND ST_Equals(eb.shape, exb.shape)
    WHERE exb.shape IS NULL
)
INSERT INTO h_biscay_iberian.catchments
SELECT *
FROM filtered_basin;--62


INSERT INTO h_biscay_iberian.riversegments
SELECT DISTINCT ON (r.hyriv_id) r.*
FROM tempo.hydro_riversegments_europe r
JOIN h_biscay_iberian.catchments c
ON r.geom && c.shape
AND ST_Intersects(r.geom, c.shape)
WHERE NOT EXISTS (
    SELECT *
    FROM h_biscay_iberian.riversegments ex
    WHERE r.geom && ex.geom
    AND ST_Equals(r.geom, ex.geom)
);--57

Figure 10: Map of catchments on the Iberian peninsula with missing endorheic basins.

Figure 11: Map of the concave hull on the Iberian peninsula.

Figure 12: Map of catchments on the Iberian peninsula without missing endorheic basins.

Step 8 : Retrieve all missing islands and coastal catchments not linked to a riversegments by using an intersection with ICES areas

We identify the last set of catchments by selecting distinct small catchments from tempo.hydro_small_catchments_europe that intersect with ices_areas.ices_areas_20160601_cut_dense_3857, specifically within subdivisions 32, 29, 28, and 27.

To avoid duplication, we exclude catchments that are already present in h_baltic_3031.catchments, h_baltic_3229_27.catchments, and h_baltic_26_22.catchments. The remaining catchments, which are not already accounted for, are inserted into h_baltic_3229_27.catchments.

WITH last_basin AS (
    SELECT DISTINCT ON (c.hybas_id) c.*
    FROM tempo.hydro_small_catchments_europe AS c
    JOIN ices_areas.ices_areas_20160601_cut_dense_3857 AS ia
    ON ST_Intersects(c.shape, ia.geom)
    WHERE ia.subdivisio=ANY(ARRAY['32','29','28','27'])
),
excluded_basins AS (
    SELECT shape 
    FROM h_baltic_3031.catchments
    UNION ALL
    SELECT shape 
    FROM h_baltic_3229_27.catchments
    UNION ALL
    SELECT shape 
    FROM h_baltic_26_22.catchments
),
filtered_basin AS (
    SELECT lb.*
    FROM last_basin lb
    LEFT JOIN excluded_basins exb
    ON lb.shape && exb.shape
    AND ST_Equals(lb.shape, exb.shape)
    WHERE exb.shape IS NULL
)
INSERT INTO h_baltic_3229_27.catchments
SELECT *
FROM filtered_basin;

Figure 13: Map of catchments on the Baltic with missing islands basins.

Figure 14: Map of catchments on the Baltic without missing islands basins.

Figure 15: Final map of catchments from HydroSHEDS split into ICES areas and ecoregions.

Hierarchical structure for working groups.

Creating a hierarchical structure for WGBAST

Based on the map of catchments, a table is created to store the referential of habitat, both continental and marine, specific to each working group : hierarchical structure. This area is meant to be a referential tables. Several levels have been layed out (Figure 16) depending on the needs of each working group.

Figure 16: The nested structure layed out in table tr_area_area, dotted box indicate that the level is optional. This table represents the structure for refbast.

The first layer of the structure is the global Stock level (Figure 17). It includes all catchments that have a river flowing into the Baltic as well as all ICES subdivisions present in the Baltic, thus this layer is in fact created after the two lower levels.

The following query generates a generalized geometry by merging all geometries (inland and marine stock) in the refbast.tr_area_are table into a single shape. First, we apply ST_Union to dissolve boundaries between geometries, then extract their exterior rings and convert them into polygons. These are passed through ST_ConcaveHull to produce a simplified, more natural outer boundary that better follows the actual spatial extent. We then calculate the area of the resulting geometry and filter out any that are too small (area ≤ 1), ensuring only significant shapes are retained and getting rid of some small artifacts. The final geometry is wrapped as a MultiPolygon and used to update one entry in the table along with metadata fields. Its are_id is 1 since it is the highest level of the hierarchy, are_are_id is NULL because there is no higher level to reference to, its are_lev_code is Stock and are_ismarine is NULL as this level is both inland and marine.

WITH unioned_polygons AS (
  SELECT (ST_ConcaveHull(ST_MakePolygon(ST_ExteriorRing((ST_Dump(ST_Union(geom_polygon))).geom)),0.0001,FALSE)) AS geom
  FROM refbast.tr_area_are
),
area_check AS (
  SELECT geom, ST_Area(geom) AS area
  FROM unioned_polygons
),
filtered_polygon AS (
  SELECT geom
  FROM area_check
  WHERE area > 1
)
UPDATE refbast.tr_area_are
SET 
  are_are_id = NULL,
  are_code = 'Baltic',
  are_lev_code = 'Stock',
  are_ismarine = NULL,
  geom_polygon = (SELECT ST_Multi(geom) FROM filtered_polygon),
  geom_line = NULL
WHERE are_id = 1;

Figure 17: Map of the full stock level.

Marine level

Stock

The global Stock level (Figure 17) is then split into two lower layers. The first one is the marine Stock level (Figure 17).

We insert a new spatial unit into the refbast.tr_area_are table to represent a marine stock area labeled “Baltic marine”. This unit is associated with the “Stock” level (are_lev_code) and is marked as marine (are_ismarine = true). To define its geometry, we aggregate spatial features from the ref.tr_fishingarea_fia table using ST_Union, specifically targeting geometries where the fia_level is 'Division' and the fia_division corresponds to either '27.3.b, c' or '27.3.d'. A new unique identifier is generated using the refbast.seq sequence for are_id, and the new area is linked to the global Stock level through the are_are_id of 1.

INSERT INTO refbast.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
SELECT nextval('refbast.seq') AS are_id,
    1 AS are_are_id,
    'Baltic marine' AS are_code,
    'Stock' AS are_lev_code,
    --are_wkg_code,  by default
    true AS are_ismarine,
    ST_Union(geom) AS geom_polygon,
    NULL AS geom_line
    FROM ref.tr_fishingarea_fia 
    WHERE"fia_level"='Division' AND "fia_division" IN ('27.3.b, c','27.3.d');

ICES division

We insert a new level into the refbast.tr_area_are table, corresponding to the fishing area “27.3.b, c”. The level of the spatial unit is set to “Division”, and it is marked as marine (are_ismarine = true). The geometry is retrieved from the ref.tr_fishingarea_fia table by selecting records where the fia_level is 'Division' and the fia_division is '27.3.b, c'. This geometry is directly passed into the insert statement without modification. The sequence refbast.seq keeps generating a new are_id, and the area is assigned the parent are_are_id of 3, linking it to the higher-level of structure (Figure 17).

INSERT INTO refbast.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
WITH select_division AS (
    SELECT geom FROM ref.tr_fishingarea_fia tff
    WHERE tff.fia_level = 'Division' AND tff.fia_division = '27.3.b, c'
)
SELECT nextval('refbast.seq') AS are_id,
        2 AS are_are_id,
        '27.3.b, c' AS are_code,
        'Division' AS are_lev_code,
        --are_wkg_code,
        true AS is_marine,
        geom AS geom_polygon,
        NULL AS geom_line
        FROM select_division;

Figure 18: Map of the ICES division level.

Subdivision grouping

Two subdivision groupings exist in the historical database. They are dividing the Baltic into two main areas and are structured as followed : Gulf of Bothnia 300 (27.3.d.30,27.3.d.31) and Main Baltic 200 (27.3.d.22,27.3.d.23,27.3.d.24,27.3.d.25,27.3.d.26,27.3.d.27,27.3.d.28,27.3.d.29). Those two areas were created by grouping ICES subdivisions together.

INSERT INTO refbast.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, are_name, geom_polygon, geom_line)
WITH select_division AS (
  SELECT geom FROM ref.tr_fishingarea_fia tff
  WHERE  tff.fia_level = 'Subdivision' AND tff.fia_subdivision IN ('27.3.d.30','27.3.d.31')
)
SELECT 7023,
    2 AS are_are_id,
    '27.3.d.30-31' AS are_code,
    'Subdivision_grouping' AS are_lev_code,
    --are_wkg_code,
    true AS is_marine,
    'Gulf of Bothnia (300)',
    st_union(geom) AS geom_polygon,
    NULL AS geom_line
    FROM select_division;

Figure 19: Map of the ICES subdivision grouping level.

ICES subdivision

The same method is applied here to create the subdivision level. To give more flexibility and reusability, the logic is encapsulated in a SQL function. The function insert_fishing_subdivision takes two parameters: a subdiv text representing the subdivision code suffix (e.g., 'd.31'), and a parent area ID p_are_are_id to define the hierarchical relationship. The subdivision code (are_code) is dynamically built by concatenating '27.3.' with the input subdiv. Unlike the previous division-level query, this function filters geometries where fia_level = 'Subdivision', and inserts the corresponding geometry into the refbast.tr_area_are table, while maintaining the correct parent-child linkage through are_are_id.

DROP FUNCTION IF EXISTS insert_fishing_subdivision(subdiv TEXT, p_are_are_id INT);
CREATE OR REPLACE FUNCTION insert_fishing_subdivision(subdiv TEXT, p_are_are_id INT)
RETURNS VOID AS 
$$
DECLARE 
  p_are_code TEXT;
BEGIN
  p_are_code := '27.3.' || subdiv;

  EXECUTE '
    INSERT INTO refbast.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
    WITH select_subdivision AS (
      SELECT geom FROM ref.tr_fishingarea_fia tff 
      WHERE tff.fia_level = ''Subdivision'' AND tff.fia_subdivision = ''' || p_are_code || '''
    )
    SELECT nextval(''refbast.seq'') AS are_id,
           ' || p_are_are_id || ' AS are_are_id,
           ''' || p_are_code || ''' AS are_code,
           ''Subdivision'' AS are_lev_code,
           true AS is_marine,
           geom AS geom_polygon,
           NULL AS geom_line
    FROM select_subdivision;
  ';
END;
$$ LANGUAGE plpgsql;

Figure 20: Map of the ICES subdivision level.

Continental level

Stock

The inland stock level includes all catchments through which rivers flow into the Baltic Sea (Figure 17). In the query above, we insert a new area into the refbast.tr_area_are table with the label 'Baltic inland' at the 'Stock' level. The geometry is built by merging (ST_Union) all relevant catchments from the tempo.catchments_baltic table that inherits from previously built catchment tables. These catchments are selected based on their source tables, identified by the suffixes 'h_baltic30to31', 'h_baltic22to26', and 'h_baltic27to29_32'. The resulting geometry is tagged as non-marine (are_ismarine = false) to distinguish inland from marine stock areas.

INSERT INTO refbast.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
SELECT nextval('refbast.seq') AS are_id,
    1 AS are_are_id,
    'Baltic inland' AS are_code,
    'Stock' AS are_lev_code,
    --are_wkg_code,  by default
    false AS are_ismarine,
    ST_Union(shape) AS geom_polygon,
    NULL AS geom_line
    FROM tempo.catchments_baltic
    WHERE rtrim(tableoid::regclass::text, '.catchments') IN ('h_baltic30to31', 'h_baltic22to26', 'h_baltic27to29_32');

Country

This next layer is optional, it is added here as an example to show what it could look like.

A function is created here to insert the country layer into the table. This insert_country_baltic function takes a country name as input and inserts a new row into the refbast.tr_area_are table representing the specified country at the 'Country' level. It does so by taking the country’s geometry in ref.tr_country_cou. The resulting area is non-marine (are_ismarine = false), and the function automatically assigns a unique are_id from the sequence, while setting the are_are_id to 3 to indicate that it is nested under the inland stock level (Figure 17).

DROP FUNCTION IF EXISTS insert_country_baltic(country TEXT);
CREATE OR REPLACE FUNCTION insert_country_baltic(country TEXT)
RETURNS VOID AS 
$$
BEGIN
  INSERT INTO refbast.tr_area_are (
    are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line
  )
  SELECT 
    nextval('refbast.seq') AS are_id,
    3 AS are_are_id,
    cou_iso3code AS are_code,
    'Country' AS are_lev_code,
    false AS are_ismarine,
    geom AS geom_polygon,
    NULL AS geom_line
  FROM ref.tr_country_cou
  WHERE cou_iso3code = country;
END;
$$ LANGUAGE plpgsql;


SELECT insert_country_baltic('FIN');
SELECT insert_country_baltic('SWE');
SELECT insert_country_baltic('EST');
SELECT insert_country_baltic('LVA');
SELECT insert_country_baltic('LTU');
SELECT insert_country_baltic('POL');
SELECT insert_country_baltic('DEU');
SELECT insert_country_baltic('DNK');
SELECT insert_country_baltic('RUS');

Figure 21: Map of the country level.

Assessment unit

Assessment units have been created by WGBAST within the Baltic Sea area. There is six of them (Figure 22) and they are being used to retrieve catchments and rivers that are within their areas. This level does not go lower than the country one but stays at the same level, assessment units stretching across several countries.

This query creates a new area at the “Assessment_unit” level and inserts it into the refbast.tr_area_are table. It first identifies all river segments from the main stretch of the reach (ord_clas = 1) that intersect with a specific assessment unit (e.g. Ass_unit = 1). Then, using the main_riv outlet identifier, it retrieves all related river geometries. These river segments are used to locate the associated catchments from the tempo.catchments_baltic table via spatial intersection. The geometries of these catchments are merged using ST_Union and inserted as a single multipolygon. The resulting area is labeled with the name of the assessment unit (e.g. '1 Northeastern Bothnian Bay'), categorized under the 'Assessment_unit' level, and marked as non-marine (false). This level references to are_are_id = 3 (Figure 17).

INSERT INTO refbast.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
WITH unit_selection AS (
    SELECT trc.geom AS geom, trc.main_riv
    FROM tempo.riversegments_baltic trc, janis.bast_assessment_units jau
    WHERE ST_Intersects(trc.geom, jau.geom) AND trc.ord_clas = 1 AND jau."Ass_unit" = 1
),
retrieve_rivers AS(
    SELECT DISTINCT trc.geom
    FROM tempo.riversegments_baltic trc, unit_selection us
    WHERE trc.main_riv IN (SELECT main_riv FROM unit_selection)
),
retrieve_catchments AS (
    SELECT DISTINCT ST_Union(tbc.shape) AS geom
    FROM tempo.catchments_baltic tbc, retrieve_rivers rr
    WHERE ST_Intersects(tbc.shape,rr.geom)
)
SELECT nextval('refbast.seq') AS are_id,
        3 AS are_are_id,
        '1 Northeastern Bothnian Bay' AS are_code,
        'Assessment_unit' AS are_lev_code,
        --are_wkg_code,
        false AS is_marine,
        ST_Union(geom) AS geom_polygon,
        NULL AS geom_line
        FROM retrieve_catchments;

Figure 22: Map of WGBAST assessment units level.

River

Within each assessment unit, rivers are split depending on their outlet. This is used to select specific rivers within each assessment unit with ease.

The function insert_area_are creates entries at the River level in the refbast.tr_area_are table by aggregating catchments associated with rivers flowing through a given assessment unit. We begin by identifying all river outlet IDs (main_riv) from the dataset tempo.riversegments_baltic that intersect with the assessment unit (Figure 22) defined by the input parameter p_ass_unit. Only the main stems of rivers, where ord_clas = 1, are considered to ensure consistent delineation.

Once the relevant river outlets are selected, we retrieve all associated river segments and then determine which catchments intersect with those segments. As multiple riversegments might intersect with one catchment, we remove duplicates by selecting a single instance per hybas_id. The resulting geometries are grouped by river outlet ID and merged into a single polygon using ST_Union.

These merged catchments are then inserted into the target table with their corresponding main_bas (that corresponds to the main_riv outlet id) used as the are_code, the are_lev_code is set to River, and the marine flag (are_ismarine) is set to false. This ensures that each outlet is associated with a unique, non-overlapping polygon representing its own river stretch. Finally, are_are_id is linked to the corresponding are_id of each assessment unit.

DROP FUNCTION IF EXISTS insert_river_areas(p_are_are_id INT, p_ass_unit INT);
CREATE OR REPLACE FUNCTION insert_river_areas(p_are_are_id INT, p_ass_unit INT) 
RETURNS VOID AS $$
BEGIN
  WITH unit_riv AS (
    SELECT DISTINCT trc.main_riv
    FROM tempo.riversegments_baltic trc
    JOIN janis.bast_assessment_units jau
      ON ST_Intersects(trc.geom, jau.geom)
    WHERE trc.ord_clas = 1
      AND jau."Ass_unit" = p_ass_unit
  ),
  all_segments AS (
    SELECT trc.main_riv, trc.geom
    FROM tempo.riversegments_baltic trc
    JOIN unit_riv ur ON ur.main_riv = trc.main_riv
  ),
  catchments_with_riv AS (
    SELECT DISTINCT tcb.hybas_id, tcb.main_bas, trc.main_riv, tcb.shape
    FROM tempo.catchments_baltic tcb
    JOIN all_segments trc ON ST_Intersects(tcb.shape, trc.geom)
  ),
  deduplicated AS (
    SELECT DISTINCT ON (hybas_id) main_riv, main_bas, hybas_id, shape
    FROM catchments_with_riv
  ),
  merged AS (
    SELECT main_riv, MIN(main_bas) AS main_bas, ST_Union(shape) AS geom
    FROM deduplicated
    GROUP BY main_riv
  )
  INSERT INTO refbast.tr_area_are (
    are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line
  )
  SELECT 
    nextval('refbast.seq'),
    p_are_are_id,
    main_bas::TEXT,
    'River',
    false,
    geom,
    NULL
  FROM merged
  WHERE geom IS NOT NULL;
  
END;
$$ LANGUAGE plpgsql;

Figure 23: Map of reaches composing WGBAST first assessment units level.

Caution

The names of the basin are not all present, and some might be wrong. We will work in the future with WGBAST to fix them and release a new version of the tables in Zenodo

River section

This query inserts entries at the River_section level in the refbast.tr_area_are table by splitting the main river areas into smaller sections. We start by selecting all polygons corresponding to rivers (are_lev_code = 'River') to define the broader river areas that will serve as the parent units.

Next, river segments are selected from the tempo.riversegments_baltic table, focusing only on the main channels (ord_clas = 1). Each river segment (hyriv_id) is spatially matched to its parent river polygon using ST_Intersects, ensuring that each segment is properly linked to its broader river unit.

Each selected segment is then assigned a new are_id generated from the sequence refbast.seq, while the original hyriv_id from the river segment dataset is used as the are_code. The are_lev_code is set to River_section, and the marine flag (are_ismarine) is set to false. Only one unique entry per hyriv_id is inserted to avoid duplication, ensuring a clean and consistent representation of river sections within the database structure.

INSERT INTO refbast.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
WITH river_level AS (
  SELECT are_id, geom_polygon
  FROM refbast.tr_area_are
  WHERE are_lev_code = 'River'
),
river_segments AS (
  SELECT DISTINCT ON (rs.hyriv_id)
    nextval('refbast.seq') AS are_id,
    rl.are_id AS are_are_id,
    rs.hyriv_id::TEXT AS are_code,
    'River_section' AS are_lev_code,
    false AS is_marine,
    NULL,
    rs.geom
  FROM tempo.riversegments_baltic rs
  JOIN river_level rl
    ON ST_Intersects(rs.geom, rl.geom_polygon)
    WHERE rs.ord_clas = 1
)
SELECT DISTINCT ON (are_code) * FROM river_segments;

Figure 24: Map of main stretch of rivers in the Baltic area.

Creating a hierarchical structure for WGNAS

The work done on the structural hierarchy for WGNAS follows what has been done for WGBAST, although there is some differences in the layers used. Futhermore, in parallel of the Assessment Unit level, a Fisheries level has been added (Figure 25).

Figure 25: Hierarchical structure of WGNAS habitat database

This structure is added into a table called refnas.tr_area_are. As for WGBAST hierarchy, the first layer of the structure is the global Stock level (Figure 26). It includes all catchments that have a river flowing into areas studied by WGNAS as well as all ICES Subareas corresponding to said areas of interest (Figure 27).

This query performs an update of the main NEAC area, corresponding to the record with the identifier (are_id) equal to 1. It begins by merging (ST_Union) all geometries found in the geom_polygon column of the refnas.tr_area_are table, then creates a concave hull around this union using the ST_ConcaveHull function. The resulting geometry is filtered to retain only shapes with an area (ST_Area) greater than 1, thereby eliminating artifacts or small fragments. The final UPDATE statement modifies the record with are_id = 1: the parent area reference (are_are_id) is set to NULL, the area code becomes simply 'NEAC', the level remains 'Stock', the marine status is left undefined (are_ismarine = NULL), the main geometry is replaced with the filtered shape converted into a multipolygon via ST_Multi, and the linear geometry (geom_line) is set to NULL.

WITH unioned_polygons AS (
  SELECT (ST_ConcaveHull(ST_MakePolygon(ST_ExteriorRing((ST_Dump(ST_Union(geom_polygon))).geom)),0.0001,FALSE)) AS geom
  FROM refnas.tr_area_are
),
area_check AS (
  SELECT geom, ST_Area(geom) AS area
  FROM unioned_polygons
),
filtered_polygon AS (
  SELECT geom
  FROM area_check
  WHERE area > 1
)
UPDATE refnas.tr_area_are
SET 
  are_are_id = NULL,
  are_code = 'NEAC',
  are_lev_code = 'Stock',
  are_ismarine = NULL,
  geom_polygon = (SELECT ST_Multi(geom) FROM filtered_polygon),
  geom_line = NULL
WHERE are_id = 1;

Figure 26: Map of the full stock level.

Marine level

Stock

The first query inserts a new record into the refnas.tr_area_are table representing the marine area called NEAC marine. It begins with a WITH clause named selected_level, which aggregates (ST_Union) geometries from the ref.tr_fishingarea_fia table where the area level (fia_level) is 'Division' and the area code (fia_area) is '27'. Specific divisions — '27.3.b, c' and '27.3.d' — are explicitly excluded from the selection. The resulting geometry (geom) is then inserted into the geom_polygon column. The area identifier (are_id) is automatically generated using a sequence (nextval('refnas.seq')), the parent area (are_are_id) is set to 1, the area code (are_code) is set to 'NEAC marine', the level is defined as 'Stock' (are_lev_code), the area is marked as marine (are_ismarine = true), and the line geometry (geom_line) is left empty (NULL).

Similarly, the second query inserts a marine area called NAC marine using the same structure. It also begins with a WITH clause named selected_level, which merges (ST_Union(geom)) geometries from the ref.tr_fishingarea_fia table, but this time focusing on area '21' at the 'Division' level. The resulting unified geometry is used to populate the geom_polygon column. The area is assigned an auto-incremented are_id, no parent (are_are_id = NULL), an area code of 'NAC marine', a level 'Stock', is explicitly identified as marine (are_ismarine = true), and has no line geometry (geom_line = NULL).

INSERT INTO refnas.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
WITH selected_level AS (
    SELECT ST_Union(geom) AS geom
    FROM ref.tr_fishingarea_fia
    WHERE "fia_level" = 'Division' AND "fia_area" = '27'
    AND "fia_division" NOT IN ('27.3.b, c','27.3.d'))
SELECT nextval('refnas.seq') AS are_id,
    1 AS are_are_id,
    'NEAC marine' AS are_code,
    'Stock' AS are_lev_code,
    --are_wkg_code,  by default
    true AS are_ismarine,
    geom AS _polygon,
    NULL AS geom_line
    FROM selected_level;
    
INSERT INTO refnas.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
SELECT nextval('refnas.seq') AS are_id,
    1 AS are_are_id,
    'NEAC inland' AS are_code,
    'Stock' AS are_lev_code,
    false AS are_ismarine,
    ST_Union(shape) AS geom_polygon,
    NULL AS geom_line
FROM tempo.catchments_nas
WHERE REGEXP_REPLACE(tableoid::regclass::text, '\.catchments$', '') IN (
    'h_barents', 'h_biscayiberian', 'h_celtic', 'h_iceland',
    'h_norwegian', 'h_nseanorth', 'h_nseasouth', 'h_nseauk',
    'h_svalbard'
);

Figure 27: Map of the Marine stock level.

Subarea

This query inserts new entries into the refnas.tr_area_are table, each representing a ICES marine subarea within the broader fishing area '27'. It pulls source data from the ref.tr_fishingarea_fia table, filtering for rows where the area level (fia_level) is 'Subarea' and the main area code (fia_area) is '27'. For each matching row, it extracts the subarea code (fia_code) to use as the new area code (are_code), and inserts the associated geometry (geom) into the geom_polygon column. The are_id is generated dynamically using the refnas.seq sequence, each inserted row is assigned the parent area ID 1 (are_are_id = 1), the level is marked as 'Subarea' (are_lev_code), and all entries are flagged as marine (are_ismarine = true). The linear geometry field (geom_line) is not used and is therefore set to NULL.

INSERT INTO refnas.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
SELECT 
  nextval('refnas.seq') AS are_id,
  1 AS are_are_id,
  fia_code AS are_code,
  'Subarea' AS are_lev_code,
  true AS are_ismarine,
  geom AS geom_polygon,
  NULL AS geom_line
FROM ref.tr_fishingarea_fia
WHERE fia_level = 'Subarea'
  AND fia_area = '27';

Figure 28: Map of the ICES Subarea level.

Division

This query inserts new records into the refnas.tr_area_are table, each corresponding to a ICES marine division within fishing area '27'. It retrieves division-level data (fia_level = 'Division') from the ref.tr_fishingarea_fia table (aliased as div) and joins it with previously inserted subarea records from refnas.tr_area_are (aliased as subarea). The join condition reconstructs the subarea code by extracting the first and second components of the division code (fia_code), separated by periods, and concatenating them — effectively matching each division to its parent subarea based on shared code prefixes.

For each resulting pair, a new are_id is generated using nextval('refnas.seq'), and the parent area (are_are_id) is set to the matching subarea’s are_id. The division code (div.fia_code) becomes the new area code (are_code), the level is explicitly set to 'Division' (are_lev_code), and all divisions are marked as marine (are_ismarine = true). The corresponding geometry (div.geom) is inserted into the geom_polygon column, while geom_line is left empty (NULL).

INSERT INTO refnas.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
SELECT 
  nextval('refnas.seq') AS are_id,
  subarea.are_id AS are_are_id,
  div.fia_code AS are_code,
  'Division' AS are_lev_code,
  true AS are_ismarine,
  div.geom AS geom_polygon,
  NULL AS geom_line
FROM ref.tr_fishingarea_fia div
JOIN refnas.tr_area_are subarea
  ON subarea.are_code = split_part(div.fia_code, '.', 1) || '.' || split_part(div.fia_code, '.', 2)
WHERE div.fia_level = 'Division'
  AND div.fia_area = '27'
  AND subarea.are_lev_code = 'Subarea';

Figure 29: Map of the ICES division level. Colours correspond to the higher hierarchical level of Subarea.

Assessment unit

After some disscussions with WGNAS, it was decided to use as Assessment units at sea postsmolt areas from Olmos et al. (2021). Those areas where recreated as close as possible using ICES Division.

This query inserts a new spatial unit into the refnas.tr_area_are table, representing a marine assessment unit called “postsmolt 1”. It begins with a Common Table Expression (WITH geomunion) that selects and merges (ST_Union) the geometries (geom) from the ref.tr_fishingarea_fia table. The filter targets rows where the area level (fia_level) is 'Division' and the division code (fia_division) matches one of the values in the array: '21.4.X', '21.4.W', or '21.5.Y'.

The resulting unified geometry is inserted as a single polygon into the geom_polygon column. A new area ID (are_id) is automatically generated using nextval('refnas.seq'), the parent area is set to 2 (are_are_id = 2), the area code is labeled 'postsmolt 1', and the level is defined as 'Assessment_unit' via are_lev_code. The area is explicitly marked as marine (are_ismarine = true), and no line geometry is provided (geom_line = NULL).

INSERT INTO refnas.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
WITH geomunion AS(
    SELECT ST_Union(geom) AS geom
    FROM ref.tr_fishingarea_fia tff 
    WHERE fia_level = 'Division' AND fia_division = ANY(ARRAY['21.4.X','21.4.W','21.5.Y']))
SELECT nextval('refnas.seq') AS are_id,
       2 AS are_are_id,
       'postsmolt 1' AS are_code,
       'Assessment_unit' AS are_lev_code,
        true AS are_ismarine,
        geom AS geom_polygon,
        NULL AS geom_line
        FROM geomunion;

Figure 30: Map of WGNAS Assessment unit level.

Question to WGNAS

Are geometries for postsmolt satisfying ? For instance, giving that we are using ICES Divisions to create them, there is an overlap between postsmolt 4 and 5.

Fisheries

We decided to create a Fisheries level following work done by WGNAS with their models. To create them we decided to follow fisheries areas present in Olmos et al. (2021).

This query updates an existing record in the refnas.tr_area_are table that represents the “GLD fishery”. It begins with a Common Table Expression (WITH geomunion) that selects and merges (ST_Union) the geometries (geom) from the ref.tr_fishingarea_fia table. It filters for entries where the area level (fia_level) is 'Division' and the division code (fia_division) is one of the specified values: '21.0.A', '21.1.A', '21.1.B', '21.0.B', '21.1.C', '21.1.D', or '21.1.E'. The merged geometry (geom) is then used in an UPDATE to overwrite the geom_polygon field of the area whose code (are_code) is 'GLD fishery'. Additionally, the parent area reference (are_are_id) is updated and set to 4.

WITH geomunion AS(
    SELECT ST_Union(geom) AS geom
    FROM ref.tr_fishingarea_fia tff 
    WHERE fia_level = 'Division' AND fia_division = ANY(ARRAY['21.0.A','21.1.A','21.1.B','21.0.B','21.1.C','21.1.D','21.1.E']))
UPDATE refnas.tr_area_are
SET geom_polygon = geom,
    are_are_id = 4
FROM geomunion
WHERE are_code = 'GLD fishery';

Figure 31: Map of the fisheries level.

Question to WGNAS

• Here we only created fisheries for 3 out of 5 fisheries present in the dataset (GLD, LB/SPM/swNF and FAR). We are missing some informations on where the two remaining ones would need to be (LB & neNF).
• Is the overall level of details for the marine hierarchical structure working for the WG needs ?
  

Continental level

Stock

This query adds an entry named NEAC inland (Figure 17) to the same table. It retrieves data from tempo.catchments_nas, a temporary table containing inland hydrographic features (shape). It selects only the records whose source table (dynamically extracted via tableoid) belongs to a specific set of tables such as 'h_barents', 'h_biscayiberian', 'h_celtic', etc. The selected shapes are merged using ST_Union(shape) to create a single polygon representing the inland part of the NEAC area. As before, a new are_id is generated, are_are_id is set to 1, are_code is 'NEAC inland', the level remains 'Stock', are_ismarine is set to false, the geometry is inserted into geom_polygon, and geom_line is again set to NULL.

INSERT INTO refnas.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
SELECT nextval('refnas.seq') AS are_id,
    1 AS are_are_id,
    'NEAC inland' AS are_code,
    'Stock' AS are_lev_code,
    false AS are_ismarine,
    ST_Union(shape) AS geom_polygon,
    NULL AS geom_line
FROM tempo.catchments_nas
WHERE REGEXP_REPLACE(tableoid::regclass::text, '\.catchments$', '') IN (
    'h_barents', 'h_biscayiberian', 'h_celtic', 'h_iceland',
    'h_norwegian', 'h_nseanorth', 'h_nseasouth', 'h_nseauk',
    'h_svalbard'
);

Assessment unit

Following data from WGNAS dataset and geometries from Olmos et al. 2020 we created inland assessment units around Europe and North America.

This function update_geom_from_wgnas is designed to update the geometry and parent area of a specific assessment unit in the refnas.tr_area_are table based on data from the janis.wgnas_su table.

It takes two input parameters: * p_are_code (TEXT): the code identifying the target area (are_code), * p_are_are_id (INT): the new parent area ID to assign (are_are_id). Inside the function, an UPDATE is performed on refnas.tr_area_are (aliased as tgt), where the are_code matches p_are_code and the level code (are_lev_code) is 'Assessment_unit'. It joins with the janis.wgnas_su table (aliased as src), using the su_ab field to find the matching spatial unit. For the matching row, it updates the geom_polygon column with the geometry from janis.wgnas_su.geom, and sets the parent reference (are_are_id) to the value passed in p_are_are_id.

CREATE OR REPLACE FUNCTION update_geom_from_wgnas(p_are_code TEXT, p_are_are_id INT)
RETURNS void AS
$$
BEGIN
  UPDATE refnas.tr_area_are tgt
  SET 
    geom_polygon = src.geom,
    are_are_id = p_are_are_id
  FROM janis.wgnas_su src
  WHERE tgt.are_code = p_are_code
    AND src.su_ab = p_are_code
    AND tgt.are_lev_code = 'Assessment_unit';
END;
$$ LANGUAGE plpgsql;

Notecou_*** Assessment units

In the dataset, assassment units (AU) that seem to refer to countries are present. We would like to merge them either inside the country layer, either with the AU that already includes areas covered by those cou_*** AU.

Figure 32: Map of the assessment unit level.

River

The function insert_river_areas_nas inserts new river catchment areas into the refnas.tr_area_are table based on spatial intersections between major river segments (ord_clas = 1) from tempo.riversegments_nas and a given assessment unit (p_ass_unit) from janis.wgnas_su. It first identifies the relevant rivers, selects all segments belonging to them, and then determines which catchments (main_bas) intersect those segments. These catchments are merged into single geometries per basin, filtered to exclude those already present in the target table or explicitly listed in p_excluded_id, and inserted as new entries with a generated ID, a parent reference (p_are_are_id), the basin code as are_code, the level set to 'River', are_ismarine marked false, and the merged geometry stored in geom_polygon. A similar function is created for North America as insert_river_areas_nac.

CREATE OR REPLACE FUNCTION insert_river_areas_nas(p_are_are_id INT, p_ass_unit TEXT, p_excluded_id bigint[]) 
RETURNS VOID AS $$
BEGIN
  WITH unit_riv AS (
    SELECT DISTINCT trc.main_riv
    FROM tempo.riversegments_nas trc 
    JOIN janis.wgnas_su jau
      ON ST_Intersects(trc.geom, jau.geom)
    WHERE trc.ord_clas = 1
      AND jau.su_ab = p_ass_unit
  ),
  river_segments AS (
    SELECT *
    FROM tempo.riversegments_nas
    WHERE main_riv IN (SELECT main_riv FROM unit_riv)
  ),
  catchments_with_riv AS (
    SELECT DISTINCT tcb.main_bas, tcb.shape
    FROM tempo.catchments_nas tcb
    JOIN river_segments rs
      ON ST_Intersects(tcb.shape, rs.geom)
  ),
  merged AS (
    SELECT main_bas, ST_Union(shape) AS geom
    FROM catchments_with_riv
    GROUP BY main_bas
  ),
  filtered AS (
    SELECT m.*
    FROM merged m
    LEFT JOIN refnas.tr_area_are a
      ON m.main_bas::TEXT = a.are_code
    WHERE a.are_code IS NULL
  )
  INSERT INTO refnas.tr_area_are (
    are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line
  )
  SELECT
    nextval('refnas.seq'),
    p_are_are_id,
    main_bas::TEXT,
    'River',
    false,
    geom,
    NULL
  FROM filtered
  WHERE geom IS NOT NULL
  AND main_bas <> ALL(p_excluded_id);
END;
$$ LANGUAGE plpgsql;

Figure 33: Map of the River level.

River section

This query inserts new river section features into the refnas.tr_area_are table by identifying river segments that spatially intersect previously defined river-level areas. It begins with a Common Table Expression (WITH) named river_level, which selects the are_id and geom_polygon of all entries in refnas.tr_area_are where the level code (are_lev_code) is 'River'. Then, in the river_segments CTE, it selects distinct river segments (hyriv_id) from tempo.riversegments_nas where the order class (ord_clas) is 1—indicating main rivers—and which intersect any of the river-level geometries. For each intersecting segment, it prepares a new row: assigning a unique ID (nextval('refnas.seq')), linking it to the intersected river area via are_are_id, using the river segment ID as are_code, setting the level to 'River_section', marking it as non-marine (false), leaving geom_polygon empty (NULL), and storing the actual river geometry in geom_line. Finally, only one entry per are_code is retained using DISTINCT ON, ensuring no duplicates are inserted. This operation adds detailed linear representations of rivers, hierarchically linked to their corresponding catchment areas.

INSERT INTO refnas.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
WITH river_level AS (
  SELECT are_id, geom_polygon
  FROM refnas.tr_area_are
  WHERE are_lev_code = 'River'
),
river_segments AS (
  SELECT DISTINCT ON (rs.hyriv_id)
    nextval('refnas.seq') AS are_id,
    rl.are_id AS are_are_id,
    rs.hyriv_id::TEXT AS are_code,
    'River_section' AS are_lev_code,
    false AS is_marine,
    NULL,
    rs.geom
  FROM tempo.riversegments_nas rs
  JOIN river_level rl
    ON ST_Intersects(rs.geom, rl.geom_polygon)
    WHERE rs.ord_clas = 1
)
SELECT DISTINCT ON (are_code) * FROM river_segments;

Figure 34: Map of the river section level.

Country

To add the country level, the same work has been done as for WGBAST (Section 3.1.2.2).

Creating a hierarchical structure for WGEEL

The hierarchical structure for WGEEL is the same as for the two other working groups. The scope of the habitat database is larger for eel since the Mediterranean Sea is part of the assessed area. Lagoons and Complex of lagoons are also part of this structure (Figure 35).

Figure 35: Hierarchical structure of WGEEL habitat database.

This structure will be added into a table named refeel.tr_area_are. The global Stock is still the first layer and is made of a fusion between the Marine and the Inland Stock (Figure 36). It includes all catchments flowing into areas studied by WGEEL as well as all ICES Divisions and GFCM Subdivisions (for the Mediterranean) corresponding to said areas of interest.

To create this layer the same approach has been used as for WGNAS (Section 3.2).

WITH unioned_polygons AS (
  SELECT (ST_ConcaveHull(ST_MakePolygon(ST_ExteriorRing((ST_Dump(ST_Union(geom_polygon))).geom)),0.0001,FALSE)) AS geom
  FROM refeel.tr_area_are
),
area_check AS (
  SELECT geom, ST_Area(geom) AS area
  FROM unioned_polygons
),
filtered_polygon AS (
  SELECT geom
  FROM area_check
  WHERE area > 1
),
final_geom AS (
SELECT ST_Multi(ST_Union(geom)) AS geom
FROM filtered_polygon
)
UPDATE refeel.tr_area_are
SET 
  are_are_id = NULL,
  are_code = 'Europe',
  are_lev_code = 'Stock',
  are_ismarine = NULL,
  geom_polygon = (SELECT geom FROM final_geom),
  geom_line = NULL
WHERE are_id = 1;

Structure of the global Stock level in the referential table

are_id	are_are_id	are_code	are_lev_code	are_wkg_code	are_ismarine
1	NA	Europe	Stock	WGEEL	NA

Figure 36: Map of the Stock level for the WGEEL habitat database. The full stock is a merge of the inland and the marine stock.

Marine level

Stock

To create the Marine Stock level, the same query has been used as for WGNAS (Section 3.2.1.1). The only difference being that the Major level of ref.tr_fishingarea_fia was used to select the Mediterranean as well as the Northern part of Europe (Figure 36).

INSERT INTO refeel.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
SELECT nextval('refeel.seq') AS are_id,
    1 AS are_are_id,
    'Marine' AS are_code,
    'Stock' AS are_lev_code,
    --are_wkg_code,  by default
    true AS are_ismarine,
    ST_Union(geom) AS geom_polygon,
    NULL AS geom_line
    FROM ref.tr_fishingarea_fia 
    WHERE"fia_level"='Major' AND "fia_code" IN ('27','37');

Assessment unit

This query inserts a new spatial unit into the refeel.tr_area_are table, representing a marine assessment unit called “Marine Barents”. It selects and merges (ST_Union) the geometries (geom) from the ref.tr_fishingarea_fia table, restricted to fishing areas whose division code (fia_division) matches one of the following values: '27.14.a', '27.5.a', '27.2.b', '27.2.a', '27.1.b', '27.1.a', or '27.14.b'.

The resulting unified geometry is converted to a multi-geometry (ST_Multi) and inserted as a single polygon into the geom_polygon column. A new area ID (are_id) is automatically generated using nextval('refeel.seq'), the parent area is set to are_are_id = 2 (Marine Stock), the area code is labeled 'Marine Barents', and the level is defined as 'Assessment_unit' via are_lev_code. The area is explicitly marked as marine (are_ismarine = true), and no line geometry is provided (geom_line = NULL).

INSERT INTO refeel.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
SELECT nextval('refeel.seq') AS are_id,
    2 AS are_are_id,
    'Marine Barents' AS are_code,
    'Assessment_unit' AS are_lev_code,
    true AS are_ismarine,
    ST_Multi(ST_Union(geom)) AS geom_polygon,
    NULL AS geom_line
FROM ref.tr_fishingarea_fia tff 
WHERE fia_division IN ('27.14.a','27.5.a','27.2.b','27.2.a','27.1.b','27.1.a','27.14.b');

Figure 37: Map of the Assessment unit level for the WGEEL habitat database. The map is showing both inland and marine levels.

Regional

This query inserts a new spatial unit into the refeel.tr_area_are table, representing a marine regional unit called “Western Mediterranean”.

It starts with a Common Table Expression (WITH region_selection) that selects and merges (ST_Union) the geometries (geom) from the ref.tr_fishingarea_fia table at the Subdivision level (fia_level = 'Subdivision'). These fishing area geometries are spatially matched (ST_Intersects) with existing assessment units from the refeel.tr_area_are table. The selection is further restricted to subdivisions whose codes (fia_subdivision) belong to the specified list and to a single parent assessment unit identified by are_id = 9. The resulting geometries are grouped by the parent area and converted into a multi-geometry using ST_Multi.

The unified geometry produced by the CTE is then inserted as a single polygon into the geom_polygon column. A new area identifier (are_id) is automatically generated using nextval('refeel.seq'), while the parent area is set to the originating assessment unit (are_are_id = are_id from the CTE). The area code is defined as 'Western Mediterranean', the spatial level is set to 'Regional' via are_lev_code, and the area is explicitly marked as marine (are_ismarine = true). No line geometry is provided (geom_line = NULL).

INSERT INTO refeel.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
    WITH region_selection AS (
      SELECT DISTINCT ON (geom) ST_Multi(ST_Union(fi.geom)) AS geom, ta.are_id
      FROM ref.tr_fishingarea_fia fi
      JOIN refeel.tr_area_are ta
      ON ST_Intersects(ta.geom_polygon,fi.geom)
      WHERE fi.fia_level = 'Subdivision'
      AND ta.are_lev_code = 'Assessment_unit'
      AND fia_subdivision IN ('37.1.1.1','37.1.1.2','37.1.1.3','37.1.1.4','37.1.1.5','37.1.1.6','37.1.2.7','37.1.3.8','37.1.3.9','37.1.3.111','37.1.3.10','37.1.3.112')
      AND are_id = 9
     GROUP BY ta.are_id
    )
    SELECT nextval('refeel.seq') AS are_id,
           are_id AS are_are_id,
           'Western Mediterranean' AS are_code,
           'Regional' AS are_lev_code,
           true AS are_ismarine,
           geom AS geom_polygon,
           NULL AS geom_line
    FROM region_selection;  

Figure 38: Map of the Regional level for the WGEEL habitat database. The map is showing both inland and marine levels.

ICES Division

This query inserts new spatial units into the refeel.tr_area_are table, creating marine Division-level areas derived from existing Regional units and ICES fishing divisions.

It begins with a Common Table Expression (WITH intersections) that joins the ref.tr_fishingarea_fia table with the refeel.tr_area_are table based on spatial intersection (ST_Intersects). The query targets fishing areas at the Division level (fia_level = 'Division') and intersects them with existing areas classified as Regional (are_lev_code = 'Regional'). Certain divisions ('21.6.H', '21.3.M', '21.1.F') and the entire area '37' are explicitly excluded. For each intersecting pair, the query computes the area of overlap using ST_Area(ST_Intersection(...)) and retains the original division geometry as a multi-geometry (ST_Multi).

A second CTE (ranked) then selects, for each fishing division (fia_division), the Regional area with which it has the largest intersection area. This has been done to make sure each ICES division was matching its right Region since some of them are slightly overlapping into another Region. This is achieved using DISTINCT ON (fia_division) combined with an ORDER BY clause sorting by descending intersection area. For each selected division, a new area ID (are_id) is generated using nextval('refeel.seq'), the parent area (are_are_id) is set to the corresponding Regional unit, and the division code itself is used as the area code (are_code). The spatial level is defined as 'Division', and the area is explicitly marked as marine (are_ismarine = true).

Finally, the selected records are inserted into the table with the division geometry stored in the geom_polygon column and no line geometry provided (geom_line = NULL).

INSERT INTO refeel.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
    WITH intersections AS (
        SELECT
            fi.fia_division,
            ST_Multi(fi.geom) AS geom,
            ta.are_id AS are_id,
            ST_Area(ST_Intersection(ta.geom_polygon, fi.geom)) AS intersection_area
        FROM ref.tr_fishingarea_fia fi
        JOIN refeel.tr_area_are ta
            ON ST_Intersects(ta.geom_polygon, fi.geom)
        WHERE fi.fia_level = 'Division'
          AND ta.are_lev_code = 'Regional'
          AND fi.fia_division NOT IN ('21.6.H', '21.3.M', '21.1.F')
          AND fi.fia_area NOT IN ('37')
    ),
    ranked AS (
        SELECT DISTINCT ON (fia_division)
            nextval('refeel.seq') AS are_id,
            are_id AS are_are_id,
            fia_division AS are_code,
            'Division' AS are_lev_code,
            true AS are_ismarine,
            geom AS geom_polygon,
            NULL AS geom_line
        FROM intersections
        ORDER BY fia_division, intersection_area DESC
    )
    SELECT *
    FROM ranked;

Figure 39: Map of the ICES Division level for the WGEEL habitat database.

ICES Subdivision

This level is created using the same query as for the Division level (Section 3.3.1.4). The only difference being that fia_level = 'Subdivision' is selected instead of Division.

INSERT INTO refeel.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
WITH intersections AS (
    SELECT
        fi.fia_subdivision,
        ST_Multi(fi.geom) AS geom,
        ta.are_id AS are_id,
        ST_Area(ST_Intersection(ta.geom_polygon, fi.geom)) AS intersection_area
    FROM ref.tr_fishingarea_fia fi
    JOIN refeel.tr_area_are ta
        ON ST_Intersects(ta.geom_polygon, fi.geom)
    WHERE fi.fia_level = 'Subdivision'
      AND ta.are_lev_code = 'Regional'
      AND fi.fia_subdivision NOT IN ('27.10.a.1', '27.12.a.1', '27.12.a.3')
),
ranked AS (
    SELECT DISTINCT ON (fia_subdivision)
        nextval('refeel.seq') AS are_id,
        are_id AS are_are_id,
        fia_subdivision AS are_code,
        'Subdivision' AS are_lev_code,
        true AS are_ismarine,
        geom AS geom_polygon,
        NULL AS geom_line
    FROM intersections
    ORDER BY fia_subdivision, intersection_area DESC
)
SELECT *
FROM ranked;

Figure 40: Map of the ICES Subivision level for the WGEEL habitat database.

Continental level

Stock

This query inserts a new spatial unit into the refeel.tr_area_are table, representing a non-marine Stock-level unit called “Inland” (Figure 36).

It selects and merges (ST_Union) the geometries (shape) from the tempo.catchments_eel table, restricted to a specific set of catchment groups. These groups are identified by extracting the schema name from the source table using tableoid::regclass and filtering it against a predefined list of catchments created beforehand (Section 2.4) (e.g. h_adriatic, h_baltic30to31, h_barents, h_medwest, h_svalbard, etc.).

The resulting unified geometry is inserted as a single polygon into the geom_polygon column. A new area identifier (are_id) is automatically generated using nextval('refeel.seq'), the parent area is set to 1 (are_are_id = 1), the area code is defined as 'Inland', and the spatial level is set to 'Stock' via are_lev_code. The area is explicitly marked as non-marine (are_ismarine = false), and no line geometry is provided (geom_line = NULL).

INSERT INTO refeel.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
SELECT nextval('refeel.seq') AS are_id,
    1 AS are_are_id,
    'Inland' AS are_code,
    'Stock' AS are_lev_code,
    false AS are_ismarine,
    ST_Union(shape) AS geom_polygon,
    NULL AS geom_line
FROM tempo.catchments_eel
WHERE regexp_replace(tableoid::regclass::text, '\.catchments$', '') IN ('h_adriatic',
  'h_baltic30to31', 'h_baltic22to26', 'h_baltic27to29_32','h_barents',
  'h_biscayiberian','h_blacksea','h_celtic','h_iceland','h_medcentral',
  'h_medeast','h_medwest','h_norwegian','h_nseanorth','h_nseasouth',
  'h_nseauk','h_southatlantic','h_southmedcentral','h_southmedeast',
  'h_southmedwest','h_svalbard'
);

Assessment_unit

This query inserts a new spatial unit into the refeel.tr_area_are table, representing a non-marine assessment unit (Figure 44) called “Inland Barents”.

It selects and merges (ST_Union) the geometries (shape) from the tempo.catchments_eel table, restricted to catchment groups identified by their schema names. These schemas are extracted from tableoid::regclass and filtered to include only 'h_barents', 'h_iceland', 'h_norwegian', and 'h_svalbard'.

The resulting unified geometry is inserted as a single polygon into the geom_polygon column. A new area identifier (are_id) is automatically generated using nextval('refeel.seq'), the parent area is set to 3 (are_are_id = 3), the area code is defined as 'Inland Barents', and the spatial level is set to 'Assessment_unit' via are_lev_code. The area is explicitly marked as non-marine (are_ismarine = false), and no line geometry is provided (geom_line = NULL).

INSERT INTO refeel.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
SELECT nextval('refeel.seq') AS are_id,
    3 AS are_are_id,
    'Inland Barents' AS are_code,
    'Assessment_unit' AS are_lev_code,
    false AS are_ismarine,
    ST_Union(shape) AS geom_polygon,
    NULL AS geom_line
FROM tempo.catchments_eel
WHERE regexp_replace(tableoid::regclass::text, '\.catchments$', '') IN ('h_barents',
  'h_iceland','h_norwegian','h_svalbard');

River

This query inserts new spatial units into the refeel.tr_area_are table, creating non-marine River-level areas derived from river networks and catchment boundaries.

It begins with a series of Common Table Expressions (CTEs) to progressively associate river segments with existing inland assessment units. The first CTE (unit_riv) identifies the main stretch of river (main_riv) whose first-order river segments (ord_clas = 1) spatially intersect (ST_Intersects) with non-marine assessment units (are_lev_code = 'Assessment_unit' and are_ismarine = false) in the refeel.tr_area_are table.

The second CTE (all_segments) retrieves all river segments belonging to these main rivers and associates them with the corresponding parent assessment unit. The third CTE (catchments_with_riv) links catchment polygons from tempo.catchments_eel to the river network by selecting catchments that spatially intersect the river segments, retaining their basin identifier (main_bas), catchment identifier (hybas_id), and geometry.

To avoid duplicates, the deduplicated CTE keeps a single record per catchment (hybas_id). The merged CTE then aggregates catchments by main basin (main_bas), assigns them to the corresponding parent assessment unit (are_id), and merges their geometries using ST_Union.

Finally, for each resulting river basin, a new area is inserted with a generated identifier (are_id via nextval('refeel.seq')), the parent area set to the associated assessment unit, and the basin code (main_bas) used as the area code. The spatial level is defined as 'River', the area is marked as non-marine (are_ismarine = false), and the merged geometry is stored as a multi-polygon (ST_Multi) in the geom_polygon column. No line geometry is provided (geom_line = NULL).

INSERT INTO refeel.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
WITH unit_riv AS (
    SELECT DISTINCT re.main_riv, taa.are_id
    FROM tempo.riversegments_eel re
    JOIN refeel.tr_area_are taa
      ON ST_Intersects(re.geom, taa.geom_polygon)
    WHERE re.ord_clas = 1
      AND taa.are_lev_code = 'Assessment_unit'
      AND taa.are_ismarine = false
),
all_segments AS (
    SELECT re.main_riv, ur.are_id, re.geom
    FROM tempo.riversegments_eel re
    JOIN unit_riv ur ON ur.main_riv = re.main_riv
),
catchments_with_riv AS (
    SELECT DISTINCT tce.hybas_id, tce.main_bas, re.main_riv, re.are_id, tce.shape
    FROM tempo.catchments_eel tce
    JOIN all_segments re ON ST_Intersects(tce.shape, re.geom)
),
deduplicated AS (
    SELECT DISTINCT ON (hybas_id) main_riv, main_bas, hybas_id, are_id, shape
    FROM catchments_with_riv
),
merged AS (
    SELECT main_bas, MIN(are_id) AS are_id, ST_Union(shape) AS geom
    FROM deduplicated
    GROUP BY main_bas
)
SELECT 
    nextval('refeel.seq'),
    are_id,
    main_bas::TEXT,
    'River',
    false,
    ST_Multi(geom),
    NULL
FROM merged
WHERE geom IS NOT NULL;

Figure 41: Map of the River level for the WGEEL habitat database. Colours are related to the higher hierarchical level (Assessment unit).

River_section

This query inserts new spatial units into the refeel.tr_area_are table, creating non-marine river section–level areas derived from existing River-level units and individual river segments.

It starts with a Common Table Expression (WITH river_level) that selects all areas from refeel.tr_area_are classified at the River level (are_lev_code = 'River'), retaining their identifiers and polygon geometries.

The second CTE (river_segments) identifies first-order river segments (ord_clas = 1) from the tempo.riversegments_eel table that spatially intersect (ST_Intersects) these River-level polygons. For each distinct river segment (hyriv_id), a new area is prepared: a unique identifier is generated using nextval('refeel.seq'), the parent area (are_are_id) is set to the intersecting River unit, and the river segment identifier is used as the area code (are_code). The spatial level is defined as 'river_section', and the area is marked as non-marine (are_ismarine = false). The segment geometry is stored as a line geometry in geom_line, while no polygon geometry is provided (geom_polygon = NULL).

Finally, the query inserts one record per river section by selecting distinct entries on are_code, ensuring that each river segment is represented only once in the table.

INSERT INTO refeel.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
WITH river_level AS (
  SELECT are_id, geom_polygon
  FROM refeel.tr_area_are
  WHERE are_lev_code = 'River'
),
river_segments AS (
  SELECT DISTINCT ON (rs.hyriv_id)
    nextval('refeel.seq') AS are_id,
    rl.are_id AS are_are_id,
    rs.hyriv_id::TEXT AS are_code,
    'river_section' AS are_lev_code,
    false AS is_marine,
    NULL,
    rs.geom
  FROM tempo.riversegments_eel rs
  JOIN river_level rl
    ON ST_Intersects(rs.geom, rl.geom_polygon)
    WHERE rs.ord_clas = 1
)
SELECT DISTINCT ON (are_code) * FROM river_segments;

Figure 42: Map of the River section level for the WGEEL habitat database.

Regional

To create the Inland Regional level (Figure 38) the same method has been used as to create the first division of catchments (Section 2.4). Here, all catchments flowing into the previously created Marine Regional level are grouped together.

Complex

In the Mediterranean, lagoons are an essential part of the eel life cylce. Depending on the country, data is provided either per lagoon or per groupings of lagoons (Complex). Thus we created a level that groups lagoons per areas when needed.

This query inserts a new spatial unit into the refeel.tr_area_are table, representing a Complex-level area called “Campania and Southern Latium”.

It begins with a Common Table Expression (WITH select_complex) that selects basin geometries (shape) from two levels of the Basin Atlas dataset. Specifically, it extracts one basin from basinatlas.basinatlas_v10_lev06 and two basins from basinatlas.basinatlas_v10_lev07, based on their hybas_id values. These geometries are combined into a single result set using UNION ALL.

The selected basin geometries are then merged into a single polygon using ST_Union and inserted into the geom_polygon column. A new area identifier (are_id) is automatically generated using nextval('refeel.seq'), the parent area is set to 295 (are_are_id = 295), the area code is defined as 'Campania and Southern Latium', and the spatial level is set to 'Complex' via are_lev_code. The area is explicitly marked as non-marine (are_ismarine = false), and no line geometry is provided (geom_line = NULL).

INSERT INTO refeel.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, geom_polygon, geom_line)
    WITH select_complex AS (
    SELECT shape AS geom FROM basinatlas.basinatlas_v10_lev06
    WHERE hybas_id IN (2060015480)
    UNION ALL 
    SELECT shape AS geom FROM basinatlas.basinatlas_v10_lev07
    WHERE hybas_id IN (2070015430,2070015320)
    )
    SELECT nextval('refeel.seq') AS are_id,
           295 AS are_are_id,
           'Campania and Southern Latium'   AS are_code,
           'Complex'     AS are_lev_code,
           FALSE          AS are_ismarine,
           ST_Union(geom) AS geom_polygon,
           NULL          AS geom_line
    FROM select_complex;

Figure 43: Map of the Complex level for the WGEEL habitat database.

Lagoons

Lagoons data were provided by different sources (GFCM, National databases, HydroSHEDS) and some of them were provided with a name while others were not. An identifier was created to identify each lagoon while names are stored in the are_name column. The identifier will follow this format : L + Country_code + id (3 digits).

This function tempo.merge_and_update() cleans and merges lagoon polygons in the tempo.all_lagoons table based on reference data from tempo.gfcm_lagoon.

The function begins by updating missing site_name values in tempo.all_lagoons. It matches lagoons with reference lagoons where habitat_type = 'LGN' and LULC = 521 (Lagoons), using a spatial intersection with a buffer of 0.02 units around the reference geometries (ST_Intersects(el.geom, ST_Buffer(gl.geom, 0.02))). This ensures that lagoons close to reference sites receive the correct name.

Next, the function creates a temporary table tmp_merged to prepare for merging. For each site_name, it identifies the lagoon polygon with the largest area (keep_ctid) and merges all polygons with the same site_name into a multi-geometry (ST_Multi(ST_Union(a.geom))). This step consolidates multiple records representing the same site into a single polygon.

The function then removes duplicate lagoons by deleting all rows that share the same site_name except for the one identified by keep_ctid. This ensures that only one record per site remains in the table.

Finally, the function updates the geometry of the remaining lagoons with the merged shapes from tmp_merged, ensuring that each site has a single, unified polygon. Throughout the process, RAISE NOTICE statements provide progress updates for each step.

DROP SEQUENCE IF EXISTS lgr_seq;
CREATE SEQUENCE lgr_seq
  START 1
  INCREMENT 1
  MINVALUE 1
  OWNED BY NONE;
 
CREATE OR REPLACE FUNCTION gen_lgr_code(country_code TEXT)
RETURNS text AS $$
BEGIN
  RETURN country_code || LPAD(nextval('lgr_seq')::text, 3, '0');
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tempo.merge_and_update()
RETURNS void
LANGUAGE plpgsql
AS
$$
DECLARE
    sql TEXT;
BEGIN
    sql := '
        UPDATE tempo.all_lagoons el
        SET site_name = gl.site_name
        FROM tempo.gfcm_lagoon gl
        WHERE el.site_name IS NULL
          AND el."LULC" = 521
          AND gl.habitat_type = ''LGN''
          AND ST_Intersects(el.geom, ST_Buffer(gl.geom, 0.02))
    ';
    EXECUTE sql;
    RAISE NOTICE 'site_name applied with buffer 0.02';

    sql := '
        CREATE TEMP TABLE tmp_merged ON COMMIT DROP AS
        SELECT 
            a.site_name,
            (SELECT b.ctid
             FROM tempo.all_lagoons b
             WHERE b.site_name = a.site_name
             ORDER BY ST_Area(b.geom) DESC
             LIMIT 1) AS keep_ctid,
            ST_Multi(ST_Union(a.geom)) AS geom
        FROM tempo.all_lagoons a
        WHERE a.site_name IS NOT NULL
        GROUP BY a.site_name
    ';
    EXECUTE sql;
    RAISE NOTICE 'tmp_merged built for fusion';

    sql := '
        DELETE FROM tempo.all_lagoons a
        USING tmp_merged t
        WHERE a.site_name = t.site_name
          AND a.ctid <> t.keep_ctid
    ';
    EXECUTE sql;
    RAISE NOTICE 'duplicates removed';

    sql := '
        UPDATE tempo.all_lagoons el
        SET geom = t.geom
        FROM tmp_merged t
        WHERE el.ctid = t.keep_ctid
    ';
    EXECUTE sql;
    RAISE NOTICE 'geometries updated';
END;
$$;

This query inserts new spatial units into the refeel.tr_area_are table, representing Lagoons-level areas derived from lagoon polygons in the tempo.all_lagoons table.

It begins by creating a sequence lgr_seq to generate unique identifiers, starting from 1.

A Common Table Expression (WITH lagunes_selection) first selects distinct lagoon geometries (geom) from tempo.all_lagoons where the land-use code (LULC) equals 521, retaining their site_name.

A second CTE (lag_with_comp) associates each lagoon with its parent spatial unit by performing a spatial intersection (ST_Intersects) with polygons in refeel.tr_area_are. Only lagoons intersecting certain parent areas (e.g., 'Northeast Italy', 'Central Italy', 'Campania and Southern Latium', etc.) are retained, and the corresponding parent area ID (are_id) is captured.

Finally, each lagoon is inserted as a new record in refeel.tr_area_are with the following properties: a new area ID generated from nextval('refeel.seq'), the parent area (are_are_id) set to the intersecting parent unit, a generated area code using gen_lgr_code('LIT'), the level set to 'Lagoons' (are_lev_code), the lagoon’s site name (are_name), and the polygon geometry (geom_polygon). No marine flag or line geometry is provided (are_ismarine = NULL, geom_line = NULL).

DROP SEQUENCE IF EXISTS lgr_seq;
CREATE SEQUENCE lgr_seq
  START 1
  INCREMENT 1
  MINVALUE 1
  OWNED BY NONE;
 
INSERT INTO refeel.tr_area_are (are_id, are_are_id, are_code, are_lev_code, are_ismarine, are_name, geom_polygon, geom_line)
WITH lagunes_selection AS (
    SELECT DISTINCT ON (al.geom) al.geom, al.site_name FROM tempo.all_lagoons al 
    WHERE al."LULC" = 521
),
lag_with_comp AS (
  SELECT
         ls.geom,
         ls.site_name,
         ta.are_id AS are_id
  FROM lagunes_selection ls
  JOIN refeel.tr_area_are ta
    ON ST_Intersects(ls.geom, ta.geom_polygon)
  WHERE ta.are_code IN('Northeast Italy','Central Italy','Foggia','Campania and Southern Latium','Lecce','Messina',
                        'Siragusa and Ragusa','Gallura and Nuoro','East Cagliari','South Cagliari','Sulcis Inglesiente',
                        'Oristano','Sassari')
)
SELECT nextval('refeel.seq') AS are_id,
       are_id AS are_are_id,
       gen_lgr_code('LIT')   AS are_code,
       'Lagoons'     AS are_lev_code,
       NULL          AS are_ismarine,
       site_name     AS are_name,
       geom          AS geom_polygon,
       NULL          AS geom_line
    FROM lag_with_comp;

Figure 44: Map of an example of the Lagoons level for the WGEEL habitat database. Red areas are lagoons nested within larger Complex.

EMU

Even though EMU are to be deprecated, they have been included within the database to help with the transition from EMU to Regional or Assessment units.

Figure 45: Map of an example of the EMU level for the WGEEL habitat database.

Sharing this referential

TipZenodo link

The referential is available in Zenodo, please make sure to connect to the latest version.

DOI

TipLink to ICES

Here is a proof of concept worked out by Carlos to download from ICES Just add a new vector layer in Qgis using http. data.ices.dk/HabitatsAPI/getCatchments?region=Adriatic

Acknowlegments and references

HydroSHEDS : Lehner, B., Verdin, K., Jarvis, A. (2008). New global hydrography derived from spaceborne elevation data. Eos, Transactions, American Geophysical Union, 89(10): 93–94. https://doi.org/10.1029/2008eo100001