projet pour gérer les statuts de taxon dans taxhub/geonature. Permet de gérer les statuts localiser (LR régionnal, sous-population, arrêtés municipaux, etc..)

geonature_status.sql

/*
--Fonction pas encore dans taxhub
CREATE OR REPLACE FUNCTION taxonomie.find_all_taxons_parents(id integer)
 RETURNS TABLE(cd_nom integer, id_rang character varying)
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
 --Param : cd_nom d'un taxon quelque soit son rang
 --Retourne une table avec le cd_nom de tout les taxons parents et leur id_rang.
 --Retourne le cd_nom de tous les taxons parents sous forme d'un jeu de données utilisable comme une table. Les cd_nom sont ordonnées du plus bas vers le plus haut (Dumm)
 --Usage SELECT * FROM taxonomie.find_all_taxons_parents(457346);
  DECLARE
    inf RECORD;
  BEGIN
  RETURN QUERY
	WITH RECURSIVE parents AS (
		SELECT tx1.cd_nom,tx1.cd_sup, tx1.id_rang, 0 AS nr FROM taxonomie.taxref tx1 WHERE tx1.cd_nom = id
		UNION ALL 
		SELECT tx2.cd_nom,tx2.cd_sup, tx2.id_rang, nr + 1
			FROM parents p
			JOIN taxonomie.taxref tx2 ON tx2.cd_nom = p.cd_sup
	)
	SELECT parents.cd_nom, parents.id_rang FROM parents
	JOIN taxonomie.taxref taxref ON taxref.cd_nom = parents.cd_nom
	ORDER BY parents.nr;
  END;
$function$
;




*/


CREATE TABLE bdc_statut.bib_doc_type(
    id_type SERIAL PRIMARY KEY,
    cd_type varchar(50) UNIQUE NOT NULL,
    lib varchar(250)
);

CREATE TABLE bdc_statut.bib_status( --Libellé (optionnel) des status
    cd_status varchar(50) PRIMARY KEY,
    lib varchar(250),
    description text
);

CREATE TABLE bdc_statut.status_doc(
    id_doc SERIAL PRIMARY KEY,
    cd_doc varchar(50) UNIQUE NOT NULL,
    id_type integer REFERENCES bdc_statut.bib_doc_type (id_type) NOT NULL,
    title varchar(250),
    full_citation text NOT NULL,
    url text NULL,
    publish_date date NULL,
    active bool NOT NULL DEFAULT true
);

CREATE TABLE bdc_statut.status(
    id_status SERIAL PRIMARY KEY,
    id_doc int REFERENCES bdc_statut.status_doc (id_doc) NOT NULL,
    cd_nom int REFERENCES taxonomie.taxref (cd_nom) NOT NULL,
    cd_status varchar(50) NOT NULL,
    nom_cite text NULL,
    rq_status text NULL
);

CREATE TABLE bdc_statut.cor_doc_area(
    id_doc integer REFERENCES bdc_statut.status_doc (id_doc) NOT NULL,
    id_area integer REFERENCES ref_geo.l_areas (id_area) NOT NULL,
    PRIMARY KEY (id_doc, id_area)
);

CREATE TABLE bdc_statut.cor_status_area(
    id_status integer REFERENCES bdc_statut.status (id_status) NOT NULL,
    id_area integer REFERENCES ref_geo.l_areas (id_area) NOT NULL,
    PRIMARY KEY (id_status, id_area)
);


--Fonctions
CREATE OR REPLACE FUNCTION bdc_statut.find_status(_cd_nom integer, _id_doc integer, _geom geometry DEFAULT NULL::geometry)
 RETURNS integer
 LANGUAGE plpgsql
 STABLE
AS $function$
    --Consulter un statut dans un document connu
    DECLARE status integer;
    DECLARE _cd_ref integer;
    BEGIN
      SELECT INTO _cd_ref taxonomie.find_cdref(_cd_nom);
      status = NULL;
      IF _geom is not NULL THEN
          SELECT INTO status st.id_status
          FROM bdc_statut.status st
          JOIN taxonomie.find_all_taxons_parents(_cd_ref ) p ON taxonomie.find_cdref(st.cd_nom) = p.cd_nom
          JOIN taxonomie.bib_taxref_rangs tr ON tr.id_rang=p.id_rang
          LEFT JOIN bdc_statut.get_areas_status(st.id_status) ast ON TRUE
          LEFT JOIN ref_geo.l_areas a ON a.id_area = ast.id_area 
          WHERE 
            st.id_doc = _id_doc
            AND st_intersects(a.geom,   _geom )
          ORDER BY 
            CASE
             	WHEN EXISTS(SELECT * FROM bdc_statut.cor_status_area  WHERE id_status=st.id_status) THEN 1
         	ELSE 2 END 
            , tr.tri_rang DESC
          LIMIT 1;
          RETURN status;
      ELSE
          SELECT INTO status st.id_status
          FROM bdc_statut.status st
          JOIN taxonomie.find_all_taxons_parents(_cd_ref ) p ON taxonomie.find_cdref(st.cd_nom) = p.cd_nom
          JOIN taxonomie.bib_taxref_rangs tr ON tr.id_rang=p.id_rang
          LEFT JOIN bdc_statut.get_areas_status(st.id_status) ast ON TRUE
          LEFT JOIN ref_geo.l_areas a ON a.id_area = ast.id_area 
          WHERE 
             st.id_doc = _id_doc
          ORDER BY 
            CASE
             	WHEN EXISTS(SELECT * FROM bdc_statut.cor_doc_area WHERE id_doc=st.id_doc) THEN 1
         	ELSE 2 END 
          , tr.tri_rang DESC
          LIMIT 1;
          RETURN status;
      END IF;
  END;
$function$
;


CREATE OR REPLACE FUNCTION bdc_statut.find_status_by_type(_cd_nom integer, _id_type integer, _geom geometry DEFAULT NULL::geometry)
 RETURNS integer
 LANGUAGE plpgsql
 STABLE
AS $function$
   DECLARE status integer;
   DECLARE _cd_ref integer;
   BEGIN
	   SELECT INTO _cd_ref taxonomie.find_cdref(_cd_nom);
       SELECT INTO status bdc_statut.find_status(_cd_nom, st.id_doc, _geom) as status
       FROM bdc_statut.status st
       JOIN bdc_statut.status_doc d ON d.id_doc = st.id_doc
       WHERE 
            d.id_type = _id_type
            AND taxonomie.find_cdref(st.cd_nom) IN ( SELECT cd_nom FROM taxonomie.find_all_taxons_parents(_cd_ref ) ) 
            AND bdc_statut.find_status(_cd_nom, st.id_doc, _geom) is not null
            and active is true
       LIMIT 1;
       RETURN status;
   END;
$function$
;



CREATE OR REPLACE FUNCTION bdc_statut.get_areas_status(_id_status integer) --Indique les id_area des zones concerné par un statut donné (sur la base du statut, ou de la zone globale du document) 
  RETURNS TABLE (id_area int)
  LANGUAGE plpgsql
  STABLE
AS $function$
  BEGIN
    IF EXISTS(SELECT * FROM bdc_statut.cor_status_area  WHERE id_status=_id_status) THEN
        RETURN QUERY
            SELECT bdc_statut.cor_status_area.id_area FROM bdc_statut.cor_status_area WHERE id_status=_id_status;
    ELSE
        RETURN QUERY
            SELECT cda.id_area 
            FROM bdc_statut.cor_doc_area cda
            JOIN bdc_statut.status st ON st.id_doc=cda.id_doc
            WHERE st.id_status= _id_status;
    END IF;
  END;
$function$
;

CREATE OR REPLACE FUNCTION bdc_statut.get_id_type(_cd_type character varying)
 RETURNS integer
 LANGUAGE plpgsql
 STABLE
AS $function$
		DECLARE _id_type integer;
		BEGIN
			SELECT INTO _id_type id_type FROM bdc_statut.bib_doc_type bdt WHERE cd_type=_cd_type;
			RETURN _id_type;
		END;
	$function$
;

CREATE OR REPLACE FUNCTION bdc_statut.get_id_doc(_cd_doc character varying)
 RETURNS integer
 LANGUAGE plpgsql
 STABLE
AS $function$
		DECLARE _id_doc integer;
		BEGIN
			SELECT INTO _id_doc id_doc FROM bdc_statut.status_doc WHERE cd_doc=_cd_doc;
			RETURN _id_doc;
		END;
	$function$
;



--Remplissage
INSERT INTO bdc_statut.bib_doc_type(cd_type, lib)
VALUES 
    ('LRR', 'Liste rouge régionnale'),
    ('LRM','Liste rouge mondiale'),
    ('LRN','Liste rouge nationale'),
    ('LRE','Liste rouge européenne'),
    ('RARR','Rareté régionale'),
    ('PR','Protection régionale'),
    ('PN','Protection nationale'),
    ('PD','Protection départementale'),
    ('ZDET','ZNIEFF Déterminantes'),
    ('PNA','Plan national en cours'),
    ('SENSNAT','Sensibilité nationale'),
    ('BONN','Convention de Bonn'),
    ('BERN','Convention de Berne'),
    ('DH','Directive Habitat'),
    ('DO','Directive Oiseaux'),
    ('REGLLUTTE','Lutte contre certaines espèces'),
    ('REGLII','Interdiction d''introduction');


INSERT INTO bdc_statut.bib_status(cd_status, lib, description)
VALUES 
    ('DD','Données insuffisantes',NULL),
    ('EN','En danger',NULL),
    ('RR', 'Très rare',NULL);


--Générer un tableau referentiel, exemple de requete a personnaliser selon contexte et souhait de rendu
/*
--Peut surement être optimisée (4~5h)
CREATE MATERIALIZED VIEW bdc_statut.referentiel AS
AS WITH my_taxon AS (
         SELECT DISTINCT tx_1.cd_ref
           FROM gn_synthese.synthese s
             JOIN taxonomie.taxref tx_1 ON tx_1.cd_nom = s.cd_nom
        )
 SELECT tx.cd_nom,
    tx.lb_nom,
    pn.cd_status AS protection_nationale,
    lrr.cd_status AS liste_rouge_picardie
   FROM my_taxon
     JOIN taxonomie.taxref tx ON tx.cd_nom = my_taxon.cd_ref
     LEFT JOIN bdc_statut.status pn ON pn.id_status = bdc_statut.find_status_by_type(tx.cd_nom, bdc_statut.get_id_type('PN'::character varying))
     LEFT JOIN bdc_statut.status lrr ON lrr.id_status = bdc_statut.find_status(tx.cd_nom, bdc_statut.get_id_doc('LRPICARDIE'::character varying)) );
*/