273 lines
9.4 KiB
Python
273 lines
9.4 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-
|
|
|
|
import geopandas as gpd
|
|
from pycen import con_sicen
|
|
|
|
drop_v_saisie_ = "DROP VIEW IF EXISTS saisie.v_saisie_observation"
|
|
v_saisie_observation = """
|
|
CREATE OR REPLACE VIEW saisie.v_saisie_observation
|
|
AS WITH auteur AS (
|
|
SELECT p_1.id_personne,
|
|
CASE
|
|
WHEN p_1.prenom ~~ '(%%'::text THEN p_1.nom
|
|
ELSE COALESCE((p_1.nom || ' '::text) || p_1.prenom)
|
|
END AS personne,
|
|
s_1.nom_structure AS structure
|
|
FROM md.personne p_1
|
|
JOIN md.structure s_1 USING (id_structure)
|
|
)
|
|
SELECT s.id_obs,
|
|
s.date_obs,
|
|
s.date_debut_obs,
|
|
s.date_fin_obs,
|
|
s.date_textuelle,
|
|
s.regne,
|
|
s.nom_vern,
|
|
s.nom_complet,
|
|
s.cd_nom,
|
|
t.cd_ref,
|
|
s.effectif_textuel,
|
|
s.effectif_min,
|
|
s.effectif_max,
|
|
CASE
|
|
WHEN s.regne = 'Plantae'::text THEN s.type_effectif
|
|
ELSE NULL::text
|
|
END AS strate_flore,
|
|
CASE
|
|
WHEN s.regne = 'Plantae'::text THEN s.phenologie
|
|
ELSE NULL::text
|
|
END AS phenologie_flore,
|
|
CASE
|
|
WHEN s.regne = 'Animalia'::text THEN s.type_effectif
|
|
ELSE NULL::text
|
|
END AS age_faune,
|
|
CASE
|
|
WHEN s.regne = 'Animalia'::text THEN s.phenologie
|
|
ELSE NULL::text
|
|
END AS sexe_faune,
|
|
s.id_waypoint,
|
|
s.longitude,
|
|
s.latitude,
|
|
s.localisation,
|
|
md.liste_nom_auteur(s.observateur::character varying) AS observateur,
|
|
a1.personne AS numerisateur,
|
|
a2.personne AS validateur,
|
|
md.liste_nom_structure(s.structure::character varying) AS structure,
|
|
s.remarque_obs,
|
|
s.code_insee,
|
|
s.id_lieu_dit,
|
|
s.diffusable,
|
|
s."precision",
|
|
s.statut_validation,
|
|
e.nom_etude AS etude,
|
|
p.libelle AS protocole,
|
|
s.effectif,
|
|
s.url_photo,
|
|
s.commentaire_photo,
|
|
s.decision_validation,
|
|
s.heure_obs,
|
|
s.determination,
|
|
s.elevation,
|
|
s.geometrie,
|
|
s.phylum,
|
|
s.classe,
|
|
s.ordre,
|
|
s.famille,
|
|
s.nom_valide,
|
|
s.qualification,
|
|
s.reprostatut AS reprostatut_faune,
|
|
s.obs_null,
|
|
s.uuid,
|
|
ld.libelle AS lot_donnee,
|
|
s.id_origine
|
|
FROM saisie.saisie_observation s
|
|
LEFT JOIN auteur a1 ON a1.id_personne = s.numerisateur
|
|
LEFT JOIN auteur a2 ON a2.id_personne = s.validateur
|
|
LEFT JOIN md.etude e ON s.id_etude = e.id_etude
|
|
LEFT JOIN md.protocole p ON s.id_protocole = p.id_protocole
|
|
LEFT JOIN md.lot_donnee ld ON s.id_lot = ld.id_lot
|
|
LEFT JOIN inpn.taxref t using (cd_nom)
|
|
;
|
|
"""
|
|
|
|
grant_v_saisie = """
|
|
ALTER TABLE saisie.v_saisie_observation OWNER TO cgeier;
|
|
GRANT ALL ON TABLE saisie.v_saisie_observation TO cgeier;
|
|
"""
|
|
|
|
with con_sicen.begin() as cnx:
|
|
cnx.execute(drop_v_saisie_)
|
|
cnx.execute(v_saisie_observation)
|
|
cnx.execute(grant_v_saisie)
|
|
|
|
|
|
drop_vm_synthese = "DROP MATERIALIZED VIEW IF EXISTS saisie.vm_synthese_observations CASCADE;"
|
|
vm_synthese_observations = """
|
|
-- saisie.vm_synthese_observations source
|
|
|
|
CREATE MATERIALIZED VIEW saisie.vm_synthese_observations
|
|
TABLESPACE pg_default
|
|
AS WITH
|
|
observateurs AS (
|
|
SELECT personne.id_personne,
|
|
(personne.nom || ' '::text) || personne.prenom AS nom_complet,
|
|
personne.role,
|
|
personne.id_structure
|
|
FROM md.personne
|
|
),
|
|
structures AS (
|
|
SELECT structure.id_structure,
|
|
structure.nom_structure,
|
|
structure.diffusable
|
|
FROM md.structure
|
|
)
|
|
SELECT DISTINCT s.id_obs,
|
|
e.nom_etude AS etude,
|
|
p.libelle AS protocole,
|
|
ld.libelle AS lot_donnee,
|
|
s.date_obs,
|
|
s.date_debut_obs,
|
|
s.date_fin_obs,
|
|
s.heure_obs,
|
|
CASE
|
|
WHEN tx.regne IS NOT NULL THEN tx.regne
|
|
ELSE ta.regne
|
|
END AS regne,
|
|
--tx.phylum,
|
|
CASE
|
|
WHEN tx.phylum IS NOT NULL THEN tx.phylum
|
|
ELSE ta.phylum
|
|
END AS phylum,
|
|
--tx.classe,
|
|
CASE
|
|
WHEN tx.classe IS NOT NULL THEN tx.classe
|
|
ELSE ta.classe
|
|
END AS classe,
|
|
--tx.ordre,
|
|
CASE
|
|
WHEN tx.ordre IS NOT NULL THEN tx.ordre
|
|
ELSE ta.ordre
|
|
END AS ordre,
|
|
tx.famille,
|
|
CASE
|
|
WHEN tx.group1_inpn IS NOT NULL THEN tx.group1_inpn
|
|
ELSE ta.group1_inpn
|
|
END AS group1_inpn,
|
|
CASE
|
|
WHEN tx.group2_inpn IS NOT NULL THEN tx.group2_inpn
|
|
ELSE ta.group2_inpn
|
|
END AS group2_inpn,
|
|
CASE
|
|
WHEN tx.lb_nom IS NOT NULL THEN tx.lb_nom
|
|
ELSE ta.lb_nom
|
|
END AS nom_latin,
|
|
CASE
|
|
WHEN tx.nom_vern IS NOT NULL THEN tx.nom_vern
|
|
ELSE ta.lb_nom
|
|
END AS nom_vern,
|
|
CASE
|
|
WHEN tx.nom_complet IS NOT NULL THEN tx.nom_complet
|
|
ELSE ta.nom_complet
|
|
END AS nom_complet,
|
|
CASE
|
|
WHEN tx.cd_nom IS NOT NULL THEN tx.cd_nom
|
|
ELSE ta.cd_nom
|
|
END AS cd_nom,
|
|
CASE
|
|
WHEN tx.cd_ref IS NOT NULL THEN tx.cd_ref
|
|
ELSE ta.cd_ref
|
|
END AS cd_ref,
|
|
--s.cd_nom,
|
|
--t.cd_ref,
|
|
CASE
|
|
WHEN s.obs_null IS TRUE THEN 'oui'::text
|
|
ELSE 'non'::text
|
|
END::character(3) AS absence_observation,
|
|
s.effectif,
|
|
s.effectif_min,
|
|
s.effectif_max,
|
|
s.effectif_textuel,
|
|
CASE
|
|
WHEN s.regne ~~ 'Plantae'::text THEN s.type_effectif
|
|
ELSE NULL::text
|
|
END::saisie.enum_type_effectif AS strate_flore,
|
|
CASE
|
|
WHEN s.regne ~~ 'Plantae'::text THEN s.phenologie
|
|
ELSE NULL::text
|
|
END::saisie.enum_phenologie AS phenologie_flore,
|
|
CASE
|
|
WHEN s.regne ~~ 'Animalia'::text THEN s.type_effectif
|
|
ELSE NULL::text
|
|
END::saisie.enum_age AS age_faune,
|
|
CASE
|
|
WHEN s.regne ~~ 'Animalia'::text THEN s.phenologie
|
|
ELSE NULL::text
|
|
END::saisie.enum_sexe AS sexe_faune,
|
|
s.reprostatut AS reprostatut_faune,
|
|
s.determination,
|
|
s.remarque_obs AS rmq_observation,
|
|
md.liste_nom_auteur(s.observateur::character varying) AS observateurs,
|
|
md.liste_nom_auteur_structure(s.observateur::character varying) AS observateurs_v2,
|
|
num.nom_complet AS numerisateur,
|
|
md.liste_nom_structure(s.structure::character varying) AS structures,
|
|
CASE
|
|
WHEN s.diffusable IS TRUE THEN 'oui'::text
|
|
ELSE 'non'::text
|
|
END::character(3) AS diffusable,
|
|
s.statut_validation,
|
|
val.nom_complet AS validateur,
|
|
s.decision_validation,
|
|
s.code_insee AS insee_commune,
|
|
c.nom AS nom_commune,
|
|
l.nom AS lieu_dit,
|
|
s.elevation AS altitude_z,
|
|
s.longitude AS longitude_x,
|
|
s.latitude AS latitude_y,
|
|
s."precision",
|
|
s.localisation AS rmq_localisation,
|
|
s.id_origine,
|
|
s.geometrie::geometry(Point,2154) AS geom
|
|
FROM saisie.saisie_observation s
|
|
LEFT JOIN inpn.taxref t ON s.cd_nom = t.cd_nom
|
|
LEFT JOIN inpn.taxons_isere_absents_taxref ta ON s.cd_nom = ta.cd_nom::text
|
|
LEFT JOIN ref_inpn_taxref.taxref_v11 tx ON s.cd_nom = tx.cd_nom::text
|
|
JOIN md.etude e ON s.id_etude = e.id_etude
|
|
LEFT JOIN md.protocole p ON s.id_protocole = p.id_protocole
|
|
LEFT JOIN md.lot_donnee ld ON s.id_lot = ld.id_lot
|
|
LEFT JOIN observateurs num ON s.numerisateur = num.id_personne
|
|
LEFT JOIN observateurs val ON s.validateur = val.id_personne
|
|
LEFT JOIN ign_bd_topo.commune c ON s.code_insee = c.code_insee
|
|
LEFT JOIN ign_bd_topo.lieu_dit l ON s.id_lieu_dit = l.id
|
|
WITH DATA;
|
|
|
|
-- View indexes:
|
|
CREATE INDEX idx_vm_synthese_observations_date_debut_obs ON saisie.vm_synthese_observations USING btree (date_debut_obs);
|
|
CREATE INDEX idx_vm_synthese_observations_date_fin_obs ON saisie.vm_synthese_observations USING btree (date_fin_obs);
|
|
CREATE INDEX idx_vm_synthese_observations_date_obs ON saisie.vm_synthese_observations USING btree (date_obs);
|
|
CREATE INDEX idx_vm_synthese_observations_group1_inpn ON saisie.vm_synthese_observations USING btree (group1_inpn);
|
|
CREATE INDEX idx_vm_synthese_observations_group2_inpn ON saisie.vm_synthese_observations USING btree (group2_inpn);
|
|
CREATE UNIQUE INDEX idx_vm_synthese_observations_id_obs ON saisie.vm_synthese_observations USING btree (id_obs);
|
|
CREATE INDEX idx_vm_synthese_observations_nom_commune ON saisie.vm_synthese_observations USING btree (nom_commune);
|
|
CREATE INDEX idx_vm_synthese_observations_nom_latin ON saisie.vm_synthese_observations USING btree (nom_latin);
|
|
CREATE INDEX idx_vm_synthese_observations_observateurs ON saisie.vm_synthese_observations USING btree (observateurs);
|
|
CREATE INDEX idx_vm_synthese_observations_structures ON saisie.vm_synthese_observations USING btree (structures);
|
|
CREATE INDEX sidx_vm_synthese_observations_geom ON saisie.vm_synthese_observations USING gist (geom);
|
|
|
|
|
|
COMMENT ON MATERIALIZED VIEW saisie.vm_synthese_observations IS 'Vue matérialisée de synthèse des données d''observations issuent de la table "saisie.saise_observation" et reformattée pour faciliter son utilisation dans QGIS. La vue est rafraîchie toutes les heures avec un script PSQL executer en SHELL via une tâche planifiée CRON côté serveur. Cette vue matérialisée peut également être rafraîchi manuellement avec la commande : REFRESH MATERIALIZED VIEW CONCURRENTLY';
|
|
"""
|
|
|
|
grant_vm_synthese = """
|
|
-- Permissions
|
|
|
|
ALTER TABLE saisie.vm_synthese_observations OWNER TO admin_ra;
|
|
GRANT ALL ON TABLE saisie.vm_synthese_observations TO admin_ra;
|
|
GRANT SELECT ON TABLE saisie.vm_synthese_observations TO grp_consult;
|
|
GRANT ALL ON TABLE saisie.vm_synthese_observations TO grp_admin;
|
|
GRANT SELECT ON TABLE saisie.vm_synthese_observations TO sicen2_gr_consult;
|
|
"""
|
|
with con_sicen.begin() as cnx:
|
|
cnx.execute(drop_vm_synthese)
|
|
cnx.execute(vm_synthese_observations)
|
|
cnx.execute(grant_vm_synthese) |