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?
Best Answer
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.