#IMPORT DES SHAPES:
ogr2ogr -f "PostgreSQL" C:\lupsig\ptut\DATA\ign\CHEMIN\CHEMIN.shp PG:"host=localhost user=postgres dbname=postgis password=tintin17" "CHEMIN"
shp2pgsql.exe -s 2154 -D -c C:\lupsig\ptut\canton\canton.shp canton| psql postgis

shp2pgsql -s 4326 -D -c C:\lupsig\ptut\DATA\osm\aquitaine.shapefiles\HIGHWAY\aquitaine_highway.shp highway| psql routing

#CONFIG POSTGRE:
psql -d routing -f postgis.SQL
psql -d routing -f C:\tmp\spatial_ref_sys.SQL
psql -d routing -f C:\tmp\routing.SQL
psql -d routing -f C:\tmp\routing_postgis.SQL


/* modifier un jeu de donnees vite fai pour utiliser l'algo dijkstra */

/*création topologie réseau*/
ALTER TABLE troncon_route ADD COLUMN SOURCE INTEGER;
ALTER TABLE troncon_route ADD COLUMN target INTEGER;
ALTER TABLE  troncon_route ADD COLUMN cost DOUBLE PRECISION;
UPDATE troncon_route SET cost=LENGTH(geom);

SELECT assign_vertex_id('troncon_route', 0.00001, 'geom', 'gid');

/*création des index*/
CREATE INDEX source_idx ON troncon_route(SOURCE);
CREATE INDEX target_idx ON troncon_route(target);
CREATE INDEX geom_idx ON troncon_route USING GIST(geom GIST_GEOMETRY_OPS);

/*requete de recherche du + court chemin  avec la core function*/
SELECT * FROM shortest_path('SELECT source, gid as id, target, cost FROM troncon_route;',1,13,FALSE,FALSE);

/*la meme fct mais en utilisant la core function */
SELECT * FROM shortest_path('SELECT gid as id,
source::integer,
target::integer,
cost::double precision
FROM troncon_route',
2, 10, FALSE, FALSE);

/* avec un wrapper  => DIJKRRA SSANS BOITE ENGLOBANTE (attention jai change le nom de qlq colonnes ) */
SELECT gid,AsText(the_geom) AS the_geom
FROM dijkstra_sp('troncon_route', 1, 13);

/* avec un wrapper ET une boite englobante */
/*Note: The projection of OSM data is “degree”, so we set a bounding box containing start and end vertex plus a
0.1 degree buffer for example.*/
SELECT gid, AsText(the_geom) AS the_geom
FROM dijkstra_sp_delta('troncon_route', 1,13, 0.1);