1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92
|
select ordre,"Type de zonage", nom, sum("Surface totale origine (ha)") AS "Surface totale origine (ha)",sum("Surface intersectée (ha)") as "Surface intersectée (ha)",gez_id_geom_zonage from (
--Gestion
--sites du conservatoir
SELECT gez_id_geom_zonage,'1' as "ordre", 'Sites du conservatoire du littoral' as "Type de zonage",nom, round(cast(superficie as numeric),2) AS "Surface totale origine (ha)", round(cast(st_area(st_intersection(gzc.gez_geometry,t.the_geom))/10000 as numeric),2) as "Surface intersectée (ha)", 'Pas de lien' As "Lien fiche"
FROM carmen.terrains_du_conservatoire_du_littoral t
JOIN tv_geom_zonages_commune gzc ON ST_INTERSECTS(gzc.gez_geometry, t.the_geom)
UNION
--Inventaires
--znieff de type I
SELECT gez_id_geom_zonage,'2' as "ordre", 'Znieff de type I' as "Type de zonage",nom, round(cast(surfsig_ha as numeric),2) AS "Surface totale origine (ha)", round(cast(st_area(st_intersection(gzc.gez_geometry,t.the_geom))/10000 as numeric),2) as "Surface intersectée (ha)", fic_inter As "Lien fiche"
FROM carmen.znieff_de_type_i t
JOIN tv_geom_zonages_commune gzc ON ST_INTERSECTS(gzc.gez_geometry, t.the_geom)
UNION
--znieff de type II
SELECT gez_id_geom_zonage,'3' as "ordre",'Znieff de type II' as "Type de zonage",nom, round(cast(surfsig_ha as numeric),2) AS "Surface totale origine (ha)", round(cast(st_area(st_intersection(gzc.gez_geometry,t.the_geom))/10000 as numeric),2) as "Surface intersectée (ha)", fic_inter As "Lien fiche"
FROM carmen.znieff_de_type_ii t
JOIN tv_geom_zonages_commune gzc ON ST_INTERSECTS(gzc.gez_geometry, t.the_geom)
UNION
--znieff zico
SELECT gez_id_geom_zonage,'4' as "ordre", 'Zico' as "Type de zonage",nom, round(cast(surfsig_ha as numeric),2) AS "Surface totale origine (ha)", round(cast(st_area(st_intersection(gzc.gez_geometry,t.the_geom))/10000 as numeric),2) as "Surface intersectée (ha)", fic_inter As "Lien fiche"
FROM carmen.zico__oiseaux_ t
JOIN tv_geom_zonages_commune gzc ON ST_INTERSECTS(gzc.gez_geometry, t.the_geom)
UNION
--N2000
--habitats
SELECT gez_id_geom_zonage,'5' as "ordre", 'N2000 directives habitats' as "Type de zonage",nom, round(cast(surfsig_ha as numeric),2) AS "Surface totale origine (ha)", round(cast(st_area(st_intersection(gzc.gez_geometry,t.the_geom))/10000 as numeric),2) as "Surface intersectée (ha)", fic_inter As "Lien fiche"
FROM carmen.n2000_directive_habitats t
JOIN tv_geom_zonages_commune gzc ON ST_INTERSECTS(gzc.gez_geometry, t.the_geom)
UNION
--oiseaux
SELECT gez_id_geom_zonage,'6' as "ordre", 'N2000 directives oiseaux' as "Type de zonage",nom, round(cast(surfsig_ha as numeric),2) AS "Surface totale origine (ha)", round(cast(st_area(st_intersection(gzc.gez_geometry,t.the_geom))/10000 as numeric),2) as "Surface intersectée (ha)", fic_inter As "Lien fiche"
FROM carmen.n2000_directive_oiseaux t
JOIN tv_geom_zonages_commune gzc ON ST_INTERSECTS(gzc.gez_geometry, t.the_geom)
UNION
--paysages
--Sites classés
SELECT gez_id_geom_zonage,'7' as "ordre", 'Sites Classés' as "Type de zonage",nom, round(cast(surfsig_ha as numeric),2) AS "Surface totale origine (ha)", round(cast(st_area(st_intersection(gzc.gez_geometry,t.the_geom))/10000 as numeric),2) as "Surface intersectée (ha)", fic_inter As "Lien fiche"
FROM carmen.sites_classes t
JOIN tv_geom_zonages_commune gzc ON ST_INTERSECTS(gzc.gez_geometry, t.the_geom)
UNION
--Sites inscrits
SELECT gez_id_geom_zonage,'8' as "ordre", 'Sites inscrits' as "Type de zonage", nom, round(cast(surfsig_ha as numeric),2) AS "Surface totale origine (ha)", round(cast(st_area(st_intersection(gzc.gez_geometry,t.the_geom))/10000 as numeric),2) as "Surface intersectée (ha)", fic_inter As "Lien fiche"
FROM carmen.sites_inscrits t
JOIN tv_geom_zonages_commune gzc ON ST_INTERSECTS(gzc.gez_geometry, t.the_geom)
UNION
--Réglementations
--APPB
SELECT gez_id_geom_zonage,'9' as "ordre", 'APPB' as "Type de zonage",nom, round(cast(superficie as numeric),2) AS "Surface totale origine (ha)", round(cast(st_area(st_intersection(gzc.gez_geometry,t.the_geom))/10000 as numeric),2) as "Surface intersectée (ha)", 'Pas de lien' As "Lien fiche"
FROM carmen.arretes_de_protection_de_biotope t
JOIN tv_geom_zonages_commune gzc ON ST_INTERSECTS(gzc.gez_geometry, t.the_geom)
UNION
--Réserves biologiques ONF
SELECT gez_id_geom_zonage,'10' as "ordre", 'Réserves biologiques ONF' as "Type de zonage",nom, round(cast(surfsig_ha as numeric),2) AS "Surface totale origine (ha)", round(cast(st_area(st_intersection(gzc.gez_geometry,t.the_geom))/10000 as numeric),2) as "Surface intersectée (ha)", fic_inter As "Lien fiche"
FROM carmen.reserves_biologiques_onf t
JOIN tv_geom_zonages_commune gzc ON ST_INTERSECTS(gzc.gez_geometry, t.the_geom)
UNION
--Réserves naturelles nationales
SELECT gez_id_geom_zonage,'11' as "ordre", 'Réserves naturelles nationales' as "Type de zonage",nom, round(cast(surfsig_ha as numeric),2) AS "Surface totale origine (ha)", round(cast(st_area(st_intersection(gzc.gez_geometry,t.the_geom))/10000 as numeric),2) as "Surface intersectée (ha)", fic_inter As "Lien fiche"
FROM carmen.reserves_naturelles_nationales t
JOIN tv_geom_zonages_commune gzc ON ST_INTERSECTS(gzc.gez_geometry, t.the_geom)
UNION
--Réserves naturelles régionales
SELECT gez_id_geom_zonage,'12' as "ordre", 'Réserves naturelles régionales' as "Type de zonage",nom, round(cast(surfsig_ha as numeric),2) AS "Surface totale origine (ha)", round(cast(st_area(st_intersection(gzc.gez_geometry,t.the_geom))/10000 as numeric),2) as "Surface intersectée (ha)", fic_inter As "Lien fiche"
FROM carmen.reserves_naturelles_regionales t
JOIN tv_geom_zonages_commune gzc ON ST_INTERSECTS(gzc.gez_geometry, t.the_geom)
) t
WHERE gez_id_geom_zonage = 24723
group by nom,"Type de zonage", ordre,gez_id_geom_zonage ORDER BY ordre asc |
Partager