pnx-si / obs_occ Goto Github PK

/* La table md.lot_donnee est nécessaire */
CREATE TABLE md.lot_donnee
(
  id_lot serial NOT NULL,
  libelle character varying(254),
  id_etude integer,
  type_donnee md.enum_type_donnee,
  resume text,
  description_genealogie text,
  echelle_utilisation text,
  licence character varying,
  limitation_acces_public boolean,
  qualite_thematique text,
  id_protocole integer,
  code character varying,
  responsable_code_personne character varying,
  CONSTRAINT pk_lot_donnee PRIMARY KEY (id_lot),
  CONSTRAINT fk_etude_id_protocole_reference_protocole FOREIGN KEY (id_protocole)
      REFERENCES md.protocole (id_protocole) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_etude_lot_donnee_reference_etude FOREIGN KEY (id_etude)
      REFERENCES md.etude (id_etude) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION,
  CONSTRAINT fk_responsable_code_personne_references_personne_code_personne FOREIGN KEY (responsable_code_personne)
      REFERENCES md.personne (code_personne) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "unique nom lot" UNIQUE (id_lot)
)
WITH (
  OIDS=FALSE
);
COMMENT ON TABLE md.lot_donnee
  IS 'Décrit les lots de données produits ou utilisé au CEN.';

/* On l'alimente avec cette vue */
-- View: md.description_lot
-- DROP VIEW md.description_lot;
CREATE OR REPLACE VIEW md.description_lot AS 
 WITH extent AS (
         SELECT COALESCE(saisie_observation.id_etude, 39) AS id_etude, 
            count(saisie_observation.id_obs) AS nb_donnees, 
            saisie_observation.id_protocole, 
            (
                CASE
                    WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Point%'::text THEN 'point'::text
                    WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%LineString%'::text THEN 'ligne'::text
                    WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Polygon%'::text THEN 'perimetre'::text
                    ELSE NULL::text
                END || '_'::text) || 
                CASE saisie_observation.regne
                    WHEN 'Plantae'::text THEN 'espece'::text
                    WHEN 'Animalia'::text THEN 'espece'::text
                    WHEN 'Fungi'::text THEN 'espece'::text
                    WHEN 'Habitat'::text THEN 'habitat'::text
                    ELSE NULL::text
                END, 
            'point_flore' AS table_nom, 
            round(st_xmin(st_extent(st_transform(saisie_observation.geometrie, 4326))::box3d)::numeric, 2) AS x_min, 
            round(st_ymin(st_extent(st_transform(saisie_observation.geometrie, 4326))::box3d)::numeric, 2) AS y_min, 
            round(st_xmax(st_extent(st_transform(saisie_observation.geometrie, 4326))::box3d)::numeric, 2) AS x_max, 
            round(st_ymax(st_extent(st_transform(saisie_observation.geometrie, 4326))::box3d)::numeric, 2) AS y_max
           FROM saisie.saisie_observation
          GROUP BY COALESCE(saisie_observation.id_etude, 39), saisie_observation.id_protocole, (
                CASE
                    WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Point%'::text THEN 'point'::text
                    WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%LineString%'::text THEN 'ligne'::text
                    WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Polygon%'::text THEN 'perimetre'::text
                    ELSE NULL::text
                END || '_'::text) || 
                CASE saisie_observation.regne
                    WHEN 'Plantae'::text THEN 'espece'::text
                    WHEN 'Animalia'::text THEN 'espece'::text
                    WHEN 'Fungi'::text THEN 'espece'::text
                    WHEN 'Habitat'::text THEN 'habitat'::text
                    ELSE NULL::text
                END
        )
 SELECT DISTINCT lot_donnee.id_lot, extent.nb_donnees, 
        CASE
            WHEN lot_donnee.libelle IS NULL THEN lot_donnee.code
            ELSE lot_donnee.libelle
        END AS lot_donnee_libelle, 
    lot_donnee.id_etude AS lot_donnee_id_etude, 
    (
        CASE
            WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Point%'::text THEN 'point'::text
            WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%LineString%'::text THEN 'ligne'::text
            WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Polygon%'::text THEN 'perimetre'::text
            ELSE NULL::text
        END || '_'::text) || 
        CASE saisie_observation.regne
            WHEN 'Plantae'::text THEN 'espece'::text
            WHEN 'Animalia'::text THEN 'espece'::text
            WHEN 'Habitat'::text THEN 'habitat'::text
            ELSE NULL::text
        END AS lot_donnee_type_donnee, 
    lot_donnee.resume AS lot_donnee_resume, 
    lot_donnee.description_genealogie AS lot_donnee_description_genealogie, 
    lot_donnee.echelle_utilisation AS lot_donnee_echelle_utilisation, 
    lot_donnee.licence AS lot_donnee_licence, 
    lot_donnee.limitation_acces_public AS lot_donnee_limitation_acces_public, 
    lot_donnee.qualite_thematique AS lot_donnee_qualite_thematique, 
    lot_donnee.id_protocole AS lot_donnee_id_protocole, 
    lot_donnee.id_etude AS etude_id_etude, etude.nom_etude AS etude_nom_etude, 
    etude.cahier_des_charges AS etude_cahier_des_charges, 
    etude.date_debut AS etude_date_debut, etude.date_fin AS etude_date_fin, 
    etude.description AS etude_description, 
    etude.lien_rapport_final AS etude_lien_rapport_final, 
    lot_donnee.id_protocole AS protocole_id_protocole, 
    protocole.resume AS protocole_resume, 
    protocole.libelle AS protocole_libelle, 
    protocole.auteurs AS protocole_auteurs, 
    protocole.id_biblio AS protocole_id_biblio, 
    (lower(spatial_ref_sys.auth_name::text) || ' '::text) || spatial_ref_sys.auth_srid AS srid, 
        CASE
            WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Point%'::text THEN 'point'::text
            WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%LineString%'::text THEN 'ligne'::text
            WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Polygon%'::text THEN 'perimetre'::text
            ELSE NULL::text
        END AS "case"
   FROM saisie.saisie_observation
   JOIN md.lot_donnee ON lot_donnee.id_protocole = saisie_observation.id_protocole AND lot_donnee.id_etude = saisie_observation.id_etude AND lot_donnee.type_donnee = (((
   CASE
       WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Point%'::text THEN 'point'::text
       WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%LineString%'::text THEN 'ligne'::text
       WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Polygon%'::text THEN 'perimetre'::text
       ELSE NULL::text
   END || '_'::text) || 
   CASE saisie_observation.regne
       WHEN 'Plantae'::text THEN 'espece'::text
       WHEN 'Animalia'::text THEN 'espece'::text
       WHEN 'Habitat'::text THEN 'habitat'::text
       ELSE NULL::text
   END)::md.enum_type_donnee)
   LEFT JOIN md.etude ON lot_donnee.id_etude = etude.id_etude
   LEFT JOIN md.protocole ON lot_donnee.id_protocole = protocole.id_protocole
   LEFT JOIN extent ON extent.id_protocole = saisie_observation.id_protocole AND extent.id_etude = saisie_observation.id_etude
   JOIN spatial_ref_sys ON st_srid(saisie_observation.geometrie) = spatial_ref_sys.srid
  ORDER BY lot_donnee.id_lot;
CREATE OR REPLACE VIEW export.sinp_dee_point_animalia AS 
 SELECT row_number() OVER (ORDER BY lpad(saisie_observation.id_obs::text, 7, '0'::text)) AS gid, 
        CASE saisie_observation.determination::text
            WHEN 'Vu'::text THEN 'te'::text
            WHEN 'Entendu'::text THEN 'te'::text
            WHEN 'Indice de présence'::text THEN 'te'::text
            WHEN 'Cadavre'::text THEN 'te'::text
            WHEN 'Capture'::text THEN 'te'::text
            WHEN 'Collection'::text THEN 'Co'::text
            WHEN 'Littérature'::text THEN 'Li'::text
            ELSE 'te'::text
        END AS statutsource, 
        CASE saisie_observation.determination::text
            WHEN 'Littérature'::text THEN 'à préciser'::text
            ELSE NULL::text
        END AS referencebiblio, 
    lpad(lot_donnee.id_lot::text, 4, '0'::text) AS jddid, 
    'SICEN-LR:00-175'::text AS jddcode, 
    lpad(saisie_observation.id_obs::text, 7, '0'::text) AS identifiantorigine, 
    NULL::text AS identifiantpermanent, 
        CASE
            WHEN lot_donnee.libelle::text ~~* 'bénévolat%'::text THEN 'Pr'::text
            WHEN saisie_observation.id_etude = ANY (ARRAY[39, 51]) THEN 'NSP'::text
            ELSE 'Ac'::text
        END AS dspublique, 
    NULL::text AS codeidcnpdispositif, 'CEN L-R'::text AS organismestandard, 
        CASE
            WHEN lower(saisie_observation.type_effectif) = 'absence'::text THEN 'No'::text
            ELSE 'Pr'::text
        END AS statutobservation, 
    btrim(pg_catalog.concat(saisie_observation.nom_vern, ' / ', saisie_observation.nom_complet), ' / '::text) AS nomcite, 
    saisie_observation.cd_nom::integer AS cdnom, 
    taxref.cd_ref::integer AS cdref, 'non'::text AS sensible, 
    COALESCE(saisie_observation.effectif, saisie_observation.effectif_min, 1::bigint)::integer AS denombrementmin, 
    COALESCE(saisie_observation.effectif, saisie_observation.effectif_max)::integer AS denombrementmax, 
        CASE
            WHEN saisie_observation.type_effectif ~~* ANY (ARRAY['abondance%'::text, 'classe%'::text]) THEN 'Es'::text
            ELSE 'Co'::text
        END AS typedenombrement, 
        CASE
            WHEN saisie_observation.type_effectif IS NOT NULL THEN 'In'::text
            ELSE NULL::text
        END AS objetdenombrement, 
        CASE
            WHEN saisie_observation.observateur = '20'::text THEN 'NSP'::text
            ELSE md.liste_nom_auteur(saisie_observation.observateur, ', '::text)
        END AS identiteobservateur, 
        CASE md.liste_nom_structure(saisie_observation.structure, ', '::text)
            WHEN 'Pas de structure'::text THEN 'indépendant'::text
            ELSE md.liste_nom_structure(saisie_observation.structure, ', '::text)
        END AS organismeobservateur, 
    'CEN L-R'::text AS organismegestionnairedonnees, 
        CASE
            WHEN saisie_observation.observateur <> '20'::text THEN md.liste_nom_auteur(saisie_observation.observateur, ', '::text)
            ELSE NULL::text
        END AS determinateur, 
    btrim(pg_catalog.concat(validateur.nom, ' ', validateur.prenom)) AS validateur, 
    saisie_observation.remarque_obs AS commentaire, 
    COALESCE(saisie_observation.date_obs, saisie_observation.date_debut_obs) AS datedebut, 
    COALESCE(saisie_observation.date_obs, saisie_observation.date_fin_obs) AS datefin, 
    NULL::time without time zone AS heuredebut, 
    NULL::time without time zone AS heurefin, 
    NULL::date AS datedeterminationobs, 
    saisie_observation.elevation::numeric AS altitudemin, 
    saisie_observation.elevation::numeric AS altitudemax, 
    NULL::numeric AS profondeurmin, NULL::numeric AS profondeurmax, 
    NULL::text AS codehabitat, NULL::text AS refhabitat, 
    st_asgml(saisie_observation.geometrie) AS geometrie, 
        CASE saisie_observation."precision"
            WHEN 'GPS'::saisie.enum_precision THEN 10
            WHEN '0 à 10m'::saisie.enum_precision THEN 10
            WHEN '10 à 100m'::saisie.enum_precision THEN 100
            WHEN '100 à 500m'::saisie.enum_precision THEN 500
            ELSE 1000
        END AS "precision", 
        CASE
            WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%POINT%'::text THEN 'St'::text
            WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%POLYGON%'::text OR st_geometrytype(saisie_observation.geometrie) ~~* '%LINE%'::text THEN 'In'::text
            ELSE 'NSP'::text
        END AS natureobjetgeo, 
    COALESCE(saisie_observation.code_insee, commune.code_insee::text) AS codecommune, 
    commune.nom AS nomcommune, NULL::text AS codeen, NULL::text AS typeen, 
    NULL::text AS codemaille, 
    saisie_observation.geometrie::geometry(Point,2154) AS geom, 
    st_x(saisie_observation.geometrie::geometry(Point,2154)) AS st_x, 
    st_y(saisie_observation.geometrie::geometry(Point,2154)) AS st_y
   FROM saisie.saisie_observation
   JOIN inpn.taxref USING (cd_nom)
   JOIN ign_bd_topo.commune ON st_intersects(commune.geometrie, saisie_observation.geometrie)
   LEFT JOIN md.lot_donnee ON saisie_observation.id_etude = lot_donnee.id_etude AND saisie_observation.id_protocole = lot_donnee.id_protocole AND ((
CASE
WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Point%'::text THEN 'point'::text
WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%LineString%'::text THEN 'ligne'::text
WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Polygon%'::text THEN 'perimetre'::text
ELSE NULL::text
END || '_'::text) || 
CASE saisie_observation.regne
    WHEN 'Plantae'::text THEN 'espece'::text
    WHEN 'Animalia'::text THEN 'espece'::text
    WHEN 'Fungi'::text THEN 'espece'::text
    WHEN 'Habitat'::text THEN 'habitat'::text
    ELSE NULL::text
END) = lot_donnee.type_donnee::text
   LEFT JOIN md.personne validateur ON saisie_observation.validateur = validateur.id_personne
  WHERE taxref.cd_nom::text ~ '^[\d+]'::text AND saisie_observation.regne = 'Animalia'::text 
  AND (md.liste_nom_structure(saisie_observation.structure, ', '::text) = ANY (ARRAY['CEN LR'::text, 'Pas de structure'::text])) 
  AND st_geometrytype(saisie_observation.geometrie) ~~* '%Point%'::text;