0

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

enter image description here

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;

enter image description here

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

enter image description here

2

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;
| improve this answer | |
  • Thanks a lots jGH but looks like the query is not returning all linestrings! can you please take a look at the posted image (I just up loaded). As you can see in the image the query always missing some segments – Mona Coder Jul 10 '18 at 19:46
  • looks like at least two segments are missing at Herald St intersecrion – Mona Coder Jul 10 '18 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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