A Beginner’s Guide to pgRouting

Please read the new instructions for pgRouting 2.0.

The aim of this post is to describe the steps necessary to calculate routes with pgRouting. In the end, we’ll visualize the results in QGIS.

This guide assumes that you have the following installed and running:

  • Postgres with PostGIS and pgAdmin
  • QGIS with PostGIS Manager and RT Sql Layer plugins

Installing pgRouting

pgRouting can be downloaded from www.pgrouting.org.

Building from source is covered by pgRouting documentation. If you’re using Windows, download the binaries and copy the .dlls into PostGIS’ lib folder, e.g. C:\Program Files (x86)\PostgreSQL\8.4\lib.

Start pgAdmin and create a new database based on your PostGIS template. (I called mine ‘routing_template’.) Open a Query dialog, load and execute the three .sql files located in your pgRouting download (routing_core.sql, routing_core_wrappers.sql, routing_topology.sql). Congratulations, you now have a pgRouting-enabled database.

Creating a routable road network

The following description is based on the free road network published by National Land Survey of Finland (NLS) (Update January 2013: Sorry, this dataset has been removed). All you get is one Shapefile containing line geometries, a road type attribute and further attributes unrelated to routing.

First step is to load roads.shp into PostGIS. This is easy using PostGIS Manager – Data – Load Data from Shapefile.

pgRouting requires each road entry to have a start and an end node id. If your dataset already contains this information, you can skip this step. Otherwise we will create the node ids now. (Update: pgRouting also offers a special function called assign_vertex_id that will create start and end node ids for your network table. It will not create a node table though.)

Next, we create start and end point geometries. I used a view:

CREATE OR REPLACE VIEW road_ext AS
   SELECT *, startpoint(the_geom), endpoint(the_geom)
   FROM road;

Now, we create a table containing all the unique network nodes (start and end points) and we’ll also give them an id:

CREATE TABLE node AS 
   SELECT row_number() OVER (ORDER BY foo.p)::integer AS id, 
          foo.p AS the_geom
   FROM (         
      SELECT DISTINCT road_ext.startpoint AS p FROM road_ext
      UNION 
      SELECT DISTINCT road_ext.endpoint AS p FROM road_ext
   ) foo
   GROUP BY foo.p;

Finally, we can combine our road_ext view and node table to create the routable network table:

CREATE TABLE network AS
   SELECT a.*, b.id as start_id, c.id as end_id
   FROM road_ext AS a
      JOIN node AS b ON a.startpoint = b.the_geom
      JOIN node AS c ON a.endpoint = c.the_geom;

(This can take a while.)

I recommend adding a spatial index to the resulting table.

Calculating shortest routes

Let’s try pgRouting’s Shortest Path Dijkstra method. The following query returns the route from node #1 to node #5110:

SELECT * FROM shortest_path('
   SELECT gid AS id, 
          start_id::int4 AS source, 
          end_id::int4 AS target, 
          shape_leng::float8 AS cost
   FROM network',
1,
5110,
false,
false);

Final step: Visualization

With RT Sql Layer plugin, we can visualize the results of a query. The results will be loaded as a new layer. The query has to contain both geometry and a unique id. Therefore, we’ll join the results of the previous query with the network table containing the necessary geometries.

SELECT * 
   FROM network
   JOIN
   (SELECT * FROM shortest_path('
      SELECT gid AS id, 
          start_id::int4 AS source, 
          end_id::int4 AS target, 
          shape_leng::float8 AS cost
      FROM network',
      1,
      5110,
      false,
      false)) AS route
   ON
   network.gid = route.edge_id;

In my case, this is how the result looks like:

Route from node #1 to node #5110

For further pgRouting-related posts check my list of pgRouting posts.

About these ads
98 comments
  1. joe larson said:

    This is a great post – thank you! I was looking into pgRouting previously – – and most tutorials use OSM data. Also, I became frustrated with how many different sets of instructions there were. Lastly, I was really thrown for a loop when I started looking into PostGIS topology http://www.pgrouting.org/docs/howto/topology.html#postgis

    I am interested in routing for public safety – which would undoubtedly require more than just shortest path. Using costs for road segments, one-ways, etc. http://www.pgrouting.org/docs/howto/topology.html#graphs-directed-undirected-reverse-costs and could also benefit from “Routing Multiple Vehicles with pgRouting DARP Function” (which it appears you posted on previously). I know there’s this plugin http://gis-lab.info/qa/road-graph-eng.html but…it would be really cool to expand all-of-this > perhaps incorporate it into http://www.ticketscad.org/ with dynamic routing and the open source community could start build a CAD system e.g. http://www.is.northropgrumman.com/products/commandpoint_map/index.html

    • underdark said:

      Hi Joe,
      Thanks for your comment and the links to the CAD systems. I haven’t used CAD tools since an introduction course I took at university. Is there a big advantage in using CAD instead of GIS for routing purposes?
      You mentioned Road Graph Plugin. All the requirements you posted are met by the plugin – as far as I remember.
      Good luck with your project!

      • underdark said:

        Ah :D
        And I’ve been wondering about TicketCAD’s online demo. Little confusing those abbreviations …
        Thanks for the clarification! I didn’t know the term.

  2. rdalton said:

    Thank you for this great posting. I am really looking forward to getting pgRouting working, but ran into a problem during setup right off the bat. I am sure there is something basic I must be overlooking, but I don’t know what it is. When I ran the “routing_core.sql” I immediately got an error stating: “ERROR: syntax error at or near “TYPE” on the line “CREATE TYPE path_result AS (vertex_id integer, edge_id integer, cost float8);”. I used the tempate_postgis and am running postgis 1.5.2 on postgresql 9.0.

    • underdark said:

      Strange, but it might be that pgRouting doesn’t support Postgres 9.0 yet. Have you checked that? I’m still working on 8.x.

      • rdalton said:

        You are correct. It looks to me like the 1.03 dll’s were not compiled for 9.0. I uninstalled 9.0 and reinstalled 8.4 and everything worked great. Thanks so much for this great tutorial in getting started!

      • Abbas said:

        Hello
        I am still wondering if pgRouting supports Postgres 9.0 yet…
        btw, do you know any other alternatives ?

        Cheers

      • underdark said:

        I’m not aware of a version supporting 9.0.
        I don’t know any alternatives for in-PostGIS routing. If you just need some routings every once in a while, you might be happy with the QGIS routing plugin.

      • thanks,

        but im working on 8.4 as well have not tried on 9

        rgds

  3. RJay said:

    Thanks for the great tutorial. I am new to GIS and trying to gain insight doing a project. I have a question on pgRouting.. I have two separate shapefiles for bus routes and train routes. How to find shortest path by combining these two informations (meaning passenger can take bus then train etc). How should I go about using pgrouting.

    • underdark said:

      I would imagine that you can create a network by combining your bus and train networks (with the union operator). You basically have to make sure to have unique node ids and connections between bus nodes and train nodes where passengers can change from one into the other.

    • rdalton said:

      I think you may be referring to “multi-modal” transportation networks (ie- using multiple modes of transport, which is the most efficient). I came across this post recently in thinking the same way. It appears pgRouting cannot do this kind of modeling. http://lists.osgeo.org/pipermail/pgrouting-users/2011-March/000644.html

      That being said, if you just need to model which method (bus vs train) is more efficient, combining the 2 layers into 1 as underdark stated could be used by then adding different cost weightings on the ares that effectively says “its faster to take a train than a bus”. Then theoretically starting at Point A you would take the bus to the closest train terminal, ride the train, stop at the next train station, then take the bus to your destination.

      As underdark stated, you would have to take extra care to deal with bus route/train route node intersections that were not actually stops.

      • RJay said:

        Thank you both. I was totally stuck. But now I can give this a try.

  4. Robert said:

    I keep getting an error when trying to run shortest distance. I’m on pgsql 8.4.8.

    ERROR: function shortest_path(unknown, integer, integer, boolean, boolean) does not exist
    LINE 1: SELECT * FROM shortest_path(‘
    ^
    HINT: No function matches the given name and argument types. You might need to add explicit type casts.

    • underdark said:

      Could you post the full query?

  5. Kirill Blajko said:

    Hello, I have the same problem!

    SELECT * FROM shortest_path(’
    SELECT gid as id,
    source::integer,
    target::integer,
    length::double precision as cost
    FROM ways’,
    605, 359, false, false);

    returns

    SQL error:

    ERROR: syntax error at or near “SELECT”
    LINE 2: SELECT gid as id,

    and if I change the whole sub-select

    ’SELECT gid as id, source::integer, target::integer, length::double precision as cost FROM ways’

    with just ‘1’, the error is

    ERROR: function shortest_path(integer, integer, integer, boolean, boolean) does not exist
    LINE 1: SELECT * FROM shortest_path(1,

    • underdark said:

      Try to use the exactly same casts I had in the example. pgRouting seems to be a little picky when it comes to data types.

      SELECT * FROM shortest_path('
      SELECT gid as id,
      source::int4 as source,
      target::int4 as target,
      length::float8 as cost
      FROM ways',
      605, 359, false, false);

      Make sure the apostrophes around the subquery are straight and not typographic ones.

      • Kirill Blajko said:

        Ok, thanks, I will try.

        By the way – the all-known PDF workshop on pgRouting has typographic apostrophes in the queries.

    • RJay said:

      I had this issue before running the three pg_routing sqls.. routing_core.sql, routing_core_wrappers.sql, routing_topology.sql…

  6. Hi,

    Do you know if PG Routing is still not supported on PostgreSQL 9.0? I am using PostgreSQL 9.0 and would like to use this plugin, but I have come accross some posts saying that you must ‘build it yourself’ if you want to use PG Routing with PostgreSQL 9.0 …. I wouldn’t mind doing this but don’t really know how… :-(

    • underdark said:

      Hi James. Judging from the release log (http://www.pgrouting.org/docs/release_notes.html), pgRouting doesn’t support PostgreSQL 9.0 yet. Have you tried the pgRouting user mailing list yet? That would be my first spot to ask for help.

      • Thanks for the reply… that’s annoying… will look into other options…

    • underdark said:

      I wasn’t aware of the assign_vertex_id() function when I wrote this post. I doubt my method has any advantage over the built-in function.

  7. dirty_work said:

    Hello,

    I’m a newbie with a Tiger2010 data (U.S., California) loaded into a PostGIS database.

    http://www.census.gov/geo/www/tiger/tgrshp2010/tgrshp2010.html

    I have geographic points stored as type geometry in my table of locations. ex. ST_GeomFromText(‘POINT(-87.5125031929475 38.0132486307176)’,4326)
    How do I convert that geometry type into the road point type in your query example?

    I can find distance ‘as the crow flies’ with good accuracy. I’d like to use PgRoute to find driving distance with reasonable accuracy. Is there something I need to do to prepare the road data from the Tiger2010 data for use in a shortest_path query?

    • underdark said:

      In Drive Time Isochrones – An Example Using Finnish Airports, I described a way to find the nearest network node to a point to a table of points of interests.

      I could imagine another solution finding the nearest network link and inserting a temporal node there. Using ST_LineLocatePoint() and ST_LineInterpolatePoint() but I haven’t tried that yet.

  8. Sah said:

    regards Anita.

    when running to the second stage, I got this error :
    ERROR: syntax error at or near “OVER”
    LINE 2: SELECT row_number()OVER (ORDER BY foo.p)::integer AS id,

    Could you help me?, thank you for the tutorial

    • underdark said:

      I think you’re just missing a space there (before OVER):

      SELECT row_number() OVER (ORDER BY foo.p)::integer AS id,

  9. Sah said:

    Thanks for the replies ..

    Anita right I forgot to give some space, but the function row_number () is not recognized as version 8.3 so I be a fox 8.4, all went well,

    problem arises when I run a stage 4 (Calculating the shortest routes), the error occurs:

    ERROR: Target vertex was not found.
    ********** Error **********

    I take my own case studies myedges.sql rather than road.shp, to truly master.

    thank you very much Anita.

  10. Sandeep said:

    Hi,
    Thanks for the wonderful post. It was really useful.
    But i have a road network shape file whose poly lines are very long and not from junctions to junction. So even if a long road intersects many roads, it will have only one start_id and end_id. How do i consider the intersections in the middle. Do i have to split the poly line at the junctions? Please help

    • underdark said:

      Hi Sandeep,
      Yes, you have to split the lines at the junctions. One way to do this is to use GRASS GIS clean topology.

  11. Ariel said:

    Hi, thanks for this very helpful tutorial. After doing every thing you posted, i finnish with a lot of doubts because the shortest_path function returns me an error “Target vertex was not found.”. When i made a hand test, connecting by myself the result set of roads, i realized that the nodes table has his own ID column (related with the row number) wich is different of the original ID of the rows fields. So for example a street in Buenos aires argentina the end_id was related with a street in Cordoba (800 km of distance between them). For that reason i need some help to understand how to connect them.
    Thanks
    Ariel

  12. Peter Granberg said:

    Hi!
    Can PGrouting handle Turn Restrictions? And do PGrouting has logic for handle vehicle types restrictions, eg. this road segment is bus and taxi-only?
    Thanks
    /Peter

    • underdark said:

      Hi Peter,
      Both should be possible. While simple Dijkstra doesn’t handle turn restrictions, there are other algorithms available that can handle turn restrictions. For bus or taxi-only segments, it should be possible to handle that when selecting the network for the routing function.

  13. Nick said:

    This article has gotten me ALOT closer, however I am still missing a critical step somewhere. I do not have a shape_leng value in a table. Where did “ways” come from? Is that the table from the shp2pgql import? Once i have that, then i think that shortest_path will work. Then my next step is how to use lat/lon pairs to specify start and end to calc the distance. Eventually i want to use driving directions. Any suggestions are much appreciated!

    • underdark said:

      The “shape_leng” is an attribute of the dataset I used in this example. Your data will have different cost attributes but if it’s road network data, there should be some kind of road length value.

      The post doesn’t mention any “ways” table. The shp2pgsql import is called “road” in this example. (If you are referring to the comments, some users seem to have called their import “ways”.)

      For your next step: So far, I used a method of finding the closest network node to a given set of coordinates to get start and end node for the routing.

      • Nick said:

        Hi underdark- thank you for your reply. I apologize for the basic questions, but where does this “shape_leng” / road length value come from? Is this something that i have to run a specific sql query with postgis functions on? Is there an example somewhere?
        I followed the post, however i am running in ubuntu, and dont have PostGIS Manager application to load the data. In the post, there are 3 queries to run, the second creating “network” table, but after running that i do not have a length column, so the 3rd query for shortest_path fails.

      • underdark said:

        “shape_leng” is already contained in the original Shapefile downloadable from National Land Survey of Finland. If you are using a different dataset for this tutorial, you have to create your ownl length column. You can calculate the length of a line using PostGIS function ST_Length().

        PostGIS Manager is a QGIS Plugin. You can install it in Ubuntu.

      • Nick said:

        Yes, i am from the US, so i wanted to use census Tiger shapefiles. I am able to run ST_Length, however per the documentation this says that it returns 2D length in spatial reference units. How can i convert these into feet / miles / m / km?
        Regarding the QGIS, i am running a headless server. I don’t “need” the visuals, but i guess they could help.

      • Nick said:

        Tiger shapefiles claim to be NAD83 (SRID=4269)

      • underdark said:

        The TIGER file I just downloaded contains an attribute called “LENGTH”. I assume they all look same. Just use that.

      • Nick said:

        Which tiger data did you use? I have the 2010st data (.shp, .prj, etc). Specifically i have arbitrarily selected: tl_2010_17023_roads to work with. When using the shp2pgsql, i do not get “Length”. I do have “linearid”. Sorry for the basic questions, i am stuck here though. What method are you using to see the “Length” attribute in the tiger data? Can you send me the name of the file you are using?

  14. forgot closing semi-colon on “create the routable network table” code snippet. Feel free to delete this post.

    • underdark said:

      Thanks Joe,
      I added all missing semi-colons.

  15. hi all. I got a problem when I perform following script

    CREATE OR REPLACE VIEW road_ext AS
    SELECT *, startpoint(geog), endpoint(geog)
    FROM routes;

    I copy these text into SQL Query dialog and run it, pgAdmin promote me some information, there is not function match the prototype startpoint(geometry).
    Can anyone let me know what happen here?

    PS: I have build one for postgresql 9.1 and boost 1.47.0 (no DD and TSP)

    • underdark said:

      You seem to be lacking PostGIS. You need to install both pgRouting and PostGIS.

      Good luck!

      • ur words wake me up! The version of postgis installed in my computer is 2.0svn, the function has been changed to ST_StartPoint() by the postgis.sql script file :)

  16. prabhat kiran mahapatro said:

    Thanks for your wonderful post , it is very useful in my application. i want to know in future if i m editing a line or adding some more lines then what i need to do ?? should i need to delete nodes and network and recreate it??

    pls i need ur replay…help me.

    • underdark said:

      If you edit the links, you have to take care to also update the “to_node” and “from_node” values. You should also ensure to keep the nodes table up-to-date. If you introduce new links, you’ll also have new nodes. But it’s not necessary to recreate the whole network, you can update only the small part you changed.

      • prabhat kiran mahapatro said:

        Thanks for your replay. I have added and edited couple of new/existing links and updated “to_node” and “from_node” in Nodes table.now i am not getting how i can update the small part (where i have added/edited links) in my network.

        pls tell me what changes i need to do in create network query to update my network.

      • underdark said:

        The automatic tools to create a network cannot update only a part of the network. It’s probably faster if you rebuild the whole network. A more efficient way would be to take care of these topics when editing the links but I haven’t tried set up such an environment yet. Things should get easier with PostGIS 2.0 Topology feature.

  17. Mocan said:

    Thanks for your tutorial it’s help me so much.
    but i have a problem, how to show routing result in webgis, that we can use map navigate , i use maserver in actually, can i implement taht in open layers, combine mapserver with openlayers,

  18. Thank you very much for this article! It allowed me to start. Can I draw your article to write in French? That can be helpfull for some french people!!!
    thank you

  19. huma saffdar said:

    i have got a problem in running Dijkstra
    SELECT * FROM shortest_path(‘
    SELECT gid as id,
    start_id::int4 AS source,
    end_id::int4 AS target,
    FROM network’,
    605, 359, false, false);

    ERROR: syntax error at or near “FROM”
    LINE 5: FROM network
    ^
    i have got there error
    what do i do?

    • underdark said:

      There must be no trailing comma after the last field name. So, no comma after “target”.
      You will also need a cost field. It is mandatory.

  20. sarah said:

    I download a data from osm and it does not contain length attribute so how can i run this algorithm

    • underdark said:

      You could use ST_Length() to calculate the length of the road in PostGIS.

  21. Sarah said:

    SELECT assign_vertex_id(‘Roads’, 0.00001, ‘the_geom’, ‘gid’);

    when i run this query i got this error
    NOTICE: CREATE TABLE will create implicit sequence “vertices_tmp_id_seq” for serial column “vertices_tmp.id”
    CONTEXT: SQL statement “CREATE TABLE vertices_tmp (id serial)”
    PL/pgSQL function “assign_vertex_id” line 14 at EXECUTE statement
    ERROR: query string argument of EXECUTE is null
    CONTEXT: PL/pgSQL function “assign_vertex_id” line 20 at EXECUTE statement

    ********** Error **********

    ERROR: query string argument of EXECUTE is null
    SQL state: 22004
    Context: PL/pgSQL function “assign_vertex_id” line 20 at EXECUTE statement

    i created table vertices_tmp manually by couldn’t solve the problem

    • underdark said:

      Try:
      SELECT assign_vertex_id(“roads”, 0.00001, “the_geom”, “gid”);

      ” is not equal to ‘ in PostGIS.

  22. Sarah said:

    no it doesn’t work when i run this query
    SELECT assign_vertex_id(“G10Roads”, 0.00001, “the_geom”, “gid”);
    it gives error like this

    ERROR: column “G10Roads” does not exist
    LINE 1: SELECT assign_vertex_id(“G10Roads”, 0.00001, “the_geom”, “gi…

    when i used your example QGIS cannot run the query as explained in your example it gives error of public “.” SELECT like this and i cannot find any thing like RT SQL layer plugin when adding postgis layer it has only build query tab and when i wrote query for displaying my layer it gives error

    i want to use this plugin pgRoutingLayer you used for displaying layer
    i am using the same version of QGis 1.7.2 wroclaw

    • underdark said:

      Please post your table layout so we can verify the table and column names. Which CRS are you using for your geometries?

  23. Sarah said:

    thank you mam i have solved these problems i mentioned earlier. i have configured fastsql layer plugin but i have got some errors there. i couldn’t found your GUI named pg-routing layer.

  24. hari said:

    Hello

    this is really good. Im new to this development. can you kindly help me as to how did you load the map and then how is the routing done on the map, coz when i tried to follow the steps given by you i get the below error
    ERROR: type “geometry” does not exist
    ********** Error **********
    ERROR: type “geometry” does not exist
    SQL state: 42704

    Your help will help me execute my work please

    thanks and regards

    • underdark said:

      It seems like you are lacking PostGIS extension for PostgreSQL. Are you sure you installed it correctly for you database?

      • hari said:

        yes i did install it correctly after which i did convert all the .SHP files into my postgre DB. I have copied the 3 .dll files in the postgres lib folder…. I get this specific error in the wrapper and core sql files…

        thanks and regards

      • Hello,

        Also would you help me as to how do i load the map in the viewer using PHP as in what or rather how are the basic steps to load the map from the DB???
        thanks and regards

      • underdark said:

        I’m afraid this is going into too much detail for the blog comment section. Let’s move this to email.

      • Hello,

        Ive loaded the map, thanks for the support, i need to draw a distance line between 2 selections on the map. Im using mapviewerphp. can you help me with this please…

        rgds
        harivaag

      • underdark said:

        Sorry I’m not familiar with mapviewerphp.

  25. Akmal said:

    Thanks for your very useful post , it helped me to learn basics of PgRouting and postgis. I have a road map with direction information (FT – FromTo, TF-ToFrom, B- Both, N-Driving not allowed). I have to implement it in my routing network table. Please help with SQL query which will create new columns and fills them with direction, cost, reverse cost information. Also pls help with SQL query which will builds route using the road rules. Thank you.

  26. Denis said:

    Is it neccessary for routing tasks split all LINESTRINGs into simple line segments (which contains two points)?

    • underdark said:

      No, that’s not necessary.

  27. Santiago said:

    Hi, I have used your begginers guide and everything worked well until i executed the “create table Network”. Im trying to create a network of the spanish roads, the shp file size was 220MB, I loaded it successfully to my db (after doing all steps described in your tutorial) and when I excecuted the “Create” statement for table network. The query started and It has been working for almost 15 hours long. Im not sure if this is wrong (even though I believe it is) What do you think? should I stop the process and start again? or Should I leave it workign for the weekend and maybe it will be finished on monday morning.

    Thank you

    • underdark said:

      Considering the size of your file it could well take more than 15 hours. I’d leave it running until Monday. If it hasn’t successfully completed until then, please look into assign_vertex_id() function which should be able to do the same thing.

      • Santiago said:

        Thak you very much for your promtly response. I will leave this working until monday and let you know as soon it is finished (or not hehehe). Also I want to tell you that probably Ill be using your post “A Closer Look at Alpha Shapes in pgRouting” into a project in which I need to find some influence areas within some boundaries around a point.

        You’r doing a terrific job here. !!!
        Cheers

  28. stefano said:

    Hi,
    I’m trying to follow your tutorial but I’m new to pgRouting and open source GIS in general, then I’m having some dufficulties…
    What exactly is the road network shapefile from National Land Survey of Finland (NLS) that you used in this guide?

    thanks

    Stefano

    • underdark said:

      You can download the road network shapefile from the NLS website mentioned above. Any other oad network shapefile will do as well. Some probably even have start and end nodes defined, then you can skip the first part.

  29. Hi underdark,

    Not really a problem as such, more a comment and thanks. After being stubborn and looking for work-arounds for a while, I have finally uninstalled PostgresSQL 9.0 and gone back to 8.4 so that I can use PGRouting – and have managed to get it all set-up I think after following your tutorial.

    Instead of using the sample data however, I decided to use a Shapefile of RoadLinks for London.

    I first download the Integrated Transport Layer from Ordnance Survey:

    http://www.ordnancesurvey.co.uk/oswebsite/products/os-mastermap/itn-layer/index.html

    Then I prepared it for use in ArcGIS using the ESRI Productivity Suite and ESRI White Paper:

    http://www.esriuk.com/literature/documents/ITN_wp.pdf

    The result being I had a Network_Dataset that I could use with the Network Analyst tool within ArcGIS. However I wanted to do things that cannot be done in ArcGIS so I am now trying to move this network into PGRouting. So what I did was I loaded the Network Dataset into ArcGIS and then exported the part of the network file called ‘RoadLinks’ as a spapefile. The columns in this table were as follows:

    Label
    Node1
    Node2
    TurnRestrictions
    OneWay
    EdgeLength

    and a few others.

    So having exported this as a shape, I then used the PostGIS ShapeLoader to load it into a table that I had created using the routing_template table from your tutorial. I then earlier today did the ‘Start and End Geometries’ query as well as the ‘Unique Network Nodes’ query and both seemed to execute properly. I am now running the ‘Create Network’ query and waiting for the results. It had been about an hour so far, but as the original shapefile was around 330MB I expect that I might have to come back on Monday to see how it has gone.

    Any thoughts/hints/tips on what I have done so far?

    Thanks again,

    James

    • underdark said:

      Hi James,

      Is Node1 the starting node’s id and Node2 the end node’s id? If yes, you don’t need to create the network from scratch. Everything you need is already there and you can start routing.

      What end result are you going for?

      Best wishes,
      Anita

      • Hi Anita.

        Thanks for the reply. I didn’t apprecaite about not needing to build from scratch. I set it going as I left work on Friday and won’t be back there until Monday morning so I will stop it then if it hasn’t finished already. Building the network from scratch won’t have done any harm though I presume? It will essentially just have re-recreated the network again yes?

        End result? Long story, but initally I want to do some routing and the output to be quite detailed, which I don’t get in ArcGIS. What I mean is that I want the result of my query from point a to point b to be broken down and show the length of each road section that was travelled upon. So depart from point a, travel 15 metres along road id 1, 20 metres along road id 3, 150 metres along road id 3, arrive at point b. And to store each of these ‘bits’ as differnt rows in a new table along with a journey ID or something like that so that I know that they are part of the same journey. Then to repeat this a few thousand times. I will then bring in some data from another table about the amount of air pollution on each of those roads so that I can calculate how much air pollution someone has been exposed to in each journey.

        THanks

        James

      • James Smith said:

        Hi Anita,

        If I don’t bother with creating the network from scrath and just use the table I already have (with Node1, Node 2 as the start and end nodes) then when I try and run this query I get the error message “ERROR: value “4000000023178687” is out of range for type integer”. I understand why this is, it’s that the data in my Node column isn’t suitable for converstion to a Int4 data type. But I’m not sure how to fix this. Any ideas?

        SELECT * FROM shortest_path(‘
        SELECT gid AS id,
        node1::int4 AS source,
        node2::int4 AS target,
        shape_leng::float8 AS cost
        FROM londonroadlinks_n’,
        1,
        5110,
        false,
        false);

        THanks

        James

      • underdark said:

        Looks like you have to recreate int4 ids for the nodes. Which range do the node ids currently cover? Maybe it can be as simple as subtracting a certain value.

      • James Smith said:

        Hi Anita. Yes I think that you might be right. The nodes are numbers like ‘400000046578’ and ‘400000046579’ etc. I guess I might create two new columns like Node1_new and Node 2_new and insert into them the existing Node IDs but with 400000000000 removed from the number. If I did this do I then need to repeat the ‘CREATE VIEW’ and ‘CREATE TABLE node’ SQL statements?

        Thanks

        James

      • underdark said:

        You don’t need to run either of those queries. The goal of these queries is to create node IDs, but you already have suitable node IDs (if you substract 400000000000).

  30. Paritosh Kowli said:

    Can you help me out if i have 3 points(startpoint,endpoints and viapoint(midpoint)) how to route this and find driving distance for same. And via point depends on me.

    • underdark said:

      I guess you could split it up into two separate routing operations.

      • Paritosh Kowli said:

        Can explain me in brief(i.e. Can you help me out if i have 3 points(startpoint,endpoints and viapoint(midpoint)) how to route this and find driving distance for same. And via point depends on me.)

Follow

Get every new post delivered to your Inbox.

Join 3,875 other followers

%d bloggers like this: