Calculating from a location to another location (calculation must be on a line/road path)

Question

I'm using QGIS. I'm new.

I have a CSV file, with the source point (the_geom) and destination point (the_geom).

I have a road network already imported into PostGIS with pgRouting, and I need to have the ability to calculate the distance from a source to a destination, by using the road path as the road. After calculating, it should save the distance back into the DB in a new column.

Is there any quick and easy way to solve this?

Thanks for all the help! :)

Answer 1

I don't know how to solve your task with QGIS. But you could just import your CSV file into PostgreSQL and use SQL queries like this one:

INSERT INTO <distance table> 
    SELECT sum(cost) FROM shortest_path(
        'SELECT id,source,target,cost FROM <road table>', 
        <source id>, <target id>, false, false
    );

You need to start with a start/end point in your network, so you need to also find the nearest road link to your start/end location. There is a simple example in the pgRouting workshop but there are also more complicated functions.