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:
The structure of the database concerning physical barriers and hydroelectric power plants also inherits the structure of the French databases:
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).
The script in its latest version is available online on the import trac script
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
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
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.
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.
Shoreline migration pathways
Tidal dam
Weir
Gate
Rock weir
Water Intake
Culvert
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.
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.
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 |
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.
Artificial river
Rock ramp
Eel ramp
Pool type fishway
Pool type fishway
Fish lock
Fish lift
Denil pass
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.
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 |
Artificial river
Rock ramp
Eel ramp
Pool type fishway
Pool type fishway
Fish lock
Fish lift
Denil pass
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
),
*/
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:
Fish-friendly turbine
Water intake equipment
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
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”.
There are six passability modalities in BDOe:
Absence of obstacles
Passable without apparent difficulty
Passable but with risk of impact
Difficult to pass
Very difficult to pass
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
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.
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 |
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