add a new expertise for pass to tell whether it is functional

Currently in C:\workspace\eda_model\eda2.3\report\montepomi\dam_montepomi.Rmd we have a text chunk that should be moved to the db for consistency

line 1886 we have this patch

dam_with_pass_not_functional <- dam_rios@dbeel_view_pass %>% 
		inner_join(dam_rios@datadam %>% select(ob_id, ouv_avis_tech_global_id)) %>%
		filter(ouv_avis_tech_global_id %in% c(2,3)) %>% pull(ob_id) %>% unique()
length(dam_with_pass_not_functional) # 15345
dam_with_pass_not_functional_due_downstream_migration <- dam_rios@dbeel_view_pass %>% 
		inner_join(dam_rios@datadam %>% select(ob_id, ouv_avis_tech_global_id, is_latest_period,ouv_conformite_montaison )) %>%
		filter(ouv_avis_tech_global_id %in% c(2,3) & is_latest_period & ouv_conformite_montaison ) %>% pull(ob_id) %>% unique()
length(dam_with_pass_not_functional_due_downstream_migration) # 4626
# elements dans dam_with_pass_not_functional mais pas dans dam_with_pass_not_functional_due_downstream_migration
#setdiff(c(1,2,3),c(1,2)) => 3
dam_with_pass_not_functional <- setdiff(dam_with_pass_not_functional,dam_with_pass_not_functional_due_downstream_migration)
length(dam_with_pass_not_functional) # 10719
sum(dam_rios@dbeel_view_pass[dam_rios@dbeel_view_pass$ob_id %in% dam_with_pass_not_functional, "po_presence_pass"], na.rm=TRUE) # 161 passes pour lesquelles l'avis technique global est négatif
dam_rios@dbeel_view_pass[dam_rios@dbeel_view_pass$ob_id %in% dam_with_pass_not_functional, "po_presence_pass"] <- FALSE

which says that if ouv_avis_tech_global_id is in (2,3) then pass are not functional, so the species cannot pass.

THIS NEEDS TO BE MOVED TO THE DB

in C:\workspace\eda_dbeel\eda2.3\sql\import_dam_france_3_pass_and_score.sql

line 38


DROP TABLE IF EXISTS montepomi.dbeel_physical_obstruction_pass_species CASCADE;
CREATE TABLE montepomi.dbeel_physical_obstruction_pass_species AS (
-- table espece c'est juste une combinaison toutes les espèces à la main avec le gre_id correspondant
WITH table_espece AS (
  SELECT
    'alose' AS species,
    3 AS gre_id
UNION ALL
  SELECT
    'lamproie' AS species,
    5 AS gre_id
UNION ALL
  SELECT
    'saumon' AS species,
    1 AS gre_id
UNION ALL
  SELECT
    'anguille' AS species,
    19 AS gre_id
UNION ALL 
  SELECT
    'civelle' AS species,
    20 AS gre_id
),
dbeel_especes AS (
SELECT
  DISTINCT dbeel_obstruction_place.op_id, id_original,
  table_espece.species, table_espece.gre_id 
FROM montepomi.dbeel_obstruction_place,
    table_espece),
    
-- tableau des passes fonctionnelles ou non (po_presence_pass) true ou false pour 
-- chaque espèce en fonction du type de passe en provenance du ROE
     
roe_type_pap AS (
SELECT fpi_ref_id AS ouv_id,
	  'civelle' AS species,
      fpi_id,
      CASE WHEN fpi_fty_id IS NULL THEN FALSE 
      else fpi_fty_id IN (4,8) AND COALESCE (distanceseam / 1000, 9999) < 50
      END AS po_presence_pass,
       -- On garde le type passe à anguilles et rivière de contournement à moins de 50km
      COALESCE(dfp_date_mes,(hfpi_date_metier||'-12-31')::date) AS date_passe
FROM roe.franchissement_piscicole
LEFT JOIN bdoe.ouvrage ON fpi_ref_id=ouv_id
LEFT JOIN roe.historique_franch_piscicole ON hfpi_fpi_id=fpi_id
LEFT JOIN bdoe.franchissement_piscicole_bdoe ON dfp_fpi_id=fpi_id
LEFT JOIN dbeel_especes ON 'ROE'||ouv_id=id_original
LEFT JOIN montepomi.join_obstruction_rn jo ON jo.op_id = dbeel_especes.op_id 
LEFT JOIN france.rn_rna ON rn_rna.idsegment = jo.idsegment

UNION ALL 

SELECT fpi_ref_id AS ouv_id,
	  'anguille' AS species,
      fpi_id,
      CASE WHEN fpi_fty_id IS NULL THEN FALSE 
      else fpi_fty_id IN (1,4,8,7,2,6) 
      END AS po_presence_pass,
      COALESCE(dfp_date_mes,(hfpi_date_metier||'-12-31')::date) AS date_passe
FROM roe.franchissement_piscicole 
LEFT JOIN roe.historique_franch_piscicole ON hfpi_fpi_id=fpi_id
LEFT JOIN bdoe.franchissement_piscicole_bdoe ON dfp_fpi_id=fpi_id 

UNION ALL 

SELECT fpi_ref_id AS ouv_id,
	  'alose' AS species,
      fpi_id,
      CASE WHEN fpi_fty_id IS NULL THEN FALSE 
      else fpi_fty_id IN (1,8,5,0,7,2,6) 
      END AS po_presence_pass,
      COALESCE(dfp_date_mes,(hfpi_date_metier||'-12-31')::date) AS date_passe
FROM roe.franchissement_piscicole 
LEFT JOIN roe.historique_franch_piscicole ON hfpi_fpi_id=fpi_id
LEFT JOIN bdoe.franchissement_piscicole_bdoe ON dfp_fpi_id=fpi_id

UNION ALL 

SELECT fpi_ref_id AS ouv_id,
	  'lamproie' AS species,
      fpi_id,
      CASE WHEN fpi_fty_id IS NULL THEN FALSE 
      else fpi_fty_id IN (1,8,5,0,7,2,6) 
      END AS po_presence_pass,     
      COALESCE(dfp_date_mes,(hfpi_date_metier||'-12-31')::date) AS date_passe
FROM roe.franchissement_piscicole 
LEFT JOIN roe.historique_franch_piscicole ON hfpi_fpi_id=fpi_id
LEFT JOIN bdoe.franchissement_piscicole_bdoe ON dfp_fpi_id=fpi_id

UNION ALL 

SELECT fpi_ref_id AS ouv_id,
	  'saumon' AS species,
      fpi_id,
      CASE WHEN fpi_fty_id IS NULL THEN FALSE 
      else fpi_fty_id IN (1,8,5,0,7,2,6) 
      END AS po_presence_pass,   
      COALESCE(dfp_date_mes,(hfpi_date_metier||'-12-31')::date) AS date_passe
FROM roe.franchissement_piscicole 
LEFT JOIN roe.historique_franch_piscicole ON hfpi_fpi_id=fpi_id
LEFT JOIN bdoe.franchissement_piscicole_bdoe ON dfp_fpi_id=fpi_id
),

-- NOTE on utilise pas ICE type_pass (voir fin du script pour chunk enlevé pour lisibilité du code)

-- JOINTURE DE LA TABLE PRECEDENTE AVEC LES ESPECES pour récupérer gre_id (en vrai sert à rien parce qu'on fait plus ICE...)
dbeel_type_pap AS (
SELECT DISTINCT dbeel_especes.*,  fpi_id, po_presence_pass, date_passe
FROM dbeel_especes
LEFT JOIN roe_type_pap ON 'ROE' || ouv_id = id_original 
AND dbeel_especes.species = roe_type_pap.species
WHERE fpi_id IS NOT NULL 
), --456 068 --> 15541 (not null)

--SELECT * FROM dbeel_type_pap --15541
-- JOINTURE AVEC physical obstruction en fonction du code ouvrage et de la date_passe 
join_with_physical_obstruction_date AS(
SELECT ob_id,dbeel_type_pap.* FROM dbeel_type_pap JOIN 
montepomi.dbeel_physical_obstruction ON
dbeel_physical_obstruction.ob_op_id = dbeel_type_pap.op_id AND
date_passe < ob_ending_date
), -- 2703

join_with_physical_obstruction_null AS(
SELECT ob_id,dbeel_type_pap.* FROM dbeel_type_pap JOIN 
montepomi.dbeel_physical_obstruction ON
dbeel_physical_obstruction.ob_op_id = dbeel_type_pap.op_id 
WHERE date_passe IS NULL)

SELECT * FROM join_with_physical_obstruction_date 
   UNION 
SELECT * FROM join_with_physical_obstruction_null
);

Admin message

add a new expertise for pass to tell whether it is functional