Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm using a PostGIS / pgRouting database setup with a road network. I'm trying to create an origin-destination table with the complete route information, so not just the sum of the length, like in this post.

I'm trying to get something like this as output table:

Origin | Destination | vertex_id | cost
1        2             415         14.2
1        2             508         4.3
1        3             919         2.4
1        3             1024        6.8

The following code for calculating a single route works fine, but how do I automate this for a lot of origin-destination pairs?

SELECT * FROM shortest_path('
SELECT gid AS id,
start_id::int4 AS source,
end_id::int4 AS target,
length::float8 AS cost
FROM network',
1, -- start_id
2, -- end_id
false, false);

I've tried something with subqueries, like this where I'm getting the start_ids from table nodes:

SELECT id,
(SELECT * FROM shortest_path('
SELECT gid AS id,
start_id::int4 AS source,
end_id::int4 AS target,
length::float8 AS cost
FROM network',
id::int4, -- start_id
450969, -- end_id
false, false))
FROM nodes;

But this doesn't work because the subquery returns multiple rows (as expected): ERROR: subquery must return only one column. Any help?

share|improve this question
    
I found a solution to my question in this thread: gis.stackexchange.com/questions/10536/… –  Otto Coster Nov 12 '12 at 13:18

2 Answers 2

If you want one row for each shortest path query you could for example use ST_Union(the_geom), similar to sum(cost) as you mentioned.

You may want to use a wrapper function (or write your own modified wrapper) that returns the geometries and not the nodes. You can take a look at the workshop for examples.

share|improve this answer
    
Actually I do want the all the rows for each query, with all the node information. I'd like to make intersections of these paths with other data. –  Otto Coster Nov 8 '12 at 8:09
    
technically, if you write a wrapper function that gives you back the entire route line, you can then intersect that line with other data very easily. –  EconGeo Jun 24 at 7:34
up vote 0 down vote accepted

For completeness, this is the method I used:

  • Creating the routes table from origin 'id' to a single destination:
CREATE TABLE routes AS
SELECT gid, id,
shortest_path('
SELECT gid AS id,
start_id::int4 AS source,
end_id::int4 AS target,
length::float8 As cost,
* FROM network',
id::int4, -- start_id
935560, -- end_id
false, false) AS segment
FROM nodes;

I'm using a single destination (end_id) here, because of the time needed for calculation. This query takes a couple of minutes for a few hundred start_ids. The result is a table with the route information in field 'segment', in the format (vertex_id, edge_id, cost).

  • Then I split the segment string into the parts and joined this with my links table using the edge_id:
CREATE TABLE merge_routes_links AS
SELECT * FROM links
JOIN (
SELECT btrim(split_part(segment::varchar, ',', 1), '()') AS vertex_id
btrim(split_part(segment::varchar, ',', 2), '()') AS edge_id
btrim(split_part(segment::varchar, ',', 3), '()') AS cost
FROM routes) AS route
ON route.edge_id::int4 = links.gid::int4;
  • Finally I calculated the total length of the routes from origin id to destination and joined this to the merge_routes_links table.
SELECT id,
sum(length::float8) AS length_route
FROM merge_routes_links
GROUP BY id;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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