Reformatting multiple Nearest Neighbor Join results into columns using QGIS

Question

When using nearest neighbor joins in QGIS, a geometry is generated for each matched pair.

I am joining Layer1 geometries with up to 8 features from Layer2 using the Processing Toolbox in QGIS.

How can I reformat the resulting attribute table to have the joined feature IDs in columns, in order to have only one entry per Layer1 feature?

Answer 1

You can do the following:

You have run the "Join Attributes by nearest" on layer 1 & layer2 features. Maximum nearest neighbors: 8, in my cases I added a max distance as well.

This is where you get a new layer nnjoin which attribute table has layer1 feature ids, layer2 feature ids, n and distance, ...

What you can do to turn this in a layer with one entry for each layer1 feature, and 8 columns with the layer2 nearest feature "id"s, is to use some SQL in a Virtual layer through Layer > Add Layer > Add/Edit Virtual Layer.

Add a virtual layer with the following query:

select nnj.geometry, nnj.osm_id,
        max(nnj.osm_id2_1) as osm_id2_1, max(nnj.osm_id2_2) as osm_id2_2,
        max(nnj.osm_id2_3) as osm_id2_3, max(nnj.osm_id2_4) as osm_id2_4,
        max(nnj.osm_id2_5) as osm_id2_5, max(nnj.osm_id2_6) as osm_id2_6,
        max(nnj.osm_id2_7) as osm_id2_7, max(nnj.osm_id2_8) as osm_id2_8

from (select geometry,osm_id,
        case when n = 1 then osm_id2 end as osm_id2_1,
        case when n = 2 then osm_id2 end as osm_id2_2,
        case when n = 3 then osm_id2 end as osm_id2_3,
        case when n = 4 then osm_id2 end as osm_id2_4,
        case when n = 5 then osm_id2 end as osm_id2_5,
        case when n = 6 then osm_id2 end as osm_id2_6,
        case when n = 7 then osm_id2 end as osm_id2_7,
        case when n = 8 then osm_id2 end as osm_id2_8
from nnjoin) as nnj

group by nnj.osm_id

Which provides you with the following layer and attribute table:

Answer 2

I have written an algorithm for this purpose: Translate Duplicate Features to Columns. It is part of the v1.5 update of ProcessX PlugIn.

You can choose a field/expression and/or geometry as identifier. In your case the field probably is osm_id or use the geometry. As output structure choose Create new field for each duplicate feature field and as Fields to translate and duplicate you may want to choose osm_id_2, code_2, fclass_2 and name_2.

Just a warning: if you have too many duplicates, the result may exceed the maximum number of fields QGIS can handle. In that case QGIS can crash as soon as you open the attribute table of the result. I am not aware of a fixed limit, so just be careful if its more than 250. The algorithm will give a warning in that case. However, in tests I was also able to open results with more than 1000 fields without an issue.