I have a postgis database with two tables. In the first table I have linestrings of a street network. The second table has points that are located on the street network.

What I want is to find the connection between these nodes using a shortest path method. The result would be an array of all the linestrings which are among these two nodes.

I want to run this code in an iterative way based on different node-pairs that I have stored in a csv file.

Which tool would you recommend me for something like this? Can pgRouting do something like this?


1 Answer 1


Yes, you can do this with pgRouting.

As a starting point I would recommend you to look at the pgRouting Workshop. Most efficient for your case might be the one-to-many shortest path funtion named kDijkstra.

You could import your node-pairs into a PostgreSQL database or just write a small application that reads the CSV file and then runs the SQL queries.

If your street network doesn't contain the network topology yet, then you first had to make sure to have source and target information available.

Because pgRouting usually routes between network nodes, you need to make sure that your nodes are either part of the network, or you need to calculate the nearest points in your network based on your node-pairs. Take a look at this custom pl/pgsql function, which is a good starting point to customize it for your needs.

  • Thanks for the answer. What I don't understand in these examples is where the column with the linestring geometries is defined. For example in the sql query "SELECT id, source, target, cost [,reverse_cost] FROM edge_table" there is not a column refering to the linestring geometry.
    – user1919
    Jan 8, 2015 at 8:01
  • Correct. The "core" functions only return the path result as a list of edges. But you can join your result with the original table. Then you have access to all the attributes again, also the line geometry: workshop.pgrouting.org/chapters/…
    – dkastl
    Jan 9, 2015 at 0:21
  • 1
    Maybe better for understanding: the core functions only use the required attributes, because every network data table may have different attributes. And line geometry is actually not necessary for a Dijkstra shortest path.
    – dkastl
    Jan 9, 2015 at 0:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.