mapdotoro

A R package to prepare the Fluvial Corridor Toolbox Python data to mapdoapp web application

https://github.com/evs-gis/mapdotoro

Repository

A R package to prepare the Fluvial Corridor Toolbox Python data to mapdoapp web application

README.Rmd

---
output: github_document
---



```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "man/figures/README-",
  out.width = "100%"
)
```

# 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](https://github.com/EVS-GIS/mapdotoro) with:

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

mapdotoro use qgisprocess package, [QGIS](https://www.qgis.org/en/site/) with the [Fluvial Corridor Toolbox](https://github.com/EVS-GIS/fct-qgis) 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](https://github.com/EVS-GIS/mapdoapp/blob/main/LICENSE).

## Workflow

### Get troncon id and cours d'eau id from the IGN BD TOPO in PostgreSQL/PostGIS database
```{sql, eval = FALSE}
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
```{r eval=FALSE}
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
```{r eval=FALSE}
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
```{r, eval=FALSE}
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
```{r, eval=FALSE}
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
```{r, eval=FALSE}
# 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
```{r, eval=FALSE}
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
```{r, eval=FALSE}
# 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
```{r, eval=FALSE}
# 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)
```

Owner

Citation (CITATION.cff)

cff-version: 1.2.0
message: "If you use this software, please cite it as below."
authors:
  - family-names: Manière
    given-names: Louis
    orcid: https://orcid.org/0000-0003-0426-2975
    affiliation: "CNRS - Environnement Ville Société (UMR 5600)"
  - family-names: Dunesme
    given-names: Samuel
title: "mapdotoro"
version: 1.0.1
repository-code: "https://github.com/EVS-GIS/mapdotoro"
license: GPL-3.0 license
identifiers:
  - type:
    value:
date-released: 2025-04-07

GitHub Events