Calculating Shortest Path in bulk

[mingteikg]

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 and 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.

I was told that I can run:

Код: Выделить всё

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

1. According to the documentation (http://workshop.pgrouting.org/chapters/topology.html) , the <road table> requires a Length/Cost field, but I do not have a Length/Cost field in my Roads table.

I can use the Shortest_path module in QGIS and it works fine - But I will need to calculate in bulk and save it back into a DB.

2. <source id> and <target id> according to pgRouting it needs to accept an integer. My source table and destination table that has the Points is as following:

- Name
- Geometry

I would like QGIS to calculate:
1st row of source to 1st row of destination - save in new DB with Source and Destinatio and Cost
1st row of source to 2nd row of destination - Save the result with Source and Destination and Cost
1st row of source to ...
1st row of source to last row of desnation - save in DB
2nd row of source to 1st row of destination - save in DB


Any idea?