pgrouting shortest_path return longest_path

Question

I've got troubles with shortest path function from pgrouting. I try to calculate the shortest path between an address and a bus stop.

I first found the nearest road points for the address and the bus stop with theses requests:

SELECT id(foo.x) as id, name(foo.x) as type FROM ( SELECT find_node_by_nearest_link_within_distance( 'POINT(521671 147119)', 50, 'route')::link_point as x ) AS foo LIMIT 1
SELECT id(foo.x) as id, name(foo.x) as type FROM ( SELECT find_node_by_nearest_link_within_distance( 'POINT(519555 150354)', 50, 'route')::link_point as x ) AS foo LIMIT 1

That works great and gives me the right start and end points. But then comes pgrouting request:

SELECT st_transform(st_union(the_geom), 21781) as geometry
FROM route
WHERE gid in (
  SELECT edge_id
  FROM shortest_path('
    SELECT gid as id,
    source::integer,
    target::integer,
    cost,
    reverse_cost
    FROM route',
    441718,
    442011,
    false,
    false
  )
);

that gives me a way from start point to end point, but surely not the shortest… see this image

Here are the first rows of my roads (table 'route')

  gid | longueur | source | target |  categorie |   x1   |   y1   |   x2   |   y2   |  the_geom | cost | reverse_cost
------------------------------------------------------------------------------------------------------------------
  1   |    1     | 995012 | 995013 |"strasse_3m"| 512636 | 146599 | 512637 | 146599 | 01050000… |  0   |    0
  2   |    3     | 995014 | 995015 |  "weg_2m"  | 512599 | 146686 | 512596 | 146685 | 01050000… |  0   |    0
  3   |    1     | 995016 | 995017 |  "weg_2m"  | 512391 | 146934 | 512393 | 146934 | 01050000… |  0   |    0
  4   |    25    | 995086 | 995087 |  "weg_2m"  | 512639 | 146277 | 512653 | 146257 | 01050000… |  0   |    0

It doesn't matter the cost, it's why both cost and reverse_cost are equal to 0. I'm just interested by the length ('longueur' here)

I'm searching for hours without finding anything...

Thanks in advance for your help!

Answer 1

If you want to get the shortest route, then the cost is the length of a road segment. You can try this query:

SELECT st_transform(st_union(the_geom), 21781) as geometry
FROM route
WHERE gid in (
  SELECT edge_id
  FROM shortest_path('
    SELECT gid as id,
    source::integer,
    target::integer,
    ST_Length(the_geom) AS cost,
    ST_Length(the_geom) AS reverse_cost
    FROM route',
    441718,
    442011,
    false,
    false
  )
);

Then if your route still isn't the shortest, there might be problems with your network topology.