0673557741
>SET PGCLIENTENCODING=LATIN1
> -nlt "MULTILINESTRING"
> -t_srs EPSG:27582
> -t_srs EPSG:2154
> -skipfailures
srid 27582 lambert2 étendu

reseau_routier_france.shp
departement.shp
limite_departement.shp

ogr2ogr --config PGCLIENTENCODING LATIN1 -update -append -f PostgreSQL -nlt "GEOMETRY" -nln route_france -t_srs EPSG:2154 PG:"host='localhost' user='postgres' dbname='test' password='postgres'" "D:\IFSTARR\donnees\DATA\data\reseau_routier_france.shp"
ogr2ogr --config PGCLIENTENCODING LATIN1 -update -append -f PostgreSQL -nlt "GEOMETRY" -nln departement -t_srs EPSG:2154 PG:"host='localhost' user='postgres' dbname='test' password='postgres'" "D:\IFSTARR\donnees\DATA\data\departement.shp"
ogr2ogr --config PGCLIENTENCODING LATIN1 -update -append -f PostgreSQL -nlt "GEOMETRY" -nln limit_depart -t_srs EPSG:2154 PG:"host='localhost' user='postgres' dbname='test' password='postgres'" "D:\IFSTARR\donnees\DATA\data\limite_departement.shp"

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- 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 membrane_pk PRIMARY KEY (ogc_fid ),
  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 );
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE OR REPLACE VIEW reg_82 AS
(SELECT * FROM departement WHERE code_reg LIKE '82');
CREATE OR REPLACE VIEW reg_91 AS
(SELECT * FROM departement WHERE code_reg LIKE '91');
CREATE OR REPLACE VIEW reg_93 AS
(SELECT * FROM departement WHERE code_reg LIKE '93');
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE OR REPLACE VIEW route_frontiere_91_93 AS
(
SELECT route_france.*
FROM route_france
WHERE ST_INTERSECTS(route_france.wkb_geometry, (SELECT (ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_93.wkb_geometry)),30)) FROM reg_93)) 
AND ST_INTERSECTS(route_france.wkb_geometry, (SELECT (ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_91.wkb_geometry)),30)) FROM reg_91))
);
CREATE OR REPLACE VIEW route_frontiere_82_93 AS
(
SELECT route_france.*
FROM route_france
WHERE ST_INTERSECTS(route_france.wkb_geometry,(SELECT (ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_93.wkb_geometry)),30)) FROM reg_93)) 
AND ST_INTERSECTS(route_france.wkb_geometry,(SELECT (ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_82.wkb_geometry)),30)) FROM reg_82))
);
CREATE OR REPLACE VIEW route_frontiere_91_82 AS
(
SELECT route_france.*
FROM route_france
WHERE ST_INTERSECTS(route_france.wkb_geometry,(SELECT (ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_82.wkb_geometry)),30)) FROM reg_82)) 
AND ST_INTERSECTS(route_france.wkb_geometry,(SELECT (ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_91.wkb_geometry)),30)) FROM reg_91))
);
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
INSERT INTO membrane
(
SELECT *
FROM route_frontiere_91_93
);
INSERT INTO membrane
(
SELECT *
FROM route_frontiere_82_93
);
INSERT INTO membrane
(
SELECT *
FROM route_frontiere_91_82
);
cela prend 10 minutes !
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
UPDATE membrane SET poids_route=1 WHERE route LIKE 'Sans objet    ';
UPDATE membrane SET poids_route=2 WHERE route LIKE 'Départementale';
UPDATE membrane SET poids_route=3 WHERE route LIKE 'Nationale     ';
UPDATE membrane SET poids_route=4 WHERE route LIKE 'Autoroute     ';
LOOP boucle sur les différentes frontières
UPDATE membrane SET contribution_directe_boolean = FALSE  FROM route_frontiere_91_93 WHERE membrane.ogc_fid=route_frontiere_91_93.ogc_fid;
UPDATE membrane SET contribution_directe_boolean = TRUE  FROM route_frontiere_82_93 WHERE membrane.ogc_fid=route_frontiere_82_93.ogc_fid;
UPDATE membrane SET contribution_directe_boolean = FALSE  FROM route_frontiere_91_82 WHERE membrane.ogc_fid=route_frontiere_91_82.ogc_fid;
END LOOP
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE OR REPLACE VIEW centroid_buffer AS 
(
SELECT ST_CENTROID(ST_UNION(ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_91.wkb_geometry)),30),ST_UNION(ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_93.wkb_geometry)),30),ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_82.wkb_geometry)),30))))
FROM reg_82, reg_91, reg_93
);
CREATE OR REPLACE VIEW centroid_distance AS
(
SELECT ST_MAXDISTANCE(membrane.wkb_geometry,centroid_buffer.ST_CENTROID), membrane.ogc_fid, membrane.poids_route, membrane.contribution_directe_boolean
FROM membrane, centroid_buffer
);

CREATE VIEW OR REPLACE S1 AS
(
SELECT SUM(poids_route/(ST_MAXDISTANCE*10^(-3)))/(SELECT COUNT(ogc_fid) FROM centroid_distance) AS r1
FROM centroid_distance
WHERE NOT centroid_distance.contribution_directe_boolean
);
CREATE VIEW OR REPLACE S2 AS
(
SELECT SUM(poids_route)/(SELECT COUNT(ogc_fid) FROM centroid_distance) AS r2
FROM centroid_distance
WHERE centroid_distance.contribution_directe_boolean
);
SELECT r1+r2 as r3
FROM S1,S2
;