ogr2ogr -update -append -f PostgreSQL -skipfailures PG:dbname="host='localhost' user='postgres' dbname='bouchsttar' password='postgres'" "D:\IFSTARR\donnees\DATA\data\departement.shp"

la présence d'apostrophe dans les noms de département empêche l'importation de celui ci:
 - on utlise -skipfailure
 - on importe avec un -select ogc_fid,wkb_geometry,id_geofla,code_dept,code_chf,x_chf_lieu,y_chf_lieu,x_centroid,y_centroid,code_reg
 
ogr2ogr -update -append -f PostgreSQL -select ogc_fid,wkb_geometry,id_geofla,code_dept,code_chf,x_chf_lieu,y_chf_lieu,x_centroid,y_centroid,code_reg -skipfailures PG:dbname="host='localhost' user='postgres' dbname='bouchsttar' password='postgres'" "D:\IFSTARR\donnees\DATA\data\departement.shp"

on reprojete -t_srs EPSG:4326
on force le multipolygon -nlt MULTIPOLYGON

ogr2ogr -update -append -f PostgreSQL -select ogc_fid,wkb_geometry,id_geofla,code_dept,code_chf,x_chf_lieu,y_chf_lieu,x_centroid,y_centroid,code_reg -skipfailures -nlt MULTIPOLYGON -t_srs EPSG:4326 PG:dbname="host='localhost' user='postgres' dbname='bouchsttar' password='postgres'" "D:\IFSTARR\donnees\DATA\data\departement.shp"

et

vf.shp

ogr2ogr -update -append -f PostgreSQL -skipfailures -t_srs EPSG:4326 PG:dbname="host='localhost' user='postgres' dbname='bouchsttar' password='postgres'" "D:\IFSTARR\donnees\DATA\data\vf.shp"

reseau_routier_france.shp

ogr2ogr -update -append -f PostgreSQL -skipfailures -t_srs EPSG:4326 PG:dbname="host='localhost' user='postgres' dbname='bouchsttar' password='postgres'" "D:\IFSTARR\donnees\DATA\data\reseau_routier_france.shp"

ogr2ogr -update -append -f PostgreSQL -skipfailures -t_srs EPSG:4326 PG:"host='localhost' user='postgres' dbname='bouchsttar' options='-cclient_encoding=latin1' password='postgres'" "D:\IFSTARR\donnees\DATA\data\reseau_routier_france.shp"

ogr2ogr --config PGCLIENTENCODING LATIN1 -update -append -f PostgreSQL -skipfailures -t_srs EPSG:4326 PG:"host='localhost' user='postgres' dbname='bouchsttar' password='postgres'" "D:\IFSTARR\donnees\DATA\data\reseau_routier_france.shp"

>SET PGCLIENTENCODING=LATIN1
> -nlt "MULTILINESTRING"

ogr2ogr --config PGCLIENTENCODING LATIN1 -update -append -f PostgreSQL -skipfailures -nln route_01 -nlt "LINESTRING" -t_srs EPSG:4326 PG:"host='localhost' user='postgres' dbname='bouchsttar' password='postgres'" "D:\IFSTARR\donnees\DATA\data\reseau_routier_france.shp"

>
Sans nouvel encodage mais avec selection des champs "numériques"

-select ogc_fid,wkb_geometry,route500,sens,numéro_route,tronçon,id_chrono

ogr2ogr -update -append -f PostgreSQL -select wkb_geometry,route500,sens,numéro_route,tronçon,id_chrono -nln route_02 -nlt "LINESTRING" -t_srs EPSG:4326 PG:"host='localhost' user='postgres' dbname='bouchsttar' password='postgres'" "D:\IFSTARR\donnees\DATA\data\reseau_routier_france.shp"

Geometry to be inserted is of type Multi Line String, whereas the layer geometry type is Line String.

ogr2ogr --config PGCLIENTENCODING LATIN1 -update -append -f PostgreSQL -skipfailures -nln route_03 -nlt "LINESTRING" -t_srs EPSG:4326 PG:"host='localhost' user='postgres' dbname='bouchsttar' password='postgres'" "D:\IFSTARR\donnees\DATA\data\reseau_routier_france.shp"

on a 289906 au lieu de 291116 soit 1210 routes perdues !

on isole le PACA on travaille à l'echelle du departement
puis on isole les routes du PACA par ST_WITHIN "création d'index"

CREATE TABLE paca as(
SELECT *
FROM departement
WHERE code_reg LIKE '93')
et ajouter contrainte : clef primaire

SELECT AddGeometryColumn('paca','the_geom',4326,'MULTIPOLYGON',2)

UPDATE paca SET the_geom=GeometryFromText('MULTIPOLYGON((||wkb_geometry||))', 4326))
ou
UPDATE paca SET the_geom=wkb_geometry

>>> avec un index sur (departement.wkb_geometry,route_03.wkb_geometry)
CREATE TABLE road_of_paca as(
SELECT route_03.*
FROM route_03,departement
WHERE code_reg LIKE '93'
AND ST_WITHIN(route_03.wkb_geometry,departement.wkb_geometry))
et ajouter contrainte : clef primaire

SELECT AddGeometryColumn('road_of_paca','the_geom',4326,'LINESTRING',2)

UPDATE road_of_paca SET the_geom=GeometryFromText('LINESTRING((||wkb_geometry||))', 4326))
ou
UPDATE road_of_paca SET the_geom=wkb_geometry


SELECT COUNT(ogc_fid),route
FROM road_of_paca
GROUP BY route
>>>>>>><<<<<<<<<<<<<
1441;"Nationale     "
361;"Autoroute     "
2843;"Sans objet    "
7316;"Départementale"
>>>>>>>><<<<<<<<<<<

CREATE TABLE paca_and_co as(
SELECT *
FROM departement
WHERE code_reg LIKE '93'
OR code_reg LIKE '82'
OR code_reg LIKE '91')
et ajouter contrainte : clef primaire

SELECT AddGeometryColumn('paca_and_co','the_geom',4326,'MULTIPOLYGON',2)

UPDATE paca_and_co SET the_geom=wkb_geometry

>>> avec un index sur (paca_and_co.wkb_geometry,route_03.wkb_geometry)
CREATE TABLE road_of_paca_and_co as(
SELECT route_03.*
FROM route_03,paca_and_co
WHERE ST_WITHIN(route_03.wkb_geometry,paca_and_co.wkb_geometry))
et ajouter contrainte : clef primaire

SELECT AddGeometryColumn('road_of_paca_and_co','the_geom',4326,'LINESTRING',2)

UPDATE road_of_paca_and_co SET the_geom=wkb_geometry

>>>>>>>>>>>>>>>>>>>>>>>>>how to "methode de comptage"<<<<<<<<<<<<<<<<<<<<<<<<<<<<
 
avec ST_INTERSECTS		http://www.postgis.org/docs/ST_Intersects.html

SELECT count(road_of_paca.ogc_fid)
FROM road_of_paca,(SELECT * FROM paca) AS d1, (SELECT * FROM paca) AS d2
WHERE d1.code_dept LIKE '04'
AND ST_INTERSECTS(road_of_paca.the_geom,d1.the_geom)
AND d2.code_dept LIKE '05'
AND ST_INTERSECTS(road_of_paca.the_geom,d2.the_geom)
GROUP BY road_of_paca.route

Retourne rien
Avec ST_Crosses

SELECT count(road_of_paca.ogc_fid)
FROM road_of_paca,(SELECT * FROM paca) AS d1, (SELECT * FROM paca) AS d2
WHERE d1.code_dept LIKE '04'
AND ST_Crosses(road_of_paca.the_geom,d1.the_geom)
AND d2.code_dept LIKE '05'
AND ST_Crosses(road_of_paca.the_geom,d2.the_geom)
GROUP BY road_of_paca.route

Retourne rien


Quelle est la longueur minimum,maximum,moyenne d'un troncon
SELECT max(LENGTH(road_of_paca_and_co.the_geom)),min(LENGTH(road_of_paca_and_co.the_geom)),avg(LENGTH(road_of_paca_and_co.the_geom))
FROM road_of_paca_and_co
max	0.330127408867916;
min 0.000179398178336969;
avg 0.0155215097103193;

on constuit un buff des frontières de une fois la taille moyenne des routes
puis on pourra chercher les routes par ST_CROSSES
ou alors on fait un ST_DWHITIN à une valeur deux,trois fois que la distance moyenne

SELECT count(road_of_paca.ogc_fid),road_of_paca.route
FROM road_of_paca,(SELECT * FROM paca) AS d1, (SELECT * FROM paca) AS d2
WHERE d1.code_dept LIKE '04'
AND ST_DWithin(road_of_paca.the_geom,d1.the_geom,0.3)
AND d2.code_dept LIKE '05'
AND ST_DWithin(road_of_paca.the_geom,d2.the_geom,0.3)
GROUP BY road_of_paca.route

198;	"Nationale     " sur 1441
10;		"Autoroute     " sur 361
331;	"Sans objet    " sur 2843
1405;	"Départementale" sur 7316

SELECT count(road_of_paca.ogc_fid),road_of_paca.route
FROM road_of_paca,paca
WHERE ST_DWithin(road_of_paca.the_geom,paca.the_geom,0.3)
GROUP BY road_of_paca.route

2987;	"Nationale     " sur 1441
694;	"Autoroute     " sur 361
5669;	"Sans objet    " sur 2843
16541;	"Départementale" sur 7316

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

SELECT count(road_of_paca.ogc_fid)
FROM road_of_paca
>> 11961
SELECT count(paca.ogc_fid)
FROM paca
>> 6
soit 71766 combinaison de ST_DWITIN !


En visualisant les données sous QGIS on peut aussi penser travaillé avec Buffer

pour le paca
SELECT ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_93.wkb_geometry)),0.05)
FROM (SELECT * FROM departement WHERE code_reg LIKE '93') AS reg_93

pour le languedoc-rousillon
SELECT ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_91.wkb_geometry)),0.05)
FROM (SELECT * FROM departement WHERE code_reg LIKE '93') AS reg_91

pour le rhone_alpe
SELECT ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_82.wkb_geometry)),0.05)
FROM (SELECT * FROM departement WHERE code_reg LIKE '93') AS reg_82

on construit les requêtes suivantes (autour d'une minute d'exécution)

CREATE TABLE border_road_91_93 AS
(
SELECT road_of_paca_and_co.*
FROM road_of_paca_and_co
WHERE ST_INTERSECTS(road_of_paca_and_co.the_geom,(SELECT ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_93.wkb_geometry)),0.05) FROM (SELECT * FROM departement WHERE code_reg LIKE '93') AS reg_93))
AND ST_INTERSECTS(road_of_paca_and_co.the_geom,(SELECT ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_91.wkb_geometry)),0.05) FROM (SELECT * FROM departement WHERE code_reg LIKE '91') AS reg_91)) 
)

CREATE TABLE border_road_82_93 AS
(
SELECT road_of_paca_and_co.*
FROM road_of_paca_and_co
WHERE ST_INTERSECTS(road_of_paca_and_co.the_geom,(SELECT ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_93.wkb_geometry)),0.05) FROM (SELECT * FROM departement WHERE code_reg LIKE '93') AS reg_93))
AND ST_INTERSECTS(road_of_paca_and_co.the_geom,(SELECT ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_82.wkb_geometry)),0.05) FROM (SELECT * FROM departement WHERE code_reg LIKE '82') AS reg_82)) 
)

CREATE TABLE border_road_91_82 AS
(
SELECT road_of_paca_and_co.*
FROM road_of_paca_and_co
WHERE ST_INTERSECTS(road_of_paca_and_co.the_geom,(SELECT ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_82.wkb_geometry)),0.05) FROM (SELECT * FROM departement WHERE code_reg LIKE '82') AS reg_82))
AND ST_INTERSECTS(road_of_paca_and_co.the_geom,(SELECT ST_BUFFER(ST_BOUNDARY(ST_UNION(reg_91.wkb_geometry)),0.05) FROM (SELECT * FROM departement WHERE code_reg LIKE '91') AS reg_91)) 
)

on fait alors un join table en s'appuyant sur les clés primaires
on nettoie les routes en double ou triple
requête trop longue malgré la création d'un index qui n'est pas utilisé (commande explain)
si c'est trop long alors c'est pas ça

SELECT border_road_91_82.*, border_road_82_93.*, border_road_91_93.*
FROM border_road_91_82,border_road_82_93,border_road_91_93
WHERE NOT border_road__91_82.ogc_fid = border_road_82_93.ogc_fid
AND NOT border_road_91_82.ogc_fid = border_road_91_93.ogc_fid
AND NOT border_road_82_93.ogc_fid = border_road_91_93.ogc_fid
>>>>> on n'utilise pas de jointure 
>>>>> on veut une table qui recapitule les trois autres en nettoyant les doublets et triplets


pour compter les routes selon leurs type on utilisera

SELECT COUNT(border_road_91_82.ogc_fid)
FROM border_road_91_82
GROUP BY border_road_91_82.route


pour les compter selon leurs types
mais surtout les classer par distance depuis le point carrefour des trois frontières
on devra d'abord identifier les noeuds puis calculé la distance du noeuds à un point d'une route