diaspara habitat database creation script

DIASPARA WP3.1 working document

Design an international database of habitat of migratory fish, version = build
Author

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

Published

01-07-2025

Choice of 2 river networks

To create the habitat database we decided to use two river networks in parallel. The Hydrological data and maps based on SHuttle Elevation Derivatives at multiple Scales (HydroSHEDS) and EU-Hydro from Copernicus (European Union’s Earth Observation Programme).

EU-Hydro, being an European only dataset, will not include the Southern Mediterranean.

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 opted to 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
Code
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
objectid NA
hyriv_id Identifier of the segment
next_down Identifier of the next downstream river segment
main_riv Identifier of the most downstream river segment of the river basin
length_km Length of the river segment
dist_dn_km Distance from the most downstream part of the segment to the most downstream part of the river network
dist_up_km Distance from the most downstream part of the segment to the most upstream part of the river network
catch_skm NA
upland_skm NA
endorheic Indicator for presence (1) or abscence (0) of a endorheic basin downstream of the segment
dis_av_cms Average long-term discharge estimate for river reach, in m3 s-1
ord_stra Strahler rank
ord_clas Classic stream rank
ord_flow River order using average river discharge
hybas_l12 Identifier of the HydroBASINS river basin level 12
dis_m3_pyr Annual average of the natural discharge in m3
dis_m3_pmn Annual minimum of the natural discharge in m3
dis_m3_pmx Annual maximum of the natural discharge in m3
run_mm_cyr Annual average land surface runoff in reach catchment
inu_pc_cmn Annual minimum Inundation Extent in reach catchment
inu_pc_umn Annual minimum Inundation Extent in total watershed upstream
inu_pc_cmx Annual maximum Inundation Extent in reach catchment
inu_pc_umx Annual maximum Inundation Extent in total watershed upstream
inu_pc_clt Long-term maximum Inundation Extent in reach catchment
inu_pc_ult Long-term maximum Inundation Extent in total watershed upstream
lka_pc_cse Limnicity (Percent Lake Area) spatial extent (%) in reach catchment
lka_pc_use Limnicity (Percent Lake Area) spatial extent (%) in total watershed upstream
lkv_mc_usu Sum of lake volume in total watershed upstream
rev_mc_usu Sum of reservoir volume in total watershed upstream
dor_pc_pva Degree of regulation at pour point of river reach
ria_ha_csu Sum of river area in reach catchment
ria_ha_usu Sum of river area in total watershed upstream
riv_tc_csu Sum of river volume in reach catchment
riv_tc_usu Sum of river volume in total watershed upstream
gwt_cm_cav Average Groundwater Table Depth in reach catchment
ele_mt_cav Average elevation in reach catchment
ele_mt_uav Average elevation in total watershed upstream
ele_mt_cmn Minimum elevation in reach catchment
ele_mt_cmx Maximum elevation in reach catchment
slp_dg_cav Terrain slope in reach catchment
slp_dg_uav Terrain slope in total watershed upstream
sgr_dk_rav Average stream gradient along reach segment
clz_cl_cmj Spatial majority of climate zone in reach catchment
cls_cl_cmj Spatial majority of climate strata in reach catchment
tmp_dc_cyr Annual average of air temperature in reach catchment
tmp_dc_uyr Annual average of air temperature in total watershed upstream
tmp_dc_cmn Annual minimum of air temperature in reach catchment
tmp_dc_cmx Annual maximum of air temperature in reach catchment
tmp_dc_c01 Average January air temperature in reach catchment
tmp_dc_c02 Average February air temperature in reach catchment
tmp_dc_c03 Average March air temperature in reach catchment
tmp_dc_c04 Average April air temperature in reach catchment
tmp_dc_c05 Average May air temperature in reach catchment
tmp_dc_c06 Average June air temperature in reach catchment
tmp_dc_c07 Average July air temperature in reach catchment
tmp_dc_c08 Average August air temperature in reach catchment
tmp_dc_c09 Average September air temperature in reach catchment
tmp_dc_c10 Average October air temperature in reach catchment
tmp_dc_c11 Average November air temperature in reach catchment
tmp_dc_c12 Average December air temperature in reach catchment
pre_mm_cyr Annual average of precipitation in reach catchment in mm
pre_mm_uyr Annual average of precipitation in total watershed upstream in mm
pre_mm_c01 Average January precipitation in reach catchment in mm
pre_mm_c02 Average February precipitation in reach catchment in mm
pre_mm_c03 Average March precipitation in reach catchment in mm
pre_mm_c04 Average April precipitation in reach catchment in mm
pre_mm_c05 Average May precipitation in reach catchment in mm
pre_mm_c06 Average June precipitation in reach catchment in mm
pre_mm_c07 Average July precipitation in reach catchment in mm
pre_mm_c08 Average August precipitation in reach catchment in mm
pre_mm_c09 Average September precipitation in reach catchment in mm
pre_mm_c10 Average October precipitation in reach catchment in mm
pre_mm_c11 Average November precipitation in reach catchment in mm
pre_mm_c12 Average December precipitation in reach catchment in mm
pet_mm_cyr Annual average of potential evapotranspiration in reach catchment in mm
pet_mm_uyr Annual average of potential evapotranspiration in total watershed upstream in mm
pet_mm_c01 January potential evapotranspiration in reach catchment in mm
pet_mm_c02 February potential evapotranspiration in reach catchment in mm
pet_mm_c03 March potential evapotranspiration in reach catchment in mm
pet_mm_c04 April potential evapotranspiration in reach catchment in mm
pet_mm_c05 May potential evapotranspiration in reach catchment in mm
pet_mm_c06 June potential evapotranspiration in reach catchment in mm
pet_mm_c07 July potential evapotranspiration in reach catchment in mm
pet_mm_c08 August potential evapotranspiration in reach catchment in mm
pet_mm_c09 September potential evapotranspiration in reach catchment in mm
pet_mm_c10 October potential evapotranspiration in reach catchment in mm
pet_mm_c11 November potential evapotranspiration in reach catchment in mm
pet_mm_c12 December potential evapotranspiration in reach catchment in mm
aet_mm_cyr Annual average of actual evapotranspiration in reach catchment in mm
aet_mm_uyr Annual average of actual evapotranspiration in total watershed upstream in mm
aet_mm_c01 January actual evapotranspiration in reach catchment in mm
aet_mm_c02 February actual evapotranspiration in reach catchment in mm
aet_mm_c03 March actual evapotranspiration in reach catchment in mm
aet_mm_c04 April actual evapotranspiration in reach catchment in mm
aet_mm_c05 May actual evapotranspiration in reach catchment in mm
aet_mm_c06 June actual evapotranspiration in reach catchment in mm
aet_mm_c07 July actual evapotranspiration in reach catchment in mm
aet_mm_c08 August actual evapotranspiration in reach catchment in mm
aet_mm_c09 September actual evapotranspiration in reach catchment in mm
aet_mm_c10 October actual evapotranspiration in reach catchment in mm
aet_mm_c11 November actual evapotranspiration in reach catchment in mm
aet_mm_c12 December actual evapotranspiration in reach catchment in mm
ari_ix_cav Average global aridity index in reach catchment
ari_ix_uav Average global aridity index in total watershed upstream
cmi_ix_cyr Annual average climate moisture index in reach catchment
cmi_ix_uyr Annual average climate moisture index in total watershed upstream
cmi_ix_c01 January climate moisture index in reach catchment
cmi_ix_c02 February climate moisture index in reach catchment
cmi_ix_c03 March climate moisture index in reach catchment
cmi_ix_c04 April climate moisture index in reach catchment
cmi_ix_c05 May climate moisture index in reach catchment
cmi_ix_c06 June climate moisture index in reach catchment
cmi_ix_c07 July climate moisture index in reach catchment
cmi_ix_c08 August climate moisture index in reach catchment
cmi_ix_c09 September climate moisture index in reach catchment
cmi_ix_c10 October climate moisture index in reach catchment
cmi_ix_c11 November climate moisture index in reach catchment
cmi_ix_c12 December climate moisture index in reach catchment
snw_pc_cyr Annual average snow cover extent in reach catchment
snw_pc_uyr Annual average snow cover extent in total watershed upstream
snw_pc_cmx Annual maximum snow cover extent in reach catchment
snw_pc_c01 January snow cover extent in reach catchment
snw_pc_c02 Fevruary snow cover extent in reach catchment
snw_pc_c03 March snow cover extent in reach catchment
snw_pc_c04 April snow cover extent in reach catchment
snw_pc_c05 May snow cover extent in reach catchment
snw_pc_c06 June snow cover extent in reach catchment
snw_pc_c07 July snow cover extent in reach catchment
snw_pc_c08 August snow cover extent in reach catchment
snw_pc_c09 September snow cover extent in reach catchment
snw_pc_c10 October snow cover extent in reach catchment
snw_pc_c11 November snow cover extent in reach catchment
snw_pc_c12 December snow cover extent in reach catchment
glc_cl_cmj Spatial majority of land cover class in reach catchment
glc_pc_c01 Land Cover Extent class 1 in reach catchment
glc_pc_c02 Land Cover Extent class 2 in reach catchment
glc_pc_c03 Land Cover Extent class 3 in reach catchment
glc_pc_c04 Land Cover Extent class 4 in reach catchment
glc_pc_c05 Land Cover Extent class 5 in reach catchment
glc_pc_c06 Land Cover Extent class 6 in reach catchment
glc_pc_c07 Land Cover Extent class 7 in reach catchment
glc_pc_c08 Land Cover Extent class 8 in reach catchment
glc_pc_c09 Land Cover Extent class 9 in reach catchment
glc_pc_c10 Land Cover Extent class 10 in reach catchment
glc_pc_c11 Land Cover Extent class 11 in reach catchment
glc_pc_c12 Land Cover Extent class 12 in reach catchment
glc_pc_c13 Land Cover Extent class 13 in reach catchment
glc_pc_c14 Land Cover Extent class 14 in reach catchment
glc_pc_c15 Land Cover Extent class 15 in reach catchment
glc_pc_c16 Land Cover Extent class 16 in reach catchment
glc_pc_c17 Land Cover Extent class 17 in reach catchment
glc_pc_c18 Land Cover Extent class 18 in reach catchment
glc_pc_c19 Land Cover Extent class 19 in reach catchment
glc_pc_c20 Land Cover Extent class 20 in reach catchment
glc_pc_c21 Land Cover Extent class 21 in reach catchment
glc_pc_c22 Land Cover Extent class 22 in reach catchment
glc_pc_u01 Land Cover Extent class 1 in total watershed upstream
glc_pc_u02 Land Cover Extent class 2 in total watershed upstream
glc_pc_u03 Land Cover Extent class 3 in total watershed upstream
glc_pc_u04 Land Cover Extent class 4 in total watershed upstream
glc_pc_u05 Land Cover Extent class 5 in total watershed upstream
glc_pc_u06 Land Cover Extent class 6 in total watershed upstream
glc_pc_u07 Land Cover Extent class 7 in total watershed upstream
glc_pc_u08 Land Cover Extent class 8 in total watershed upstream
glc_pc_u09 Land Cover Extent class 9 in total watershed upstream
glc_pc_u10 Land Cover Extent class 10 in total watershed upstream
glc_pc_u11 Land Cover Extent class 11 in total watershed upstream
glc_pc_u12 Land Cover Extent class 12 in total watershed upstream
glc_pc_u13 Land Cover Extent class 13 in total watershed upstream
glc_pc_u14 Land Cover Extent class 14 in total watershed upstream
glc_pc_u15 Land Cover Extent class 15 in total watershed upstream
glc_pc_u16 Land Cover Extent class 16 in total watershed upstream
glc_pc_u17 Land Cover Extent class 17 in total watershed upstream
glc_pc_u18 Land Cover Extent class 18 in total watershed upstream
glc_pc_u19 Land Cover Extent class 19 in total watershed upstream
glc_pc_u20 Land Cover Extent class 20 in total watershed upstream
glc_pc_u21 Land Cover Extent class 21 in total watershed upstream
glc_pc_u22 Land Cover Extent class 22 in total watershed upstream
pnv_cl_cmj Spatial majority of potential natural vegetation classes in reach catchment
pnv_pc_c01 Potential natural vegetation extent class 1 in reach catchment
pnv_pc_c02 Potential natural vegetation extent class 2 in reach catchment
pnv_pc_c03 Potential natural vegetation extent class 3 in reach catchment
pnv_pc_c04 Potential natural vegetation extent class 4 in reach catchment
pnv_pc_c05 Potential natural vegetation extent class 5 in reach catchment
pnv_pc_c06 Potential natural vegetation extent class 6 in reach catchment
pnv_pc_c07 Potential natural vegetation extent class 7 in reach catchment
pnv_pc_c08 Potential natural vegetation extent class 8 in reach catchment
pnv_pc_c09 Potential natural vegetation extent class 9 in reach catchment
pnv_pc_c10 Potential natural vegetation extent class 10 in reach catchment
pnv_pc_c11 Potential natural vegetation extent class 11 in reach catchment
pnv_pc_c12 Potential natural vegetation extent class 12 in reach catchment
pnv_pc_c13 Potential natural vegetation extent class 13 in reach catchment
pnv_pc_c14 Potential natural vegetation extent class 14 in reach catchment
pnv_pc_c15 Potential natural vegetation extent class 15 in reach catchment
pnv_pc_u01 Potential natural vegetation extent class 1 in total watershed upstream
pnv_pc_u02 Potential natural vegetation extent class 2 in total watershed upstream
pnv_pc_u03 Potential natural vegetation extent class 3 in total watershed upstream
pnv_pc_u04 Potential natural vegetation extent class 4 in total watershed upstream
pnv_pc_u05 Potential natural vegetation extent class 5 in total watershed upstream
pnv_pc_u06 Potential natural vegetation extent class 6 in total watershed upstream
pnv_pc_u07 Potential natural vegetation extent class 7 in total watershed upstream
pnv_pc_u08 Potential natural vegetation extent class 8 in total watershed upstream
pnv_pc_u09 Potential natural vegetation extent class 9 in total watershed upstream
pnv_pc_u10 Potential natural vegetation extent class 10 in total watershed upstream
pnv_pc_u11 Potential natural vegetation extent class 11 in total watershed upstream
pnv_pc_u12 Potential natural vegetation extent class 12 in total watershed upstream
pnv_pc_u13 Potential natural vegetation extent class 13 in total watershed upstream
pnv_pc_u14 Potential natural vegetation extent class 14 in total watershed upstream
pnv_pc_u15 Potential natural vegetation extent class 15 in total watershed upstream
wet_cl_cmj Spatial majority wetland class in reach catchment
wet_pc_cg1 Wetland extent class group 1 in reach catchment
wet_pc_ug1 Wetland extent class group 1 in total watershed upstream
wet_pc_cg2 Wetland extent class group 2 in reach catchment
wet_pc_ug2 Wetland extent class group 2 in total watershed upstream
wet_pc_c01 Wetland extent class 1 in reach catchment
wet_pc_c02 Wetland extent class 2 in reach catchment
wet_pc_c03 Wetland extent class 3 in reach catchment
wet_pc_c04 Wetland extent class 4 in reach catchment
wet_pc_c05 Wetland extent class 5 in reach catchment
wet_pc_c06 Wetland extent class 6 in reach catchment
wet_pc_c07 Wetland extent class 7 in reach catchment
wet_pc_c08 Wetland extent class 8 in reach catchment
wet_pc_c09 Wetland extent class 9 in reach catchment
wet_pc_u01 Wetland extent class 1 in total watershed upstream
wet_pc_u02 Wetland extent class 2 in total watershed upstream
wet_pc_u03 Wetland extent class 3 in total watershed upstream
wet_pc_u04 Wetland extent class 4 in total watershed upstream
wet_pc_u05 Wetland extent class 5 in total watershed upstream
wet_pc_u06 Wetland extent class 6 in total watershed upstream
wet_pc_u07 Wetland extent class 7 in total watershed upstream
wet_pc_u08 Wetland extent class 8 in total watershed upstream
wet_pc_u09 Wetland extent class 9 in total watershed upstream
for_pc_cse Spatial extent (%) of forest cover in reach catchment
for_pc_use Spatial extent (%) of forest cover in total watershed upstream
crp_pc_cse Spatial extent (%) of cropland in reach catchment
crp_pc_use Spatial extent (%) of cropland in total watershed upstream
pst_pc_cse Spatial extent (%) of pasture in reach catchment
pst_pc_use Spatial extent (%) of pasture in total watershed upstream
ire_pc_cse Spatial extent (%) of irrigated area in reach catchment
ire_pc_use Spatial extent (%) of irrigated area in total watershed upstream
gla_pc_cse Spatial extent (%) of glacier in reach catchment
gla_pc_use Spatial extent (%) of glacier in total watershed upstream
prm_pc_cse Spatial extent (%) of permafrost in reach catchment
prm_pc_use Spatial extent (%) of permafrost in total watershed upstream
pac_pc_cse Spatial extent (%) of protected area in reach catchment
pac_pc_use Spatial extent (%) of protected area in total watershed upstream
tbi_cl_cmj Spatial majority of terrestrial biomes in reach catchment
tec_cl_cmj Spatial majority of terrestrial ecoregions in reach catchment
fmh_cl_cmj Spatial majority of freshwater major habitat types in reach catchment
fec_cl_cmj Spatial majority of freshwater ecoregions in reach catchment
cly_pc_cav Average clay fraction in soil in reach catchment
cly_pc_uav Average clay fraction in soil in total watershed upstream
slt_pc_cav Average silt fraction in soil in reach catchment
slt_pc_uav Average silt fraction in soil in total watershed upstream
snd_pc_cav Average sand fraction in soil in reach catchment
snd_pc_uav Average sand fraction in soil in total watershed upstream
soc_th_cav Average organic carbon content in soil in reach catchment
soc_th_uav Average organic carbon content fraction in soil in total watershed upstream
swc_pc_cyr Annual average soil water content in reach catchment
swc_pc_uyr Annual average soil water content in total watershed upstream
swc_pc_c01 January soil water content in reach catchment
swc_pc_c02 February soil water content in reach catchment
swc_pc_c03 March soil water content in reach catchment
swc_pc_c04 April soil water content in reach catchment
swc_pc_c05 May soil water content in reach catchment
swc_pc_c06 June soil water content in reach catchment
swc_pc_c07 July soil water content in reach catchment
swc_pc_c08 August soil water content in reach catchment
swc_pc_c09 September soil water content in reach catchment
swc_pc_c10 October soil water content in reach catchment
swc_pc_c11 November soil water content in reach catchment
swc_pc_c12 December soil water content in reach catchment
lit_cl_cmj Spatial majority of lithological classes in reach catchment
kar_pc_cse Spatial extent (%) of Karst area in reach catchment
kar_pc_use Spatial extent (%) of Karst area in total watershed upstream
ero_kh_cav Average soil erosion in reach catchment
ero_kh_uav Average soil erosion in total watershed upstream
pop_ct_csu Population count in reach catchment
pop_ct_usu Population count in total watershed upstream
ppd_pk_cav Average population density in reach catchment
ppd_pk_uav Average population density in total watershed upstream
urb_pc_cse Spatial extent (%) of urban area in reach catchment
urb_pc_use Spatial extent (%) of urban area in total watershed upstream
nli_ix_cav Average nighttime lights in reach catchment
nli_ix_uav Average nighttime lights in total watershed upstream
rdd_mk_cav Average road density in reach catchment
rdd_mk_uav Average road density in total watershed upstream
hft_ix_c93 Human foorprint in reach catchment in 1993
hft_ix_u93 Human footprint in total watershed upstream in 1993
hft_ix_c09 Human foorprint in reach catchment in 2009
hft_ix_u09 Human footprint in total watershed upstream in 2009
gad_id_cmj Spatial majority of global administrative area in reach catchment
gdp_ud_cav Average gross domestic product in reach catchment
gdp_ud_csu Sum of gross domestic product in reach catchment
gdp_ud_usu Sum of gross domestic product in total watershed upstream
hdi_ix_cav Average human development index in reach catchment
shape_length NA
geom NA

Importing HydroSHEDS

The first thing is to download and process the hydrological network from the HydroSHEDS website. To do so we created 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 cleanschema 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 has been used to import a country layer and ICES divisions. ICES divisions are downloaded from the ICES website.

Inserting data

Then we have to insert the downloaded data to the database. To do so we used 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 downloaded we chose 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)

We decided to extract European hydrological data 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 refined 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 ensured that these smaller catchments were spatially contained within the large catchments using ST_Within, saving them in tempo.hydro_small_catchments_europe, and added a GIST index on geometries to improve spatial query efficiency. Finally, we selected river segments from riveratlas.riveratlas_v10 that intersected with the chosen small catchments using ST_Intersects, storing the results in tempo.hydro_riversegments_europe.

Code to split data into smaller entities
-- 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 has been used to select catchemnts and river segments in the Southern Mediterranean area.

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

Step 2 : Selecting the most downstream riversegment for each reach

Next we needed to extract the most downstream river segments from tempo.hydro_riversegments_europe. To achieve this, we filtered 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 were then stored in tempo.riveratlas_mds.

Code to select the most downstream river segments
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, was 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 was correctly identified. The table was then updated by assigning the extracted downstream point to the corresponding hyriv_id. Finally, a GIST index on downstream_point was created to improve the efficiency of spatial queries.

Code to create the most downstream point
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 has been 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 decided to keep the 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, was 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 was 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 were included. The same method is used for the whole Baltic area.

Code to retrieve most downstream points for 27, 28, 29 & 32 ICES areas
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 have been used to group catchments and river segments together.

Figure 6: Map of ICES fishing ecoregions.

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

First, we retrieved distinct downstream points that were 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 had to be within 0.02 degrees of the geometries in tempo.ne_10m_admin_0_countries, ensuring they were located in Norway or Sweden.

Similarly, we selected downstream points that were within 0.04 degrees of the geometries in ices_areas.ices_areas_20160601_cut_dense_3857, specifically for subdivisio = '23', while also ensuring they were 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 combined both sets of points while applying an exclusion condition: any points from the ICES area selection that already existed in the ICES ecoregion selection were removed, ensuring no duplicates while maintaining priority for the ecoregion-based selection. The resulting dataset tempo.ices_ecoregions_nsea_north contained the most downstream points filtered by ICES ecoregions, ICES areas, and country boundaries for Norway and Sweden.

Code to retrieve most downstream points for the Northern part of the North Sea ICES ecoregion
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 : Redo the intersection using a larger buffer to retrieve missing points

Baltic

We selected the most downstream points from tempo.riveratlas_mds that were 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 compiled 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 identified the missing points by filtering out any downstream points from our selection that matched an already excluded point. This ensured that only new and unique points were retained.
Finally, we inserted these missing points into tempo.ices_areas_3229_27.

Code to retrieve missing most downstream points for 27, 28, 29 & 32 ICES areas using a larger buffer
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 selected distinct downstream points from tempo.riveratlas_mds using two spatial filters. The first selection included 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 were 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 were merged to form the set of filtered points.

To prevent duplication, we compiled 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 identified missing points by removing any downstream points from our selection that matched an already excluded point. This ensured that only new and unique points were retained.

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

Code to retrieve most downstream points for the Northern part of the North Sea ICES ecoregion with a larger buffer
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 was created to store the selected river segments. Within this schema, we created the riversegments table by selecting distinct river segments from tempo.hydro_riversegments_europe that matched the main_riv values found in tempo.ices_areas_3229_27. This ensured that only river segments associated with the relevant ICES subdivisions were included.

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

Code to retrieve all river segments corresponding to the selected area
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 created 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 excluded 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 added 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.

Code to retrieve all catchments corresponding to previously selected river segments
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 constructed 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 created 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 identified endorheic basins from basinatlas.basinatlas_v10_lev12 that intersected with tempo.oneendo_bisciber. To ensure only relevant basins were selected, we excluded 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 belongs to another area. The remaining filtered basins were then inserted into h_biscay_iberian.catchments.

Finally, we populated 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 excluded any river segments that already existed in h_biscay_iberian.riversegments.

Code to retrieve missing endorheic basins
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 catchemnts on the Iberian peninsula with missing endorheic basins.
Figure 11: Map of the concave hull on the Iberian peninsula.
Figure 12: Map of catchemnts 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 identified the last set of catchments by selecting distinct small catchments from tempo.hydro_small_catchments_europe that intersected with ices_areas.ices_areas_20160601_cut_dense_3857, specifically within subdivisions 32, 29, 28, and 27.

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

Code to retrieve all missing islands and coastal 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 catchemnts on the Baltic with missing islands basins.
Figure 14: Map of catchemnts on the Baltic without missing islands basins.
Figure 15: Final map of catchments from HydroSHEDS split into ICES areas and ecoregions.

Creating a hierarchical structure for WGBAST

We decided to add a hierarchical structure into the database. It will be handled through referential tables. Several levels will be layed out (Figure 16) depending on the needs of each working group.

clusterZ Stock clusterA Inland Stock clusterB Assessment_unit clusterC River clusterD River_section clusterE Country clusterF Marine Stock clusterG Division clusterH Subdivision
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 refnas.

In this document I will describe how the hierarchical structure has been built in the Baltic for WGBAST. All the work is done in a table called refbast.tr_area_are that references to the ref.tr_area_are table.

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 (Figure 21) as well as all ICES subdivisions present in the Baltic (Figure 18), thus this layer is in fact created after the two lower levels.

We use this query to generate 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.

Code to create the global stock level in the Baltic
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;
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 Baltic Stock WGBAST NA
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 18)

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.

Code to create the marine stock level in the Baltic
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');
Structure of the marine Stock level in the referential table
are_id are_are_id are_code are_lev_code are_wkg_code are_ismarine
1 NA Baltic Stock WGBAST NA
2 1 Baltic marine Stock WGBAST TRUE
Figure 18: Map of the marine stock level.

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 18).

Code to create the 27.3.b, c ICES division level in the Baltic
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;
Structure of the ICES division level in the referential table
are_id are_are_id are_code are_lev_code are_wkg_code are_ismarine
1 NA Baltic Stock WGBAST NA
2 1 Baltic marine Stock WGBAST TRUE
7003 2 27.3.b, c Division WGBAST TRUE
7004 2 27.3.d Division WGBAST TRUE
Figure 19: Map of the ICES division level.

ICES subdivision

The same method is applied here to create the subdivision level. However, this time the logic is encapsulated in a SQL function, allowing for more flexibility and reusability. 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.

Code to create a function to add all ICES subdivisions level in the Baltic
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. 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.

Code to create the inland stock level in the Baltic
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');
Structure of the inland Stock level in the referential table
are_id are_are_id are_code are_lev_code are_wkg_code are_ismarine
1 NA Baltic Stock WGBAST NA
3 1 Baltic inland Stock WGBAST FALSE
Figure 21: Map of the inland stock level.

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 talbe. 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 21).

Code to create the country level in the Baltic
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 22: 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 23) 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.

Figure 23: Map of WGBAST assessment units.

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 21).

Code to create the WGBAST assessment unit 1 level in the Baltic
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 24: Map of WGBAST assessment units level.

River

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

This 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 24) 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.

Code to create a function to retrieve main rivers within an 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 25: Map of reaches composing WGBAST first assessment units level.

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.

Code to create a function to retrieve main stretch of river section within each reach
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 26: 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 27).

clusterZ Stock clusterA Inland Stock clusterB Assessment_unit clusterC River clusterD River_section clusterE Country clusterF Marine Stock clusterG Subarea clusterH Division clusterI Assessment_unit clusterJ Fisheries
Figure 27: Hierarchical structure of WGNAS habitat database

This structure will be 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 28). It includes all catchments that have a river flowing into areas studied by WGNAS (Figure 34) as well as all ICES Subareas corresponding to said areas of interest (Figure 29).

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.

Code to create the global stock level for WGNAS
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;
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 NEAC Stock WGNAS NA
Figure 28: 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).

Code to create the marine stock level for WGNAS
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 29: Map of the full 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.

Code to create the ICES subarea level for WGNAS
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 30: Map of the full stock 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).

Code to create the ICES Division level for WGNAS
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 31: Map of the full stock level.

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).

Code to create the assessment unit 1 level for WGNAS
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 32: Map of the 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.

Code to create the fisheries GLD level for WGNAS
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 33: 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 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.

Code to create the inland stock level for WGNAS
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 34: Map of the inland stock level.

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.

Code to create the inland assessment unit level for WGNAS
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;
cou_*** 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 35: 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.

Code to create the River level for WGNAS
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 36: 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.

Code to create the river_section level for WGNAS
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 37: Map of the river section level.

Country

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

Figure 38: Map of the country level.

EU-Hydro

Data description

EU-Hydro is a pan-European hydrographic dataset developed under the Copernicus Land Monitoring Service. It provides a detailed and consistent interpretation of surface water bodies and river networks across 39 European Environment Agency (EEA) countries. Based on high-resolution remote sensing imagery from 2006, 2009, and 2012, EU-Hydro includes a photo-interpreted river network, inland water bodies, canals, and ditches. From version 1.2, it integrates river segments derived from the EU Digital Elevation Model (EU-DEM), improving the completeness and accuracy of the dataset. It serves as a key resource for environmental monitoring, water resource management, and hydrological studies at the European level.

Description of river segments 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 = 'e_gota'
  AND cols.table_name = 'euhydro_gota_v013 — River_Net_l';")

knitr::kable(data_description) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))
Variables description
var_name description
OBJECTID Non-unique identifier for the segment
geom Geometry
DFDD DGIWG Feature Data Dictionary
RN_I_ID Connector between line and area data if any overlapping polygon feature exists
REX Defines the country a hydrological feature is located in
HYP Degree of persistence of water 1 (perennial) to 4 (dry)
LOC Vertical position of a hydrological feature relative to the ground surface. Below (40), on (44) or above (45) surface
FUN The state of planning, construction, repair, and or maintenance of the structures and / or equipment comprising a facility and/or located at a site, as a whole.
NVS Navigability information
LENGTH Length of the corresponding line segment in km
TR Segment ID transferred from the attribute TR in ECRINS if possible, NA otherwise
LONGPATH Longest path from a mouth, which is not capturing all rivers from the same mouth
CUM_LEN Cumulated length of all segments upstream in m
PENTE Slope computed from the EU DEM according to the formula “highest z-value minus lowest z-value divided by the length of the line”
CGNELIN Warning: Incomplete, not updated
BEGLIFEVER Specifies the date and time at which this version of the spatial object was inserted into the data set
ENDLIFEVER Specifies the date and time at which this version of the spatial object was superseded
UPDAT_BY ID of the updater
UPDAT_WHEN Date of update
ERM_ID National Hydrological Identification Code (NHI), transferred from ERM
MC Watercourse Channel Type. Braided (0) or normal (1)
MONOT_Z 0: z-values of the feature do not decrease monotonically along the flow direction 1: z-values of the feature decrease monotonically along the flow direction
LENGTH_GEO Feature length (geodetic) in meters. In contrast to the internally managed SHAPE_LENGTH attribute, the value of thisattribute is not influenced by the projection setting
INSPIRE_ID External identifier of the spatial object
thematicId European WFD code. Transferred from the WISE geospatial dataset
OBJECT_ID Unique identifier for the segment
TNODE Downstream node OBJECT_ID
STRAHLER Strahler level of the segment
nameTxtInt English version of the name of the geographical feature or spatial object, extracted from WISE geospatial dataset
nameText Name in a national language, extracted from WISE geospatial dataset
NEXTUPID OBJECT_ID of the previous line object (Canals, River or Ditches) in the upstream direction
NEXTDOWNID OBJECT_ID of the previous line object (Canals, River or Ditches) in the downstream direction
FNODE Upstream node OBJECT_ID
CatchID Number indicating a Catchment ID
Shape_Length Feature length in meters in case of line geometries
PFAFSTETTER Pfafstetter indicator

We will also be using a Nodes layer that contains informations on the source, the outlet and the branching tributaries of rivers.

Importing EU-Hydro

To be decided. We cannot make it work at the moment

Building the database

The EU-Hydro data is split into several schemas following main basins. River segments, canals, ditches and inland waters are divided into several tables. The first work will be done on river segments. Other data will be added later on.

Figure 39: Map of river network from EU-Hydro split into basins.

The river network will be devided following the same idea as for HydroSHEDS’. ICES Areas and Ecoregions will be used as a base layer to create different schemas.

Step 1 : Restructuring and fixing issues

To be able to properly integrate data to our database, some changes have been made on the data. To make those changes, functions have been created.

Firstly, the SRID and geometry had to be switched from 3035 to 4326 and from 3D to 2D (the z-dimension of the geometry being always equals to 0).

Code to create a function that changes srid and geometry
DROP FUNCTION IF EXISTS altergeometry(basin TEXT);
CREATE OR REPLACE FUNCTION altergeometry(basin TEXT)
RETURNS TABLE (table_name TEXT, srid INTEGER) AS
$$
DECLARE 
    schema_name TEXT := quote_ident('e_' || basin);
    river_table TEXT := quote_ident('euhydro_' || basin || '_v013 — River_Net_l');
    nodes_table TEXT := quote_ident('euhydro_' || basin || '_v013 — Nodes');
    sql_query TEXT;
BEGIN 
    sql_query := 
        'ALTER TABLE ' || schema_name || '.' || river_table || 
        ' ALTER COLUMN geom TYPE geometry(MultiLineString, 4326) 
          USING ST_Transform(ST_Force2D(geom), 4326);';
    EXECUTE sql_query;
    sql_query := 
        'ALTER TABLE ' || schema_name || '.' || nodes_table || 
        ' ALTER COLUMN geom TYPE geometry(Point, 4326) 
          USING ST_Transform(ST_Force2D(geom), 4326);';
    EXECUTE sql_query;

    RETURN QUERY EXECUTE 
        'SELECT ' || quote_literal(river_table) || ' AS table_name, srid 
         FROM (SELECT DISTINCT ST_SRID(geom) AS srid FROM ' || schema_name || '.' || river_table || ') sub
         UNION ALL 
         SELECT ' || quote_literal(nodes_table) || ' AS table_name, srid 
         FROM (SELECT DISTINCT ST_SRID(geom) AS srid FROM ' || schema_name || '.' || nodes_table || ') sub;';
END;
$$ LANGUAGE plpgsql;

Then, a function to restructure the column order of the river segment tables was created to harmonise tables’ structure between different basins.

Code to restructure columns order
DROP FUNCTION IF EXISTS restructurecolumns(basin TEXT);
CREATE OR REPLACE FUNCTION restructurecolumns(basin TEXT)
RETURNS VOID AS $$
DECLARE 
    schema_name TEXT := quote_ident('e_' || basin);
    source_table TEXT := quote_ident('euhydro_' || basin || '_v013 — River_Net_l');
    target_table TEXT := quote_ident('riverseg');
    sql_query TEXT;
BEGIN
    sql_query := 
        'CREATE TABLE ' || schema_name || '.' || target_table || ' AS SELECT ' ||
        '"OBJECTID",
         geom,
         "DFDD",
         "RN_I_ID",
         "REX",
         "HYP",
         "LOC",
         "FUN",
         "NVS",
         "LENGTH",
         "TR",
         "LONGPATH",
         "CUM_LEN",
         "PENTE",
         "CGNELIN",
         "BEGLIFEVER",
         "ENDLIFEVER",
         "UPDAT_BY",
         "UPDAT_WHEN",
         "ERM_ID",
         "MC",
         "MONOT_Z",
         "LENGTH_GEO",
         "INSPIRE_ID",
         "thematicId",
         "OBJECT_ID",
         "TNODE",
         "STRAHLER",
         "nameTxtInt",
         "nameText",
         "NEXTUPID",
         "NEXTDOWNID",
         "FNODE",
         "CatchID",
         "Shape_Length",
         "PFAFSTETTER" ' ||
        ' FROM ' || schema_name || '.' || source_table;

    EXECUTE sql_query;
END;
$$ LANGUAGE plpgsql;

Finally, indexes and unicity constraints are added to variables that will be used to divide the dataset as well as river segments ids.

Code to retrieve all missing islands and coastal catchments
DROP FUNCTION IF EXISTS create_indexes_and_constraint(basin TEXT);
CREATE OR REPLACE FUNCTION create_indexes_and_constraint(basin TEXT)
RETURNS VOID AS
$$
DECLARE 
    schema_name TEXT := quote_ident('e_' || basin);
    river_table TEXT := quote_ident('euhydro_' || basin || '_v013 — River_Net_l');
    sql_query TEXT;
BEGIN 
    sql_query := 
        'CREATE INDEX IF NOT EXISTS idx_t_node_' || basin || '_riv 
         ON ' || schema_name || '.' || river_table || ' USING btree("TNODE");';
    EXECUTE sql_query;
    sql_query := 
        'CREATE INDEX IF NOT EXISTS idx_next_did_' || basin || '_riv 
         ON ' || schema_name || '.' || river_table || ' USING btree("NEXTDOWNID");';
    EXECUTE sql_query;
    sql_query := 
        'CREATE INDEX IF NOT EXISTS idx_obj_id_' || basin || ' 
         ON ' || schema_name || '.' || river_table || ' USING btree("OBJECT_ID");';
    EXECUTE sql_query;
    sql_query := 
        'ALTER TABLE ' || schema_name || '.' || river_table || ' 
         ADD CONSTRAINT c_uk_object_id_' || basin || ' UNIQUE("OBJECT_ID");';
    EXECUTE sql_query;
END;
$$ LANGUAGE plpgsql;

Step 2: Selecting outlets

We create the table tempo.selected_tnodes_balt_3031 to identify river outlet nodes near ICES marine subdivisions 31 and 30 in the Baltic region. First, in the select_outlet CTE, we extract nodes where "HYDRONODCT" = 'Outlet' from the Neva, Angerman, Kemi, and Gota river networks. Then, in ices_nodes, we filter these outlet nodes by checking if they are within ST_DWithin(sr.geom, ia.geom, 0.04) of ICES marine areas. Finally, in select_riv, we retrieve river segments from "euhydro_*_v013 — River_Net_l" tables that are connected to the selected outlet nodes via "TNODE" = ir."OBJECT_ID", ensuring only distinct entries are included in the final table.

Code to retrieve river segments outlets for basins within range of ICES subdivisions 30 and 31
DROP TABLE IF EXISTS tempo.selected_tnodes_balt_3031;
CREATE TABLE tempo.selected_tnodes_balt_3031 AS
WITH select_outlet AS (
    SELECT * FROM e_neva."euhydro_neva_v013 — Nodes" WHERE "HYDRONODCT" = 'Outlet'
    UNION ALL
    SELECT * FROM e_angerman."euhydro_angerman_v013 — Nodes" WHERE "HYDRONODCT" = 'Outlet'
    UNION ALL
    SELECT * FROM e_kemi."euhydro_kemi_v013 — Nodes" WHERE "HYDRONODCT" = 'Outlet'
    UNION ALL
    SELECT * FROM e_gota."euhydro_gota_v013 — Nodes" WHERE "HYDRONODCT" = 'Outlet'
),
ices_nodes AS (
    SELECT sr.*
    FROM select_outlet sr
    JOIN ices_areas.ices_areas_20160601_cut_dense_3857 AS ia
    ON ST_DWithin(sr.geom, ia.geom, 0.04)
    WHERE ia.subdivisio = ANY(ARRAY['31','30'])
),
select_riv AS (
    SELECT enr.*
    FROM e_neva."euhydro_neva_v013 — River_Net_l" enr
    JOIN ices_nodes ir ON enr."TNODE" = ir."OBJECT_ID"
    UNION ALL
    SELECT enr.*
    FROM e_angerman."euhydro_angerman_v013 — River_Net_l" enr
    JOIN ices_nodes ir ON enr."TNODE" = ir."OBJECT_ID"
    UNION ALL
    SELECT enr.*
    FROM e_kemi."euhydro_kemi_v013 — River_Net_l" enr
    JOIN ices_nodes ir ON enr."TNODE" = ir."OBJECT_ID"
    UNION ALL
    SELECT enr.*
    FROM e_gota."euhydro_gota_v013 — River_Net_l" enr
    JOIN ices_nodes ir ON enr."TNODE" = ir."OBJECT_ID"
)
SELECT DISTINCT ON ("OBJECT_ID") "OBJECT_ID" AS seaoutlet, select_riv.* FROM select_riv;

Step 3: Merging basins

To facilitate the next query, all basins touching the selected ICES areas have been merged into a temporary table. While doing to the name of the basin is added to the table.

Code to merge all riversegments into one table
DROP TABLE IF EXISTS tempo.e_balt_3031;
CREATE TABLE tempo.e_balt_3031 AS(
    SELECT *, 'neva' AS basin FROM e_neva."euhydro_neva_v013 — River_Net_l"
        UNION ALL
        SELECT *, 'angerman' AS basin FROM e_angerman."euhydro_angerman_v013 — River_Net_l"
        UNION ALL
        SELECT *, 'kemi' AS basin FROM e_kemi."euhydro_kemi_v013 — River_Net_l"
        UNION ALL
        SELECT *, 'gota' AS basin FROM e_gota."euhydro_gota_v013 — River_Net_l"
);
Figure 40: Map of river network from EU-Hydro and selected outlets in the Baltic for ICES subdivisions 30 & 31.

Step 4: Retrieving river segments

To facilitate the workflow we created a function to retrieve all river segments linked to the outlet segments. This function also modify all variables names to harmonize them with the rest of the database.

We create the function makesegments(schema text, outlet_table text, segment_table text) to recursively trace river segments upstream from specified outlet nodes. In the returned variables we decided to keep the "OBJECT_ID" of each outlet’s river segment as seaoutlet. Inside the function, we define the recursive CTE river_tree, which starts by selecting river segments where "TNODE" matches "TNODE" from the outlet table. It then iteratively joins upstream segments using "NEXTDOWNID", tracking depth and preventing cycles using is_cycle and path. Finally, we return all river segments along with their associated seaoutlet.

Code to retrieve all river segments linked to preselected outlets
DROP FUNCTION IF EXISTS makesegments(schema text, outlet_table text, segment_table text);
CREATE FUNCTION makesegments(schema text, outlet_table text, segment_table text)
RETURNS TABLE(
    objectid bigint,
    geom public.geometry,
    dfdd character varying(5),
    rn_i_id character varying(256),
    rex character varying(256),
    hyp integer,
    loc integer,
    fun integer,
    nvs integer,
    length double precision,
    tr character varying(10),
    longpath double precision,
    cum_len double precision,
    pente double precision,
    cgnelin integer,
    beglifever timestamp without time zone,
    endlifever timestamp without time zone,
    updat_by character varying(15),
    updat_when timestamp without time zone,
    erm_id character varying(256),
    mc integer,
    monot_z integer,
    length_geo double precision,
    inspire_id character varying(256),
    thematicid character varying(42),
    object_id character varying(255),
    tnode character varying(255),
    strahler double precision,
    nametxtint character varying(254),
    nametext character varying(254),
    nextupid character varying(255),
    nextdownid character varying(255),
    fnode character varying(255),
    catchid integer,
    shape_length double precision,
    pfafstetter character varying(255),
    basin text,
    seaoutlet character varying(255)
) 
AS
$$
DECLARE 
    schema TEXT := quote_ident(schema::text);
    seg_table TEXT := quote_ident(segment_table::text);
    out_table TEXT := quote_ident(outlet_table::text);
    sql_query TEXT;
BEGIN 
    sql_query := 
    'WITH RECURSIVE river_tree ("OBJECTID", "NEXTDOWNID", "OBJECT_ID", seaoutlet, basin, depth, is_cycle, path) AS (
        SELECT enr."OBJECTID", enr."NEXTDOWNID", enr."OBJECT_ID", ttn.seaoutlet, enr.basin, 0, FALSE, 
            ARRAY[enr."OBJECT_ID"]::varchar[]
        FROM ' ||schema||'.'|| seg_table || ' enr
        JOIN ' ||schema||'.'|| out_table || ' ttn ON enr."TNODE" = ttn."TNODE"
        UNION ALL
        SELECT enr."OBJECTID", enr."NEXTDOWNID", enr."OBJECT_ID", rt.seaoutlet, rt.basin, rt.depth+1,
            enr."OBJECT_ID" = ANY(path),
            path || ARRAY[rt."OBJECT_ID"]
        FROM ' ||schema||'.'|| seg_table || ' enr
        JOIN river_tree rt ON enr."NEXTDOWNID" = rt."OBJECT_ID" AND NOT is_cycle
    )
    SELECT en.*, river_tree.seaoutlet
    FROM ' ||schema||'.'|| seg_table || ' en
    JOIN river_tree ON (en."OBJECTID", en.basin) = (river_tree."OBJECTID", river_tree.basin)';

    RETURN QUERY EXECUTE sql_query;
END
$$ LANGUAGE plpgsql;
Figure 41: Map of river network from EU-Hydro for the Baltic ICES subdivisions 30 & 31.

The same method is applied for all basins using either ICES subdivision or ICES ecoregions.

WIP - Issues with some basins to fix Map of river network from EU-Hydro split into ICES areas.

Acknowlegment

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

EU-Hydro : COPERNICUS Land Monitoring Service, 2019: EU-Hydro. Last access: 24/03/2025 https://land.copernicus.eu/imagery-in-situ/eu-hydro

 

EU is not reponsible for the content of the project