465 lines
16 KiB
Python
465 lines
16 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-.
|
|
|
|
from sqlalchemy import create_engine, text
|
|
from sqlalchemy.engine import URL
|
|
from datetime import datetime as dt
|
|
import pandas as pd
|
|
import geopandas as gpd
|
|
|
|
|
|
# Parametres bdd
|
|
user = 'cen_admin'
|
|
pwd = '#CEN38@venir'
|
|
adr = '91.134.194.221'
|
|
port = '5432'
|
|
base = 'azalee'
|
|
|
|
url = URL.create('postgresql+psycopg2',
|
|
username=user,
|
|
password=pwd,
|
|
host=adr,
|
|
database=base,
|
|
)
|
|
con = create_engine(url)
|
|
|
|
v_zoneshumides = """
|
|
DROP VIEW IF EXISTS zones_humides.v_zoneshumides;
|
|
CREATE OR REPLACE VIEW zones_humides.v_zoneshumides
|
|
AS WITH temp1 as (
|
|
SELECT DISTINCT ON (s.id)
|
|
s.id,
|
|
max(s6.date) "date"
|
|
FROM sites.sites s
|
|
JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
JOIN sites.r_sites_geom s6 ON s.id::text = s6.id_site::text
|
|
WHERE s.date_fin IS NULL
|
|
AND s3.nom_court::text = 'Milieux humides'::text
|
|
GROUP BY s.id
|
|
), hab as (
|
|
SELECT
|
|
s62.id_geom_site,
|
|
string_agg(s62.id_cb,';') code_cb,
|
|
string_agg(s621.lb_hab_fr,';') lib_cb
|
|
FROM zones_humides.r_site_habitat s62 JOIN ref_habitats.corine_biotope s621 ON s62.id_cb = s621.id
|
|
WHERE s62."valid"
|
|
GROUP BY 1
|
|
), usagprocess AS (
|
|
SELECT
|
|
s65.id_geom_site,
|
|
string_agg(s651.nom,';') activ_hum,
|
|
string_agg(s652.nom,';') impact,
|
|
string_agg(s653.nom,';') "position",
|
|
string_agg(s65.remarques,';') rmq_activ_hum
|
|
FROM zones_humides.r_site_usageprocess s65
|
|
JOIN zones_humides.param_activ_hum s651 ON s65.id_activ_hum = s651.id
|
|
JOIN zones_humides.param_impact s652 ON s65.id_impact = s652.id
|
|
JOIN zones_humides.param_position s653 ON s65.id_position = s653.id
|
|
WHERE s65."valid"
|
|
GROUP BY 1
|
|
), cnx as (
|
|
SELECT
|
|
s63.id_geom_site,
|
|
s631.nom connexion
|
|
FROM zones_humides.r_site_type_connect s63
|
|
JOIN zones_humides.param_type_connect s631 ON s63.id_param_connect = s631.id
|
|
WHERE s63."valid"
|
|
), tmp_auteur_site as (
|
|
SELECT
|
|
s.id,
|
|
string_agg(btrim(concat(s11.nom, ' ', s11.prenom,' (',COALESCE(s111.abbrev, s111.nom),')')),';') AS auteur_site
|
|
FROM sites.sites s
|
|
JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
LEFT JOIN (sites.r_sites_auteur s1 JOIN (personnes.personne s11
|
|
JOIN personnes.organisme s111 ON s11.id_organisme = s111.id) ON s1.id_auteur = s11.id
|
|
) ON s.id::text = s1.id_site::text
|
|
WHERE s.date_fin IS NULL
|
|
AND s3.nom_court::text = 'Milieux humides'::text
|
|
GROUP BY s.id
|
|
), tmp_auteur_geom as (
|
|
SELECT
|
|
s6.id,
|
|
string_agg(btrim(concat(s611.nom, ' ', s611.prenom,' (',COALESCE(s6111.abbrev, s6111.nom),')')),';') AS auteur_geom
|
|
FROM sites.sites s
|
|
JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
LEFT JOIN (sites.r_sites_geom s6
|
|
LEFT JOIN (sites.r_geomsites_auteur s61 JOIN (personnes.personne s611
|
|
JOIN personnes.organisme s6111 ON s611.id_organisme = s6111.id) ON s61.id_auteur = s611.id
|
|
) ON s6.id = s61.id_geom_site
|
|
) ON s.id = s6.id_site
|
|
WHERE s.date_fin IS NULL
|
|
AND s3.nom_court::text = 'Milieux humides'::text
|
|
GROUP BY s6.id
|
|
)
|
|
SELECT
|
|
s.id AS site_code,
|
|
s.nom,
|
|
s1.auteur_site,
|
|
s61.auteur_geom,
|
|
s6.geom,
|
|
s.date_deb AS date_site,
|
|
s6.date AS date_geom,
|
|
s3.nom_court AS type_milieu,
|
|
s4.nom AS type_site,
|
|
s5.nom AS typo_sdage,
|
|
s.remarques AS rmq_site,
|
|
s6.rmq_fct_majeur,
|
|
s6.rmq_interet_patri,
|
|
s6.rmq_bilan_menace,
|
|
s6.rmq_orient_act,
|
|
s6.rmq_usage_process,
|
|
s62.code_cb,
|
|
s62.lib_cb,
|
|
ct1.fct_bio,
|
|
ct1.fct_hydro,
|
|
ct1.int_patri,
|
|
ct1."val_socioEco",
|
|
ct2.crit_delim,
|
|
ct2.crit_def_esp,
|
|
s65.activ_hum,
|
|
s65.impact,
|
|
s65."position",
|
|
s65.rmq_activ_hum,
|
|
(string_to_array(ct3.entree_eau,'//'))[1] entree_eau_reg,
|
|
(string_to_array(ct3.entree_eau,'//'))[2] entree_eau_perm,
|
|
(string_to_array(ct3.entree_eau,'//'))[3] entree_eau_topo,
|
|
(string_to_array(ct3.sortie_eau,'//'))[1] sortie_eau_reg,
|
|
(string_to_array(ct3.sortie_eau,'//'))[2] sortie_eau_perm,
|
|
(string_to_array(ct3.sortie_eau,'//'))[3] sortie_eau_topo,
|
|
s63.connexion,
|
|
CASE WHEN s64."valid" THEN s64.id_origsub END origine_sub,
|
|
CASE WHEN s64."valid" THEN s641.nom END freq_sub,
|
|
CASE WHEN s64."valid" THEN s642.nom END etendu_sub
|
|
FROM sites.sites s
|
|
JOIN temp1 w USING (id)
|
|
LEFT JOIN tmp_auteur_site s1 ON s.id = s1.id
|
|
LEFT JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
LEFT JOIN sites.type_site s4 ON s.id_type_site::text = s4.id::text
|
|
LEFT JOIN sites.typo_sdage s5 ON s.id_typo_sdage::text = s5.id::text
|
|
LEFT JOIN (sites.r_sites_geom s6
|
|
LEFT JOIN tmp_auteur_geom s61 ON s6.id = s61.id
|
|
LEFT JOIN hab s62 ON s6.id = s62.id_geom_site
|
|
LEFT JOIN cnx s63 ON s6.id = s63.id_geom_site
|
|
LEFT JOIN (zones_humides.r_site_sub s64
|
|
JOIN (zones_humides.param_sub s641 JOIN zones_humides.type_param_sub s6411 ON s6411.id = s641.id_type) ON s64.id_freqsub = s641.id
|
|
JOIN (zones_humides.param_sub s642 JOIN zones_humides.type_param_sub s6421 ON s6421.id = s642.id_type) ON s64.id_etendsub = s642.id
|
|
) ON s6.id = s64.id_geom_site
|
|
LEFT JOIN usagprocess s65 ON s6.id = s65.id_geom_site
|
|
LEFT JOIN crosstab(
|
|
'SELECT
|
|
s63.id_geom_site::bigint,
|
|
s632.nom_court type_param,
|
|
string_agg(
|
|
CASE WHEN s63.description IS NULL THEN s631.nom
|
|
WHEN s63.description = '''' THEN s631.nom
|
|
ELSE CONCAT(s631.nom,'' ('',s63.description,'')'') END,
|
|
'';'') fct
|
|
FROM zones_humides.r_site_fctecosociopatri s63
|
|
JOIN (zones_humides.param_fct_eco_socio_patri s631
|
|
JOIN zones_humides.type_param_fct s632 ON s631.id_type = s632.id
|
|
) ON s63.id_fct = s631.id
|
|
WHERE s63."valid"
|
|
GROUP BY 1,2
|
|
ORDER BY 1,2,3',
|
|
'SELECT DISTINCT s632.nom_court FROM zones_humides.r_site_fctecosociopatri s63
|
|
JOIN (zones_humides.param_fct_eco_socio_patri s631
|
|
JOIN zones_humides.type_param_fct s632 ON s631.id_type = s632.id
|
|
) ON s63.id_fct = s631.id
|
|
ORDER BY 1 DESC;'
|
|
) AS ct1 ("id_geom_site" bigint, "fct_bio" text, "fct_hydro" text, "int_patri" text, "val_socioEco" text) ON s6.id = ct1.id_geom_site
|
|
LEFT JOIN crosstab(
|
|
'SELECT
|
|
s64.id_geom_site::bigint,
|
|
s642.nom_court type_param,
|
|
string_agg(
|
|
CASE WHEN s64.description IS NULL THEN s641.nom
|
|
WHEN s64.description = '''' THEN s641.nom
|
|
ELSE CONCAT(s641.nom,'' ('',s64.description,'')'') END,
|
|
'';'') fct
|
|
FROM zones_humides.r_site_critdelim s64
|
|
JOIN (zones_humides.param_delim_fct s641
|
|
JOIN zones_humides.type_param_delim_fct s642 ON s641.id_type = s642.id
|
|
) ON s64.id_crit_delim = s641.id
|
|
WHERE s64."valid"
|
|
GROUP BY 1,2
|
|
ORDER BY 1,2,3'
|
|
) AS ct2 ("id_geom_site" bigint, "crit_delim" text, "crit_def_esp" text) ON s6.id = ct2.id_geom_site
|
|
LEFT JOIN crosstab(
|
|
'SELECT
|
|
s64.id_geom_site,
|
|
s64.in_out,
|
|
CONCAT(
|
|
--''reg_hydro : '',
|
|
string_agg(s641.nom,'';''),''//'',
|
|
--''permanence : '',
|
|
string_agg(s642.nom,'';''),''//'',
|
|
--''rmq_toponymie : '',
|
|
string_agg(s64.rmq_toponymie,'';'')
|
|
) hydro
|
|
FROM zones_humides.r_site_reghydro s64
|
|
LEFT JOIN zones_humides.param_reg_hydro s641 ON s64.id_reg_hydro = s641.id
|
|
LEFT JOIN zones_humides.param_permanence s642 ON s64.id_permanence = s642.id
|
|
WHERE s64."valid"
|
|
GROUP BY 1,2
|
|
ORDER BY 1,2',
|
|
'SELECT DISTINCT in_out FROM zones_humides.r_site_reghydro ORDER BY 1 DESC;'
|
|
) AS ct3 ("id_geom_site" bigint, "entree_eau" text, "sortie_eau" text) ON s6.id = ct3.id_geom_site
|
|
|
|
) ON s.id::text = s6.id_site::text
|
|
WHERE s.date_fin IS NULL
|
|
AND s3.nom_court::text = 'Milieux humides'::text
|
|
AND s6.date = w.date
|
|
--AND s63."valid" AND s64."valid"
|
|
ORDER BY s.id ASC NULLS FIRST;
|
|
"""
|
|
grant = """
|
|
GRANT ALL ON TABLE zones_humides.v_zoneshumides TO grp_admin;
|
|
GRANT SELECT ON TABLE zones_humides.v_zoneshumides TO grp_consult;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(v_zoneshumides)
|
|
cnx.execute(grant)
|
|
|
|
|
|
|
|
|
|
v_pelouseseche = """
|
|
DROP VIEW IF EXISTS ps.v_pelouseseches;
|
|
CREATE OR REPLACE VIEW ps.v_pelouseseches
|
|
AS WITH temp1 as (
|
|
SELECT DISTINCT ON (s.id)
|
|
s.id,
|
|
max(s6.date) "date"
|
|
FROM sites.sites s
|
|
JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
JOIN sites.r_sites_geom s6 ON s.id::text = s6.id_site::text
|
|
WHERE s.date_fin IS NULL
|
|
AND s3.nom_court::text = 'Pelouses sèches'::text
|
|
GROUP BY s.id
|
|
), hab as (
|
|
SELECT
|
|
id_geom_site,
|
|
date,
|
|
(string_to_array(hab1,'//'))[1] n_hab1,
|
|
(string_to_array(hab1,'//'))[2] cb_hab1,
|
|
(string_to_array(hab1,'//'))[3] lb_hab1,
|
|
(string_to_array(hab2,'//'))[1] n_hab2,
|
|
(string_to_array(hab2,'//'))[2] cb_hab2,
|
|
(string_to_array(hab2,'//'))[3] lb_hab2,
|
|
(string_to_array(hab3,'//'))[1] n_hab3,
|
|
(string_to_array(hab3,'//'))[2] cb_hab3,
|
|
(string_to_array(hab3,'//'))[3] lb_hab3,
|
|
(string_to_array(hab4,'//'))[1] n_hab4,
|
|
(string_to_array(hab4,'//'))[2] cb_hab4,
|
|
(string_to_array(hab4,'//'))[3] lb_hab4
|
|
FROM crosstab(
|
|
$$WITH tmp as (
|
|
SELECT
|
|
id_geom_site,
|
|
MAX("date") date
|
|
FROM ps.r_site_habitat
|
|
GROUP BY 1
|
|
)
|
|
SELECT
|
|
s61.id_geom_site,
|
|
s61.date,
|
|
s61.index,
|
|
CONCAT(s61.n_hab,'//',
|
|
s62.code_hab,'//',
|
|
s621.lb_hab_fr) hab
|
|
FROM ps.r_site_habitat s61
|
|
JOIN (ps.r_hab_cb s62 JOIN ref_habitats.corine_biotope s621 ON s62.code_hab = s621.id) ON s61.id = s62.id_sitehab
|
|
JOIN tmp USING (id_geom_site)
|
|
WHERE s61.date = tmp.date
|
|
ORDER BY 1,2,3;$$,
|
|
'SELECT DISTINCT index FROM ps.r_site_habitat'
|
|
) AS ct ("id_geom_site" bigint, "date" date, "hab1" text, "hab2" text, "hab3" text, "hab4" text)
|
|
), tmp_auteur_site as (
|
|
SELECT
|
|
s.id,
|
|
string_agg(btrim(concat(s11.nom, ' ', s11.prenom,' (',COALESCE(s111.abbrev, s111.nom),')')),';') AS auteur_site
|
|
FROM sites.sites s
|
|
JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
LEFT JOIN (sites.r_sites_auteur s1 JOIN (personnes.personne s11
|
|
JOIN personnes.organisme s111 ON s11.id_organisme = s111.id) ON s1.id_auteur = s11.id
|
|
) ON s.id::text = s1.id_site::text
|
|
WHERE s.date_fin IS NULL
|
|
AND s3.nom_court::text = 'Pelouses sèches'::text
|
|
GROUP BY s.id
|
|
), tmp_auteur_geom as (
|
|
SELECT
|
|
s6.id,
|
|
string_agg(btrim(concat(s611.nom, ' ', s611.prenom,' (',COALESCE(s6111.abbrev, s6111.nom),')')),';') AS auteur_geom
|
|
FROM sites.sites s
|
|
JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
LEFT JOIN (sites.r_sites_geom s6
|
|
LEFT JOIN (sites.r_geomsites_auteur s61 JOIN (personnes.personne s611
|
|
JOIN personnes.organisme s6111 ON s611.id_organisme = s6111.id) ON s61.id_auteur = s611.id
|
|
) ON s6.id = s61.id_geom_site
|
|
) ON s.id = s6.id_site
|
|
WHERE s.date_fin IS NULL
|
|
AND s3.nom_court::text = 'Pelouses sèches'::text
|
|
GROUP BY s6.id
|
|
)
|
|
SELECT DISTINCT ON (s.id)
|
|
s.id AS site_code,
|
|
s.nom,
|
|
s1.auteur_site,
|
|
s61.auteur_geom,
|
|
s6.geom,
|
|
s.date_deb AS date_site,
|
|
s6.date AS date_geom,
|
|
s3.nom_court AS type_milieu,
|
|
s4.nom AS type_site,
|
|
s.remarques AS rmq_site,
|
|
s6.rmq_fct_majeur,
|
|
s6.rmq_interet_patri,
|
|
s6.rmq_bilan_menace,
|
|
s6.rmq_orient_act,
|
|
s6.rmq_usage_process,
|
|
ct1.statut,
|
|
ct1.pratique,
|
|
ct1.recouvrement,
|
|
ct1.embrouss,
|
|
ct1.taux tx_embrouss,
|
|
hab.n_hab1,
|
|
hab.cb_hab1,
|
|
hab.lb_hab1,
|
|
hab.n_hab2,
|
|
hab.cb_hab2,
|
|
hab.lb_hab2,
|
|
hab.n_hab3,
|
|
hab.cb_hab3,
|
|
hab.lb_hab3,
|
|
hab.n_hab4,
|
|
hab.cb_hab4,
|
|
hab.lb_hab4
|
|
FROM sites.sites s
|
|
JOIN temp1 w USING (id)
|
|
LEFT JOIN tmp_auteur_site s1 ON s.id = s1.id
|
|
LEFT JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
LEFT JOIN sites.type_site s4 ON s.id_type_site::text = s4.id::text
|
|
LEFT JOIN (sites.r_sites_geom s6
|
|
LEFT JOIN tmp_auteur_geom s61 ON s6.id = s61.id
|
|
LEFT JOIN hab ON s6.id = hab.id_geom_site
|
|
LEFT JOIN crosstab(
|
|
'SELECT
|
|
s63.id_geom_site::bigint,
|
|
s63.taux,
|
|
s632.nom,
|
|
s631.description
|
|
FROM ps.r_site_param s63
|
|
JOIN (ps.param s631
|
|
JOIN ps.type_param s632 ON s631.id_type = s632.id
|
|
) ON s63.id_param = s631.id
|
|
--GROUP BY 1,2
|
|
ORDER BY 1,2,3',
|
|
'SELECT nom FROM ps.type_param'
|
|
) AS ct1 ("id_geom_site" bigint, "taux" text, "recouvrement" text, "embrouss" text, "pratique" text, "statut" text) ON s6.id = ct1.id_geom_site
|
|
) ON s6.id_site = s.id
|
|
WHERE s.date_fin IS NULL AND s3.nom_court::text = 'Pelouses sèches'::text
|
|
ORDER BY s.id, s6.date DESC NULLS LAST
|
|
;"""
|
|
grant = """
|
|
GRANT ALL ON TABLE ps.v_pelouseseches TO grp_admin;
|
|
GRANT SELECT ON TABLE ps.v_pelouseseches TO grp_consult;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(v_pelouseseche)
|
|
cnx.execute(grant)
|
|
|
|
|
|
|
|
###########################
|
|
######## TEST CODE ########
|
|
###########################
|
|
|
|
select_import = """
|
|
WITH temp1 as (
|
|
SELECT DISTINCT ON (s.id)
|
|
s.id,
|
|
max(s6.date) "date"
|
|
FROM sites.sites s
|
|
JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
JOIN sites.r_sites_geom s6 ON s.id::text = s6.id_site::text
|
|
WHERE s.date_fin IS NULL
|
|
AND s3.nom_court::text = 'Pelouses sèches'::text
|
|
GROUP BY s.id
|
|
), tmp_auteur_site as (
|
|
SELECT
|
|
s.id,
|
|
string_agg(btrim(concat(s11.nom, ' ', s11.prenom,' (',COALESCE(s111.abbrev, s111.nom),')')),';') AS auteur_site
|
|
FROM sites.sites s
|
|
JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
LEFT JOIN (sites.r_sites_auteur s1 JOIN (personnes.personne s11
|
|
JOIN personnes.organisme s111 ON s11.id_organisme = s111.id) ON s1.id_auteur = s11.id
|
|
) ON s.id::text = s1.id_site::text
|
|
WHERE s.date_fin IS NULL
|
|
AND s3.nom_court::text = 'Pelouses sèches'::text
|
|
GROUP BY s.id
|
|
), tmp_auteur_geom as (
|
|
SELECT
|
|
s6.id,
|
|
string_agg(btrim(concat(s611.nom, ' ', s611.prenom,' (',COALESCE(s6111.abbrev, s6111.nom),')')),';') AS auteur_geom
|
|
FROM sites.sites s
|
|
JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
LEFT JOIN (sites.r_sites_geom s6
|
|
LEFT JOIN (sites.r_geomsites_auteur s61 JOIN (personnes.personne s611
|
|
JOIN personnes.organisme s6111 ON s611.id_organisme = s6111.id) ON s61.id_auteur = s611.id
|
|
) ON s6.id = s61.id_geom_site
|
|
) ON s.id = s6.id_site
|
|
WHERE s.date_fin IS NULL
|
|
AND s3.nom_court::text = 'Pelouses sèches'::text
|
|
GROUP BY s6.id
|
|
)
|
|
SELECT DISTINCT ON (s.id)
|
|
s.id AS site_code,
|
|
s.nom,
|
|
s1.auteur_site,
|
|
s61.auteur_geom,
|
|
s6.geom,
|
|
s.date_deb AS date_site,
|
|
s6.date AS date_geom,
|
|
s3.nom_court AS type_milieu,
|
|
s4.nom AS type_site,
|
|
s.remarques AS rmq_site,
|
|
s6.rmq_fct_majeur,
|
|
s6.rmq_interet_patri,
|
|
s6.rmq_bilan_menace,
|
|
s6.rmq_orient_act,
|
|
s6.rmq_usage_process
|
|
FROM sites.sites s
|
|
JOIN temp1 w USING (id)
|
|
LEFT JOIN tmp_auteur_site s1 ON s.id = s1.id
|
|
LEFT JOIN sites.type_milieu s3 ON s.id_type_milieu = s3.id
|
|
LEFT JOIN sites.type_site s4 ON s.id_type_site::text = s4.id::text
|
|
LEFT JOIN (sites.r_sites_geom s6
|
|
LEFT JOIN tmp_auteur_geom s61 ON s6.id = s61.id
|
|
LEFT JOIN crosstab(
|
|
'SELECT
|
|
s63.id_geom_site::bigint,
|
|
s63.taux tx_embrouss,
|
|
s632.nom type_param,
|
|
s631.description param
|
|
FROM ps.r_site_param s63
|
|
JOIN (ps.param s631
|
|
JOIN ps.type_param s632 ON s631.id_type = s632.id
|
|
) ON s63.id_param = s631.id
|
|
--GROUP BY 1,2
|
|
ORDER BY 1,2,3',
|
|
'SELECT nom FROM ps.type_param'
|
|
) AS ct1 ("id_geom_site" bigint, "taux" text, "statut" text, "embrouss" text, "pratique" text, "recouvremt" text) ON s6.id = ct1.id_geom_site
|
|
) ON s6.id_site = s.id
|
|
WHERE s.date_fin IS NULL AND s3.nom_court::text = 'Pelouses sèches'::text
|
|
ORDER BY s.id, s6.date DESC NULLS LAST
|
|
;
|
|
"""
|
|
|
|
select_import = """
|
|
SELECT * FROM zones_humides.v_zoneshumides
|
|
;
|
|
"""
|
|
data = gpd.read_postgis(
|
|
sql = text(select_import),
|
|
con = con,
|
|
geom_col='geom') |