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.
- For Postgres, download the installer from enterprisedb.com.
- Run the installer. You’ll have to pick a superuser password – remember it, you’ll need it again soon.
- At the end of the installation process, allow the installer to start Stack Builder.
- In Stack Builder, select the Postgres 9.2 installation and install PostGIS from the list of available extensions.
- The PostGIS installation procedure will prompt you for the superuser password you picked before.
- I suggest letting the installer create a sample database We’ll need it later anyway.
Now for the pgRouting part:
- Download the pgRouting zip file for your system (32 or 64 bit) from Winnie.
- Unzip the file. It contains bin, lib and share folders as well as two text files.
- Copy these folders and files over to your Postgres installation. In my case
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:
- In postgis20, execute the following query to create the pgrouting extension. This will add the pgRouting-specific functions:
CREATE EXTENSION pgrouting;
- Test if everything worked fine:
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.
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/geo?service=WFS&request=GetFeature&version=1.1.0&typeName=ogdwien:OEFFLINIENOGD&srsName=EPSG:4326&outputFormat=json:
- Just copy paste the url in Add Vector Layer | Protocol to load the dataset.
- 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.)
- 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.
- 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.)
- We can immediately see the routing results using the Load as new layer option:
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.
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’
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.
Yes I copy the whole URL.
I tried also click on the URL and copy than the given URL (http://data.wien.gv.at/daten/wfs?service=WFS&request=GetFeature&version=1.1.0&typeName=ogdwien:OEFFLINIENOGD&srsName=EPSG:4326&outputFormat=json)
I tried with 1.8 and 1.9 qgis versions and the result is the same: “is not a recognized data source”
I don’t know if have any interest to this, I use an http proxy to access the web and I have that in qgis settings (I always was able to add layers from wms and wfs so I think its not a problem).
Maybe the proxy is causing the problem. You can try to download the geojson using a browser or wget first and then open from local disk.
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!
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.
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?
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.
Pingback: pgRouting 2.0.0rc1 and PostGIS 2.1.0rc1 - Postgres OnLine Journal
how to open this SQL query window of PostGIS 2.0
That’s QGIS – DB Manager – SQL window.
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
‘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.
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
‘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?
Did you import the ogrgeojson file into the network schema of your database?
yes, I made an schema named network and import ogrgeojson to network schema
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
I’m not sure. Could be a rights issue. Did you download and unzip the pgRouting files as stated in the post? If you keep having problems, please try the pgrouting mailing list http://lists.osgeo.org/mailman/listinfo/pgrouting-users
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.
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.
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?
The function has been removed. For a replacement check http://docs.pgrouting.org/v2.0.0-rc1/doc/src/developer/discontinued.html
Thanks, i will check with the new function. Hope isn’t much different.
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
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.
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
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 ",".
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
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?
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
‘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.
If you still have problems you could try this sample
At the end is a link to a simple data.
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.
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.
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?
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.
M. Rizwan Khan
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 ;
M. Rizwan Khan
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.
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!
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.
To your knowledge, is there a pgRouting for Mac?
Yes, see http://docs.pgrouting.org/dev/doc/src/installation/index.html
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.
how can I get contact with that person? From experience, do you expect that might be because of Windows version?
I’d try http://lists.osgeo.org/mailman/listinfo/pgrouting-users
Did you make sure to get the pgRouting version for Postgres 9.3? http://winnie.postgis.net/download/windows/pg93/buildbot/
I changed the version.. that error disappeared, but got new Error:
Target vertex was not found.
Did you make sure the target vertex id exists?
changed 3000 to 100. Now it worked. Thanks alot
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. ?
Did you make sure to get the correct pgRouting version from http://winnie.postgis.net/download/windows/pg93/buildbot/?
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.
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.
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.