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.

A view of all European and Northern African catchments — 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.

A view of the Baltic with the most downstream river segments highlighted — 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.

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.

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

A view of the Northern Baltic with the most downstream points highlighted — 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.

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

A view of the Northern Sea with the most downstream points highlighted — 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);

A view of the Baltic with selected river segments for 27, 29-32 ICES areas — 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);

A view of the Baltic with selected catchments for 27, 29-32 ICES areas — 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

A view of the Iberian peninsula with missing endorheic basins — Figure 10: Map of catchemnts on the Iberian peninsula with missing endorheic basins.

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

A view of the Iberian peninsula without missing endorheic basins — 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;

A view of the Baltic with missing islands — Figure 13: Map of catchemnts on the Baltic with missing islands basins.

A view of the Baltic without missing islands — Figure 14: Map of catchemnts on the Baltic without missing islands basins.

A view of Europe and Northern Africa catchments — 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.

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

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

A view of of the Baltic with a polygon surrounding the Baltic Sea, Bothian Sea and Finland Gulf — 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

A view of of the Baltic with polygons surrounding each ICES divisions — 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;

A view of of the Baltic with polygons surrounding each ICES subdivisions — 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

A view of Scandinavia with a polygon surrounding the inland stock hierarchical level — 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');

A view of Scandinavia with polygons surrounding each countries that has a catchment flowing into the Baltic — 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.

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;

A view of the Baltic Sea with polygons surrounding all catchments that belongs to all 6 WGBAST assessment units — 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;

A view of the Bothian bay with polygons surrounding each reach within the first assessment unit — 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;

A view of Scandinavia with lines representing main stretch of rivers that flow into the Baltic — 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).

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

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'
);

A view of of the North Atlantic with two polygons surrounding the marine stock hierarchical level — 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';

A view of the European side of the North Atlantic with polygons surrounding the ICES subarea level — 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';

A view of the European side of the North Atlantic with polygons surrounding the ICES division level — 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;

A view of of the North Atlantic with polygons surrounding the assessment unit level — 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';

A view of of the North Atlantic with three polygons surrounding the fisheries level — 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'
);

A view of Europe with a polygon surrounding the inland stock hierarchical level — 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.

A view with polygons surrounding the assessment units spread across Europe and North America — 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;

A view with polygons surrounding the river level spread across Europe and North America — 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;

A view with lines representing the river sections spread across Europe and North America — 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).

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.

A view of Europe river networks — 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"
);

A view of selected outlets for Baltic subdivisions 30 & 31 — 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;

A view of the Baltic river networks for ICES subdivisions 30 & 31 — 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

--- title: "diaspara habitat database creation script" subtitle: "DIASPARA WP3.1 working document" author: "Oliviéro Jules, Briand Cédric, Helminen Jani" date: last-modified date-format: "DD-MM-YYYY" description: "Design an international database of habitat of migratory fish, version = build" title-block-banner: "images/diaspara_bandeau.png" title-block-banner-color: "white" format: html: code-fold: true code-tools: true self-contained: true theme: styles.scss smooth-scroll: true fontcolor: black toc: true toc-location: left toc-title: Summary toc-depth: 3 execute: keep-md: true crossref: true filters: - include-code-files reference-location: document bibliography: diaspara.bib include-after-body: "footer.html" --- # 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. ```{r init} #| echo: FALSE #| warning: FALSE #| message: FALSE #| results: 'hide' #if (!grepl("montepomi", getwd())) { if(Sys.info()[["user"]] == 'joliviero'){ setwd("D:/workspace/DIASPARA_WP3_habitat/R") datawd <- "D:/DIASPARA/wgbast" } else if (Sys.info()[["user"]] == 'cedric.briand'){ setwd("C:/workspace/DIASPARA_WP3_habitat/R") datawd <- "C:/Users/cedric.briand/OneDrive - EPTB Vilaine/Projets/DIASPARA/wgbast" } source("utilities/load_library.R") load_library("tidyverse") load_library("knitr") load_library("kableExtra") load_library("icesVocab") load_library("readxl") load_library("janitor") load_library("skimr") load_library("RPostgres") load_library("yaml") load_library("DBI") load_library("ggplot2") load_library("sf") load_library("janitor") # clean_names load_library("rnaturalearth") cred <- read_yaml("../credentials.yml") con_diaspara <- dbConnect(Postgres(), dbname = cred$dbnamehydro, host = cred$host, port = cred$port, user = cred$userdiaspara, password = cred$passworddiaspara) con_diaspara_admin <- dbConnect(Postgres(), dbname = cred$dbnamehydro, host = cred$host, port = cred$port, user = cred$usersalmo, password = cred$passwordsalmo) ``` <details> <summary>Descriptions of riversegments variables</summary> ```{r} #| label: riversegment variables #| echo: TRUE #| warning: FALSE #| message: FALSE #| tbl-cap: Variables description 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")) ``` </details> ## 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. <details> <summary>PowerShell code to download HydroSHEDS</summary> ```{.ps1 include="../bash/0_powershell_import_hydroatlas.ps1"} ``` </details> 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. <details> <summary>Code to import data to the database</summary> ```{.ps1 include="../bash/1_osgeo4w_ogr2ogr_gdb_postgres_hydroatlas.ps1"} ``` </details> 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`. ```{sql} #| label: smaller groups #| code-summary: Code to split data into smaller entities #| echo: TRUE #| eval: FALSE #| warning: FALSE #| message: FALSE -- 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. ![Map of all catchemnts present in the database.](images/fig-all_catchments.png "A view of all European and Northern African catchments "){#fig-all_catchments} ### 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`. ```{sql} #| label: most downstream rivers #| code-summary: Code to select the most downstream river segments #| echo: TRUE #| eval: FALSE #| warning: FALSE #| message: FALSE 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. ![Map of most downstream river segments in the Baltic area.](images/fig-mds_baltic.png "A view of the Baltic with the most downstream river segments highlighted"){#fig-mds_baltic} ### 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. ```{sql} #| label: most downstream point #| code-summary: Code to create the most downstream point #| echo: TRUE #| eval: FALSE #| warning: FALSE #| message: FALSE 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. ![Map of most downstream points in the Baltic area.](images/fig-mdp_baltic.png "A view of the Baltic with the most downstream points highlighted"){#fig-mdp_baltic} ### 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. ![Map of ICES fishing areas at subdivision level, source NAFO, FAO, ICES, GFCM.](images/fig-ices_baltic.png "A view of the Baltic with ICES fishing subdivision"){#fig-ices_baltic} 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. ```{sql} #| label: baltic downstream points #| code-summary: Code to retrieve most downstream points for 27, 28, 29 & 32 ICES areas #| eval: FALSE #| echo: TRUE #| warning: FALSE #| message: FALSE 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 ``` ![Map of most downstream points in the Northern Baltic ICES area.](images/fig-mdp_baltic2732.png "A view of the Northern Baltic with the most downstream points highlighted"){#fig-mdp_baltic2732} #### Ecoregions For the rest of the dataset, ICES ecoregions have been used to group catchments and river segments together. ![Map of ICES fishing ecoregions.](images/fig-ices_ecoregions.png "A view of Europe and Northern Africa with ICES fishing ecoregions"){#fig-ices_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. ```{sql} #| label: northern north sea downstream points #| code-summary: Code to retrieve most downstream points for the Northern part of the North Sea ICES ecoregion #| eval: FALSE #| echo: TRUE #| warning: FALSE #| message: FALSE 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 ``` ![Map of most downstream points in the Northern Sea.](images/fig-mdp_ecoregions.png "A view of the Northern Sea with the most downstream points highlighted"){#fig-mdp_ecoregions} ### 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`. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to retrieve missing most downstream points for 27, 28, 29 & 32 ICES areas using a larger buffer #| warning: FALSE #| message: FALSE 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. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to retrieve most downstream points for the Northern part of the North Sea ICES ecoregion with a larger buffer #| warning: FALSE #| message: FALSE 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. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to retrieve all river segments corresponding to the selected area #| warning: FALSE #| message: FALSE 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); ``` ![Map of river segments for 27, 29-32 ICES areas.](images/fig-rs_baltic2732.png "A view of the Baltic with selected river segments for 27, 29-32 ICES areas"){#fig-rs_baltic2732} ### 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. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to retrieve all catchments corresponding to previously selected river segments #| warning: FALSE #| message: FALSE 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); ``` ![Map of catchments for 27, 29-32 ICES areas.](images/fig-catch_baltic2732.png "A view of the Baltic with selected catchments for 27, 29-32 ICES areas"){#fig-catch_baltic2732} ### 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`. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to retrieve missing endorheic basins #| warning: FALSE #| message: FALSE 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 ``` ![Map of catchemnts on the Iberian peninsula with missing endorheic basins.](images/fig-endo_biscay.png "A view of the Iberian peninsula with missing endorheic basins"){#fig-endo_biscay} ![Map of the concave hull on the Iberian peninsula.](images/fig-conc_biscay.png "A view of the concave hull on the Iberian peninsula"){#fig-conc_biscay} ![Map of catchemnts on the Iberian peninsula without missing endorheic basins.](images/fig-fendo_biscay.png "A view of the Iberian peninsula without missing endorheic basins"){#fig-fendo_biscay} ### 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`. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to retrieve all missing islands and coastal catchments #| warning: FALSE #| message: FALSE 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; ``` ![Map of catchemnts on the Baltic with missing islands basins.](images/fig-missing_baltic2732.png "A view of the Baltic with missing islands"){#fig-missing_baltic2732} ![Map of catchemnts on the Baltic without missing islands basins.](images/fig-full_baltic2732.png "A view of the Baltic without missing islands"){#fig-full_baltic2732} ![Final map of catchments from HydroSHEDS split into ICES areas and ecoregions.](images/fig-final.png "A view of Europe and Northern Africa catchments"){#fig-final} ## Creating a hierarchical structure for WGBAST We decided to add a [hierarchical structure](https://diaspara.bordeaux-aquitaine.inrae.fr/deliverables/wp3/p7/midb.html#area-tr_area_are) into the database. It will be handled through [referential tables](https://diaspara.bordeaux-aquitaine.inrae.fr/deliverables/wp3/p7/midb.html#referential-tables). Several levels will be layed out (@fig-area_hierarchy_bast) depending on the needs of each working group. ```{dot} //| label: fig-area_hierarchy_bast //| fig-cap: 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. digraph { compound=true; newrank=true; subgraph clusterZ { label = Stock style=full color=black center=true subgraph clusterA { label = "Inland Stock" style=full color=green fontcolor=green center=true subgraph clusterB { label = Assessment_unit style=full color=green3 fontcolor=green3 center=true subgraph clusterC { label = River style=full color=green2 fontcolor=green2 center=true subgraph clusterD { label = River_section style=full color=green1 fontcolor=green1 section [ label=data, shape=box, style =invis ] } } } subgraph clusterE{ label=Country style=dashed color=firebrick fontcolor=firebrick Fishing [ style=invis shape=square ] } } subgraph clusterF { label = "Marine Stock" style=full color=royalblue4 fontcolor=royalblue4 center=true subgraph clusterG { label = Division style=full color=royalblue3 fontcolor=royalblue3 center=true subgraph clusterH { label = Subdivision style=full color=royalblue2 fontcolor=royalblue2 center=true here [ label=data, shape=box, style =invis ] } } } } } ``` 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 (@fig-fullstocklvl). It includes all catchments that have a river flowing into the Baltic (@fig-inlandstocklvl) as well as all ICES subdivisions present in the Baltic (@fig-marinestocklvl), 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. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the global stock level in the Baltic #| warning: FALSE #| message: FALSE 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; ``` ```{r} #| label: refbast.tr_area_are Stock level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the global Stock level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refbast.tr_area_are WHERE are_id = 1;") knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of the full stock level.](images/fig-full_stock_level.png "A view of of the Baltic with a polygon surrounding the stock hierarchical level"){#fig-fullstocklvl} ### Marine level #### Stock The global Stock level (@fig-fullstocklvl) is then split into two lower layers. The first one is the marine Stock level (@fig-marinestocklvl) 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`. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the marine stock level in the Baltic #| warning: FALSE #| message: FALSE 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'); ``` ```{r} #| label: refbast.tr_area_are marine Stock level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the marine Stock level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refbast.tr_area_are WHERE are_id = ANY(ARRAY[1,2]);") knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of the marine stock level.](images/fig-marine_stock_level.png "A view of of the Baltic with a polygon surrounding the Baltic Sea, Bothian Sea and Finland Gulf"){#fig-marinestocklvl} #### 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 (@fig-marinestocklvl). ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the 27.3.b, c ICES division level in the Baltic #| warning: FALSE #| message: FALSE 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; ``` ```{r} #| label: refbast.tr_area_are ICES division level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the ICES division level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refbast.tr_area_are WHERE are_id = ANY(ARRAY[1,2]) OR are_are_id = 2 ORDER BY are_id;") knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of the ICES division level.](images/fig-marine_division_level.png "A view of of the Baltic with polygons surrounding each ICES divisions"){#fig-marinedivlvl} #### 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`. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create a function to add all ICES subdivisions level in the Baltic #| warning: FALSE #| message: FALSE 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; ``` ```{r} #| label: refbast.tr_area_are ICES subdivision level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the ICES subdivision level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refbast.tr_area_are WHERE are_id = ANY(ARRAY[1,2]) OR are_lev_code = ANY(ARRAY['Division', 'Subdivision']) ORDER BY are_id;") DT::datatable(fstock_lvl, rownames = FALSE, class = "stripe hover compact", options = list(dom = 't', pageLength = 100)) #knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of the ICES subdivision level.](images/fig-marine_subdivision_level.png "A view of of the Baltic with polygons surrounding each ICES subdivisions"){#fig-marinesubdivlvl} ### 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. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the inland stock level in the Baltic #| warning: FALSE #| message: FALSE 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'); ``` ```{r} #| label: refbast.tr_area_are inland Stock level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the inland Stock level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refbast.tr_area_are WHERE are_id = ANY(ARRAY[1,3]);") knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of the inland stock level.](images/fig-inland_stock_level.png "A view of Scandinavia with a polygon surrounding the inland stock hierarchical level"){#fig-inlandstocklvl} #### Country {#sec-wgbast-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 (@fig-inlandstocklvl). ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the country level in the Baltic #| warning: FALSE #| message: FALSE 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'); ``` ```{r} #| label: refbast.tr_area_are country level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the country level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refbast.tr_area_are WHERE are_id = ANY(ARRAY[1,3,4,5,6,7,8,9,10,11,12]) ORDER BY are_id;") DT::datatable(fstock_lvl, rownames = FALSE, class = "stripe hover compact", options = list(dom = 't', pageLength = 100)) #knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of the country level.](images/fig-country_level.png "A view of Scandinavia with polygons surrounding each countries that has a catchment flowing into the Baltic"){#fig-countrylvl} #### Assessment unit Assessment units have been created by [WGBAST](https://diaspara.bordeaux-aquitaine.inrae.fr/deliverables/wp3/p5/wgbast_database_description.html#assessment-units-within-the-baltic-sea-area) within the Baltic Sea area. There is six of them (@fig-bastassunit) 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. ![Map of WGBAST assessment units.](images/fig-assunit.png "A view of Scandinavia with polygons representing WGBAST assessment units"){#fig-bastassunit} 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 (@fig-inlandstocklvl). ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the WGBAST assessment unit 1 level in the Baltic #| warning: FALSE #| message: FALSE 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; ``` ```{r} #| label: refbast.tr_area_are assessment unit level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the assessment unit level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refbast.tr_area_are WHERE are_id = ANY(ARRAY[1,3]) OR are_lev_code = 'Assessment_unit' ORDER BY are_id;") DT::datatable(fstock_lvl, rownames = FALSE, class = "stripe hover compact", options = list(dom = 't', pageLength = 100)) #knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of WGBAST assessment units level.](images/fig-assunit_level.png "A view of the Baltic Sea with polygons surrounding all catchments that belongs to all 6 WGBAST assessment units"){#fig-assunitlvl} #### 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 (@fig-assunitlvl) 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. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create a function to retrieve main rivers within an assessment unit #| warning: FALSE #| message: FALSE 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; ``` ```{r} #| label: refbast.tr_area_are main river level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the main river level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refbast.tr_area_are WHERE are_id = ANY(ARRAY[1,3]) OR are_lev_code = 'Assessment_unit' OR are_are_id = 13 ORDER BY are_id;") DT::datatable(fstock_lvl, rownames = FALSE, class = "stripe hover compact", options = list(dom = 't', pageLength = 100)) #knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of reaches composing WGBAST first assessment units level.](images/fig-riverlevel.png "A view of the Bothian bay with polygons surrounding each reach within the first assessment unit"){#fig-riverlvl} #### 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. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create a function to retrieve main stretch of river section within each reach #| warning: FALSE #| message: FALSE 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; ``` ![Map of main stretch of rivers in the Baltic area.](images/fig-river_section_level.png "A view of Scandinavia with lines representing main stretch of rivers that flow into the Baltic"){#fig-riverseclvl} ## 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 (@fig-area_hierarchy_nas). ```{dot} //| label: fig-area_hierarchy_nas //| fig-cap: Hierarchical structure of WGNAS habitat database digraph { compound=true; newrank=true; rankdir="LR"; subgraph clusterZ { label = Stock style=full color=black center=true subgraph clusterA { label = "Inland Stock" style=full color=green fontcolor=green center=true subgraph clusterB { label = Assessment_unit style=full color=green3 fontcolor=green3 center=true subgraph clusterC { label = River style=full color=green2 fontcolor=green2 center=true subgraph clusterD { label = River_section style=full color=green1 fontcolor=green1 section [ label=data, shape=box, style =invis ] } } } subgraph clusterE{ label=Country style=dashed color=firebrick fontcolor=firebrick Fishing [ style=invis ] } } subgraph clusterF { label = "Marine Stock" style=full color=royalblue4 fontcolor=royalblue4 center=true subgraph clusterG { label = Subarea style=full color=royalblue3 fontcolor=royalblue3 center=true subgraph clusterH { label = Division style=full color=royalblue2 fontcolor=royalblue2 center=true subgraph clusterI { label = Assessment_unit style=full color=royalblue fontcolor=royalblue here [ label=data, shape=box, style =invis ] } subgraph clusterJ{ label=Fisheries style=full color=royalblue fontcolor=royalblue other [ style=invis ] } } } } } section -> Fishing -> here -> other [style=invis]; } ``` 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 (@fig-fullnas1). It includes all catchments that have a river flowing into areas studied by WGNAS (@fig-inlandnas1) as well as all ICES Subareas corresponding to said areas of interest (@fig-stcknas1). 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`. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the global stock level for WGNAS #| warning: FALSE #| message: FALSE 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; ``` ```{r} #| label: refnas.tr_area_are Stock level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the global Stock level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refnas.tr_area_are WHERE are_id = 1 ORDER BY are_id;") knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of the full stock level.](images/fig-full_stock_nas.png "A view of of the Europe with a polygon surrounding the stock hierarchical level"){#fig-fullnas1} ## 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`). ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the marine stock level for WGNAS #| warning: FALSE #| message: FALSE 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' ); ``` ```{r} #| label: refnas.tr_area_are marine Stock level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the marine Stock level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refnas.tr_area_are WHERE are_id = ANY(ARRAY[1,2,4]);") DT::datatable(fstock_lvl, rownames = FALSE, class = "stripe hover compact", options = list(dom = 't')) #knitr::kable(fstock_lvl, format = "html") %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of the full stock level.](images/fig-stock_marine_nas.png "A view of of the North Atlantic with two polygons surrounding the marine stock hierarchical level"){#fig-stcknas1} ### 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`. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the ICES subarea level for WGNAS #| warning: FALSE #| message: FALSE 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'; ``` ```{r} #| label: refnas.tr_area_are marine ICES Subarea level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the marine ICES Subarea level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refnas.tr_area_are WHERE are_id = ANY(ARRAY[1,2]) OR are_lev_code = 'Subarea' ORDER BY are_id;") DT::datatable(fstock_lvl, rownames = FALSE, class = "stripe hover compact", options = list(dom = 't', pageLength = 100)) #knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of the full stock level.](images/fig-subarea_nas.png "A view of the European side of the North Atlantic with polygons surrounding the ICES subarea level"){#fig-subnas1} ### 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`). ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the ICES Division level for WGNAS #| warning: FALSE #| message: FALSE 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'; ``` ```{r} #| label: refnas.tr_area_are marine ICES Division level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the marine ICES Division level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refnas.tr_area_are WHERE are_id = ANY(ARRAY[1,2]) OR are_lev_code = 'Subarea' OR are_lev_code = 'Division' ORDER BY are_id;") DT::datatable(fstock_lvl, rownames = FALSE, class = "stripe hover compact", options = list(dom = 't', pageLength = 100)) #knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of the full stock level.](images/fig-division_nas.png "A view of the European side of the North Atlantic with polygons surrounding the ICES division level"){#fig-divnas1} ### Assessment unit After some disscussions with WGNAS, it was decided to use as Assessment units at sea postsmolt areas from [Olmos et al. (2021)](https://doi.org/10.1111/gcb.14913). 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`). ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the assessment unit 1 level for WGNAS #| warning: FALSE #| message: FALSE 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; ``` ```{r} #| label: refnas.tr_area_are marine assessment unit level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the marine assessment unit in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refnas.tr_area_are WHERE are_id = ANY(ARRAY[1,2,4]) OR are_lev_code = 'Assessment_unit' AND are_ismarine = 'true' ORDER BY are_id;") DT::datatable(fstock_lvl, rownames = FALSE, class = "stripe hover compact", options = list(dom = 't', pageLength = 100)) #knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of the Assessment unit level.](images/fig-postsmolt.png "A view of of the North Atlantic with polygons surrounding the assessment unit level"){#fig-psnas10} :::{.questionbox} ::::{.questionbox-header} ::::{.questionbox-icon} :::: Question to WGNAS :::: ::::{.questionbox-body} 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](https://diaspara.bordeaux-aquitaine.inrae.fr/deliverables/wp3/p4/wgnas_salmoglob_description.html#area) with their models. To create them we decided to follow fisheries areas present in [Olmos et al. (2021)](https://doi.org/10.1111/gcb.14913). 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`. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the fisheries GLD level for WGNAS #| warning: FALSE #| message: FALSE 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'; ``` ```{r} #| label: refnas.tr_area_are marine fisheries level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the marine fisheries level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refnas.tr_area_are WHERE are_id = ANY(ARRAY[1,2,4]) OR are_lev_code = 'Fisheries';") DT::datatable(fstock_lvl, rownames = FALSE, class = "stripe hover compact", options = list(dom = 't', pageLength = 100)) #knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of the fisheries level.](images/fig-fisheries_nas.png "A view of of the North Atlantic with three polygons surrounding the fisheries level"){#fig-fishnas10} :::{.questionbox} ::::{.questionbox-header} ::::{.questionbox-icon} :::: Question to WGNAS :::: ::::{.questionbox-body} - 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`. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the inland stock level for WGNAS #| warning: FALSE #| message: FALSE 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' ); ``` ```{r} #| label: refnas.tr_area_are inland Stock level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the inland Stock level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refnas.tr_area_are WHERE are_id = ANY(ARRAY[1,3]);") DT::datatable(fstock_lvl, rownames = FALSE, class = "stripe hover compact", options = list(dom = 't', pageLength = 100)) #knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of the inland stock level.](images/fig-stock_inland_nas.png "A view of Europe with a polygon surrounding the inland stock hierarchical level"){#fig-inlandnas1} ### Assessment unit Following data from [WGNAS dataset](https://diaspara.bordeaux-aquitaine.inrae.fr/deliverables/wp3/p4/wgnas_salmoglob_description.html#area) and geometries from [Olmos et al. 2020](https://doi.org/10.1111/gcb.14913) 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`. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the inland assessment unit level for WGNAS #| warning: FALSE #| message: FALSE 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; ``` ```{r} #| label: refnas.tr_area_are inland assessment unit level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the inland assessment unit level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refnas.tr_area_are WHERE are_id = 3 OR are_lev_code = 'Assessment_unit' AND are_ismarine = FALSE ORDER BY are_id;") DT::datatable(fstock_lvl, rownames = FALSE, class = "stripe hover compact", options = list(dom = 't', pageLength = 100)) #knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` :::{.callout-note appearance="simple"} ## 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. ::: ![Map of the assessment unit level.](images/fig-au_inland_nas.png "A view with polygons surrounding the assessment units spread across Europe and North America"){#fig-auinnas1} ### 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`. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the River level for WGNAS #| warning: FALSE #| message: FALSE 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; ``` ```{r} #| label: refnas.tr_area_are river level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the river level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refnas.tr_area_are WHERE are_id = ANY(ARRAY[1,3,78]) OR are_are_id = 78 ORDER BY are_id;") DT::datatable(fstock_lvl, rownames = FALSE, class = "stripe hover compact", options = list(dom = 't', pageLength = 50)) #knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ![Map of the River level.](images/fig-river_nas.png "A view with polygons surrounding the river level spread across Europe and North America"){#fig-rivnas1} ### 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. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create the river_section level for WGNAS #| warning: FALSE #| message: FALSE 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; ``` ![Map of the river section level.](images/fig-river_section_nas.png "A view with lines representing the river sections spread across Europe and North America"){#fig-rivsecnas1} ```{r} #| label: refnas.tr_area_are river_section level #| echo: FALSE #| warning: FALSE #| message: FALSE #| tbl-cap: Structure of the river_section level in the referential table fstock_lvl <- dbGetQuery(con_diaspara, "SELECT are_id, are_are_id, are_code, are_lev_code, are_wkg_code, are_ismarine FROM refnas.tr_area_are WHERE are_id = ANY(ARRAY[1,3,78,3152]) OR are_are_id = 3152 ORDER BY are_id;") DT::datatable(fstock_lvl, rownames = FALSE, class = "stripe hover compact", options = list(dom = 't', pageLength = 100)) #knitr::kable(fstock_lvl) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed")) ``` ### Country To add the country level, the same work has been done as for WGBAST (@sec-wgbast-country). ![Map of the country level.](images/fig-country_level.png "A view of Scandinavia with polygons surrounding each countries that has a catchment flowing into the Baltic"){#fig-countrylvl1} # 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. ```{r} #| label: river segment variables #| code-summary: Description of river segments variables #| echo: TRUE #| warning: FALSE #| message: FALSE #| tbl-cap: Variables description 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")) ``` 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. ![Map of river network from EU-Hydro split into basins.](images/fig-euhydrobase.png "A view of Europe river networks"){#fig-eubase} 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). ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to create a function that changes srid and geometry #| warning: FALSE #| message: FALSE 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. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to restructure columns order #| warning: FALSE #| message: FALSE 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. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to retrieve all missing islands and coastal catchments #| warning: FALSE #| message: FALSE 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. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to retrieve river segments outlets for basins within range of ICES subdivisions 30 and 31 #| warning: FALSE #| message: FALSE 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. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to merge all riversegments into one table #| warning: FALSE #| message: FALSE 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" ); ``` ![Map of river network from EU-Hydro and selected outlets in the Baltic for ICES subdivisions 30 & 31.](images/fig-euhydrooutlets.png "A view of selected outlets for Baltic subdivisions 30 & 31"){#fig-euoutlets} ### 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`. ```{sql} #| eval: FALSE #| echo: TRUE #| code-summary: Code to retrieve all river segments linked to preselected outlets #| warning: FALSE #| message: FALSE 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; ``` ![Map of river network from EU-Hydro for the Baltic ICES subdivisions 30 & 31.](images/fig-euhydrobalt3031.png "A view of the Baltic river networks for ICES subdivisions 30 & 31"){#fig-eubalt3031} 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.](images/fig-euhydroices.png "A view of Europe river networks"){#fig-eubase} # 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