Prepare The Fluvial Corridor Toolbox Python Data • mapdotoro

Mapdotoro aim to prepared the Fluvial Corridor Toolbox data outputs to the shiny application mapdoapp.

Installation

You can install the development version of mapdotoro from GitHub with:

# install.packages("devtools")
devtools::install_github("EVS-GIS/mapdotoro")

mapdotoro use qgisprocess package, QGIS with the Fluvial Corridor Toolbox plugin installed.

Tested with QGIS version 3.28.13 and Fluvial Corridor Toolbox version 1.0.11.

How to cite

Manière, L. (2024). mapdotoro (Version 1.0.0) [Computer software]. https://github.com/EVS-GIS/mapdotoro

Licence

This program is released under the GNU Public License v3.

Workflow

Get troncon id and cours d’eau id from the IGN BD TOPO in PostgreSQL/PostGIS database

SELECT 
    cleabs AS id_troncon,
   -- we can have several cours d'eau for one troncon like COURDEAU0000002215482270/COURDEAU0000002215482269
   -- keep ony the first (like in BDTOPO2REFHYDRO) the create BIGINT like 2215482270
    CASE 
        WHEN POSITION('/' IN liens_vers_cours_d_eau) > 0
            THEN CAST(regexp_replace(
                regexp_replace(
                    SUBSTRING(liens_vers_cours_d_eau FROM 1 FOR POSITION('/' IN liens_vers_cours_d_eau) - 1), 
                    '[^0-9]', '', 'g'), '0*$', '') AS BIGINT)
        ELSE CAST(regexp_replace(
            regexp_replace(liens_vers_cours_d_eau, 
                           '[^0-9]', '', 'g'), '0*$', '') AS BIGINT)
      END AS axis
FROM public.troncon_hydrographique
-- filter to keep only troncon where liens_vers_cours_d_eau is not NULL or empty
WHERE (liens_vers_cours_d_eau IS NOT NULL AND liens_vers_cours_d_eau != '')

Export the table in csv to ./data-raw/raw-datasets/troncon_bdtopo_id.csv

Testing data

input_bassin_hydrographique <- bassin_hydrographique
input_region_hydrographique <- region_hydrographique
input_roe <- roe
input_hydro_sites <- hydro_sites
input_referentiel_hydro <- referentiel_hydro
input_swaths <- swaths
input_talweg_metrics <- talweg_metrics
input_landcover <- landcover
input_continuity <- continuity
input_valley_bottom <- valley_bottom
input_troncon_bdtopo_id <- troncon_bdtopo_id
input_elevation_profiles <- elevation_profiles

Datasets from the Fluvial Corridor Toolbox

input_bassin_hydrographique <- sf::st_read(dsn = file.path("data-raw", "raw-datasets",
                                                 "bassin_hydrographique.gpkg"))
input_region_hydrographique <- sf::st_read(dsn = file.path("data-raw", "raw-datasets",
                                                 "region_hydrographique.gpkg"))
input_roe <- sf::st_read(dsn = file.path("data-raw", "raw-datasets", "roe.gpkg"))
input_hydro_sites <- import_hydro_sites()
input_talweg_metrics <- readr::read_csv(file.path("data-raw", "raw-datasets", "TALWEG_METRICS.csv"))
input_referentiel_hydro <- sf::st_read(dsn = file.path("data-raw", "raw-datasets", "REFERENTIEL_HYDRO.shp"))
input_swaths <- sf::st_read(dsn = file.path("data-raw", "raw-datasets", "SWATHS_MEDIALAXIS.shp"))
input_landcover <- readr::read_csv(file.path("data-raw", "raw-datasets", "WIDTH_LANDCOVER.csv"))
input_continuity <- readr::read_csv(file.path("data-raw", "raw-datasets", "WIDTH_CONTINUITY.csv"))
input_valley_bottom <- readr::read_csv(file.path("data-raw", "raw-datasets", "WIDTH_VALLEY_BOTTOM.csv"))
input_troncon_bdtopo_id <- readr::read_csv(file.path("data-raw", "raw-datasets", "troncon_bdtopo_id.csv"))
input_elevation_profiles <- readr::read_csv(file.path("data-raw", "raw-datasets", "SWATH_ELEVATION_PROFILES.csv"))

Prepare dataset

bassin_hydrographique <- prepare_bassin_hydrographique(input_bassin_hydrographique)

region_hydrographique <- prepare_region_hydrographique(input_region_hydrographique)

hydro_sites <- prepare_hydro_sites(dataset = input_hydro_sites,
                                   region_hydro = region_hydrographique)

talweg_metrics <- prepare_talweg_metrics(dataset = input_talweg_metrics)

landcover_area <- prepare_landcover_area(dataset = input_landcover)

continuity_area <- prepare_continuity_area(dataset = input_continuity)

continuity_width <- prepare_continuity_width(dataset = input_continuity)

valley_bottom <- prepare_valley_bottom(dataset = input_valley_bottom)

hydro_swaths_and_axis <- prepare_hydro_swaths_and_axis(swaths_dataset = input_swaths,
                                                       referentiel_hydro_dataset = input_referentiel_hydro,
                                                       region_hydro = region_hydrographique)

roe <- prepare_roe(input_roe,
                   region_hydro = region_hydrographique,
                   troncon_bdtopo_id = input_troncon_bdtopo_id,
                   hydro_axis = hydro_swaths_and_axis$hydro_axis)

elevation_profiles <- prepare_elevation_profiles(dataset = input_elevation_profiles)

Set database structure

create_table_bassin_hydrographique(table_name = "bassin_hydrographique",
                                    db_con = db_con())

create_table_region_hydrographique(table_name = "region_hydrographique",
                                   db_con = db_con())

create_table_hydro_axis(table_name = "hydro_axis",
                                   db_con = db_con())

create_table_hydro_swaths(table_name = "hydro_swaths",
                          db_con = db_con())

create_table_roe(table_name = "roe",
                 db_con = db_con())

create_table_hydro_sites(table_name = "hydro_sites",
                         db_con = db_con())

create_table_talweg_metrics(table_name = "talweg_metrics",
                            db_con = db_con())

create_table_landcover_area(table_name = "landcover_area",
                            db_con = db_con())

create_table_continuity_area(table_name = "continuity_area",
                             db_con = db_con())

create_table_continuity_width(table_name = "continuity_width",
                              db_con = db_con())

create_table_valley_bottom(table_name = "valley_bottom",
                           db_con = db_con())

create_table_elevation_profiles(table_name = "elevation_profiles",
                                db_con = db_con())

Add functions and triggers to Postgresql database

# hydro_swaths triggers
fct_hydro_swaths_insert_delete_reaction(db_con = db_con(), table_name = "hydro_swaths")
trig_hydro_swaths(db_con = db_con(), table_name = "hydro_swaths")

# talweg_metrics triggers
fct_talweg_metrics_insert_delete_reaction(db_con = db_con(), table_name = "talweg_metrics")
trig_talweg_metrics(db_con = db_con(), table_name = "talweg_metrics")

# landcover_area triggers
fct_landcover_area_insert_delete_reaction(db_con = db_con(), table_name = "landcover_area")
trig_landcover_area(db_con = db_con(), table_name = "landcover_area")

# continuity_area triggers
fct_continuity_area_insert_delete_reaction(db_con = db_con(), table_name = "continuity_area")
trig_continuity_area(db_con = db_con(), table_name = "continuity_area")

# continuity_width triggers
fct_continuity_width_insert_delete_reaction(db_con = db_con(), table_name = "continuity_width")
trig_continuity_width(db_con = db_con(), table_name = "continuity_width")

# valley_bottom triggers
fct_valley_bottom_insert_delete_reaction(db_con = db_con(), table_name = "valley_bottom")
trig_valley_bottom(db_con = db_con(), table_name = "valley_bottom")

# elevation_profiles triggers
fct_elevation_profiles_insert_delete_reaction(db_con = db_con(), table_name = "elevation_profiles")
trig_elevation_profiles(db_con = db_con(), table_name = "elevation_profiles")

Create views

create_landcover_area_full_side_matview(db_con = db_con(), view_name = "landcover_area_full_side")
create_continuity_width_full_side_matview(db_con = db_con(), view_name = "continuity_width_full_side")
create_continuity_area_full_side_matview(db_con = db_con(), view_name = "continuity_area_full_side")
create_valley_bottom_full_side_matview(db_con = db_con(), view_name = "valley_bottom_full_side")
create_network_metrics_matview(db_con = db_con(), view_name = "network_metrics")
create_network_axis_matview(db_con = db_con(), view_name = "network_axis")

Fixed tables database : insert bassins and regions

# Rows can't be deleted for these two tables 
upsert_bassin_hydrographique(dataset = bassin_hydrographique,
                             table_name = "bassin_hydrographique",
                             db_con = db_con(),
                             field_identifier = "cdbh")

upsert_region_hydrographique(dataset = region_hydrographique,
                             table_name = "region_hydrographique",
                             db_con = db_con(),
                             field_identifier = "cdregionhy")

Update / insert tables and refresh materialized view

# change display_codes_bassin_or_region to set the bassins and regions that can be explored by mapdoapp user
set_displayed_bassin_region(table_name = "bassin_hydrographique",
                            display_codes_bassin_or_region = c("06"),
                            field_identifier = "cdbh",
                            db_con = db_con())

set_displayed_bassin_region(table_name = "region_hydrographique",
                            display_codes_bassin_or_region = c("W"),
                            field_identifier = "cdregionhy",
                            db_con = db_con())

upsert_hydro_sites(dataset = hydro_sites,
                   table_name = "hydro_sites",
                   db_con = db_con(),
                   field_identifier = "code_site")

upsert_hydro_swaths_and_axis(hydro_swaths_dataset = hydro_swaths_and_axis$hydro_swaths,
                             hydro_swaths_table_name = "hydro_swaths",
                             hydro_axis_dataset = hydro_swaths_and_axis$hydro_axis,
                             hydro_axis_table_name = "hydro_axis",
                             db_con = db_con(),
                             field_identifier = "axis")

upsert_roe(dataset = roe,
           table_name = "roe", 
           db_con = db_con(), 
           field_identifier = "cdobstecou")

upsert_talweg_metrics(dataset = talweg_metrics,
                      table_name = "talweg_metrics",
                      db_con = db_con(),
                      field_identifier = "axis")

upsert_landcover_area(dataset = landcover_area,
                      table_name = "landcover_area",
                      db_con(),
                      field_identifier = "axis")

upsert_continuity_area(dataset = continuity_area,
                      table_name = "continuity_area",
                      db_con(),
                      field_identifier = "axis")

upsert_continuity_width(dataset = continuity_width,
                      table_name = "continuity_width",
                      db_con(),
                      field_identifier = "axis")

upsert_valley_bottom(dataset = valley_bottom,
                     table_name = "valley_bottom",
                     db_con(),
                     field_identifier = "axis")

upsert_elevation_profiles(dataset = elevation_profiles,
                          table_name = "elevation_profiles",
                          db_con(),
                          field_identifier = "axis")

# Refresh all the materialized views
# !! order matters !! network_metrics depend of full_side views, need to be at last.
materialized_views <- c("landcover_area_full_side", "continuity_area_full_side",
                        "continuity_width_full_side", "valley_bottom_full_side",
                        "network_axis", "network_metrics")

refresh_all_materialized_views(db_con = db_con(), materialized_views)