 
 
 
-
-
-
--- 
--- /**
--- 
--- Intégration prod ener CIGALE 
--- Dans une nouvelle table
--- 
--- src_prod_energie.tpk_grande_filiere_cigale - Grandes filières avec codes_couleur.
--- src_prod_energie.tpk_detail_filiere_cigale - Détail des filières avec code_couleur pour ENR.
--- src_prod_energie.tpk_filiere - Les id_filiere de la table bilan_prod et les liens vers les deux tables précédentes.
--- 
--- Mise en forme des valeurs
--- - Regroupement 
--- 	* Par grande_filiere_cigale et detail_filiere_cigale
--- 	* Type production
--- - Ajout des champs EPCI et dep dans la table pour éviter les requêtes 
---   trop longues lors de l'extraction
--- */
--- 
--- drop table if exists total.bilan_comm_v5_prod;
--- create table total.bilan_comm_v5_prod as
--- select 
--- 	a.an, 
--- 	a.id_comm, 
--- 	a.id_type_prod, b.lib_type_prod,
--- 	c.id_grande_filiere_cigale, d.grande_filiere_cigale, d.color_grande_filiere_cigale,
--- 	c.id_detail_filiere_cigale, dd.detail_filiere_cigale, dd.color_detail_filiere_cigale,
--- 	sum(a.production) as val, 
--- 	a.id_unite,
--- 	a.id_comm / 1000 as dep,
--- 	e.siren_epci_2017,
--- 	e.nom_epci_2017
--- from total_prod_energie.prod_comm_v1 as a
--- left join src_prod_energie.tpk_type_prod as b using (id_type_prod)
--- left join src_prod_energie.tpk_filiere as c using (id_filiere)
--- left join src_prod_energie.tpk_grande_filiere_cigale as d using (id_grande_filiere_cigale)
--- left join src_prod_energie.tpk_detail_filiere_cigale as dd using (id_detail_filiere_cigale)
--- left join commun.tpk_commune_2015_2016 as e using (id_comm)
--- group by
--- 	a.an, 
--- 	a.id_comm, 
--- 	a.id_type_prod, b.lib_type_prod,
--- 	c.id_grande_filiere_cigale, d.grande_filiere_cigale, d.color_grande_filiere_cigale,
--- 	c.id_detail_filiere_cigale, dd.detail_filiere_cigale, dd.color_detail_filiere_cigale,
--- 	a.id_unite,
--- 	a.id_comm / 1000,
--- 	e.siren_epci_2017,
--- 	e.nom_epci_2017
--- order by
--- 	an, 
--- 	id_comm, 
--- 	id_type_prod, lib_type_prod,
--- 	grande_filiere_cigale,
--- 	detail_filiere_cigale,
--- 	id_unite;
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
-
-
-
-
-
-
-
-
-
-/*
-PARTIE DE COPIE DES TABLES APPLICATION ET DE MISE EN FORME DES DONNEES POUR RAPIDITE D'EXEC
-
-###############################################################################################
-###############################################################################################
-###############################################################################################
-###############################################################################################
-###############################################################################################
-###############################################################################################
-###############################################################################################
-###############################################################################################
-###############################################################################################
-###############################################################################################
-###############################################################################################
-###############################################################################################
-###############################################################################################
-###############################################################################################
-*/
-
-
--- 
--- drop table if exists cigale.epci;
--- create table cigale.epci as 
--- select 
--- 	siren_epci_2017 as siren_epci, 
--- 	nom_epci_2017 as nom_epci,
--- 	superficie
--- from sig.epci_geofla_4326;
--- 
--- SELECT AddGeometryColumn ('cigale','epci','geom',4326,'MULTIPOLYGON',2, false);
--- 
--- update cigale.epci as a
--- set geom = b.geom
--- from sig.epci_geofla_4326 as b
--- where a.siren_epci = b.siren_epci_2017;
--- 
--- alter table cigale.epci add constraint "pk.cigale.epci" primary key (siren_epci);
--- CREATE INDEX "gidx.cigale.epci.geom.gist" ON cigale.epci USING GIST (geom);
--- 
--- comment on table cigale.epci is '
--- EPCI crée à partir des communes GEOFLA 2017
--- Le champ superficie est en hectares
--- Code SQL de création de la table:
--- ----------------------------------------
--- create table cigale.epci as 
--- select 
--- 	siren_epci_2017 as siren_epci, 
--- 	nom_epci_2017 as nom_epci,
--- 	superficie
--- from sig.epci_geofla_4326;
--- SELECT AddGeometryColumn (''cigale'',''epci'',''geom'',4326,''MULTIPOLYGON'',2, false);
--- update cigale.epci as a
--- set geom = b.geom
--- from sig.epci_geofla_4326 as b
--- where a.siren_epci = b.siren_epci_2017;
--- alter table cigale.epci add constraint "pk.cigale.epci" primary key (siren_epci);
--- CREATE INDEX "gidx.cigale.epci.geom.gist" ON cigale.epci USING GIST (geom);
--- ';
--- 
--- 
--- 
--- 
--- drop table cigale.epci_2154;
--- create table cigale.epci_2154 as 
--- select siren_epci, nom_epci
--- from cigale.epci;
--- 
--- SELECT AddGeometryColumn ('cigale','epci_2154','geom',2154,'MULTIPOLYGON',2, false);
--- 
--- update cigale.epci_2154 as a
--- set geom = st_transform(b.geom, 2154)
--- from cigale.epci as b
--- where a.siren_epci = b.siren_epci;
--- 
--- alter table cigale.epci_2154 add constraint "pk.cigale.epci_2154" primary key (siren_epci);
--- CREATE INDEX "gidx.cigale.epci_2154.geom.gist" ON cigale.epci_2154 USING GIST (geom);
--- 
--- comment on table cigale.epci_2154 is '
--- create table cigale.epci_2154 as 
--- select siren_epci, nom_epci
--- from cigale.epci;
--- SELECT AddGeometryColumn (''cigale'',''epci_2154'',''geom'',2154,''MULTIPOLYGON'',2, false);
--- update cigale.epci_2154 as a
--- set geom = st_transform(b.geom, 2154)
--- from cigale.epci as b
--- where a.siren_epci = b.siren_epci;
--- alter table cigale.epci_2154 add constraint "pk.cigale.epci_2154" primary key (siren_epci);
--- CREATE INDEX "gidx.cigale.epci_2154.geom.gist" ON cigale.epci_2154 USING GIST (geom);
--- ';
--- 
--- 
--- 
--- 
--- drop table if exists cigale.epci_poll;
--- create table cigale.epci_poll WITH OIDS as 
--- select 
--- 	row_number() over () as gid, *
--- from (
--- 	select  	
--- 		an,
--- 		nom_abrege_polluant, 
--- 		siren_epci_2017 as siren_epci,
--- 		nom_epci_2017 as nom_epci,
--- 		sum(val)::numeric / (superficie / 100.)::numeric as val -- Superficie hectares -> km2
--- 	from total.bilan_comm_v5_secten1 as a
--- 	left join commun.tpk_commune_2015_2016 as b using (id_comm)
--- 	left join commun.tpk_polluants as c using (id_polluant)
--- 	left join cigale.epci as d on b.siren_epci_2017 = d.siren_epci
--- 	where 
--- 		an = 2015
--- 		and not (id_polluant in (38,65,108,16,48,36,11) and code_cat_energie in ('8', '6')) -- Emissions: Approche cadastrée: Pord d'énergie mais pas d'élec ni chaleur
--- 		and not (id_polluant not in (38,65,108,16,48,36,11) and id_secten1 = '1') -- GES et Ener = Finale
--- 		and ss_epci is false -- Sans aucune donnée soumise au SS à l'EPCI	
--- 	group by an, nom_abrege_polluant, siren_epci_2017, nom_epci_2017, superficie
--- 
--- 	union all
--- 
--- 	-- Ajout des productions primaires
--- 	select  	
--- 		an,
--- 		'prod'::text as nom_abrege_polluant, 
--- 		a.siren_epci_2017 as siren_epci,
--- 		nom_epci_2017 as nom_epci,
--- 		sum(val)::numeric / (superficie / 100.)::numeric as val -- Superficie hectares -> km2
--- 	from total.bilan_comm_v5_prod as a
--- 	left join cigale.epci as d on a.siren_epci_2017 = d.siren_epci
--- 	where 
--- 		an = 2015
--- 		and id_grande_filiere_cigale = 1 -- ENR
--- 	group by an, siren_epci_2017, nom_epci_2017, superficie
--- ) as a
--- order by an, nom_abrege_polluant, siren_epci, nom_epci;
--- 
--- SELECT AddGeometryColumn ('cigale','epci_poll','geom',4326,'MULTIPOLYGON',2, false);
--- 
--- ALTER TABLE cigale.epci_poll drop CONSTRAINT enforce_geotype_geom;
--- 
--- update cigale.epci_poll as a
--- set geom = ST_SimplifyPreserveTopology(b.geom,0.0004) -- Simplification de la géométrie pour rapidité d'affichage
--- from cigale.epci as b
--- where a.siren_epci = b.siren_epci;
--- 
--- alter table cigale.epci_poll add constraint "pk.cigale.epci_poll" primary key (nom_abrege_polluant, siren_epci);
--- CREATE INDEX "gidx.cigale.epci_poll.geom.gist" ON cigale.epci_poll USING GIST (geom);
--- CREATE INDEX "idx.cigale.epci_poll.id_polluant" ON cigale.epci_poll (nom_abrege_polluant);
--- 
--- comment on table cigale.epci_poll is '
--- NOTE: Pour la conso énergie primaire c''est à dire sans l''élec
--- NOTE: Pour les GES, émissions directes c''est à dire sans l''élec
--- NOTE: Pour les prod, énergie primaire c''est à dire ENR
--- ';
--- 
--- vacuum analyze cigale.epci_poll;
--- vacuum freeze cigale.epci_poll;
--- 
--- 
--- 
--- drop table if exists cigale.comm_poll;
--- create table cigale.comm_poll WITH OIDS as  
--- select row_number() over () as gid, *
--- from (
--- 	select  
--- 		nom_abrege_polluant, id_comm, nom_comm, siren_epci_2017 as siren_epci, 
--- 		val / (d.superficie / 100.) as val, -- Superficie en hectares dans les données geofla
--- 		st_transform(geom, 4326) as geomtmp
--- 	from (
--- 		select id_polluant, an, id_comm, sum(val) as val
--- 		from total.bilan_comm_v5_secten1
--- 		where 
--- 			an = 2015
--- 			and not (id_polluant in (38,65,108,16,48,36,11,16) and code_cat_energie in ('8', '6')) -- Emissions: Approche cadastrée: Pord d'énergie mais pas d'élec ni chaleur
--- 			and not (id_polluant not in (38,65,108,16,48,36,11,16) and id_secten1 = '1') -- GES et Ener = Finale
--- 			and ss is false -- Sans aucune donnée en SS
--- 		group by id_polluant, an, id_comm
--- 	) as a
--- 	left join commun.tpk_polluants as b using (id_polluant)
--- 	left join commun.tpk_commune_2015_2016 as c using (id_comm)
--- 	left join (
--- 		select 
--- 			case 
--- 				when insee_com::integer between 13201 and 13216 then 13055 
--- 				when insee_com in ('04198', '04033') then 4033 -- Fusion 2017
--- 				else insee_com::integer end as id_comm,
--- 			sum(superficie) as superficie,
--- 			st_union(geom) as geom
--- 		from sig.geofla2016_communes
--- 		where
--- 			code_reg = '93'
--- 		group by 
--- 			case 
--- 				when insee_com::integer between 13201 and 13216 then 13055 
--- 				when insee_com in ('04198', '04033') then 4033 -- Fusion 2017
--- 				else insee_com::integer end
--- 	) as d using (id_comm)
--- 	where 
--- 		nom_abrege_polluant in ('conso','so2','nox','pm10','pm2.5','covnm','nh3','co','co2','ch4.co2e','n2o.co2e','prg100.3ges')
--- 
--- 	union all
--- 
--- 	-- Ajout des prod
--- 	select 
--- 		'prod'::text as nom_abrege_polluant, id_comm, nom_comm, siren_epci_2017 as siren_epci, 
--- 		val / (d.superficie / 100.) as val, -- Superficie en hectares dans les données geofla
--- 		st_transform(geom, 4326) as geomtmp
--- 	from (
--- 		select 999::integer as id_polluant, an, id_comm, sum(val) as val
--- 		from total.bilan_comm_v5_prod
--- 		where 
--- 			an = 2015
--- 			and id_grande_filiere_cigale = 1
--- 		group by id_polluant, an, id_comm
--- 	) as a
--- 	left join commun.tpk_commune_2015_2016 as c using (id_comm)
--- 	left join (
--- 		select 
--- 			case 
--- 				when insee_com::integer between 13201 and 13216 then 13055 
--- 				when insee_com in ('04198', '04033') then 4033 -- Fusion 2017
--- 				else insee_com::integer end as id_comm,
--- 			sum(superficie) as superficie,
--- 			st_union(geom) as geom
--- 		from sig.geofla2016_communes
--- 		where
--- 			code_reg = '93'
--- 		group by 
--- 			case 
--- 				when insee_com::integer between 13201 and 13216 then 13055 
--- 				when insee_com in ('04198', '04033') then 4033
--- 				else insee_com::integer end
--- 	) as d using (id_comm)
--- ) as a;
--- 
--- SELECT AddGeometryColumn ('cigale','comm_poll','geom',4326,'MULTIPOLYGON',2, false);
--- 
--- update cigale.comm_poll
--- set geom = ST_Multi(ST_SimplifyPreserveTopology(geomtmp,0.001)); -- Simplification de la géométrie pour rapidité d'affichage
--- 
--- alter table cigale.comm_poll add constraint "pk.cigale.comm_poll" primary key (gid);
--- CREATE INDEX "gidx.cigale.comm_poll.geom.gist" ON cigale.comm_poll USING GIST (geom);
--- 
--- comment on table cigale.comm_poll is '
--- NOTE: Pour la conso énergie primaire c''est à dire sans l''élec
--- NOTE: Pour les GES, émissions directes c''est à dire sans l''élec
--- NOTE: Pour les prod, énergie primaire c''est à dire ENR
--- ';
--- 
--- vacuum analyse cigale.comm_poll;
--- vacuum freeze cigale.comm_poll;
--- 
--- -- Clusterisation des tables géographiques pour améliorer les temps d'affichage
--- CREATE INDEX "cigale.comm_poll.nom_abrege_polluant.siren_epci"
---   ON cigale.comm_poll
---   USING btree
---   (nom_abrege_polluant, siren_epci);
--- ALTER TABLE cigale.comm_poll CLUSTER ON "cigale.comm_poll.nom_abrege_polluant.siren_epci";
--- 
--- ALTER TABLE cigale.epci_poll CLUSTER ON "idx.cigale.epci_poll.id_polluant";
--- 
--- 
--- -- Création d'une table de liste des entités administratives pour une récupération rapide de l'info
--- -- et des temps d'affichage améliorés.
--- drop table if exists cigale.liste_entites_admin;
--- create table cigale.liste_entites_admin as
---     -- Région PACA
---     select 1 as order_field, 93 as valeur, 'Région PACA' as texte
---     union all
---     -- Départements
---     select 2 as order_field, id_dep as valeur, joli_nom_dep as texte 
---     from commun.tpk_depts
---     where id_reg = 93
---     union all
---     -- EPCI
---     select distinct 3 as order_field, siren_epci_2017, nom_epci_2017
---     from commun.tpk_commune_2015_2016
---     where siren_epci_2017 is not null
---     union all
---     -- Communes
---     select order_field, id_comm, b.nom_comm || ' (' || lpad((id_comm / 1000)::text, 2, '0') || ')' as nom_comm
---     from (
---         select distinct 4 as order_field, a.id_comm
---         from total.bilan_comm_v5_secten1 as a
---     ) as a
---     left join commun.tpk_communes as b using (id_comm)
--- order by order_field, valeur;
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- 
--- /** 
--- 
--- Création du compte utilisateur + acces pour grand public
--- 
--- 
--- 
--- -- Création de l'utilisateur plateforme J
--- CREATE USER *** WITH PASSWORD 'user=*** dbname=*** host=*** password=***';
--- 
--- -- Autorisation de la connexion (sudo vi pg_hba.conf)
--- GRANT ALL PRIVILEGES ON DATABASE "***" to ***;
--- SELECT pg_reload_conf();
--- 
--- -- Affectation des droits sur les tables
--- GRANT SELECT ON ALL TABLES IN SCHEMA public TO ***;
--- GRANT USAGE ON SCHEMA public TO ***;
--- GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO ***;
--- GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ***;
--- 
--- GRANT SELECT ON ALL TABLES IN SCHEMA cigale TO ***;
--- GRANT USAGE ON SCHEMA cigale TO ***;
--- GRANT USAGE ON ALL SEQUENCES IN SCHEMA cigale TO ***;
--- GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA cigale TO ***;
--- 
--- GRANT SELECT ON ALL TABLES IN SCHEMA total TO ***;
--- GRANT USAGE ON SCHEMA total TO ***;
--- GRANT USAGE ON ALL SEQUENCES IN SCHEMA total TO ***;
--- GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA total TO ***;
--- 
--- GRANT SELECT ON ALL TABLES IN SCHEMA transversal TO ***;
--- GRANT USAGE ON SCHEMA transversal TO ***;
--- GRANT USAGE ON ALL SEQUENCES IN SCHEMA transversal TO ***;
--- GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA transversal TO ***;
--- 
--- GRANT SELECT ON ALL TABLES IN SCHEMA commun TO ***;
--- GRANT USAGE ON SCHEMA commun TO ***;
--- GRANT USAGE ON ALL SEQUENCES IN SCHEMA commun TO ***;
--- GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA commun TO ***;
--- */
--- 
--- 
--- 

from total.bilan_comm_v5_ges as a
left join total.corresp_energie_synapse as b on a.id_energie = b.espace_id_energie
left join transversal.tpk_energie as c on b.synapse_id_energie = c.id_energie
left join (select * from src_ind.def_corresp_sources where id_version_corresp = 5 and actif is true) as d using (id_corresp)
where 
	id_polluant in (15, 123, 124, 128)
	and val is not null -- NOTE: Certaines valeurs nulles dans les tables bilan de chaque secteur
	and a.id_comm <> 99999 -- Sans les émissions associées à l'objet mer
group by 
	id_secteur,
	id_polluant,

select * from public.tmp_fusion;


/**
Suppression des données hors années d'inventaire
*/
delete from total.bilan_comm_v5_pcaet where an in (2009,2009,2011);



