A simple project if you are familiar with GIS or Quantum GIS and SQL.
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:
Code: Select all
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 ([url removed, login to view]) , 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:
I would like QGIS to calculate:
1st row of source to 1st row of destination - save in new DB with Source and Destination 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 destination - save in DB
2nd row of source to 1st row of destination - save in DB
I need someone to help me to achieve all the above.