-- creation de la table membrane
	-- Table: membrane
	-- DROP TABLE membrane;
	CREATE TABLE membrane
	(
	  ogc_fid serial NOT NULL,
	  wkb_geometry geometry,
	  route500 double precision,
	  vocation character(18),
	  "chaussées" character(11),
	  voies character(26),
	  physique character(15),
	  "accès" character(10),
	  "réseau_ver" character(16),
	  sens character(12),
	  "réseau_eur" character(20),
	  "numéro_rou" character(10),
	  route character(14),
	  "tronçon" numeric(6,2),
	  id_chrono character(32),
	  poids_route integer,
	  contribution_directe integer,
	  contribution_directe_boolean boolean,
	  CONSTRAINT enforce_dims_wkb_geometry CHECK (st_ndims(wkb_geometry) = 2),
	  CONSTRAINT enforce_srid_wkb_geometry CHECK (st_srid(wkb_geometry) = 27582)
	)
	WITH (
	  OIDS=FALSE
	);
	ALTER TABLE membrane
	  OWNER TO postgres;
	-- Index: membrane_geom_idx
	-- DROP INDEX membrane_geom_idx;
	CREATE INDEX membrane_geom_idx
	  ON membrane
	  USING gist
	  (wkb_geometry );
	-- Index: membrane_fid_idx
	-- DROP INDEX membrane_fid_idx;
	CREATE INDEX membrane_fid_idx
	ON membrane
	USING btree
	  (ogc_fid );


CREATE OR REPLACE FUNCTION membrane_paca(x1 character(2),x2 character(2),x3 character(2),p1 integer,p2 integer,p3 integer,p4 integer)
RETURNS void AS $$
DECLARE
--
BEGIN
	  
	-- création des vues des departments contigus
	CREATE TABLE reg_1 AS
	(SELECT * FROM departement WHERE code_reg LIKE x1);
	CREATE TABLE reg_2 AS
	(SELECT * FROM departement WHERE code_reg LIKE x2);
	CREATE TABLE reg_3 AS
	(SELECT * FROM departement WHERE code_reg LIKE x3);
	
	-- création des buffers correspondant à la membrane : zone frontière
	CREATE TABLE buff01 AS
	(
	SELECT (ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_1.wkb_geometry)),30)) AS the_geom
	FROM reg_1
	);
	CREATE TABLE buff02 AS
	(
	SELECT (ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_2.wkb_geometry)),30)) AS the_geom
	FROM reg_2
	);
	CREATE TABLE buff03 AS
	(
	SELECT (ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_3.wkb_geometry)),30)) AS the_geom
	FROM reg_3
	);
	
	-- création des vues des routes passant les frontières
	CREATE TABLE route_frontiere_1_2 AS
	(
	SELECT route_france.*
	FROM route_france, buff01, buff02
	WHERE ST_INTERSECTS(route_france.wkb_geometry, buff01.the_geom) 
	AND ST_INTERSECTS(route_france.wkb_geometry, buff02.the_geom)
	);
	CREATE TABLE route_frontiere_1_3 AS
	(
	SELECT route_france.*
	FROM route_france, buff01, buff03
	WHERE ST_INTERSECTS(route_france.wkb_geometry, buff01.the_geom) 
	AND ST_INTERSECTS(route_france.wkb_geometry, buff03.the_geom)
	);
	CREATE TABLE route_frontiere_3_2 AS
	(
	SELECT route_france.*
	FROM route_france, buff03, buff02
	WHERE ST_INTERSECTS(route_france.wkb_geometry, buff03.the_geom) 
	AND ST_INTERSECTS(route_france.wkb_geometry, buff02.the_geom)
	);
	DROP TABLE buff01,buff02,buff03;
	
	
	-- ajout de clef primaire
	-- Constraint: pk_route_frontiere_1_2
	-- ALTER TABLE route_frontiere_1_2 DROP CONSTRAINT pk_route_frontiere_1_2;
	ALTER TABLE route_frontiere_1_2
	  ADD CONSTRAINT pk_route_frontiere_1_2 PRIMARY KEY(ogc_fid );
	-- ajout de clef primaire
	-- Constraint: pk_route_frontiere_1_3
	-- ALTER TABLE route_frontiere_1_3 DROP CONSTRAINT pk_route_frontiere_1_3;
	ALTER TABLE route_frontiere_1_3
	  ADD CONSTRAINT pk_route_frontiere_1_3	  PRIMARY KEY(ogc_fid );
	-- ajout de clef primaire
	-- Constraint: pk_route_frontiere_3_2
	-- ALTER TABLE route_frontiere_3_2 DROP CONSTRAINT pk_route_frontiere_3_2;
	ALTER TABLE route_frontiere_3_2
	  ADD CONSTRAINT pk_route_frontiere_3_2 PRIMARY KEY(ogc_fid );
	
	--puis on met a jour la membrane sur les routes
	INSERT INTO membrane
	(
	SELECT *
	FROM route_frontiere_1_2
	);
	INSERT INTO membrane
	(
	SELECT *
	FROM route_frontiere_1_3
	);
	INSERT INTO membrane
	(
	SELECT *
	FROM route_frontiere_3_2
	);

	-- on verifie que l'on a pas ajouté de doublet
	CREATE TABLE doublet01 AS
	(
	SELECT route_frontiere_1_2.ogc_fid
	FROM route_frontiere_1_2, route_frontiere_1_3, route_frontiere_3_2
	WHERE route_frontiere_1_2.ogc_fid=route_frontiere_1_3.ogc_fid
	OR route_frontiere_1_2.ogc_fid=route_frontiere_3_2.ogc_fid
	);
	CREATE TABLE doublet02 AS
	(
	SELECT route_frontiere_3_2.ogc_fid
	FROM route_frontiere_1_3, route_frontiere_3_2
	WHERE route_frontiere_3_2.ogc_fid=route_frontiere_1_3.ogc_fid
	);
	-- si oui on les supprime
	DELETE FROM membrane
	WHERE ogc_fid IN (SELECT ogc_fid FROM doublet01);
	DELETE FROM membrane
	WHERE ogc_fid IN (SELECT ogc_fid FROM doublet02);
	DROP TABLE doublet01, doublet02;
	
	-- on rajoute notre cléf primaire
	-- Constraint: pk_membrane
	-- ALTER TABLE membrane DROP CONSTRAINT pk_membrane;
	ALTER TABLE membrane
	  ADD CONSTRAINT pk_membrane PRIMARY KEY(ogc_fid );
	
	--on met a jour sur les informations externe
	UPDATE membrane SET poids_route=p1 WHERE route LIKE 'Sans objet    ';
	UPDATE membrane SET poids_route=p2 WHERE route LIKE 'Départementale';
	UPDATE membrane SET poids_route=p3 WHERE route LIKE 'Nationale     ';
	UPDATE membrane SET poids_route=p4 WHERE route LIKE 'Autoroute     ';
	
	--on constuit le centroid de la membrane
	CREATE TABLE centroid_buffer AS 
	(
	SELECT ST_CENTROID(ST_UNION(ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_1.wkb_geometry)),30),ST_UNION(ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_2.wkb_geometry)),30),ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_3.wkb_geometry)),30))))
	FROM reg_1, reg_2, reg_3
	);
	
	
	-- puis on effectue le calcul en permutant sur les trois régions
	FOR i IN 1..3 LOOP
		IF (i=1) THEN
			
			--on met à jour la colonne des contributions directe
			UPDATE membrane SET contribution_directe_boolean = TRUE  FROM route_frontiere_1_2 WHERE membrane.ogc_fid=route_frontiere_1_2.ogc_fid;
			UPDATE membrane SET contribution_directe_boolean = FALSE  FROM route_frontiere_1_3 WHERE membrane.ogc_fid=route_frontiere_1_3.ogc_fid;
			UPDATE membrane SET contribution_directe_boolean = FALSE  FROM route_frontiere_3_2 WHERE membrane.ogc_fid=route_frontiere_3_2.ogc_fid;
			
			-- on determine les distances en kilomètres au centroid et on distingue les contributions directes des autres
			CREATE TABLE centroid_distance AS
			(
			SELECT ST_MAXDISTANCE(membrane.wkb_geometry,centroid_buffer.ST_CENTROID)*10^(-3) AS dist, membrane.ogc_fid, membrane.poids_route, membrane.contribution_directe_boolean
			FROM membrane, centroid_buffer
			);

			-- contribution par l'inverse des distances
			CREATE TABLE S1 AS
			(
			SELECT SUM(poids_route/dist)/(SELECT COUNT(ogc_fid) FROM centroid_distance) AS r1
			FROM centroid_distance
			WHERE NOT centroid_distance.contribution_directe_boolean
			);
			
			-- contribtion directe
			CREATE TABLE S2 AS
			(
			SELECT SUM(poids_route)/(SELECT COUNT(ogc_fid) FROM centroid_distance) AS r2
			FROM centroid_distance
			WHERE centroid_distance.contribution_directe_boolean
			);
			
			-- on acceuil le nouveau poids calculé dans la table des poids
			CREATE TABLE poids AS 
			(
			SELECT 12 AS id, r1+r2 AS p FROM S1,S2
			);
			
		END IF;
	
		IF (i=2) THEN
			
			--on met à jour la colonne des contributions directe
			UPDATE membrane SET contribution_directe_boolean = FALSE  FROM route_frontiere_1_2 WHERE membrane.ogc_fid=route_frontiere_1_2.ogc_fid;
			UPDATE membrane SET contribution_directe_boolean = TRUE  FROM route_frontiere_1_3 WHERE membrane.ogc_fid=route_frontiere_1_3.ogc_fid;
			UPDATE membrane SET contribution_directe_boolean = FALSE  FROM route_frontiere_3_2 WHERE membrane.ogc_fid=route_frontiere_3_2.ogc_fid;
			
			-- on determine les distances en kilomètres au centroid et on distingue les contributions directes des autres
			CREATE TABLE centroid_distance AS
			(
			SELECT ST_MAXDISTANCE(membrane.wkb_geometry,centroid_buffer.ST_CENTROID)*10^(-3) AS dist, membrane.ogc_fid, membrane.poids_route, membrane.contribution_directe_boolean
			FROM membrane, centroid_buffer
			);

			-- contribution par l'inverse des distances
			CREATE TABLE S1 AS
			(
			SELECT SUM(poids_route/dist)/(SELECT COUNT(ogc_fid) FROM centroid_distance) AS r1
			FROM centroid_distance
			WHERE NOT centroid_distance.contribution_directe_boolean
			);
			
			-- contribtion directe
			CREATE TABLE S2 AS
			(
			SELECT SUM(poids_route)/(SELECT COUNT(ogc_fid) FROM centroid_distance) AS r2
			FROM centroid_distance
			WHERE centroid_distance.contribution_directe_boolean
			);
			
			-- on met a jour la table des poids
			INSERT INTO poids
			(
			SELECT 13 AS id, r1+r2 AS p1
			FROM S1,S2
			);
			
		END IF;
		
		IF (i=3) THEN
			
			--on met à jour la colonne des contributions directe
			UPDATE membrane SET contribution_directe_boolean = FALSE  FROM route_frontiere_1_2 WHERE membrane.ogc_fid=route_frontiere_1_2.ogc_fid;
			UPDATE membrane SET contribution_directe_boolean = FALSE  FROM route_frontiere_1_3 WHERE membrane.ogc_fid=route_frontiere_1_3.ogc_fid;
			UPDATE membrane SET contribution_directe_boolean = TRUE  FROM route_frontiere_3_2 WHERE membrane.ogc_fid=route_frontiere_3_2.ogc_fid;
			
			-- on determine les distances en kilomètres au centroid et on distingue les contributions directes des autres
			CREATE TABLE centroid_distance AS
			(
			SELECT ST_MAXDISTANCE(membrane.wkb_geometry,centroid_buffer.ST_CENTROID)*10^(-3) AS dist, membrane.ogc_fid, membrane.poids_route, membrane.contribution_directe_boolean
			FROM membrane, centroid_buffer
			);

			-- contribution par l'inverse des distances
			CREATE TABLE S1 AS
			(
			SELECT SUM(poids_route/dist)/(SELECT COUNT(ogc_fid) FROM centroid_distance) AS r1
			FROM centroid_distance
			WHERE NOT centroid_distance.contribution_directe_boolean
			);
			
			-- contribtion directe
			CREATE TABLE S2 AS
			(
			SELECT SUM(poids_route)/(SELECT COUNT(ogc_fid) FROM centroid_distance) AS r2
			FROM centroid_distance
			WHERE centroid_distance.contribution_directe_boolean
			);
			
			-- on met a jour la table des poids
			INSERT INTO poids
			(
			SELECT 23 AS id, r1+r2 AS p
			FROM S1,S2
			);

		END IF;
		
		DROP TABLE centroid_distance,S1,S2;
		
	END LOOP;
	DROP TABLE centroid_buffer;
	
	DROP TABLE reg_1, reg_2, reg_3, route_frontiere_1_2, route_frontiere_1_3, route_frontiere_3_2;
	
END;
$$ LANGUAGE plpgsql;

SELECT membrane_paca('93','91','82',1,3,5,10);