After a long discussion in the comments I reviewed my answer, taking the sample data and it´s upload into PostgreSQL from within QGIS into account. I deleted my EDIT 2 as it became useless.
The problem is that you initially run
pgr_dijkstra on multilinestrings. Next to the topology (that you created), the basic geometric structure for any pgRouting function to run the way it should is to have a properly noded network, consisting of simple linestrings instead, defined by two nodes (->
pgRouting has the built in pgr_nodeNetwork function to do just that for you (albeit with possible drawbacks, see EDIT 1). In order to get expected results:
- run data import: in the GUI, check the box 'Create single-part geometries instead of multi-part' to create the table yourtablename.
Note: at this point you can also project your data into a CRS suitable for your region of interest, to have all further calculations and their outputs in meter instead of (decimal) degrees. This will affect the tolerance settings I used below.
pgr_nodeNetwork: create a noded network from your initial set of linestrings (Note: the function does not keep any attributes, it will add an old_id column instead to refer to your initial data!). Since your data is in EPSG:4326, use a low tolerance in degrees to treat nearby vertices as equal:
SELECT pgr_nodeNetwork('<yourtablename>', 0.00001, 'id', 'geom');
pgr_createTopology: create the topology reference with the noded network table created above (usually called yourtablename_noded):
SELECT pgr_createTopology('<yourtablename_noded>', 0.00001, 'geom');
pgr_dijkstra: calculate shortest path between nodes on the same noded network table yourtablename_noded.
Note: you have to refer to the created vertice table yourtablename_noded_vertices_pgr for the start and end points of your desired route (e.g. visualize that table in QGIS and pick the source/target vertice ids to route from/to)!
JOIN those with the yourtablename_noded table and the original table to get your route with combined columns:
ST_Length(geom) AS cost
) AS route
JOIN <yourtablename_noded> AS edges
ON route.edge = edges.id
JOIN <yourtablename> AS original
ON original.id = edges.old_id;
As @dkastl pointed out,
pgr_nodeNetwork does try to split the given network at every intersection found, which might be undesirable for many cases, e.g. where two lines visually intersect but thematically should be treated as not intersecting (consider bridges in road networks [thanks to @dkastl]).
The more general rule here should be to make sure the network you want to route on consists of simple linestrings with nodes only at desired intersections, and pgr_nodeNetwork being a tool to achieve that efficiently if no intersection restrictions occur.