pgRouting 2.0 for Windows quick guide

This post is a quick instruction for installing Postgres 9.2, PostGIS 2.0 and pgRouting 2.0.

  1. For Postgres, download the installer from enterprisedb.com.
  2. Run the installer. You’ll have to pick a superuser password – remember it, you’ll need it again soon.
  3. At the end of the installation process, allow the installer to start Stack Builder.
  4. In Stack Builder, select the Postgres 9.2 installation and install PostGIS from the list of available extensions.
  5. The PostGIS installation procedure will prompt you for the superuser password you picked before.
  6. I suggest letting the installer create a sample database We’ll need it later anyway.

Now for the pgRouting part:

  1. Download the pgRouting zip file for your system (32 or 64 bit) from Winnie.
  2. Unzip the file. It contains bin, lib and share folders as well as two text files.
  3. Copy these folders and files over to your Postgres installation. In my case C:\Program Files\PostgreSQL\9.2

Installation – done.

Next, fire up pgAdmin. If you allowed the PostGIS installer to create a sample database, you will find it named postgis20 or similar. Otherwise just create a new database using the PostGIS template database. You can enable pgRouting in a database using the following steps:

  1. In postgis20, execute the following query to create the pgrouting extension. This will add the pgRouting-specific functions:
    CREATE EXTENSION pgrouting;
  2. Test if everything worked fine:
    SELECT pgr_version();

    It should return "(2.0.0-dev,v2.0.0-beta,18,a3be38b,develop,1.46.1)" or similar – depending on the version you downloaded.

pgadmin_pgrouting

How about some test data? I’ll be using the public transport network of the city of Vienna provided in GeoJSON format from http://data.wien.gv.at/daten/wfs?service=WFS&request=GetFeature&version=1.1.0&typeName=ogdwien:OEFFLINIENOGD&srsName=EPSG:4326&outputFormat=json:

  1. Just copy paste the url in Add Vector Layer | Protocol to load the dataset.
  2. Use DB Manager to load the layer into your database. (As you can see in the screenshot, I created a schema called network but that’s optional.)
  3. import_publictransport

  4. To make the line vector table routable, we use pgr_createTopology. This function assumes the columsn called “source” and “target” exist so we have to create those as well:
    alter table network.publictransport add column source integer;
    alter table network.publictransport add column target integer;
    select pgr_createTopology('network.publictransport', 0.0001, 'geom', 'id');
    

    I’m quite generously using a tolerance of 0.0001 degrees to build the topology. Depending on your dataset, you might want to be more strict here.

  5. Let’s test it! Route from source #1 to target #3000 using pgr_dijkstra:
    SELECT seq, id1 AS node, id2 AS edge, cost, geom
      FROM pgr_dijkstra(
        'SELECT id, source, target, st_length(geom) as cost FROM network.publictransport',
        1, 3000, false, false
      ) as di
      JOIN network.publictransport pt
      ON di.id2 = pt.id ;

    Note how the query joins the routing results and the network table together. (I’m aware that using the link length as a cost attribute will not lead to realistic results in a public transport network but bear with me for this example.)

  6. We can immediately see the routing results using the Load as new layer option:

select_dijkstra
route

Nice work! pgRouting 2.0 has come a long way. In a post from April this year, Boston GIS even announced to add pgRouting into the Stack Builder. That’s going to make the installation even more smooth.

About these ads
68 comments
  1. Pedro said:

    Hi Anita,

    First of all great blog!!

    When I paste the url in Add Vector Layer | Protocol to load the dataset, I get an error like “invalid data source”.
    (even when I try to “add wfs layer”, I choose the layer OEFFLINIENOGD, and I had no geometries in that layer)

    Do you have any idea what am I doing wrong’

    thanks,

    • Hi Pedro,
      Strange the URL works for me in Add Vector Layer | Protocol. Did you make sure to copy the whole URL? The WFS does not work because QGIS does not support the WFS version used by the city server.

  2. Great post!

    I’m just trying to recreate the routing you used in your example (I have just started to use pgRouting).
    The test-data from the URL works fine for me. I can import it into QGIS without any problems (tried it with the current development version of QGIS from GIT).

    But there is a thing that does not work right out of the box for me. And that is the routing query itself (the one with the pgr_dijkstra call). I do not find a way from node 1 to node 3000!
    Is this just some test-query that should not work or should this work with the test data provided?

    I am not sure how to find the node ids to route from/to in general!
    Is there a way to map latitude/longitude to node_id? Or how do I handle routing like “get me from latitude x1/longitude y1 to latitude x2/longitude y2″ (or before the geocoding adress1 to adress2)?

    Thanks for this great blog entry!
    Niko

  3. nikolauskrismer said:

    Hmm…
    after generating the source/target data a second time (using pgr_createTopology) the pgr_dijkstra query works now, but it shows a completely different route!?

    This brings me back to my question on how to find the id’s for specific points to route from/to.

    • As far as I’ve seen, every time you execute pgr_createTopology, the node ids change randomly. You can check my follow-up post which covers how to extract nodes from the network Public transport isochrones with pgRouting.

  4. Hi Anita,

    Thank for this great post! For me the uri works fine, but at step 3 I can’t get the same DB Manager interface allowing me to iimport the road networg in json format to postgis. I’m using qgis version 1.8. I’m figuring out may be you’ve used the dev version. Is the DB Manager plugin different from version 1.8 to version 1.9? I’ve parsed the plugin to version 1.9, but when i try to connect to my Postgis schema I get this following message: postgis: cannot import name tz
    Any idea what is going on?

    Thanks!

    Tahir

    • Hi Tahir,
      You’re correct! DB Manager has been improved a lot since 1.8. Actually, in 1.8 PostGIS Manager is more powerful than DB Manager but even that one can only import Shapefiles, so you first have to save the GeoJSON as Shapefile to then import it.
      Best wishes

    • That’s QGIS – DB Manager – SQL window.

  5. stephinova said:

    Hi Anita!

    Thank you for this great post!
    However, I have a question regarding step 4, for which I get the error message:
    FEHLER: Error, query must return columns ‘id’, ‘source’, ‘target’ and ‘cost’

    I adapted the SQL-command to:

    SELECT seq, id1 AS node, id2 AS edge, cost,geom
    FROM pgr_dijkstra(
    ‘SELECT gid, source, target, st_length(geom) as cost FROM public.publictransport3′ ,
    1, 3000, false, false
    ) as di
    JOIN public.publictransport3 pt
    ON di.id2 = pt.gid ;

    Do you know what’s going wrong?

    • As the error states, the query has to return a column called ‘id’. Currently, the query you are using resturns ‘gid’ but not ‘id’. That should be fixed by writing ‘gid’ AS id.

      • stephinova said:

        Thanks! ;)

  6. Francois Goulet said:

    Don’t know if the comments needs to be approved, but I didn’t see my previous question here. Feel free to delete this one if it’s a duplicate! ;)

    When I tried to create the extention in Postgresql, I got the following error message: “ERREUR: n’a pas pu ouvrir le fichier de contrôle d’extension « C:/Program Files/PostgreSQL/9.2/share/extension/pgrouting.control » : No such file or directory”.

    I downloaded every version from Winnie and the file is nowhere to be found. Any clue?

    Thanks!

  7. Francois Goulet said:

    I’m soooo sorry and feel like an idiot… I downloaded and copied PostGIS 2.0 files instead of pgRouting…

    Thanks!

    • No worries! And yes, comments from new accounts have to be approved before they show up. From now on, your posts should appear immediately.

      • Francois Goulet said:

        Thanks! After a long “battle”, QGIS is now supported by IT here so it’s now an “official” software for our agency. Should come here more ofter now! :)

  8. iqnaul said:

    Hello anita
    I followed the tutorial and when I try to make the route it always error. I got the error message : “relation “network.ogrgeojson” does not exist
    LINE 6: JOIN network.OGRGeoJSON pt
    ^
    my shapefile named “ogrgeojson” I’m using QGIS 1.8.0 and postgresql 9.2. here is my complete SQL script:
    SELECT seq, id1 AS node, id2 AS edge, cost, geom
    FROM pgr_dijkstra(
    ‘SELECT gid, source, target, st_length(the_geom) as cost FROM network.OGRGeoJSON’,
    1, 3000, false, false
    ) as di
    JOIN network.OGRGeoJSON pt
    ON di.id2 = pt.id ;

    what’s wrong why I got this error message?

    regards

    • Did you import the ogrgeojson file into the network schema of your database?

      • iqnaul said:

        yes, I made an schema named network and import ogrgeojson to network schema

  9. username27 said:

    I have PostgreSQL installed in C:\Program Files (x86)\PostgreSQL\9.2 and already running queries without any issues using PgadminIII and POSTGIS=””2.0.3 r11128″. Now I require the functionality of pgrouting and followed this post (which is really helpful). But when I execute the query ‘CREATE EXTENSION pgrouting;’, the result is
    ERROR: could not open extension control file “/usr/pgsql-9.2/share/extension/pgrouting.control”: No such file or directory

    But I can see that the pgrouting.control is available in C:\Program Files (x86)\PostgreSQL\9.2\share\extension. So do you have any suggestions for this? Thanks for your posts

      • username27 said:

        Yes. I have downloaded and unzipped in the way mentioned in this post. I will post it into the mailing list now. Thanks for your quick reply.

      • username27 said:

        I have posted it in the mail list, but no reply. I have removed everything (postgreSQL, postgis) and re-installed everything.I looked into every .ini file and config files but no luck so-far. I have couple of questions.
        1. You said, it might be some right issues. What is that?
        2. Is there any way other than pgrouting? or pgrouting with any other software to use queries?
        3. any other suggestions
        I am stuck with one of my project with this issue. Spent more than a day to figure this out. Please if you can shed some light. I really appreciate your works.

  10. I have one question. On the instructions i don’t see the steps to install the pg_routing functions.
    like C:\Program Files\PostgreSQL\9.2\share\contrib\pgrouting\routing_core.sql

    Im trying to do it by myself but ….

    first i got an error because the option LANGUAGE ‘c’ IMMUTABLE STRICT; have ‘C’ uppercase instead of lowercase i did fix that on the scripts.

    i could create the function shortest_path_astar()
    now i try to create the function shortest_path_shooting_star()
    but looks like the function signature isnt on the librouting.dll file. I check all dll with a DLL viewer and coulldnt find in any of those.

    • CREATE EXTENSION pgrouting; should take care of adding the functions to your database.

      • I create the extension, and got several function with pgr_* prefix.

        But the function i used in previous version was shortest_path_shooting_star() and i cant find anything with that name. the closest is pgr_astar()

        I need shortest_path_shooting_star because my road have turn restrictions.

        Was that function replaced with a new one?

      • Thanks, i will check with the new function. Hope isn’t much different.

  11. Radek said:

    I have tried uploading shapefile using the PosGIS Manager, but I am getting following error message: “‘shp2pgsql’ is not recognized as an internal or external command,\n”, ‘operable program or batch file.\n’. Can someone help me please

      • Radek said:

        I have program installed in the correct directory but it did not work until I have added required information into the “PATH” section. Question on Part 3 of the process, is this SQL query actually modifying table network.publictransport, as when I run it I am not seeing any changes being applied to my table. Should this be run from pgAdmin screen or from QGIS? Also in the data loaded from the provided sample link (Vienna Public Transport) I can see a column called ‘qid’ but not one called ‘id’ is this an issue?

      • They must have changed the dataset. I will have a look.

  12. Hello again.

    I’m working with the function pgr_trsp()
    using this steps http://docs.pgrouting.org/dev/src/trsp/doc/index.html

    Already test one way restrictions and left and right restriction
    but when trying the code to use the multiple links restriction they use a query

    SELECT to_cost, to_edge AS target_id, from_edge || coalesce(”,” || via, ””) AS via_path
    FROM restrictions;

    in this case return –>
    to_cost target_id via_path
    4 8 3 <—

    but i think should return
    4 8 3,5<—

    Don't know if is my server fault the functions coalesce doesn't work properly to create the string with the edges values separated by ",".

    • FYI
      i solve this issue changing the COALESCE for a CASE

      SELECT to_cost, to_edge AS target_id, case when via is null then from_edge || ””
      else via || ”,” || from_edge
      end AS via_path
      FROM restrictions

      • Hello,

        I’ve replaced the query of the doc by yours, and I get a syntax error. Strange thing is the query, separately, returns the value expected.

        Anyway, I’m not sure I’ve understood the logic of turn restrictions here. Basically, the restrictions table says: “the cost from this edge to this edge via this path will be X”, being X an arbitrary cost (for example, a really high value if you want to restrict that turn). Am I right?

        If that’s the situation, what’s the difference between doing that and just setting the cost of an edge to a really high value?

  13. Radek said:

    Hi Members,

    I am finding it really frustrating to perform the pgRouting exercise. I have done all as per the instructions and imported shape file using PostGIS Manager and the imported file has following columns after adding source and target:

    gid [PK] serial; OBJECTID integer; LIBEZEICHNU char var; LTYP char var; LTYPTXT char var; SE_ANNO_CA char var; GEOM geometry(linestring); SOURCE integer, TARGET integer.

    I also can see that running below query creates a new table called

    select pgr_createTopology(‘network.publictransport_gid’, 0.0001, ‘geom’, ‘gid’);

    However when I run Step 4, I get no result, query runs but it shows no output. Can someone please explain what am I doing incorrectly. I am fairly ne to GIS however I have been running SQL queries for a while thus I am understanding what each SQL is trying to achieve but I am not sure I am importing or setting up stuff correctly. I have modified SQL slightly

    SELECT seq, id1 AS node, id2 AS edge, cost, geom
    FROM pgr_dijkstra(
    ‘SELECT gid AS id, source, target, st_length(geom) as cost FROM network.publictransport_gid’,
    1, 3000, false, false
    ) as di
    JOIN network.publictransport_gid pt
    ON di.id2 = pt.gid ;

    Below Step 4 there is a comment “Note how the query joins the routing results and the network table together.” Please explain what is this meaning?

    I have tried also running the process after importing shape file using Database -> Split option as this method allows for ‘gid’ to be replaced by ‘id’ but this still makes no difference.

    • If I import the GeoJSON layer into PostGIS using QGIS master’s DB manager, I get an “id” column. Note that I’m not using any Shapefile nor PostGIS Manager.

  14. Radek said:

    Hi All,

    I think I have it resolved now after running and testing multiple scenarios, I think the issue resolves around the fact that when creating topology occasional what happens is that the resulting topology creates a set of numbers where it seems the source and target are either too far out, too hard to connect or there is an error in the nodes. This prohibits the next query from finding ultimate route. I had to rerun create topology query (each time the results are different and after a couple of times the shortest route query started to work. However I found it to work more often when the using “id” an “the_geom” fields.

  15. Hi Anita. do you know how calculate the fraction for the starting / end node having the x,y to use the other function signature?

    source_edge: int4 EDGE id of the start edge
    source_pos: float8 fraction of 1 defines the position on the start edge <—

    • I think st_line_locate_point should work well for this use case.

  16. Vivek said:

    I am not getting any postgis20 named sample database.I am new to it ..please help

    • Are you working on Windows? Did you notice the option for creation of the sample database during the installation procedure?

  17. Rizwan said:

    Hi Anita,

    As per the http://docs.pgrouting.org/dev/src/dijkstra/doc/index.html
    It seems the pgr_dijkstra function returns a set of pgr_costResult (seq, id1, id2, cost) rows and no Geometry column. Hence I am getting error in
    select seq, id1 AS node, id2 AS edge, cost, geom from pgr_dijkstra(
    ‘SELECT gid as id, source, target, st_length(the_geom) AS cost FROM road’, 1, 3000, false, false);

    as ERROR: column “geom” does not exist

    Could you let me know how can I extract the geometry too.

    Regards,
    M. Rizwan Khan

  18. M. Rizwan Khan said:

    Apologies for posting a stupid question of getting the geometry of the shortest path.
    I modified your query to get it

    select seq, id1 AS node, id2 AS edge, cost, pt.the_geom from pgr_bddijkstra(
    ‘SELECT gid as id, source, target, st_length(the_geom) AS cost FROM road’, 1, 150, false, false)
    as di JOIN lt_line pt ON di.id2 = pt.gid ;

    Regards,
    M. Rizwan Khan

    • Heinz said:

      Hi, Anita, I follow your instruction and try to activate pgrouting in the very beginning procedure, but I can not find pgrouting.control file after I finished the installation of Postgre, I thought that’s why I can’t introduce pgrouting. How can I solve this problem?

      • Did you copy the contents of the pgRouting ZIP file to the Postgres installation, e.g. C:\Program Files\PostgreSQL\9.2 as specified? You need to be specific about which steps you performed and what exactly is the error message and when does it appear.

  19. Philippe Crist said:

    Hello Anita,

    I am trying to replicate your example in the above quick guide but am having some difficulty getting the sample database (postgis_21_sample) created in step 6 of the very first procedure you describe to be visible in the database manager in Qgis 2.0. The database is visible in pgAdmin III but when I go to load the Wien public transport data layer from Qgis into “postgis_21_sample” in Qgis’s Database manager, I cannot seem to find it. Should I first create a database layer in Qgis? If so, I also cannot seem to find where “postgis_21_sample” is located. Perhaps I have missed a very simple and basic step?

    Thank you and keep up the great work!

    Philippe

    • In the “Add PostGIS Layer” dialog, you can set up a new database connection. If you installed PostGIS on your local computer, you can simply specify localhost as the database host.

  20. Professor,

    To your knowledge, is there a pgRouting for Mac?

  21. Sandy said:

    Hi Anita, thanks for your efforts..

    I got Unknown Error as following:

    Error: Query failed: ERROR: could not load library “C:/Program Files/PostgreSQL/9.3/lib/librouting.dll”: unknown error 126
    (58P01) QPSQL: Unable to create query

    What could be the reason for such error? I made sure that the file exists via the mentioned path, but I can’t identify the reason why this did not load. I’m using version 2.1.1 on Windows8

    • Sorry I haven’t tested Win8 yet. I suggest to get in touch with the creators of the PgRouting Windows packages.

  22. romi said:

    Hello, Anita thanks for this great tutorial, can you help me with this error :
    “incompatible library “D:/Program Files/PostgreSQL/9.3/lib/librouting.dll”: version mismatch
    DETAIL: Server is version 9.3, library is version 9.2.”
    How can I upgrade library version from 9.2 to 9.3. ?

      • romi said:

        Please correct me if I’m wrong, I downloaded this one “postgis-bundle-pg93x32-setup-2.1.3-1.exe”. I have win xp32.

      • You need the pgrouting zip file not the postgis exe.

      • romi said:

        I downloaded pgroutng and I did everything as written, then says : “pgroutng already exists”
        and I have this version “pgr_version (2.0.0,pgrouting-2.0.0,0,d6ed2cb,master,1.46.1)”. It should be correct pgRouting version but still the same error.

      • romi said:

        Hi, me again
        I uninstalled postgis and postgres, then installed again. Now when creating new spatial database trough stack builder get this error:

        “psql: could not connect to server: Connection refused (0x0000274D/10061)
        Is the server running on host “localhost” (127.0.0.1) and accepting
        TCP/IP connections on port 5432?”

        Can you help me with that please ?

      • I think you will get more qualified answers to these questions if you ask the postgis developers.

Follow

Get every new post delivered to your Inbox.

Join 2,955 other followers

%d bloggers like this: