logoSUDOANG

1 Introduction

Collecting data from various sources about eel populations and impact is the first step towards the estimation of escapment. All data received have been collated in the SUDOANG database, whose structure is inherited from the database for eels (DBEEL), developed during the Pilot projects to estimate potential and actual escapement of silver eel (POSE, Service Contract No. SI2.539598). The POSE project objective was aimed at providing methods to estimate the current and potential (without mortality) biomass of silver eel in the Baltic, Atlantic and Mediterranean regions. The database is managed in PostgreSQL, an object-relational database management system, free and open-source. This facilitates the sharing of complex data structures between operators from different countries! Furthermore, PostGIS, a spatial database extender, is used to store GIS objects and work with spatial data.

Several developments have been integrated in the SUDOANG database compared to the originalthe POSE structure to allow the integration of all the relevant information compiled by other GT’s. The information that has been collected refers to:

  • electrofishing surveys
  • hydrographic network and environmental characteristics
  • hydrographic waterbodies connected to the river network (ponds, estuaries, lakes ….)
  • physical attributes from the river network such as the flow, the water surface and width
  • physical obstacles in rivers (dams that may be associated with hydroelectric power plants).

The structure of the database concerning physical barriers and hydroelectric power plants also inherits the structure of the French databases:

  • ROE: Référentiel des Obstacles à l’Ecoulement
  • BDOe: Base de Données sur les Obstacles à l’écoulement
  • ICE: Information sur la Continuité Écologique
  • Géobs: Géoréférenceur des observations.

These correspond to standardized french databases on flow barriers and their impacts on ecological continuity in France.

This deliverable presents the data import routines about physical obstacles and hydropower plants in France, Spain and Portugal. First we describe the import of physical obstacles data from France, then we describe the import of physical obstacles data from Spain and Portugal. Thirdly, we describe the import of data concerning hydroelectric power plants for the three countries. Finally, the process for calculating the cumulated number of obstacles in each river segment of the SUDOANG hydrographic network is described for later use in the silver eel biomass estimation model (EDA model in GT4).

2 Data import on physical obstacles in France (Geobs, BDOe, ICE and ROE)

The script in its latest version is available online on the import trac script

2.1 General import

First, the ROE table already present in the SUDOANG database is deleted so that it can then be updated. The table will be recreated and filled from the Geobs data.

The data are then projected on the RHT network, points located less than 300 meters from a river segment are projected on the latter. Those that have not been projected, the geometry is updated with the reference position of the table.

DROP TABLE IF EXISTS roe.roesudo;
CREATE TABLE roe.roesudo AS SELECT * FROM roe.obstacle_referentiel; -- 112934 rows

SELECT UpdateGeometrySRID('roe','roesudo','ref_position_carthage',3035);
update roe.roesudo set ref_position_carthage= ST_Transform(ST_SetSRID(ref_position_carthage,4326),3035);
reindex TABLE roe.roesudo;
SELECT * FROM roe.roesudo WHERE ref_position_carthage is NULL

-- Data projection ROE ICE on the RHT network (france.rn layer)
SELECT addgeometrycolumn('roe','roesudo','geom_reproj',3035,'POINT',2);

-- SELECT count (*) FROM roe.roesudo WHERE ref_position_carthage is not null; -- 101995 rows
update roe.roesudo set geom_reproj = sub2.geom_reproj FROM (
SELECT DISTINCT on (ref_id) ref_id, geom_reproj, distance FROM(
SELECT roe.ref_id, 
ST_ClosestPoint(r.geom,roe.ref_position_carthage) AS geom_reproj ,
ST_distance(r.geom,roe.ref_position_carthage) AS distance
FROM roe.roesudo roe  join
france.rn r on st_dwithin(r.geom,roe.ref_position_carthage,300) 
ORDER BY ref_id, distance
)sub )sub2
WHERE sub2.ref_id= roesudo.ref_id; -- 91224 rows

/* 
For those missing adding the geom from ref_position not ref_position_carthage
*/
update roe.roesudo set geom_reproj = sub2.geom_reproj FROM (
SELECT DISTINCT on (ref_id) ref_id, geom_reproj, distance FROM(
SELECT roe.ref_id, 
ST_ClosestPoint(r.geom,st_transform(roe.ref_position,3035)) AS geom_reproj ,
ST_distance(r.geom,st_transform(roe.ref_position,3035)) AS distance
FROM roe.roesudo roe  join
france.rn r on st_dwithin(r.geom,st_transform(roe.ref_position,3035),300) 
WHERE ref_position_carthage is NULL  -- where ref_postition_carthage is null
ORDER BY ref_id, distance
)sub )sub2
WHERE sub2.ref_id= roesudo.ref_id; -- 4835 rows
--SELECT count(*) FROM roe.roesudo -- 112934 rows 

CREATE INDEX 
  ON roe.roesudo
  USING gist
  (geom_reproj);

---------------
-- TODO
---------------
/*
The reprojection information on the bd_carthage is present on the ROE (column cd_Entite_hy and seems to match the code cd_hyd_cdo).
See if it is possible to retrieve the strahler rank of the bd_carthage or attach the information concerning the join between rht and bd_carthage sections.
http://www.sandre.eaufrance.fr/ftp/documents/fr/scn/obsgeo/1/sandre_sc_geo_obs_1.pdf
http://id.eaufrance.fr/ddd/ETH/2002-1/IdTronconHydrograElt
*/
DELETE  FROM sudoang.dbeel_obstruction_place WHERE country='FR'; -- 80324 rows => 92078 rows, 11:44 mins  

INSERT INTO sudoang.dbeel_obstruction_place
    SELECT uuid_generate_v4() AS op_id, 
    'AFB' AS op_gis_systemname, 
    'roe' AS op_gis_layername, 
    3035 AS op_gislocation, 
    ref_nom AS op_placename, 
    281 AS op_no_observationplacetype, 
    NULL op_op_id, 
    geom_reproj AS the_geom, 
    ref_id_national AS id_original, 
    'FR' AS country
    FROM (SELECT * FROM roe.roesudo WHERE geom_reproj IS NOT NULL and ref_sta_id in (0,4)) unique_obs
    ; --  95067 rows affected, 03:59 minutes execution time. => 84457 03:27  => including pb geom 88743

--VACUUM FULL ANALYSE sudoang.dbeel_obstruction_place;  --17s
--REINDEX TABLE sudoang.dbeel_obstruction_place;  -- 8s
--    2414 / only the one projected on rivers
--SELECT * FROM sudoang.dbeel_obstruction_place
-- record back the link with dbeel

--  In the SANDRE dictionary there is a type 6 industrial installation, 
--    this type is not integrated in the ROE, why?

Regarding the treatment of the linked obstacles, when the column referring to the linked obstacles is filled in the ROE, this information is retrieved to introduce it in the SUDOANG database. If several obstacles belong to the same “complex”, a reference is made to the main obstacle in the records of secondary obstacles (associated with the main one).

-----------------------------------------------
--  Related obstacles
-------------------------------------------------
-- In ROE when the column ouvrageLie is filled, this information is retrieved to import in SUDOANG
-- First query for recovery of the secondary link
WITH recupere_moi_le_code_op_id_pere AS (
SELECT op_id AS op_id_pere, liens_ref_id,liens_ref_id_fils FROM sudoang.dbeel_obstruction_place join
     roe.liens  on 'ROE'||liens.liens_ref_id=id_original),
     recupere_moi_le_code_op_id_fils AS (
   SELECT op_id_pere, op_id AS op_id_fils, liens_ref_id,liens_ref_id_fils FROM recupere_moi_le_code_op_id_pere 
   join
     sudoang.dbeel_obstruction_place  on 'ROE'||liens_ref_id_fils=id_original)
-- The main code is referenced in the line of the secondary obstacle
update sudoang.dbeel_obstruction_place set op_op_id=recupere_moi_le_code_op_id_fils.op_id_pere 
FROM recupere_moi_le_code_op_id_fils
WHERE recupere_moi_le_code_op_id_fils.op_id_fils=dbeel_obstruction_place.op_id; -- 5951 rows, 2.2 s

Then, oonly obstacles that have been validated by their position or by an expert have been retained. Only existing longitudinal barriers were kept in the database (lateral obstacles such as the dikes, or river groins, were removed from the data, as well as the obstacles that had been completely destroyed, razed, under construction or planned).

/*
Adaptation of Soizic's script for the choice of obstacles, thanks Soizic !!!
*/
DELETE FROM sudoang.dbeel_obstruction_place WHERE id_original in ('ROE106058','ROE88566'); -- 2 rows
-- The dikes are removed
DELETE FROM sudoang.dbeel_obstruction_place 
    WHERE op_id in (
    SELECT op_id FROM sudoang.dbeel_obstruction_place 
    JOIN roe.roesudo on dbeel_op_id = op_id 
    JOIN roe.type ON ref_typ_id=typ_id
    WHERE typ_nom = 'Digue' ); -- 282 rows
    
DELETE FROM sudoang.dbeel_obstruction_place 
    WHERE op_id in (
    SELECT op_id FROM sudoang.dbeel_obstruction_place 
    JOIN roe.roesudo on dbeel_op_id = op_id 
    JOIN roe.type ON ref_typ_id=typ_id
    WHERE typ_nom In ('Epis en rivière','Grille de pisciculture')); -- 644 rows

DELETE FROM sudoang.dbeel_obstruction_place 
    WHERE op_id in (
    SELECT op_id FROM sudoang.dbeel_obstruction_place 
    JOIN roe.roesudo on dbeel_op_id = op_id 
    JOIN roe.etat ON ref_eta_id=eta_id
    WHERE eta_nom In ('Détruit entièrement','En construction','En projet')); -- 3347 rows

DELETE FROM sudoang.dbeel_obstruction_place 
    WHERE op_id in (
    SELECT op_id FROM sudoang.dbeel_obstruction_place 
    JOIN roe.roesudo on dbeel_op_id = op_id 
    JOIN bdoe.ouvrage on 'ROE'||ouv_id=ref_id_national
    WHERE ouv_derasement); -- 196 rows

SELECT count(*) FROM sudoang.dbeel_obstruction_place WHERE country ='FR'
; -- 110 013 (SP+PT+FR) 88741 (before removal) 84272 (after removal)
 
/*
We go over it again to remove the links which do not exist any more (same request as before).
*/
UPDATE roe.roesudo set dbeel_op_id = NULL; -- 112394 rows

UPDATE roe.roesudo SET dbeel_op_id = op_id 
FROM sudoang.dbeel_obstruction_place 
WHERE dbeel_obstruction_place.id_original=roesudo.ref_id_national;  -- 84272 rows, 8.6 secs

2.2 Importing the drop height

First, the drop heights (difference between upstream and downstream water level, important factor influencing obstacle passability) available in the three French databases ROE, ICE and BDOe are imported. A decision rule was then created to select the most relevant drop height for all obstacles depending on available data in the three databases.

Decision rule:

  • First, the ICE values are taken

  • Second, the BDOe values

  • Third, the old ROE values

  • The last of the ROE classes

  • If there is only one value available, it is the one taken.

Decision rule for drop height selection

Decision rule for drop height selection

2.2.1 BDOE

Retrieving this data is simple. Several surveys may have been carried out on the same obstacle at different dates, and only the most recent survey will be kept.

2.2.2 ICE

2.2.2.1 Obstacle drop height value

The situation is more complex with the ICE since the ICE provides a more precise description of the obstacle and provides a drop height for each fish passage route (for example, on a dam, ICE can provide a drop height for the fish pass, the water intake and the culvert etc.). In such a situation, we listed all the drop heights values of the obstacles for effective fish passage route (i.e. in which water is indeed flowing). Then we, classify the routes according to the following sequence and keep the first value recorded.

  1. Shoreline migration pathways

  2. Tidal dam

  3. Weir

  4. Gate

  5. Rock weir

  6. Water Intake

  7. Culvert

  8. Fish pass

It is possible that several ICE have been made on the same obstacle at different dates, in which case the lowest height will be retained.

2.2.2.2 Value of the overall drop heights of the ICE file

An overall drop height at the entire obstacle is recorded when carrying out ICE protocols. Several ICE protocols may have been carried out on the same obstacle, in which case the greatest (most limiting) height will be retained.

The drop height of the obstacle will be the priority drop height. If this value is not available but there is an overall drop height, the latter value will be used.

2.2.3 ROE

For the ROE drop height there are classes (below 0.5, 0.5-1, 1-1.5, 1.5-2, 2-3, 3-5, 5-10, above 10).

For ROE class drop heights, the center of each class will be used as the drop height value:

  • Class below 0.5 => 0.25

  • Class 0.5-1 => 0.75

  • Class 1-1.5 => 1.25

  • Class 1.5-2 => 1.75

  • Class 2-3 => 2.5

  • Class 3-5 => 4

  • Class 5-10 => 7.5

  • Class above 10 => 20

WITH 
hauteur_chute_bdoe_nlignes AS (
    
    SELECT hco_hauteur AS h_chute_bdoe, hco_ouv_id::integer AS ouv_id FROM  bdoe.hauteur_chute 
    ), -- 58380 rows
hauteur_chute_bdoe AS ( 
    SELECT avg(h_chute_bdoe) h_chute_bdoe,ouv_id FROM  hauteur_chute_bdoe_nlignes GROUP BY ouv_id
    ),
--SELECT * FROM hauteur_chute_bdoe -- 57895 rows
/* Drop height of turbines is not used  
hauteur_chute_pre AS (
    SELECT pre.pre_id, min(tuh_hauteur_chute) AS tuh_hauteur_chute FROM  ice.turbine_usine_hydrolelectrique 
    join ice.prise_eau pre on tuh_pre_id=pre_id
    WHERE tuh_hauteur_chute is not null
    GROUP by pre_id
    )
*/

-- No interest in looking into obstacle structure (meeting of 22/08), the info is in opi.
-- Disagree, we make our own choice, because we also choose the least restrictive opi.

hauteur_chute_sto AS (
    SELECT   ooi_ref_id AS ouv_id, *
    FROM ice.obstacle_operation_ice JOIN
    ice.operation_ice on opi_ooi_id=ooi_id JOIN
    ice.partie_obstacle ON pao_opi_id=opi_id LEFT JOIN
    ice.structure_obstacle on sto_pao_id=pao_id --LEFT JOIN
    --hauteur_chute_pre on pre_id=sto_id
    ),

hauteur_chute_reordonne AS (
    SELECT *,
     CASE WHEN sto_tso_id=5 THEN 2          -- Tidal dam
                   WHEN sto_tso_id=1 THEN 3 -- Weir
                   WHEN sto_tso_id=3 THEN 4 -- Gate
                   WHEN sto_tso_id=2 THEN 5 -- Rock weir    
                   WHEN sto_tso_id=7 THEN 6 -- Water intake
                   WHEN sto_tso_id=4 THEN 7 -- Culvert
                   WHEN sto_tso_id=6 THEN 8 -- Fish pass             
                   WHEN sto_tso_id=8 THEN 1 -- Shoreline migration pathways
                   END AS order_sto_tso_id 
     FROM hauteur_chute_sto     
     WHERE pao_eco_id in (1,3) -- yes, or discontinuous
     ORDER BY order_sto_tso_id),
--SELECT * FROM hauteur_chute_reordonne
     
-- Within an operation the first drop height from the previous order is taken.   
hauteur_chute_ice_sto AS (
    SELECT distinct on (ouv_id,opi_id) *
     FROM hauteur_chute_reordonne                                  
    ),
--SELECT * FROM hauteur_chute_ice_sto limit 50

-- If there are two drop heights in successive operations, the smaller one is taken
hauteur_chute_ice_par_ouvrage As
(SELECT ouv_id, 
    min(sto_hauteur_chute) AS hauteur_chute_ice,
    max(opi_hauteur_chute) AS hauteur_chute_ice_plus_limitante  
    FROM hauteur_chute_ice_sto
    GROUP BY ouv_id),
-- Our drop heights are generally equal, sometimes lower, but NOT systematically?? 
-- In some cases the drop height coming from an opi while for us nothing ... 
--    maybe because in ICE the nulls are treated better.

hauteur_chute_ice AS
(SELECT ouv_id, 
    coalesce(hauteur_chute_ice,hauteur_chute_ice_plus_limitante) AS hauteur_chute_ice
    FROM hauteur_chute_ice_par_ouvrage),

selectionne_roe AS (    
    SELECT regexp_replace(ref_id_national, 'ROE', '')::integer AS ouv_id,
    CASE WHEN ref_typ_id = 0 THEN 291                                       -- Dam
         WHEN ref_typ_id = 1 AND (ref_sty_id=10 OR ref_sty_id=11) THEN 292  -- Weir
         WHEN ref_typ_id = 1 AND (ref_sty_id IS NULL) THEN 292              -- Weir
         WHEN ref_typ_id = 1 AND ref_sty_id=12 THEN 293                     -- Rock ramp
         WHEN ref_typ_id = 2 THEN 298                                       -- Dike
         WHEN ref_typ_id = 3 AND (ref_sty_id=17 OR ref_sty_id=21) THEN 296  -- Bridge
         WHEN ref_typ_id = 3 AND (ref_sty_id IS NULL) THEN 296              -- Bridge
         WHEN ref_typ_id = 3 AND ref_sty_id = 20 THEN 295                   -- Ford
         WHEN ref_typ_id = 3 AND ref_sty_id = 18 THEN 294                   -- Culvert
         WHEN ref_sty_id = 9 OR  ref_sty_id = 19 OR ref_sty_id=13 THEN 297  -- Other
         WHEN ref_typ_id = 5 THEN 299                                       -- Grid
         ELSE 219                                                           -- Physical obstruction (unknown)
         END AS obstruction_type_no_id,
    * 
    FROM roe.roesudo
    WHERE geom_reproj IS NOT NULL and
    ref_sta_id in(0,4) 
     ),
-- SELECT count(*) FROM SELECTionne_roe -- 88743 rows
hauteur_chute_roe AS (
      SELECT ouv_id, dbeel_op_id, ref_hauteur_chute_etiage_classe, ref_hauteur_chute_etiage 
        AS h_chute_roe, obstruction_type_no_id  
      FROM selectionne_roe
      ),
--SELECT * FROM hauteur_chute_roe
height AS (
     SELECT  
     coalesce(hauteur_chute_bdoe.ouv_id,hauteur_chute_ice.ouv_id,hauteur_chute_roe.ouv_id) AS ouv_id,
     dbeel_op_id,
     obstruction_type_no_id,
     round(
     CASE 
     WHEN hauteur_chute_ice is NOT NULL THEN h_chute_ice -- ICE first
     WHEN hauteur_chute_ice IS NULL and h_chute_bdoe is NOT NULL  THEN h_chute_bdoe -- BDOe then
     WHEN h_chute_bdoe is NULL and hauteur_chute_ice is NULL AND h_chute_roe IS NOT NULL 
            THEN h_chute_roe::numeric -- ROE then
     WHEN h_chute_bdoe IS NULL AND h_chute_roe IS NULL and hauteur_chute_ice is NULL 
            AND ref_hauteur_chute_etiage_classe='INDETERMINEE' THEN NULL 
     WHEN h_chute_bdoe IS NULL AND h_chute_roe IS NULL and hauteur_chute_ice is NULL 
          AND ref_hauteur_chute_etiage_classe='INFERIEURE A 0,5m' THEN 0.25 
          -- single values class <0.5
     WHEN h_chute_bdoe IS NULL AND h_chute_roe IS NULL and hauteur_chute_ice is NULL 
          AND ref_hauteur_chute_etiage_classe='DE 0,5m A INFERIEURE A 1m' THEN 0.75
     WHEN h_chute_bdoe IS NULL AND h_chute_roe IS NULL and hauteur_chute_ice is NULL 
          AND ref_hauteur_chute_etiage_classe='DE 1m A INFERIEURE A 1,5m' THEN 1.25
     WHEN h_chute_bdoe IS NULL AND h_chute_roe IS NULL and hauteur_chute_ice is NULL 
            AND ref_hauteur_chute_etiage_classe='DE 1,5m A INFERIEURE A 2m' THEN 1.75
     WHEN h_chute_bdoe IS NULL AND h_chute_roe IS NULL and hauteur_chute_ice is NULL 
            AND ref_hauteur_chute_etiage_classe='DE 2m A INFERIEURE A 3m' THEN 2.5     
     WHEN h_chute_bdoe IS NULL AND h_chute_roe IS NULL and hauteur_chute_ice is NULL 
            AND ref_hauteur_chute_etiage_classe='DE 3m A INFERIEURE A 5m' THEN 4
     WHEN h_chute_bdoe IS NULL AND h_chute_roe IS NULL and hauteur_chute_ice is NULL 
            AND ref_hauteur_chute_etiage_classe='DE 5m A INFERIEURE A 10m' THEN 7.5
     WHEN h_chute_bdoe IS NULL AND h_chute_roe IS NULL and hauteur_chute_ice is NULL 
            AND ref_hauteur_chute_etiage_classe='SUPERIEURE OU EGALE A 10m' THEN 20
     ELSE NULL  
     END,2) AS hauteur_chute,    
     CASE 
     WHEN hauteur_chute_ice is NOT NULL THEN 'Choix ice (1)' 
     -- ICE first (because otherwise we don't have any)
     WHEN hauteur_chute_ice IS NULL and h_chute_bdoe is NOT NULL  THEN  'Choix bdoe (2)' -- BDOe then
     WHEN h_chute_bdoe is NULL and hauteur_chute_ice is NULL AND h_chute_roe IS NOT NULL THEN 'Choix roe (3)' 
     -- Extremely rare
     WHEN h_chute_bdoe IS NULL AND h_chute_roe IS NULL and hauteur_chute_ice is NULL 
          AND ref_hauteur_chute_etiage_classe IS NOT NULL 
          AND ref_hauteur_chute_etiage_classe!='INDETERMINEE' THEN 'Choix classe (4)' -- never
     ELSE 'Pas de hauteur'  
     END  AS commentaire_hauteur_chute  
     FROM hauteur_chute_roe 
     LEFT JOIN  hauteur_chute_ice on hauteur_chute_roe.ouv_id=hauteur_chute_ice.ouv_id 
     LEFT JOIN hauteur_chute_bdoe On hauteur_chute_bdoe.ouv_id= hauteur_chute_roe.ouv_id
   -- WHERE coalesce(h_chute_roe, hauteur_chute_ice, h_chute_bdoe) IS NOT NULL 
   --     AND ref_hauteur_chute_etiage_classe IS NOT NULL 
        ),
--SELECT count(*) FROM  height; -- 88743 rows

After applying the decision rule, the drop height data is imported as follows:

type_height n
Height:Pas de hauteur, Height NULL in bdoe, correc 2
Height:Pas de hauteur 28341
Height:Choix bdoe (2) 45697
Height:Choix ice (1) 3187
Height:Choix roe (3) 131
Height:Total 84272
Height:Choix classe (4) 6914

2.3 Importing the fishway types in France

2.3.1 ICE

CE Pass List A priori used by eels
Pool type fishway Yes
Artificial river Yes
Ascenseur No
Fish lift No
Rock ramp Yes
Fish lock Yes
Pool type fishway Yes
Eel ramp Yes
Unknown No

It is possible to have several passes at a single obstacle. To deal with these cases, the types of passes will be ordered (according to their possible passability by eels). At an obstacle with several passes, only the first type of pass encountered (according to the order below) will be kept.

  1. Artificial river

  2. Rock ramp

  3. Eel ramp

  4. Pool type fishway

  5. Pool type fishway

  6. Fish lock

  7. Fish lift

  8. Denil pass

  9. Unknown

An additional ‘Eel pass’ table has been created to detail whether a fishway can be passed by eels or not. Obstacles with a passable fishway can be latter ignored depending on scenario.

2.3.2 ROE

The same process as for ICE is carried out, but the types of fish passes are not exactly the same.

ROE Pass List A priori used by eels
No pass No
Fish lift No
Pool type fishway Yes
Fish lock Yes
Downstream migration pathway No
Eel ramp Yes
Fish lift No
Ramp Yes
Artificial river Yes
Other type of pass No
Unknown No

  1. Artificial river

  2. Rock ramp

  3. Eel ramp

  4. Pool type fishway

  5. Pool type fishway

  6. Fish lock

  7. Fish lift

  8. Denil pass

  9. Unknown

It happens that the passes are not of the same type in ICE and ROE, 11 cases, with 7 cases a conflict on passability:

  • in 2 cases, the pool type fishway classified as another type in the ROE and therefore considered as not passable

  • 1 pool type fishway classified as a Denil pass in ROE

  • 1 rock ramp classified as Denil pass in ROE

  • 1 artificial river classified as unknown in ROE

  • 1 rock ramp classified as unknown in ROE

  • 1 Denil pass classified as a pool type fishway in ROE

The types of passes present in ROE and ICE will then be reassigned to the different pass codes present in the SUDOANG database.

First the ICE passes are taken, then the ROE passes (if no pass in ICE).

ice_presence_eel_pass AS(
-- The first of the passes is picked up 
SELECT  distinct on (ouv_id) 
    ouv_id,
    CASE WHEN dfp_tdfp_id in (1,2,5,6,7,8) THEN TRUE ELSE FALSE END AS po_presence_eel_pass,
    -- Several passes can exist at the same time, in this case they are taken in the following order:
    CASE WHEN dfp_tdfp_id = 2 THEN 278
        WHEN dfp_tdfp_id = 5 THEN 275
        WHEN dfp_tdfp_id = 8 THEN 276
        WHEN dfp_tdfp_id = 1 THEN 271
        WHEN dfp_tdfp_id = 7 THEN 271
        WHEN dfp_tdfp_id = 6 THEN 272
        WHEN dfp_tdfp_id = 3 THEN 274       
        WHEN dfp_tdfp_id = 4 THEN 273
        WHEN dfp_tdfp_id = 9 THEN 279
        ELSE NULL END AS fishway_type   
    FROM ice.obstacle_operation_ice 
    JOIN ice.operation_ice on opi_ooi_id=ooi_id 
    JOIN ice.partie_obstacle ON pao_opi_id=opi_id 
    JOIN ice.structure_obstacle on sto_pao_id=pao_id
        JOIN ice.dispositif_franchissement_piscicole  on sto_id= dfp_id 
         JOIN hauteur_chute_roe hh on hh.ouv_id=ooi_ref_id
        ),
--SELECT * FROM ice_presence_eel_pass -- 125 rows
union_roe_pass AS (
          SELECT fty_code::numeric AS cdtypedisp, 
          fpi_ref_id AS ouv_id
          FROM roe.franchissement_piscicole 
          join roe.franchissement_piscicole_type on fpi_fty_id=fty_id
      ),
roe_presence_eel_pass AS (
    SELECT DISTINCT on (ouv_id)
    ouv_id,
    CASE WHEN cdtypedisp in (2,3,5,7,8,9) THEN TRUE ELSE FALSE END AS po_presence_eel_pass,
    -- Several passes can exist at the same time, in this case they are taken in the following order:
    CASE WHEN cdtypedisp = 9 THEN 278
        WHEN cdtypedisp = 8 THEN 275
        WHEN cdtypedisp = 5 THEN 276
        WHEN cdtypedisp = 2 THEN 271
        WHEN cdtypedisp = 7 THEN 271
        WHEN cdtypedisp = 3 THEN 272
        WHEN cdtypedisp = 6 THEN 274        
        WHEN cdtypedisp = 1 THEN 273
        WHEN cdtypedisp in (10,11) THEN 279
        ELSE NULL -- type 0 no pass
        END AS fishway_type 
    FROM union_roe_pass
    ),

/*
SELECT * FROM roe_presence_eel_pass 
JOIN 
ice_presence_eel_pass on roe_presence_eel_pass.ouv_id=ice_presence_eel_pass.ouv_id WHERE 
roe_presence_eel_pass.fishway_type!=ice_presence_eel_pass.fishway_type

The comparison roe and ice was looked at, some passes are NOT of the same type, 11 cases, 
  in 7 cases a conflict on passability exists.
In 2 cases, pool type fishway classified as another type in ROE and therefore considered as not passable, 
  1 denil type, 1 rock ramp Denil pass
1 artificial river and unknown, 1 rock ramp unknown, the last Denil pool type fishway.
We always consider ICE is better !
*/

presence_eel_pass AS (
    SELECT coalesce(ice.ouv_id,roe.ouv_id) AS ouv_id,
    CASE WHEN ice.fishway_type is not NULL THEN ice.fishway_type else roe.fishway_type END AS fishway_type,
    CASE WHEN ice.po_presence_eel_pass is not NULL THEN ice.po_presence_eel_pass else roe.po_presence_eel_pass END AS po_presence_eel_pass  
       FROM roe_presence_eel_pass roe FULL OUTER JOIN ice_presence_eel_pass ice on roe.ouv_id=ice.ouv_id
       ),

--SELECT * FROM presence_eel_pass -- 58754 rows
--SELECT DISTINCT on (ouv_id) * FROM presence_eel_pass
/*
No more need because we release the freezes at the beginning of the script (they aren't in the start of the DBEEL database, so they won't be in the join)
presence_eel_pass_no_gele AS (
    SELECT pep.* FROM presence_eel_pass pep JOIN
    hauteur_chute_roe_with_null hh on hh.ouv_id=pep.ouv_id
), 
*/

2.4 Expert assessment

2.4.1 Expert assessment on downstream passability

The main types of facilities can improve downstream passage: fish-friendly turbines, water intake equipment and fish adapted management. It is possible to have several facilities for the same obstacle. Here, we keep only one the first of:

  1. Fish-friendly turbine

  2. Water intake equipment

  3. Fish adapted management

Only the first of these modalities met will be retained.

expertise_devalaison_tous AS (
SELECT dou.* FROM bdoe.devalaison_ouvrage dou
JOIN    hauteur_chute_roe hh on hh.ouv_id=dou.doe_ouv_id
ORDER BY CASE WHEN doe_mcd_id=3 THEN 1
      WHEN doe_mcd_id=2 THEN 2
      WHEN doe_mcd_id=1 THEN 3
      END
      ), -- 126 rows
expertise_devalaison AS (
SELECT DISTINCT on (doe_ouv_id) doe_ouv_id AS ouv_id, 
    CASE WHEN doe_mcd_id =3 THEN 283  -- Fish-friendly turbine
    WHEN doe_mcd_id=2 THEN 282        -- Water intake equipment
    WHEN doe_mcd_id=1 THEN 284        -- Fish adapted management
    END AS mitigationmeasureid
FROM expertise_devalaison_tous  
), -- 119 rows

2.4.2 Expert assessment on upstream migration

2.4.2.1 ICE

There are three modalities of obstacle passability in ICE:

  • 0 Impassable obstacle

  • 0.33 Partial obstacle with major impact

  • 0.66 Passable obstacle with significant impact

  • 1 Passable obstacle with limited impact

When different routes in an obstacle have an ICE score, the least penalizing score is selected, with no choice of date (there are only 15 duplicates) and for both glass eels and eels. The passability note of glass eels is only taken into account at less than 50 km from the sea. If at this distance, the obstacle is assessed as being passable for eel or glass eel, we consider it passable. For obstacles located more than 50 km from the sea, only eel expertise will be taken into account. If the date of the assessment is not available, we take the date of creation of the ICE record. An obstacle is considered as passable by eels if classified as “Passable obstacle with limited impact”.

2.4.2.2 BDOE

There are six passability modalities in BDOe:

  1. Absence of obstacles

  2. Passable without apparent difficulty

  3. Passable but with risk of impact

  4. Difficult to pass

  5. Very difficult to pass

  6. Impassable

If there have been different expertises carried out, the most recent one is taken. For those for which no date is available, the date ‘1900-01-01’ has been arbitrarily used. The two modalities that will be estimated as passable are: “No obstacles” and “Passable without apparent difficulty”.

If there is a BDOe note and an ICE note the most recent dignostic is retained.

-- ICE PASSABILITY
-- In the ICE the least penalizing expertise note is taken, without choice of date, 
--    and for both glass eels and eels
-- To be done later, the glass eel passability note will only be taken into account at less 
--    than 50 km from the sea (check)
notes_expertise AS (
SELECT calcul_ice.*,
ooi_ref_id,
CASE WHEN opi_date_releve is NULL THEN opi_date_creation::date 
else opi_date_releve END AS opi_date_releve,
 cli_code, 
 cli_libelle,
 coalesce(ouv_lineaire_aval,9999) AS ouv_lineaire_aval -- if there is no value, is not calculated
FROM ice.obstacle_operation_ice JOIN ice.operation_ice on opi_ooi_id=ooi_id 
JOIN ice.partie_obstacle ON pao_opi_id=opi_id 
JOIN ice.structure_obstacle on sto_pao_id=pao_id 
JOIN ice.calcul_ice ON sto_id=cai_sto_id
join ice.classe_ice on calcul_ice.cai_cli_id=classe_ice.cli_id 
LEFT JOIN bdoe.ouvrage on ouvrage.ouv_id=ooi_ref_id
WHERE cai_gre_id in (19,20)
ORDER BY ooi_ref_id,cai_gre_id,opi_date_releve),
--select count(ooi_ref_id) OVER( PARTITION BY ooi_ref_id), *
-- FROM notes_expertise ORDER BY count desc, cai_gre_id, cli_code desc);  -- 11221 rows
union_civ_ang AS (
SELECT DISTINCT on (ooi_ref_id) * FROM notes_expertise WHERE cai_gre_id = 20 and ouv_lineaire_aval <50
-- Selecting notes for glasss eel only when < 50 km
UNION
SELECT DISTINCT on (ooi_ref_id) * FROM notes_expertise WHERE cai_gre_id = 19),
-- If one of the two notes is passable, it is considered passable.
ice_both_stages AS (
SELECT  * FROM union_civ_ang WHERE cli_code NOT IN ('DNA','NC','DA') ORDER BY ooi_ref_id,  cli_code desc), 
-- 1 is taken first, then 0.66 then 0.33 

ice_unique AS (
SELECT DISTINCT on (ooi_ref_id) * FROM ice_both_stages), -- the least penalizing is taken

-- ROE PASSABILITY
--- NO DATA

-- BDOe PASSABILITY
-- Final selection, as for ICE, the least penalizing note is taken 
--    (the date is not taken because there were only 15 duplicates).
-- Obstacles with zero and 1 are considered passable.
orderedbdoe AS (
SELECT exm_ouv_id, exm_not_id,  
coalesce (exm_date, '1900-01-01') AS exm_date
FROM bdoe.expertise_montaison WHERE exm_esp_id=5 ORDER BY exm_not_id ASC),
distinctbdoe AS (
SELECT distinct on (exm_ouv_id) * FROM orderedbdoe ),-- 7645 rows

decision_note AS (
    SELECT
    exm_ouv_id,
    exm_date,
-- Regarding the date, the most recent one is taken
--    when we have both an ICE and a BDOe, and the date of ICE is more recent than the date of bdoe 
--    (or dboe is null and then we put 1900)
 CASE WHEN exm_ouv_id is not null and ooi_ref_id is NOT NULL AND exm_date < opi_date_releve THEN NULL
            ELSE exm_not_id END AS exm_not_id,
       CASE WHEN exm_ouv_id is not null and ooi_ref_id is NOT NULL AND  opi_date_releve< exm_date THEN NULL
            ELSE cli_code END AS cli_code,            
            ooi_ref_id,
            opi_date_releve
-- then the BDOe note is removed
-- From now on we have either one or the other in two separate columns, 
--    so in the following CASE WHEN we'll take
--    always either BDOE or ICE
 FROM distinctbdoe FULL OUTER JOIN ice_unique on exm_ouv_id=ooi_ref_id), --10918
 
 --SELECT * FROM decision_note WHERE exm_ouv_id is not null and ooi_ref_id is NOT NULL 
final_franch AS( 
SELECT  COALESCE(exm_ouv_id,ooi_ref_id) AS ouv_id,
CASE WHEN exm_not_id=0 THEN 209
         WHEN exm_not_id=1 THEN 210
         WHEN exm_not_id=2 THEN 211 
         WHEN exm_not_id=3 THEN 212     
         WHEN exm_not_id=4 THEN 213     
         WHEN exm_not_id=5 THEN 214
         WHEN cli_code='1' THEN 287
         WHEN cli_code='0.66' THEN 288
         WHEN cli_code='0.33' THEN 289
         WHEN cli_code='0' THEN 290
         END AS po_no_obstruction_passability,
CASE WHEN coalesce(exm_date,opi_date_releve)  = '1900-01-01' 
       THEN NULL ELSE COALESCE(exm_ouv_id,ooi_ref_id) END AS exm_date
FROM decision_note), -- 10879 rows

2.4.2.3 Downstream reception problem

reception_devalaison_tous AS (
    SELECT ooi_ref_id AS ouv_id, 
    TRUE AS po_downs_pb
    FROM ice.obstacle_operation_ice 
    JOIN ice.operation_ice on opi_ooi_id=ooi_id 
    JOIN ice.probleme_reception_devalaison_operation_ice ON  prdoi_opi_id=opi_id
    JOIN hauteur_chute_roe hh on hh.ouv_id=ooi_ref_id -- to remove frozen codes and other
    ORDER BY ouv_id, coalesce(opi_date_releve,'1900-01-01') desc
    ),
    
reception_devalaison AS (SELECT DISTINCT on (ouv_id) * FROM reception_devalaison_tous),

Only two possibilities: True ou False.

2.5 Types of obstructions

The script inserts the obstruction types from the ROE types and subtypes:

selectionne_roe AS (    
    SELECT regexp_replace(ref_id_national, 'ROE', '')::integer AS ouv_id,
    CASE WHEN ref_typ_id = 0 THEN 291                                           -- Dam
         WHEN ref_typ_id = 1 AND (ref_sty_id=10 OR ref_sty_id=11) THEN 292      -- Weir
         WHEN ref_typ_id = 1 AND (ref_sty_id IS NULL) THEN 292                  -- Weir
         WHEN ref_typ_id = 1 AND ref_sty_id=12 THEN 293                         -- Rock ramp
         WHEN ref_typ_id = 2 THEN 298                                           -- Dike
         WHEN ref_typ_id = 3 AND (ref_sty_id=17 OR ref_sty_id=21) THEN 296      -- Bridge
         WHEN ref_typ_id = 3 AND (ref_sty_id IS NULL) THEN 296                  -- Bridge
         WHEN ref_typ_id = 3 AND ref_sty_id = 20 THEN 295                       -- Ford
         WHEN ref_typ_id = 3 AND ref_sty_id = 18 THEN 294                       -- Culvert
         WHEN ref_sty_id = 9 OR  ref_sty_id = 19 OR ref_sty_id=13 THEN 297      -- Other
         WHEN ref_typ_id = 5 THEN 299                                           -- Grid
         ELSE 219                                                               -- Unknown
         END AS obstruction_type_no_id,
    * 
    FROM roe.roesudo
    WHERE geom_reproj IS NOT NULL and
    ref_sta_id in(0,4) 
     ),
-- SELECT count(*) FROM selectionne_roe -- 88743 rows

SUDOANG types are defined here:

# This is just to load the method
require(kableExtra)
rios <- new('BaseEdaRiosRiversegments',
        baseODBC = "baseODBCrios",
        schema = "dbeel_rivers",        
        table = "rn",
        prkey = "idsegment")
# object <- rios
sql="SELECT * FROM dbeel_nomenclature.obstruction_type limit 10;"
pretty_table(rios, sql)%>%  
    kable_styling(bootstrap_options = c("striped", "hover",
                "condensed"))
no_id no_code no_type no_name
218 UN Obstruction type Unknown
291 DA Obstruction_type Dam
292 WE Obstruction_type Weir
293 RR Obstruction_type Rock ramp
294 CU Obstruction_type Culvert
295 FO Obstruction_type Ford
296 BR Obstruction_type Bridge
297 OT Obstruction_type Other
298 DI Obstruction_type Dike
299 GR Obstruction_type Grid

2.6 Importing water intake data

Regarding the spacing between grids, the information can be found in both BDOe and ICE. If available, values from ICE are kept in priority rather than values from the BDOe. The same is true for maximum turbine flow, instream flow.

The ICE data are first integrated in the following steps:

WITH roe_usage_hydro AS(
SELECT DISTINCT 'ROE'||usa_obs_id AS ouv_id
        FROM  roe.usage  
        WHERE usa_uty_id=5
        EXCEPT
        SELECT DISTINCT 'ROE'||usa_obs_id AS ouv_id
        FROM  roe.usage  
        WHERE usa_uty_id=12 -- obsolete
        ), -- 5406 rows
        
bdoe_usage_hydro AS (
    SELECT bdoe.ouvrage.* FROM bdoe.ouvrage 
    JOIN  roe_usage_hydro on roe_usage_hydro.ouv_id='ROE'||ouvrage.ouv_id
    ),
    
--SELECT * FROM bdoe_usage_hydro -- 5326 rows
prise_eau_ice AS (
SELECT * FROM ice.obstacle_operation_ice 
    JOIN ice.operation_ice on opi_ooi_id=ooi_id 
    JOIN ice.partie_obstacle ON pao_opi_id=opi_id 
    JOIN ice.structure_obstacle on sto_pao_id=pao_id
        JOIN ice.prise_eau  on sto_id= pre_id
        ORDER BY opi_date_releve DESC
        ),
prise_eau_ice_unique AS (
    SELECT distinct ON (ooi_id) * FROM  prise_eau_ice
    ),
    
--SELECT * FROM prise_eau_ice_unique -- 562 rows

bdoe_ice_sudoang AS (  
    SELECT * 
    FROM sudoang.dbeel_obstruction_place 
    JOIN roe.roesudo on dbeel_op_id = op_id
    JOIN sudoang.dbeel_physical_obstruction On ob_op_id=op_id
    JOIN prise_eau_ice_unique on ref_id_national='ROE'||ooi_ref_id
    LEFT JOIN bdoe_usage_hydro on 'ROE'||bdoe_usage_hydro.ouv_id=ref_id_national 
         ),
-- SELECT count(*) FROM bdoe_ice_sudoang WHERE (pre_id is not NULL OR usa_uty_id=5); -- 4727 rows
-- SELECT count(*) FROM bdoe_ice_sudoang WHERE (pre_id is not NULL ); -- 505 rows (after joint SUDOANG)
-- SELECT count(*) FROM bdoe_ice_sudoang WHERE usa_uty_id=5; -- 4462 rows (ROE source, after joint SUDOANG) 
--SELECT * FROM bdoe_ice_sudoang limit 10;
union_roe_pass AS (
          SELECT fty_code::numeric AS cdtypedisp, 
          fpi_ref_id AS ouv_id
          FROM roe.franchissement_piscicole 
          join roe.franchissement_piscicole_type on fpi_fty_id=fty_id
          WHERE fty_code='4' -- 365 rows
          and 'ROE'||fpi_ref_id in (SELECT ref_id_national FROM bdoe_ice_sudoang)          
      ),
      
roe_presence_bypass AS (
    SELECT DISTINCT on (ouv_id)
    ouv_id,
    TRUE AS hpp_presence_bypass
    FROM union_roe_pass     
),  
 
--SELECT * FROM roe_presence_bypass; -- 261 rows 

angle_between_0_and_90 AS (
SELECT CASE WHEN pre_seuil_barrage_angle >90 THEN pre_seuil_barrage_angle - 90 
else pre_seuil_barrage_angle  end AS pre_seuil_barrage_angle_recode ,
*
FROM bdoe_ice_sudoang),

recode_angle AS (
SELECT 
CASE WHEN pre_seuil_barrage_angle_recode <30 THEN 241                                           -- <30º
     WHEN pre_seuil_barrage_angle_recode >=30 AND pre_seuil_barrage_angle_recode <50 THEN 240   -- [30-50º[
     WHEN pre_seuil_barrage_angle_recode >=50 AND pre_seuil_barrage_angle_recode <70 THEN 239   -- [50-70º[
     WHEN pre_seuil_barrage_angle_recode >=70 AND pre_seuil_barrage_angle_recode <=90 THEN 238  -- [70-90º] 
     END AS hpp_orient_flow_no_id,
     *
FROM angle_between_0_and_90),
-- First ICE is taken, then BDOe
/* #TODO
Dans le Tableau ice il y a une partie ouvrage en dérivation, elle est appelée pre_derivation_debit mais c'est bien le débit réservé qui passe
dans l'ouvrage en dérivation, même si la representation de l'ouvrage principal comme un ouvrage en dérivation
ne correspond pas à la réalité terrain (pour les migrations).
*/
choix_bdoe_ice AS (
SELECT CASE WHEN pre_sppe_entrefer is NOT NULL THEN pre_sppe_entrefer
ELSE ouv_ecartement_grille -- Thi one's from BDOe
END AS pre_sppe_entrefer_final,
CASE WHEN pre_debit_maximum is NOT NULL THEN pre_debit_maximum
ELSE ouv_debit_maximal --  BDOe
END AS pre_debit_maximum_final,
CASE WHEN pre_derivation_debit is NOT NULL THEN  pre_derivation_debit
ELSE ouv_debit_reserve --  BDOe
END AS pre_derivation_debit_final,
CASE WHEN pre_debit_devalaison is NOT NULL THEN pre_debit_devalaison
ELSE ouv_debit_devalaison --  BDOe
END AS pre_debit_devalaison_final,
*
FROM
recode_angle),
--SELECT * FROM choix_bdoe_ice limit 10;
final_insert AS (
SELECT 
  uuid_generate_v4() AS hpp_id,
  choix_bdoe_ice.ob_id AS hpp_ob_id,
  ref_nom AS hpp_name,
  NULL::integer hpp_main_grid_or_production , 
  roe_presence_bypass.hpp_presence_bypass AS hpp_presence_bypass, 
  NULL::numeric AS hpp_total_flow_bypass, 
  hpp_orient_flow_no_id,
  pre_existe_systeme_protection AS hpp_presence_of_bar_rack,
  choix_bdoe_ice.pre_sppe_entrefer_final AS hpp_bar_rack_space ,
  pre_lpg*pre_ltg AS hpp_surface_bar_rack,
  pre_b AS hpp_inclination_bar_rack,
  pre_a AS hpp_orientation_bar_rack, 
  pre_existence_exutoire AS hpp_presence_bypass_trAShrack,
  pre_nombre_exutoire AS hpp_nb_trAShrack_bypass,
  choix_bdoe_ice.pre_debit_maximum_final AS hpp_turb_max_flow,
  choix_bdoe_ice.pre_derivation_debit_final AS hpp_reserved_flow, 
  -- Yes, it's not a mistake, see explanation below
  choix_bdoe_ice.pre_debit_devalaison_final AS hpp_flow_trAShrack_bypass,
  choix_bdoe_ice.pre_puissance_amenagement AS hpp_max_power,
  'ROE'||pre_id AS hpp_id_original,
  'ICE' AS hpp_source
  FROM choix_bdoe_ice 
  FULL OUTER JOIN roe_presence_bypass on 'ROE'||roe_presence_bypass.ouv_id=id_original)
INSERT INTO sudoang.dbeel_hpp
  (hpp_id ,
  hpp_ob_id ,
  hpp_name ,
  hpp_main_grid_or_production ,
  hpp_presence_bypass,
  hpp_total_flow_bypass , 
  hpp_orient_flow_no_id,
  hpp_presence_of_bar_rack,
  hpp_bar_rack_space,
  hpp_surface_bar_rack,
  hpp_inclination_bar_rack,
  hpp_orientation_bar_rack,
  hpp_presence_bypass_trAShrack,
  hpp_nb_trAShrack_bypass,
  hpp_turb_max_flow,
  hpp_reserved_flow,
  hpp_flow_trAShrack_bypass,
  hpp_max_power, 
  hpp_id_original,
  hpp_source) SELECT * FROM final_insert
; -- 505 rows => 528 rows (after correcting for missing geom bd_carthage)

In a second step, the data on hydropower plants from ROE obstacles are integrated. One problem is that the obstacles referred as “water intake” is generally distinct from the obstacle referred as “hydropower use”, therefore, it is necessary to search for all the obstacles related to water intakes already entered using a recursive function, and remove them from the selection.

/* This table corresponds to obstacles having a hydropower use which aren't also obsolete in the use 
 * which aren't also obsolete in the use
 * and which are inserted in the table of obstacles (dbeel_physical_obstruction)
 */
DROP TABLE IF EXISTS france.ouvrages_inseres_hpp;
CREATE TABLE france.ouvrages_inseres_hpp AS (
WITH roe_usage_hydro AS(
    SELECT DISTINCT 'ROE'||usa_obs_id AS ouv_id
        FROM  roe.usage  
        WHERE usa_uty_id=5
        EXCEPT
        SELECT DISTINCT 'ROE'||usa_obs_id AS ouv_id
        FROM  roe.usage  
        WHERE usa_uty_id=12 -- obsolete
        ),
--SELECT count(*) FROM roe_usage_hydro; -- 5393 rows
roe_sudoang AS (  
    SELECT * 
    FROM sudoang.dbeel_obstruction_place 
    Join roe.roesudo on dbeel_op_id = op_id
    JOIn sudoang.dbeel_physical_obstruction On ob_op_id=op_id
    JOIN roe_usage_hydro on ref_id_national=roe_usage_hydro.ouv_id
         )

SELECT id_original AS ouv_id, geom_reproj AS geom FROM roe_sudoang  ); -- 469rows

/* This table corresponds to obstacles having a hydropower use
 * but which have not been imported into the DBEEL's obstacle table, either because
 * they are obsolete or destroyed or bad type 
 */

DROP TABLE IF EXISTS france.ouvrages_missing_hpp;
CREATE TABLE france.ouvrages_missing_hpp AS (
 WITH roe_usage_hydro AS(
    SELECT DISTINCT 'ROE'||usa_obs_id AS ouv_id
        FROM  roe.usage  
        WHERE usa_uty_id=5
        EXCEPT
        SELECT DISTINCT 'ROE'||usa_obs_id AS ouv_id
        FROM  roe.usage  
        WHERE usa_uty_id=12 -- obsolete
        ), -- 5406 rows
 -- filter using an except cause       
 roe_filtered AS (
   SELECT ouv_id,
   st_transform(ref_position, 3035) AS geom
   FROM roe_usage_hydro JOIN roe.obstacle_referentiel on ouv_id=ref_id_national),
  --SELECT * FROM roe_filtered;
  missing_dams AS (
  SELECT ouv_id FROM roe_filtered 
  EXCEPT
  SELECT ouv_id FROM france.ouvrages_inseres_hpp)
  --SELECT * FROm missing_dams;
  -- 696 rows

  SELECT * FROM roe_filtered WHERE ouv_id IN (SELECT ouv_id FROM missing_dams)
  ); -- 696 rows
  
/*
 * View of obstacles inserted from ICE (which have a description of the water intake and the turbines)
 */
  
DROP MATERIALIZED VIEW if EXISTS sudoang.view_dbeel_hpp;
CREATE MATERIALIZED VIEW sudoang.view_dbeel_hpp AS (
SELECT row_number() OVER (PARTITION BY true) AS id, * FROM sudoang.dbeel_obstruction_place
    JOIN sudoang.dbeel_physical_obstruction on op_id=ob_op_id 
    JOIN sudoang.dbeel_hpp on hpp_ob_id=ob_id
   --WHERE hpp_source='ICE' 
   ); -- 1532 rows

--SELECT * FROM sudoang.view_dbeel_hpp LIMIT 10; 

/* INSERTION OF MISSING OBSTACLES:
 * They must have a hydropower use, which have been imported into the physical_obstruction table,
 * and that they are not already imported
 * and that there are no related obstacles in the already imported ones.
 */

-- linked dam returns a recursive with all the links  
-- union ALL does not remove repeated records as UNION
-- but the problem remains because the path will never be the same
-- Stop as soon as a gid is already in path (id_original=ANY(chemin) =TRUE for loop)
WITH RECURSIVE linkeddams(op_id,op_op_id,profondeur,chemin,boucle) AS (
    SELECT op_id, op_op_id,1, 
    ARRAY[id_original::TEXT] AS chemin,
    FALSE AS boucle
    FROM sudoang.dbeel_obstruction_place 
    WHERE op_op_id IS NOT NULL
  UNION ALL
    SELECT * FROM (
    SELECT o.op_id,o.op_op_id, l.profondeur+1,
    array_append(chemin,id_original::TEXT) AS chemin,
    id_original::TEXT = ANY(chemin) AS boucle
    FROM linkeddams l JOIN
    sudoang.dbeel_obstruction_place o ON l.op_id=o.op_op_id) sub
    WHERE NOT sub.boucle
),
--SELECT * FROM linkeddams WHERE profondeur>1
dams_already_integrated AS (
    SELECT ouv_id FROM france.ouvrages_inseres_hpp JOIN
    sudoang.view_dbeel_hpp ON id_original=ouv_id
),
--SELECT * FROM dams_already_integrated;
dams_with_ancestor AS (
    SELECT ouv_id FROM france.ouvrages_inseres_hpp 
    JOIN linkeddams  ON ouv_id=ANY(chemin)
),
--SELECT * FROM dams_with_ancestor),
--SELECT * FROM dams_with_ancestor;
-- Finally we insert dams that are not linked in anyway with other hpp dam either 
-- if they have been already inserted or they are linked (even degree 3) to a 
-- dam that was already inserted
ouv_id_to_insert AS (
    SELECT ouv_id FROM france.ouvrages_inseres_hpp
    EXCEPT (
    SELECT ouv_id FROM dams_with_ancestor
    UNION
    SELECT ouv_id FROM dams_already_integrated
)),
--SELECT count(*) FROM ouv_id_to_insert 
-- 3725 rows (Since 5393 already inserted obstacles with hydroelectric use have been removed)
bdoe_sudoang AS (  
    SELECT * 
    FROM sudoang.dbeel_obstruction_place 
    JOIN roe.roesudo on dbeel_op_id = op_id
    JOIN sudoang.dbeel_physical_obstruction On ob_op_id=op_id
    LEFT JOIN bdoe.ouvrage ON 'ROE'||ouvrage.ouv_id=ref_id_national 
         ),
union_roe_pass AS (
  SELECT fty_code::numeric AS cdtypedisp, 
  fpi_ref_id AS ouv_id
  FROM roe.franchissement_piscicole 
  join roe.franchissement_piscicole_type on fpi_fty_id=fty_id
  WHERE fty_code='4'),    
roe_presence_bypass AS (
    SELECT DISTINCT on (ouv_id)
    ouv_id,
    TRUE AS hpp_presence_bypass
    FROM union_roe_pass     
)

INSERT INTO sudoang.dbeel_hpp
  (hpp_id ,
  hpp_ob_id ,
  hpp_name,
  hpp_main_grid_or_production ,
  hpp_presence_bypass,
  hpp_total_flow_bypass , 
  hpp_orient_flow_no_id,
  hpp_presence_of_bar_rack,
  hpp_bar_rack_space,
  hpp_surface_bar_rack,
  hpp_inclination_bar_rack,
  hpp_orientation_bar_rack,
  hpp_presence_bypass_trAShrack,
  hpp_nb_trAShrack_bypass,
  hpp_turb_max_flow,
  hpp_reserved_flow,
  hpp_flow_trAShrack_bypass,
  hpp_max_power, 
  hpp_id_original,
   hpp_source) 

SELECT 
  uuid_generate_v4() AS hpp_id,
  p.ob_id AS hpp_ob_id,
  r.ref_nom AS hpp_name,
  NULL hpp_main_grid_or_production , 
  rp.hpp_presence_bypass AS hpp_presence_bypass, 
  NULL AS hpp_total_flow_bypass, 
  NULL AS hpp_orient_flow_no_id,
  NULL AS hpp_presence_of_bar_rack,
  bdoe_sudoang.ouv_ecartement_grille AS hpp_bar_rack_space ,
  NULL AS  hpp_surface_bar_rack,
  NULL AS  hpp_inclination_bar_rack,
  NULL AS  hpp_orientation_bar_rack, 
  NULL AS  hpp_presence_bypass_trAShrack,
  NULL AS  hpp_nb_trAShrack_bypass,
  bdoe_sudoang.ouv_debit_maximal AS hpp_turb_max_flow,
  bdoe_sudoang.ouv_debit_reserve AS hpp_reserved_flow, -- yes, it's not a mistake see explanation below
  bdoe_sudoang.ouv_debit_devalaison AS hpp_flow_trAShrack_bypass,
  NULL AS hpp_max_power,
  ouv_id_to_insert.ouv_id AS hpp_id_original,
  'ROE' AS hpp_source
  FROM ouv_id_to_insert
  JOIN sudoang.dbeel_obstruction_place o ON ouv_id_to_insert.ouv_id=id_original
  JOIN sudoang.dbeel_physical_obstruction p ON ob_op_id=op_id
  JOIN roe.roesudo r ON ref_id_national=ouv_id_to_insert.ouv_id
  LEFT JOIN bdoe_sudoang ON 'ROE'||bdoe_sudoang.ouv_id=ouv_id_to_insert.ouv_id 
  -- With a joint there are 3714 obstacles, so there are less obstacles in BDOe?
  LEFT JOIN roe_presence_bypass rp ON 'ROE'||rp.ouv_id=ouv_id_to_insert.ouv_id; -- 3725 rows

/*
THIS VIEW MUST BE RELAUNCHED NOW WE HAVE INTEGRATED NEW DATA
*/
DROP MATERIALIZED VIEW if EXISTS sudoang.view_dbeel_hpp;
CREATE MATERIALIZED VIEW sudoang.view_dbeel_hpp AS (
SELECT row_number() OVER (PARTITION BY true) AS id, * FROM sudoang.dbeel_obstruction_place
    JOIN sudoang.dbeel_physical_obstruction on op_id=ob_op_id 
    JOIN sudoang.dbeel_hpp on hpp_ob_id=ob_id
   --WHERE hpp_source='ICE' 
   ); -- 1532 rows