2

First of all, I am new to map making, and I am mostly relying on tutorials to learn. As part of a project, I am making an airway map using QGIS and I do it by uploading the points with lat/long thru a csv file, and then connect them thru Points2One plugin. After that, I used pgrouting to create topology and calculate shortest path with dijkstra. However, when input the start and end points for calculation, the pgr_dijkstra won't return anything unless I used the "source" and "target" points. Below is the route network:

If I use point 5 & 6, it will yield result since in the table, there are the source and target. but if I use 29 and 23, it won't give result.

Is pgr_djikstra supposed to work like that? Anything that I am doing wrong?

enter image description here

enter image description here

yields one result

no result

2

EDIT 3:
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 (-> source, target).

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.
  • run 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');
  • run 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');
  • run 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:


SELECT route.*,
       original.*
FROM pgr_dijkstra(
        'SELECT id,
                old_id,
                source,
                target,
                ST_Length(geom) AS cost
         FROM <yourtablename_noded>',
         29, 96,
         FALSE
) AS route
JOIN <yourtablename_noded> AS edges
  ON route.edge = edges.id
JOIN <yourtablename> AS original
  ON original.id = edges.old_id;



EDIT 1:

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.

  • 2
    Be careful with recommending pgr_nodeNetwork, because this will create nodes at intersections, which may be wrong (ie. bridges)! Second, pgRouting will automatically only care about the first linestring in a multilinestring, if that geometry type is used. So this shouldn't be the problem, but you're correct, that having linestring geometry type is the only one, that makes sense. Running pgr_createTopology makes sense, because it seems that there is something wrong with it. – dkastl Jan 25 '18 at 12:18
  • @dkastl good catch with pgr_nodeNetwork...I will edit that into my answer. however, taking only the first linepart into account should very well be the problem, since then there might simply be no connection/possible path between the routing source and target ([5, 6] might be found along the same multiline since it's defined as it's source/target nodes, [29, 23] however is supposedly across multiple multilines with their (wrongly assigned) nodes not necessary connected) – ThingumaBob Jan 25 '18 at 12:33
  • @ThingumaBob I used the pgr_nodeNetwork but its giving me a FAIL result. I don't know why. So I tried to test intersecting lines containing only 2 points, but it still shows MULTILINESTING in the geom column when I upload. I tried using the pgr_nodeNetwork but still gives a FAIL result. Are there any conditions to fulfill for the database to upload my data as a LINESTRING? – Vnox Jan 25 '18 at 17:35
  • @Vnox ah yeah...damn, there were times when I gave consistent answers in the first place...okay, so you need to use ST_Dump to partition each MULTILINESTRING into it´s LINESTRINGS. I will update my answer with a query to include referrences to the initial MULTILINESTRING... – ThingumaBob Jan 25 '18 at 18:08
  • @ThingumaBob yes I created a topology first. But still gives same result. I tried the code that you gave me (thank you btw), pgr_createTopology runs fine, but the resulting table is blank. Also, pgr_nodeNetwork still gives FAIL. – Vnox Jan 25 '18 at 18:59

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.