{"id":62,"date":"2014-07-23T09:05:52","date_gmt":"2014-07-23T08:05:52","guid":{"rendered":"http:\/\/elcep.legtux.org\/?p=62"},"modified":"2014-07-23T09:06:02","modified_gmt":"2014-07-23T08:06:02","slug":"pgrouting-postgis-postgresql-sont-sur-un-bateau","status":"publish","type":"post","link":"http:\/\/elcep.legtux.org\/?p=62","title":{"rendered":"pgRouting, postGIS, postgreSQL sont sur un bateau !"},"content":{"rendered":"

C’est un sujet que j’avais d\u00e9j\u00e0 trait\u00e9 dans un post ant\u00e9rieur, mais comme le blog de l’\u00e9poque a disparu, il a fallu que je me replonge dans les bas-fonds de postgresql, postgis et pgRouting. Heureusement que les articles de Anita Graser (alias underdarck) sont encore en ligne, eux, mais ils ont n\u00e9cessit\u00e9 quelques ajustements avec pgRouting 2!<\/p>\n

Si vous en \u00eates l\u00e0 c’est que vous cherchez un moyen de faire des calculs d’isochrones et que, petit \u00e0 petit, la solution pgRouting (m\u00eame si elle peut sembler disproportionn\u00e9e) s’impose \u00e0 vous.<\/p>\n

A ce stade, je partirais du principe que vous avez un serveur postgreSQL\/postGIS fonctionnel. Vous trouverez plusieurs ressources sur Internet. Pour ma part, j’ai r\u00e9alis\u00e9 ce petit tuto sur Fedora 20 (un bon tuto pour l’installation postgresql postgis su le blog de Marcus Neteler <\/a>et pgRouting est dans les packages de la distribution<\/a>).<\/p>\n

Attention toutefois \u00e0 la version. Il se trouve que, sur Fedora, pgRouting est encore \u00e0 sa version 1.05 qui n\u00e9cessite quelques ajustement \u00e0 la mano<\/a> (r\u00e9v\u00e9l\u00e9 par Gregor the map guy). De mon c\u00f4t\u00e9, j’ai plut\u00f4t opt\u00e9 pour une compilation depuis les sources qui sont disponibles sur gitHub<\/a>.<\/p>\n

Cr\u00e9er un r\u00e9seau \u00ab\u00a0routable\u00a0\u00bb<\/h1>\n

La premi\u00e8re chose \u00e0 faire est de pr\u00e9parer la base de donn\u00e9es :<\/p>\n

createdb --encoding=UTF8 --owner=delaye geodb\r\npsql -d geodb -c \"CREATE EXTENSION postgis;\"\r\npsql -d geodb -c \"CREATE EXTENSION postgis_topology;\"\r\npsql -d geodb -c \"CREATE EXTENSION pgrouting;\"<\/pre>\n
Ensuite,\u00a0 il faut importer un shapefile dans postgreSQL. Vous pouvez utiliser un client graphique comme shp2pgsql-gui ou deux petites lignes de commande (des infos ici<\/a> )<\/p>\n
#transformer en requ\u00eate SQL un shapefile\r\nshp2pgsql -I -t 2D -s 2154 reseau_total_AOC.shp acces_banyuls access_banyuls > acces_banyuls.sql\r\n#envoyer les donn\u00e9es dans la base \r\npsql -f acces_banyuls.sql -d geodb<\/pre>\n
Vous pouvez d\u00e8s \u00e0 present vous connecter \u00e0 la base avec QGIS et avoir acc\u00e8s aux donn\u00e9es<\/p>\n
\"connection<\/a>
connection \u00e0 postgreSQL avec QGIS 2.4<\/figcaption><\/figure>\n
Pour pouvoir cr\u00e9er un graph connect\u00e9, il faut identifier pour chaque ligne ou polyligne le d\u00e9but et la fin. C’est ce que nous allons faire avec :<\/p>\n
--evaluer  le type de la colonne geom\r\nselect * from geometry_columns;\r\n--pour \u00e9valuer  la dimension des coordonn\u00e9es de vos g\u00e9om\u00e9tries\r\nselect distinct st_coordDim(geom) from acces_banyuls;\r\n\r\n--convertir la colonne en deux dimensions si les donn\u00e9es sont en 2D si vous n'avez pas utilis\u00e9 l'option -t 2D de shp2pgsql\r\nalter table acces_banyuls alter column geom type geometry(Multilinestring, 2154) using st_force2D(geom);<\/pre>\n
On va ensuite devoir transformer des multiLine-string en line-string dans postGIS parce que dans sa version 2 pgRouting ne travaille plus qu’avec \u00e7a .<\/p>\n
--d'utilisation de st_dump pour transformer multiLine-string en line-string\r\nCREATE TABLE simple_ways AS SELECT *, (ST_Dump(geom)).geom AS simple_geom FROM acces_banyuls WHERE geom IS NOT NULL;\r\n--au passage on suprime la colonne multiline string\r\nALTER TABLE simple_ways DROP COLUMN geom;<\/pre>\n
On va ensuite cr\u00e9er une colonne temps de trajet \u00e0 partir d’une colonne existante dans la base de donn\u00e9es qui contient, pour chaque tron\u00e7on, la vitesse limite :<\/p>\n
--mise \u00e0 jour des temps de trajet\r\nALTER TABLE simple_ways ADD COLUMN length_m INTEGER;\r\nUPDATE simple_ways SET length_m = st_length(st_transform(simple_geom,2154));\r\n\r\nALTER TABLE simple_ways ADD COLUMN traveltime_min DOUBLE PRECISION;\r\nUPDATE simple_ways set traveltime_min = length_m \/ (vtss_pt \/ 60 * 1000)\u00a0 --pour avoir la vitesse en metre<\/pre>\n
On peut ensuite essayer les fonctions de pgRouting proposer par Anita Graser<\/p>\n
-- ajouter des col \"source\" et \"target\" \u00e0 la table\r\nALTER TABLE acces_banyuls ADD COLUMN \"source\" integer;\r\nALTER TABLE acces_banyuls ADD COLUMN \"target\" integer;\r\n\r\n--Creation de colonne startPoint et endpoint (syntaxe \u00e0 changer ne pas oublier st_)\r\nCREATE OR REPLACE VIEW road_ext AS\r\n\u00a0\u00a0 SELECT *, st_startpoint(simple_geom), st_endpoint(simple_geom)\r\n\u00a0\u00a0 FROM simple_ways;\r\n\r\n--Une table qui contient tout les noeuds\r\nCREATE TABLE node AS\r\n\u00a0\u00a0 SELECT row_number() OVER (ORDER BY foo.p)::integer AS id,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foo.p AS the_geom\r\n\u00a0\u00a0 FROM (\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT DISTINCT road_ext.st_startpoint AS p FROM road_ext\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 UNION\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT DISTINCT road_ext.st_endpoint AS p FROM road_ext\r\n\u00a0\u00a0 ) foo\r\n\u00a0\u00a0 GROUP BY foo.p;\r\n\r\n\u00a0 \u00a0\r\n--creation du r\u00e9seau\u00a0 -> 128330 ms \r\nCREATE TABLE network AS\r\n\u00a0\u00a0 SELECT a.*, b.id as start_id, c.id as end_id\r\n\u00a0\u00a0 FROM road_ext AS a\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN node AS b ON a.st_startpoint = b.the_geom\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN node AS c ON a.st_endpoint = c.the_geom;\r\n<\/pre>\n
Pour la derni\u00e8re commande il faut faire attention, en effet \u00e0 chaque fois que vous reg\u00e9n\u00e9rez une table node, les id de ces derniers peuvent changer. Donc quand vous allez devoir sp\u00e9cifier l’ID du noeud source (6791 dans la requ\u00eate suivante), faites attention de bien le mettre \u00e0 jour.<\/p>\n
--Creation de la table qui contient les temps de trajet\r\n--besoin d'une colonne avec travelTime pour faire le calcul\r\n-- temps de calcul 123190 ms\r\ncreate table cost_road as\r\nselect\r\n\u00a0\u00a0\u00a0 id,\r\n\u00a0\u00a0\u00a0 the_geom,\r\n\u00a0\u00a0\u00a0 (select sum(cost) from (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT * FROM pgr_dijkstra('\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT gid AS id,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 start_id::int4 AS source,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 end_id::int4 AS target,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 traveltime_min::float8 AS cost\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM network',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6791,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 false,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 false)) as foo ) as cost\r\nfrom node;\r\n<\/pre>\n
Vous pouvez ensuite charger ces points dans Qgis et lancer une interpolation avec l’extension interpolation, par exemple, qui va vous en proposer deux types :<\/p>\n
    \n
  1. TIN (Triangulated Irregular Netwaork)<\/li>\n
  2. IDW (Inverse Distance Weighted)<\/li>\n<\/ol>\n
    Voil\u00e0 le r\u00e9sultat d’une interpolation IDW (avec une discr\u00e9tisation moche je vous l’accorde \ud83d\ude42 mais on a qu’\u00e0 dire que c’est pas l’objet ici).<\/p>\n
    \"isochrones\"<\/a><\/p>\n
    Ou, si vous voulez une interpolation plus propre, vous pouvez aussi passer sous R.<\/p>\n","protected":false},"excerpt":{"rendered":"
    C’est un sujet que j’avais d\u00e9j\u00e0 trait\u00e9 dans un post ant\u00e9rieur, mais comme le blog de l’\u00e9poque a disparu, il a fallu que je me replonge dans les bas-fonds de postgresql, postgis et pgRouting. Heureusement que les articles de Anita Graser (alias underdarck) sont encore en ligne, eux, mais ils ont n\u00e9cessit\u00e9 quelques ajustements avec … Continuer la lecture de pgRouting, postGIS, postgreSQL sont sur un bateau !<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[15,14,13,16],"class_list":["post-62","post","type-post","status-publish","format-standard","hentry","category-geomatique","tag-isochrones","tag-pgrouting","tag-postgresqln-postgis","tag-qgis"],"_links":{"self":[{"href":"http:\/\/elcep.legtux.org\/index.php?rest_route=\/wp\/v2\/posts\/62","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/elcep.legtux.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/elcep.legtux.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/elcep.legtux.org\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/elcep.legtux.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=62"}],"version-history":[{"count":19,"href":"http:\/\/elcep.legtux.org\/index.php?rest_route=\/wp\/v2\/posts\/62\/revisions"}],"predecessor-version":[{"id":85,"href":"http:\/\/elcep.legtux.org\/index.php?rest_route=\/wp\/v2\/posts\/62\/revisions\/85"}],"wp:attachment":[{"href":"http:\/\/elcep.legtux.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=62"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/elcep.legtux.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=62"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/elcep.legtux.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=62"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}