Issue on Getting Geometry of pgRouting Query in PostGIS

Question

I am running this query against a Table victoria enabled with pgRouting and PostGIS

SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('
                SELECT gid AS id,
                source::integer,
                target::integer,
                length::double precision AS cost
                FROM victoria',
9411, 3986, false, false);

which in result return 269 rows in a table like

I also need to get geometry multi string of nodes so I tried to achieve them like

SELECT route.*, ST_AsText(geom) FROM victoria
JOIN(
SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('
                         SELECT gid AS id,
                         source::integer,
                         target::integer,
                         length::double precision AS cost
                         FROM victoria',
                         9411, 3986, false, false)) AS route
ON              
victoria.gid = route.seq;

this returns 268! rows plus the exactly same result of geom for all queries no matter if I change the 9411, 3986 any other number the geometry is always same. can you please let me know how I can get the correct geometry

Update

Answer 1

the returned seq is just a sequence ranging from 1 to the number of nodes in the route. It is NOT related to the source data and should not be used in a join. It is however very useful for sorting the route segments.

So, if you want the original segments, you would do the join on the edge ID

SELECT route.*, ST_AsText(victoria.geom) 
FROM victoria
JOIN(
SELECT seq, node, edge, cost FROM pgr_dijkstra('
                         SELECT gid AS id,
                         source::integer,
                         target::integer,
                         length::double precision AS cost
                         FROM victoria',
                         9411, 3986, false, false)) AS route
ON              
victoria.gid = route.edge;