28 lines
699 B
Python
28 lines
699 B
Python
|
|
from sqlalchemy import text
|
|
from pycen import con_fon
|
|
|
|
v_veillefonciere = """
|
|
DROP VIEW IF EXISTS _tdb_f6cen.v_veillefonciere;
|
|
CREATE OR REPLACE VIEW _tdb_f6cen.v_veillefonciere
|
|
AS
|
|
SELECT
|
|
row_number() OVER (ORDER BY SPLIT_PART(site_id,'_',1) ASC) AS gid,
|
|
SPLIT_PART(site_id,'_',1) AS site_id,
|
|
SPLIT_PART(site_nom,' - Z',1) AS site_nom,
|
|
enjeu_lib,
|
|
'CONSERVATOIRE D''ESPACES NATURELS ISERE'::text AS cen,
|
|
38::int AS dept,
|
|
st_area(st_union(geom_vf)) AS superficie,
|
|
st_union(geom_vf) geom
|
|
FROM sites.sites
|
|
JOIN sites.d_enjeux USING (enjeu_id)
|
|
WHERE veillefonciere
|
|
GROUP BY 2,3,4
|
|
ORDER BY 1
|
|
;
|
|
"""
|
|
|
|
with con_fon.begin() as cnx:
|
|
cnx.execute(text(v_veillefonciere))
|