3

I have used the distance matrix tool for a large point set (100 000+ points) to find three closest POI's from another layer and their distances. The distance matrix outputs a table much like this:

source   target_id   distance  
1        2          100
1        3          110
1        4          120 
2        1          100
2        3          200
2        4          210

My point layer is now even bigger because each original point has three separate features which only difference is the target_id and the distance from source to target. I would like to have the target_id and the distance in separate fields in one feature per source_id.

source   target_1   distance_1   target_2   distance_2   target_3   distance_3   
1        2          100          3          110          4          120
2        1          100          3          200          4          210
...            

I have tried all the basic tools I can use in QGIS without any luck. Seems like I am trying to do a "many-to-one" join, which can not be made with join-by-attribute. Merging or dissolving the layer to itself doesn't help either. I suspect this could be a simple SQL join operation but I have no idea how I could perform it.

2

If PostgreSQL is an option for you, it can work this way.

Creating the table with dummy data:

DROP TABLE IF EXISTS distance_matrix_output;
CREATE TABLE distance_matrix_output(
   source INTEGER NOT NULL ,
   target INTEGER NOT NULL,
   distance INTEGER NOT NULL
);

INSERT INTO distance_matrix_output(source, target, distance)
VALUES  (1,2,100),
        (1,3,110),
        (1,4,120),
        (2,1,100),
        (2,3,200),
        (2,4,210);

Creating the empty table with the transformed layout described above.

DROP TABLE IF EXISTS distance_matrix_transformed;
CREATE TABLE distance_matrix_transformed(
   source INTEGER NOT NULL,
   target_1 INTEGER NOT NULL,
   distance_1 INTEGER NOT NULL,
   target_2 INTEGER NOT NULL,
   distance_2 INTEGER NOT NULL,
   target_3 INTEGER NOT NULL,
   distance_3 INTEGER NOT NULL
);

What you want to achieve is to first group the values together given the source id which you can achieve using string_agg and GROUP BY 1. Afterwards you use regexp_split_to_array to cast this concatenated string into an array of values. This array then can be used to insert into your expected table.

INSERT INTO distance_matrix_transformed(
    source, 
    target_1,
    distance_1, 
    target_2, 
    distance_2, 
    target_3, 
    distance_3)
SELECT 
    sg.source, 
    sg.target_agg[1]::INTEGER, 
    sg.distance_agg[1]::INTEGER, 
    sg.target_agg[2]::INTEGER, 
    sg.distance_agg[2]::INTEGER, 
    sg.target_agg[3]::INTEGER, 
    sg.distance_agg[3]::INTEGER 
FROM (
    SELECT  
        source, 
        regexp_split_to_array(string_agg(target::TEXT, ', '), ', ') AS target_agg, 
        regexp_split_to_array(string_agg(distance::TEXT, ', '), ', ') AS distance_agg
    FROM distance_matrix_output
    GROUP BY 1
) AS sg;

Which will give you (SELECT * FROM distance_matrix_transformed) :

source  | target_1 | distance_1 | target_2 | distance_2 | target_3 | distance_3
--------+----------+------------+----------+------------+----------+------------
      2 |        1 |        100 |        3 |        200 |        4 |        210
      1 |        2 |        100 |        3 |        110 |        4 |        120
2
  • 1
    This seemed to work exactly as intended.
    – Tom
    Jan 15 at 12:28
  • 1
    Glad to hear @Tom - please accept the answer if you can. Jan 15 at 12:34

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.