Creating many origin-destination routes with pgRouting

Question

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?

Answer 1

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;

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