653 lines
26 KiB
Python
653 lines
26 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-.
|
|
|
|
from sqlalchemy import text
|
|
from datetime import datetime as dt
|
|
import geopandas as gpd
|
|
from pycen import con
|
|
# Parametres bdd
|
|
from sqlalchemy import create_engine, text
|
|
from sqlalchemy.engine import URL
|
|
user = 'cen_admin'
|
|
pwd = '#CEN38@venir'
|
|
adr = '91.134.194.221'
|
|
port = '5432'
|
|
base = 'azalee_20240731'
|
|
|
|
url = URL.create('postgresql+psycopg2',
|
|
username=user,
|
|
password=pwd,
|
|
host=adr,
|
|
database=base,
|
|
)
|
|
# con = create_engine(url)
|
|
|
|
drop_v_zh = 'DROP VIEW IF EXISTS zones_humides.v_zoneshumides CASCADE;'
|
|
with con.begin() as cnx:
|
|
cnx.execute(drop_v_zh)
|
|
|
|
|
|
v_zh_hab = """
|
|
DROP VIEW IF EXISTS zones_humides.v_zh_hab;
|
|
CREATE OR REPLACE VIEW zones_humides.v_zh_hab
|
|
AS
|
|
WITH author AS (
|
|
SELECT DISTINCT ON (id_sitehab)
|
|
c.id_sitehab,
|
|
string_agg(c1.auteur,';' ORDER BY c1.auteur) auteur
|
|
FROM zones_humides.r_rsitehab_auteur c
|
|
JOIN personnes.v_personne c1 ON c1.id = c.id_auteur
|
|
GROUP BY c.id_sitehab
|
|
ORDER BY 1
|
|
),
|
|
d_max as (
|
|
SELECT distinct on (id_site)
|
|
a.id_site,
|
|
c.auteur,
|
|
a."date"
|
|
FROM zones_humides.r_site_habitat a
|
|
JOIN author c ON c.id_sitehab = a.id
|
|
WHERE a."valid"
|
|
ORDER BY a.id_site, "date" DESC
|
|
)
|
|
SELECT --distinct on (a.id_cb)
|
|
d.id_site,
|
|
d.auteur,
|
|
d."date",
|
|
string_agg(a.id_cb,';' order by a.id_cb asc) code_cb,
|
|
string_agg(b.lb_hab_fr,';' order by a.id_cb asc) lib_cb
|
|
FROM (SELECT distinct on (id_site,id_cb) * FROM zones_humides.r_site_habitat ) a
|
|
JOIN ref_habitats.corine_biotope b ON a.id_cb = b.id
|
|
JOIN d_max d on a.id_site = d.id_site
|
|
WHERE a."valid"
|
|
GROUP BY 1,2,3
|
|
;"""
|
|
grant = """
|
|
GRANT ALL ON TABLE zones_humides.v_zh_hab TO grp_admin;
|
|
GRANT SELECT ON TABLE zones_humides.v_zh_hab TO grp_consult;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(text(v_zh_hab))
|
|
cnx.execute(grant)
|
|
|
|
|
|
v_zh_usgprocess = """
|
|
DROP VIEW IF EXISTS zones_humides.v_zh_usgprocess;
|
|
CREATE OR REPLACE VIEW zones_humides.v_zh_usgprocess
|
|
AS
|
|
WITH auteur AS (
|
|
SELECT DISTINCT ON (id_siteusage)
|
|
c.id_siteusage,
|
|
string_agg(c1.auteur,';' ORDER BY c1.auteur) auteur
|
|
FROM zones_humides.r_rsiteusage_auteur c
|
|
JOIN personnes.v_personne c1 ON c1.id = c.id_auteur
|
|
GROUP BY c.id_siteusage
|
|
ORDER BY 1
|
|
), usgprocess as (
|
|
SELECT distinct on (a.id_site,a1.nom,a2.nom)
|
|
--a.id_geom_site,
|
|
a.id_site,
|
|
c.auteur,
|
|
a."date",
|
|
a1.nom activ_hum,
|
|
a.activ_hum_autre,
|
|
a2.nom impact,
|
|
a3.nom "position",
|
|
a.remarques rmq_activ_hum
|
|
FROM zones_humides.r_site_usageprocess a
|
|
JOIN zones_humides.param_activ_hum a1 ON a.id_activ_hum = a1.id
|
|
JOIN zones_humides.param_impact a2 ON a.id_impact = a2.id
|
|
JOIN zones_humides.param_position a3 ON a.id_position = a3.id
|
|
JOIN auteur c ON c.id_siteusage = a.id
|
|
WHERE a."valid"
|
|
--AND date = a.date
|
|
--GROUP BY a.id_geom_site,c.auteur,a1.nom,a.activ_hum_autre,a2.nom,a3.nom,a.remarques
|
|
--GROUP BY a.id_site,c.auteur,a1.nom,a.activ_hum_autre,a2.nom,a3.nom,a.remarques
|
|
ORDER BY a.id_site,a1.nom,a2.nom, a."date" desc,row_number() OVER (ORDER BY a.id_site) desc
|
|
)
|
|
SELECT DISTINCT ON (id_site)
|
|
--id_geom_site,
|
|
id_site,
|
|
auteur,
|
|
"date",
|
|
string_agg(
|
|
CASE WHEN activ_hum = 'Autre (préciser dans l''encart réservé aux remarques)' THEN activ_hum_autre
|
|
ELSE activ_hum END,';') activ_hum,
|
|
string_agg(
|
|
CASE WHEN impact = 'Autre (préciser dans l''encart réservé aux remarques)' THEN rmq_activ_hum
|
|
ELSE impact END,';') impact,
|
|
string_agg("position",';') "position",
|
|
string_agg(
|
|
CASE WHEN impact = 'Autre (préciser dans l''encart réservé aux remarques)' THEN NULL
|
|
ELSE rmq_activ_hum END,';') rmq_activ_hum
|
|
FROM usgprocess
|
|
GROUP BY 1,2,3
|
|
ORDER BY id_site, "date" desc,row_number() OVER (ORDER BY id_site) desc
|
|
;"""
|
|
grant = """
|
|
GRANT ALL ON TABLE zones_humides.v_zh_usgprocess TO grp_admin;
|
|
GRANT SELECT ON TABLE zones_humides.v_zh_usgprocess TO grp_consult;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(v_zh_usgprocess)
|
|
cnx.execute(grant)
|
|
|
|
|
|
v_zh_connex = """
|
|
DROP VIEW IF EXISTS zones_humides.v_zh_connex;
|
|
CREATE OR REPLACE VIEW zones_humides.v_zh_connex
|
|
AS
|
|
WITH auteur AS (
|
|
SELECT DISTINCT ON (id_siteconnect)
|
|
c.id_siteconnect,
|
|
string_agg(c1.auteur,';' ORDER BY c1.auteur) auteur
|
|
FROM zones_humides.r_rsiteconnect_auteur c
|
|
JOIN personnes.v_personne c1 ON c1.id = c.id_auteur
|
|
GROUP BY c.id_siteconnect
|
|
ORDER BY 1
|
|
)
|
|
SELECT DISTINCT ON (id_site)
|
|
--a.id_geom_site,
|
|
a.id_site,
|
|
c.auteur,
|
|
a."date",
|
|
b.nom connexion
|
|
FROM zones_humides.r_site_type_connect a
|
|
JOIN zones_humides.param_type_connect b ON a.id_param_connect = b.id
|
|
JOIN auteur c ON c.id_siteconnect = a.id
|
|
WHERE a."valid"
|
|
--AND a.date = (SELECT MAX(date) FROM zones_humides.r_site_type_connect WHERE id_site = a.id_site)
|
|
ORDER BY a.id_site,"date" desc,row_number() OVER (ORDER BY a.id_site) desc
|
|
;"""
|
|
grant = """
|
|
GRANT ALL ON TABLE zones_humides.v_zh_connex TO grp_admin;
|
|
GRANT SELECT ON TABLE zones_humides.v_zh_connex TO grp_consult;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(v_zh_connex)
|
|
cnx.execute(grant)
|
|
|
|
|
|
v_zh_submertion = """
|
|
DROP VIEW IF EXISTS zones_humides.v_zh_submertion;
|
|
CREATE OR REPLACE VIEW zones_humides.v_zh_submertion
|
|
AS
|
|
WITH auteur AS (
|
|
SELECT DISTINCT ON (id_sitesub)
|
|
c.id_sitesub,
|
|
string_agg(c1.auteur,';' ORDER BY c1.auteur) auteur
|
|
FROM zones_humides.r_rsitesub_auteur c
|
|
JOIN personnes.v_personne c1 ON c1.id = c.id_auteur
|
|
GROUP BY c.id_sitesub
|
|
ORDER BY 1
|
|
)
|
|
SELECT DISTINCT ON (a.id_site)
|
|
--a.id_geom_site,
|
|
a.id_site,
|
|
c.auteur,
|
|
a."date",
|
|
a1.nom subm_freq,
|
|
a2.nom subm_etend,
|
|
a.id_origsub subm_orig
|
|
FROM zones_humides.r_site_sub a
|
|
JOIN (zones_humides.param_sub a1 JOIN zones_humides.type_param_sub s6411 ON s6411.id = a1.id_type) ON a.id_freqsub = a1.id
|
|
JOIN (zones_humides.param_sub a2 JOIN zones_humides.type_param_sub s6421 ON s6421.id = a2.id_type) ON a.id_etendsub = a2.id
|
|
JOIN auteur c ON c.id_sitesub = a.id
|
|
WHERE a."valid"
|
|
--AND a.date = (SELECT MAX(date) FROM zones_humides.r_site_sub WHERE id_site = a.id_site)
|
|
AND s6411.nom = 'Submersion fréquente'
|
|
AND s6421.nom = 'Submersion étendue'
|
|
ORDER BY a.id_site,"date" desc,row_number() OVER (ORDER BY a.id_site) desc
|
|
;"""
|
|
grant = """
|
|
GRANT ALL ON TABLE zones_humides.v_zh_submertion TO grp_admin;
|
|
GRANT SELECT ON TABLE zones_humides.v_zh_submertion TO grp_consult;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(v_zh_submertion)
|
|
cnx.execute(grant)
|
|
|
|
|
|
v_zh_fctecosociopatri_cross = """
|
|
DROP VIEW IF EXISTS zones_humides.v_zh_fctecosociopatri_cross;
|
|
CREATE OR REPLACE VIEW zones_humides.v_zh_fctecosociopatri_cross
|
|
AS
|
|
WITH auteur AS (
|
|
SELECT DISTINCT ON (id_sitefct)
|
|
c.id_sitefct,
|
|
string_agg(c1.auteur,';' ORDER BY c1.auteur) auteur
|
|
FROM zones_humides.r_rsitefct_auteur c
|
|
JOIN personnes.v_personne c1 ON c1.id = c.id_auteur
|
|
GROUP BY c.id_sitefct
|
|
ORDER BY 1
|
|
),max_author as (
|
|
SELECT DISTINCT ON (id_site)
|
|
a.id_site,
|
|
--(SELECT regexp_split_to_table(auth,',') FROM (values (string_agg(auteur,',' order by ROW()))) AS value(auth) Limit 1) auteur,
|
|
auteur,
|
|
a."date"
|
|
FROM auteur
|
|
JOIN zones_humides.r_site_fctecosociopatri a ON id_sitefct = a.id
|
|
order by 1,3 DESC,2
|
|
)
|
|
SELECT DISTINCT ON (id_site)
|
|
--id_geom_site,
|
|
id_site,
|
|
--(SELECT auteur ORDER BY ct."date" DESC) "date",
|
|
"date",
|
|
auteur,
|
|
fct_bio,
|
|
fct_hydro,
|
|
int_patri,
|
|
"val_socioEco"
|
|
FROM crosstab(
|
|
$$ WITH tmp_select as (
|
|
SELECT
|
|
max(a."id") id_sitefct,
|
|
id_site,
|
|
--(SELECT regexp_split_to_table(auth,',') FROM (values (string_agg(d.auteur,',' order by ROW()))) AS value(auth) Limit 1) auteur,
|
|
--MAX(a.date) date,
|
|
c.nom_court type_param,
|
|
b.nom,
|
|
string_agg(DISTINCT a.description,'\n') remark
|
|
FROM zones_humides.r_site_fctecosociopatri a
|
|
JOIN (zones_humides.param_fct_eco_socio_patri b
|
|
JOIN zones_humides.type_param_fct c ON b.id_type = c.id
|
|
) ON a.id_fct = b.id
|
|
--JOIN auteur d ON d.id_sitefct = a.id
|
|
WHERE a."valid"
|
|
GROUP BY a.id_site,c.nom_court,b.nom
|
|
ORDER BY a.id_site,c.nom_court DESC,b.nom
|
|
)
|
|
SELECT
|
|
id_site,
|
|
--(SELECT regexp_split_to_table(auth,',') FROM (values (string_agg(d.auteur,',' order by ROW()))) AS value(auth) Limit 1) auteur,
|
|
--MAX("date") "date",
|
|
type_param,
|
|
string_agg(distinct
|
|
CASE WHEN remark IS NULL THEN nom
|
|
WHEN remark = '' THEN nom
|
|
ELSE CONCAT(nom,' (',remark,')') END,
|
|
';') fct
|
|
FROM tmp_select
|
|
--JOIN auteur d using (id_sitefct)
|
|
GROUP BY id_site,type_param
|
|
ORDER BY id_site,type_param;$$,
|
|
$$SELECT DISTINCT nom_court FROM zones_humides.type_param_fct ORDER BY 1 ASC;$$
|
|
) AS ct (
|
|
"id_site" text,
|
|
--"auteur" text,
|
|
--"date" date,
|
|
"fct_bio" text,
|
|
"fct_hydro" text,
|
|
"int_patri" text,
|
|
"val_socioEco" text
|
|
)
|
|
JOIN max_author USING (id_site)
|
|
--GROUP BY id_site,auteur,fct_bio,fct_hydro,int_patri,"val_socioEco"
|
|
--WHERE id_site in ('38CG0110','38RD0057','38VS0063')
|
|
ORDER BY id_site,row_number() OVER (ORDER BY id_site) desc
|
|
;"""
|
|
grant = """
|
|
GRANT ALL ON TABLE zones_humides.v_zh_fctecosociopatri_cross TO grp_admin;
|
|
GRANT SELECT ON TABLE zones_humides.v_zh_fctecosociopatri_cross TO grp_consult;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(v_zh_fctecosociopatri_cross)
|
|
cnx.execute(grant)
|
|
|
|
|
|
v_zh_critdelim_cross = """
|
|
DROP VIEW IF EXISTS zones_humides.v_zh_critdelim_cross;
|
|
CREATE OR REPLACE VIEW zones_humides.v_zh_critdelim_cross
|
|
AS
|
|
WITH auteur AS (
|
|
SELECT DISTINCT ON (id_sitedelim)
|
|
c.id_sitedelim,
|
|
string_agg(c1.auteur,';' ORDER BY c1.auteur) auteur
|
|
FROM zones_humides.r_rsitedelim_auteur c
|
|
JOIN personnes.v_personne c1 ON c1.id = c.id_auteur
|
|
GROUP BY c.id_sitedelim
|
|
ORDER BY 1
|
|
),max_author as (
|
|
SELECT DISTINCT ON (id_site)
|
|
a.id_site,
|
|
--(SELECT regexp_split_to_table(auth,',') FROM (values (string_agg(auteur,',' order by ROW()))) AS value(auth) Limit 1) auteur,
|
|
auteur,
|
|
a."date"
|
|
FROM auteur
|
|
JOIN zones_humides.r_site_critdelim a ON id_sitedelim = a.id
|
|
order by 1,3 DESC,2
|
|
)
|
|
SELECT DISTINCT ON (id_site)
|
|
--id_geom_site,
|
|
id_site,
|
|
"date",
|
|
auteur,
|
|
crit_delim,
|
|
crit_def_esp
|
|
FROM crosstab(
|
|
$$ SELECT
|
|
--a.id_geom_site::bigint,
|
|
id_site,
|
|
c.nom_court type_param,
|
|
string_agg( DISTINCT
|
|
CASE WHEN a.description IS NULL THEN b.nom
|
|
WHEN a.description = '' THEN b.nom
|
|
ELSE CONCAT(b.nom,' (',a.description,')') END,
|
|
'; ') fct
|
|
FROM zones_humides.r_site_critdelim a
|
|
JOIN (zones_humides.param_delim_fct b
|
|
JOIN zones_humides.type_param_delim_fct c ON b.id_type = c.id
|
|
) ON a.id_crit_delim = b.id
|
|
--JOIN auteur d ON d.id_sitedelim = a.id
|
|
WHERE a."valid"
|
|
GROUP BY 1,2
|
|
ORDER BY 1$$,
|
|
$$SELECT DISTINCT nom_court FROM zones_humides.type_param_delim_fct ORDER BY 1 DESC;$$
|
|
) AS ct (
|
|
"id_site" text,
|
|
--"auteur" text,
|
|
--"date" date,
|
|
"crit_delim" text,
|
|
"crit_def_esp" text)
|
|
JOIN max_author USING (id_site)
|
|
--GROUP BY id_site,auteur,crit_delim,crit_def_esp
|
|
--WHERE id_site in ('38CG0110','38RD0057','38VS0063')
|
|
ORDER BY id_site,row_number() OVER (ORDER BY id_site) desc
|
|
;"""
|
|
grant = """
|
|
GRANT ALL ON TABLE zones_humides.v_zh_critdelim_cross TO grp_admin;
|
|
GRANT SELECT ON TABLE zones_humides.v_zh_critdelim_cross TO grp_consult;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(v_zh_critdelim_cross)
|
|
cnx.execute(grant)
|
|
|
|
|
|
v_zh_reghydro_cross = """
|
|
DROP VIEW IF EXISTS zones_humides.v_zh_reghydro_cross;
|
|
CREATE OR REPLACE VIEW zones_humides.v_zh_reghydro_cross
|
|
AS
|
|
SELECT DISTINCT ON (id_site)
|
|
--id_geom_site,
|
|
id_site,
|
|
auteur,
|
|
"date",
|
|
(string_to_array(ct.entree_eau,'//'))[1] entree_eau_reg,
|
|
(string_to_array(ct.entree_eau,'//'))[2] entree_eau_perm,
|
|
(string_to_array(ct.entree_eau,'//'))[3] entree_eau_topo,
|
|
(string_to_array(ct.sortie_eau,'//'))[1] sortie_eau_reg,
|
|
(string_to_array(ct.sortie_eau,'//'))[2] sortie_eau_perm,
|
|
(string_to_array(ct.sortie_eau,'//'))[3] sortie_eau_topo
|
|
FROM crosstab(
|
|
$$WITH auteur AS (
|
|
SELECT DISTINCT ON (id_sitehydro)
|
|
c.id_sitehydro,
|
|
string_agg(c1.auteur,';' ORDER BY c1.auteur) auteur
|
|
FROM zones_humides.r_rsitehydro_auteur c
|
|
JOIN personnes.v_personne c1 ON c1.id = c.id_auteur
|
|
GROUP BY c.id_sitehydro
|
|
ORDER BY 1
|
|
)
|
|
SELECT
|
|
--a.id_geom_site,
|
|
id_site,
|
|
d.auteur,
|
|
MAX(a.date) date,
|
|
a.in_out,
|
|
CONCAT(
|
|
string_agg(a1.nom,';'),'//', -- reg_hydro
|
|
string_agg(a2.nom,';'),'//', -- permanence
|
|
string_agg(a.rmq_toponymie,';') -- rmq_toponymie
|
|
) hydro
|
|
FROM zones_humides.r_site_reghydro a
|
|
LEFT JOIN zones_humides.param_reg_hydro a1 ON a.id_reg_hydro = a1.id
|
|
LEFT JOIN zones_humides.param_permanence a2 ON a.id_permanence = a2.id
|
|
JOIN auteur d ON d.id_sitehydro = a.id
|
|
WHERE a."valid"
|
|
GROUP BY a.id_site,d.auteur,a.in_out
|
|
ORDER BY 1,2$$,
|
|
$$SELECT DISTINCT in_out FROM zones_humides.r_site_reghydro ORDER BY 1 DESC;$$
|
|
) AS ct (
|
|
"id_site" text,
|
|
"auteur" text,
|
|
"date" date,
|
|
"entree_eau" text,
|
|
"sortie_eau" text)
|
|
--GROUP BY id_site,auteur,entree_eau,sortie_eau
|
|
ORDER BY id_site,"date" desc,row_number() OVER (ORDER BY id_site) desc
|
|
;"""
|
|
grant = """
|
|
GRANT ALL ON TABLE zones_humides.v_zh_reghydro_cross TO grp_admin;
|
|
GRANT SELECT ON TABLE zones_humides.v_zh_reghydro_cross TO grp_consult;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(v_zh_reghydro_cross)
|
|
cnx.execute(grant)
|
|
|
|
|
|
v_zoneshumides = """
|
|
DROP VIEW IF EXISTS zones_humides.v_zoneshumides CASCADE;
|
|
CREATE OR REPLACE VIEW zones_humides.v_zoneshumides
|
|
AS
|
|
WITH tmp_auteur_site as (
|
|
SELECT
|
|
s0.id,
|
|
string_agg(s11.auteur,';' ORDER BY s11.auteur) AS auteur_site
|
|
FROM sites.sites s0
|
|
JOIN sites.type_milieu s3 ON s0.id_type_milieu = s3.id
|
|
LEFT JOIN (sites.r_sites_auteur s1
|
|
JOIN personnes.v_personne s11 ON s1.id_auteur = s11.id
|
|
) ON s0.id::text = s1.id_site::text
|
|
WHERE s0.date_fin IS NULL
|
|
AND s3.nom_court::text = 'Milieux humides'::text
|
|
GROUP BY s0.id
|
|
ORDER BY s0.id
|
|
), tmp_auteur_geom as (
|
|
SELECT
|
|
s6.id,
|
|
string_agg(s611.auteur,';' ORDER BY s611.auteur) AS auteur_geom
|
|
FROM sites.sites s0
|
|
JOIN sites.type_milieu s3 ON s0.id_type_milieu = s3.id
|
|
LEFT JOIN (sites.r_sites_geom s6
|
|
LEFT JOIN (sites.r_geomsites_auteur s61
|
|
JOIN personnes.v_personne s611 ON s61.id_auteur = s611.id
|
|
) ON s6.id = s61.id_geom_site
|
|
) ON s0.id = s6.id_site
|
|
WHERE s0.date_fin IS NULL
|
|
AND s3.nom_court::text = 'Milieux humides'::text
|
|
GROUP BY s6.id
|
|
ORDER BY s6.id
|
|
),
|
|
tmp_other_name as (
|
|
SELECT
|
|
id_site_new,
|
|
ARRAY_AGG(id_site_old) id_site_old
|
|
FROM sites.r_site_maj
|
|
WHERE id_site_old IS NOT NULL
|
|
GROUP BY id_site_new
|
|
)
|
|
SELECT
|
|
s0.id AS site_code,
|
|
s7.id_site_old old_code,
|
|
s0.nom,
|
|
s2.autre_nom,
|
|
s1.auteur_site,
|
|
s6.auteur_geom,
|
|
(
|
|
SELECT DISTINCT ON (date) auteur
|
|
FROM (values (s6.date,s6.auteur_geom),(s62.date,s62.auteur),(s63.date,s63.auteur),(s64.date,s64.auteur),(s65.date,s65.auteur),
|
|
(s66.date,s66.auteur),(s67.date,s67.auteur),(s68.date,s68.auteur)) AS value(date,auteur)
|
|
WHERE date = (SELECT MAX(date) FROM (values (s6.date),(s62.date),(s63.date),(s64.date),(s65.date),(s66.date),(s67.date),(s68.date)) AS value(date))
|
|
) as auteur_last_maj,
|
|
s0.date_deb AS date_site,
|
|
s6.date AS date_geom,
|
|
(SELECT MAX(date) FROM (values (s6.date),(s62.date),(s63.date),(s64.date),(s65.date),(s66.date),(s67.date),(s68.date)) AS value(date)) as date_last_maj,
|
|
s3.nom_court AS type_milieu,
|
|
s4.nom AS type_site,
|
|
s5.id||' - '||s5.nom AS typo_sdage,
|
|
s5.mnemo mnemo_sdage,
|
|
s0.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,
|
|
s63.activ_hum,
|
|
s63.impact,
|
|
s63."position",
|
|
s63.rmq_activ_hum,
|
|
s64.connexion,
|
|
s65.subm_orig,
|
|
s65.subm_freq,
|
|
s65.subm_etend,
|
|
s66.fct_bio,
|
|
s66.fct_hydro,
|
|
s66.int_patri,
|
|
s66."val_socioEco",
|
|
s67.crit_delim,
|
|
s67.crit_def_esp,
|
|
s68.entree_eau_reg,
|
|
s68.entree_eau_perm,
|
|
s68.entree_eau_topo,
|
|
s68.sortie_eau_reg,
|
|
s68.sortie_eau_perm,
|
|
s68.sortie_eau_topo,
|
|
s6.id_origine,
|
|
s6.geom
|
|
FROM sites.sites s0
|
|
LEFT JOIN tmp_auteur_site s1 ON s0.id = s1.id
|
|
LEFT JOIN sites.autre_nom s2 ON s0.id = s2.id_site
|
|
LEFT JOIN sites.type_milieu s3 ON s0.id_type_milieu = s3.id
|
|
LEFT JOIN sites.type_site s4 ON s0.id_type_site::text = s4.id::text
|
|
LEFT JOIN sites.typo_sdage s5 ON s0.id_typo_sdage::text = s5.id::text
|
|
LEFT JOIN (SELECT DISTINCT ON (sg.id_site) *
|
|
FROM sites.r_sites_geom sg
|
|
LEFT JOIN tmp_auteur_geom s61 USING (id) --ON s6.id = s61.id
|
|
ORDER BY sg.id_site, "date" DESC, date_insert DESC
|
|
) s6 ON s0.id::text = s6.id_site::text
|
|
LEFT JOIN zones_humides.v_zh_hab s62 ON s0.id = s62.id_site
|
|
LEFT JOIN zones_humides.v_zh_usgprocess s63 ON s0.id = s63.id_site
|
|
LEFT JOIN zones_humides.v_zh_connex s64 ON s0.id = s64.id_site
|
|
LEFT JOIN zones_humides.v_zh_submertion s65 ON s0.id = s65.id_site
|
|
LEFT JOIN zones_humides.v_zh_fctecosociopatri_cross s66 ON s0.id = s66.id_site
|
|
LEFT JOIN zones_humides.v_zh_critdelim_cross s67 ON s0.id = s67.id_site
|
|
LEFT JOIN zones_humides.v_zh_reghydro_cross s68 ON s0.id = s68.id_site
|
|
LEFT JOIN tmp_other_name s7 ON s0.id = s7.id_site_new
|
|
WHERE s0.date_fin IS NULL
|
|
AND s3.nom_court::text = 'Milieux humides'::text
|
|
AND s6.date = (SELECT MAX(date) FROM sites.r_sites_geom WHERE r_sites_geom.id_site = s0.id)
|
|
ORDER BY s0.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;
|
|
"""
|
|
comment = """
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.site_code IS 'Identifiant de la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.old_code IS 'Ancien identifiant de la zone humide pouvant se retrouver dans des données historiques.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.nom IS 'Nom de la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.autre_nom IS 'Autre nom possible de la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.auteur_site IS 'Auteur ayant caractérisé la zone humide pour la première fois.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.auteur_geom IS 'Auteur ayant définis la géometrie actuelle de la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.auteur_last_maj IS 'Auteur le plus récent ayant défini les attributs de la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.date_site IS 'Date de la première description de la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.date_geom IS 'Date de la géométrie.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.date_last_maj IS 'Date des attributs les plus récents.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.type_milieu IS 'Caractérisation du milieu.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.type_site IS '';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.typo_sdage IS 'Typologie sdage de la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.mnemo_sdage IS 'Libellé mnémonique sdage de la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.rmq_site IS 'Remarques générale concernant la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.rmq_fct_majeur IS 'Remarques sur les fonctions majeurs de la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.rmq_interet_patri IS 'Remarques sur les intérêts patrimoniaux de la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.rmq_bilan_menace IS 'Remarques globales sur les menaces qui concernent la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.rmq_orient_act IS 'Remarques sur les orientations et les actes de la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.rmq_usage_process IS 'Remarques concernant les usages et les processus naturels de la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.code_cb IS 'Liste des codes CORINE Biotopes identifiés sur la zone humide. Chaque élément de la liste est séparé par un '';''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.lib_cb IS 'Liste des habitats CORINE Biotopes identifiés sur la zone humide. Chaque élément de la liste est séparé par un '';''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.activ_hum IS 'Liste des activités humaines identifiées sur la zone humide. Chaque élément de la liste est séparé par un '';'' et est lié à la colonne ''impacts'' et ''position''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.impact IS 'Liste des impacts identifiés et engendrés par les activités humaine sur la zone humide. Chaque élément de la liste est séparé par un '';'' et est lié à la colonne ''activ_hum'' et ''position''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides."position" IS 'Liste des potionnements des différentes activités humaines sur la zone humide. Chaque élément de la liste est séparé par un '';'' et est lié à la colonne ''activ_hum'' et ''impact''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.rmq_activ_hum IS 'Remarques concernant activités humaines identifiées sur la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.connexion IS 'Connexion de la zone humide dans son environnement.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.subm_orig IS 'Origine de l''eau submerssive.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.subm_freq IS 'Fréquence de la submersion de la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.subm_etend IS 'Etendue de la submersion de la zone humide.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.fct_bio IS 'Liste des fonctions biologiques de la zone humide. Chaque élément de la liste est séparé par un '';''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.fct_hydro IS 'Liste des fonctions hydrologiques de la zone humide. Chaque élément de la liste est séparé par un '';''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.int_patri IS 'Liste des interêts patrimoniaux de la zone humide. Chaque élément de la liste est séparé par un '';''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides."val_socioEco" IS 'Liste des valeurs socio-economiques de la zone humide. Chaque élément de la liste est séparé par un '';''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.crit_delim IS 'Liste des critères de délimitation de la zone humide. Chaque élément de la liste est séparé par un '';''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.crit_def_esp IS 'Liste des critères de l''espace de fonctionnalité de la zone humide. Chaque élément de la liste est séparé par un '';''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.entree_eau_reg IS 'Liste des entrées d''eau du régime hydrique de la zone humide. Chaque élément de la liste est séparé par un '';''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.entree_eau_perm IS 'Liste des permanances respectivement de chaque entrée d''eau de la zone humide. Chaque élément de la liste est séparé par un '';''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.entree_eau_topo IS 'Liste de la toponymie respectivement de chaque entrée d''eau de la zone humide. Chaque élément de la liste est séparé par un '';''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.sortie_eau_reg IS 'Liste des sorties d''eau du régime hydrique de la zone humide. Chaque élément de la liste est séparé par un '';''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.sortie_eau_perm IS 'Liste des permanances respectivement de chaque sortie d''eau de la zone humide. Chaque élément de la liste est séparé par un '';''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.sortie_eau_topo IS 'Liste de la toponymie respectivement de chaque sortie d''eau de la zone humide. Chaque élément de la liste est séparé par un '';''.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.id_origine IS 'Identifiant d''origine de la zone fournie par un partenaire exterieur.';
|
|
COMMENT ON COLUMN zones_humides.v_zoneshumides.geom IS 'Géometrie de la zone humide.';
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(text(v_zoneshumides))
|
|
cnx.execute(grant)
|
|
cnx.execute(comment)
|
|
|
|
|
|
v_rhomeosite = """
|
|
DROP VIEW IF EXISTS zones_humides.v_rhomeosite;
|
|
CREATE OR REPLACE VIEW zones_humides.v_rhomeosite
|
|
AS
|
|
with t1 as (
|
|
SELECT
|
|
v.site_code,
|
|
r.nom,
|
|
st_area(st_intersection(v.geom, r.geom)) area_intersect
|
|
FROM zones_humides.v_zoneshumides v
|
|
join ref_territoire.ref_biogeo r on st_intersects(v.geom, r.geom)
|
|
--group by 1,2
|
|
), t2 as (
|
|
select
|
|
site_code,
|
|
nom,
|
|
row_number() over (partition by site_code order by area_intersect desc) as ismax_ter
|
|
from t1
|
|
)
|
|
SELECT (v.site_code::text || ' - '::text) || v.nom::text AS "NAME",
|
|
split_part(v.auteur_geom, ' ('::text, 1) AS "REFERENT",
|
|
replace(split_part(v.auteur_geom, ' ('::text, 2), ')'::text, ''::text) AS "ORG",
|
|
split_part(v.typo_sdage, ' - '::text, 1) AS "TYPE",
|
|
CASE
|
|
WHEN t2.nom = 'alpin'::text THEN '1'::text
|
|
WHEN t2.nom = 'continental'::text THEN '2'::text
|
|
WHEN t2.nom = 'mediterraneen'::text THEN '4'::text
|
|
ELSE NULL::text
|
|
END AS "ODONATE",
|
|
v.geom
|
|
FROM zones_humides.v_zoneshumides v
|
|
join t2 on v.site_code = t2.site_code and t2.ismax_ter = 1
|
|
;
|
|
"""
|
|
grant = """
|
|
GRANT ALL ON TABLE zones_humides.v_rhomeosite TO grp_admin;
|
|
GRANT SELECT ON TABLE zones_humides.v_rhomeosite TO grp_consult;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(text(v_rhomeosite))
|
|
cnx.execute(grant)
|
|
|
|
|
|
test = """
|
|
SELECT * FROM zones_humides.v_zoneshumides;
|
|
"""
|
|
data = gpd.pd.read_sql_query(
|
|
sql = text(test),
|
|
con = con)
|
|
|
|
|
|
data[~data.index.isin(data.drop_duplicates(subset='site_code',keep=False).index)]
|
|
data[data.duplicated(subset='site_code',keep=False)] |