2

I have two point layers, one of origin and one of targets.

Every origin point refer to a specific target point.

I would like to compute the cost of the travel between every starting point and his target point, and then join the output table to the origin point layer to show origin points who are "badly affected".

I have tried to use the Qneat3 plugin, but it generates an OD-matrix for every couple of points in the origin and target layer.

I have also tried to use the destination's ID field as unique ID for the origin point, in order to be able to filter my output OD-matrix, but I'm then unable to perform the join between the table and the origin point layer.

1

I am sure this problem can be solved using the output of the QNEAT3 plugin:

SQL Multi-key-join is the magic word you are looking for. To achieve this in QGIS out of the box, you have to take a little detour through virtual layers. Those allow you to use SQL on multiple layers which enables you to perform the more complex join that is necessary to work with the QNEAT3 output. I assume you have the same situation as in this screenshot:

situation You have

  1. a source point layer with their associated target keys (source_layer_with_target_info - top left table)
  2. a target layer with the corresponding target keys (target_layer bottom right table)
  3. a computed M:N OD-Matrix from QNEAT3 (qneat3_od_matrix table in the middle)

The fourth table on the right is the result you want to get through joining.

In order to perform the join, follow these steps:

  1. go to the menu Layer > Add Layer > Add/Edit Virtual Layer...
  2. Import the source_layer_with_target_info layer and the qneat3_od_matrix table
  3. Use the following Query to perform the multi-key-join (change the column names according to the names in your layer) and then add the layer:

    select
       source_layer_with_target_info.source_id,
       source_layer_with_target_info.target_id,
       qneat3_od_matrix.total_cost
    from 
       source_layer_with_target_info
    join
       qneat3_od_matrix
    on 
       source_layer_with_target_info.source_id = qneat3_od_matrix.origin_id 
    and 
       source_layer_with_target_info.target_id = qneat3_od_matrix.destination_id;
    
  4. Add the layer

It should look like this screenshot:

enter image description here

You might want to save all layers to disk first - I ran into some errors when creating the virtual layer from memory layers.

And there you go - the attribute table of the virtual table should now show only the combinations that have been listed in your source layer.

3
  • Seems to be the right way to achieve what I'm looking for but I've got 4000 origin points and 15 destination points. The OD matrix generation already took a while, and I can't get the query ending until now... That's why I was wondering for a way to filter before calling the algorithm. Oct 31 '18 at 17:21
  • 1
    I see the problem here: QNEAT3 performs much faster when you start from a small number of origins to many destinations. Maybe you could reverse the process (provided you don't include one-ways in your analysis) and use the target points as origins. In this case you would also have to switch all from/to identifiers in the SQL query. A little brain work - but it should speed up the process considerably.
    – root676
    Nov 1 '18 at 11:29
  • In case the answer helped you, would you mind accepting it?
    – root676
    Nov 4 '18 at 17:43

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.