QGIS Join issue on tables with generated unique value index field

Question

I'm trying to make a simple join in QGIS 3.10 between a table with a generated ID (using "Add unique value index field") named POINTID and a table created by the Distance Matrix algorithm with an InputID field (whose values come from the POINTID).

But this simply doesn't work as I get an error message during the processing saying that all my features could not be matched…

First, it looked like a duplicate of Joining attributes by field value not working but I double checked the fields type and it is the same: int/integer(0,0) as you can see in both the layers properties and in the logs.

I also checked that IDs are common to both tables so that the join can be done.

Strange thing but maybe irrelevant: the length of the generated field is 0. But the documentation of this function doesn't say anything about setting a field length (see enter link description here). The way the values are sorted in the table from Distance matrix is strange too (1,10,11…,2,20,21,…).

Also, it looks like the join works well on fields of type string, but I didn't find a way to generate unique ID fields of type string (I'm using the graphical modeler).

This gets me confused, any ideas what I'm doing wrong ?

[EDIT] : here is some simple csv data to check the process (SRID is 2154)

Table A

STR_ID;X;Y;Z
P_0;311794;6644698;-0.885
P_1;311803;6644708;-0.689
P_2;311813;6644718;-0.516
P_3;311823;6644728;-0.337

From table A, I generate a POINTID field using X column as class for algorithm "Add unique value index field".

Table B (one point from which to compute distance)

STR_ID;X;Y;Z
P_REF;312556;6646432;-0.885

Table which is the result of Distance Matrix (distance values may be wrong but it doesn't matter):

InputID;TargetID;Distance
0;0;392.1
1;0;378.3
2;0;365.8
3;0;358.4

Answer 1

I suppose you did not save the output of the distance matrix: it's a temporary layer, right? Replicating your workflow, I encountered the same problem as you as long as the layer was a temporary layer. If you save it as Geopackage, the problem disappears.

If you have just a temporary layer, the InputID of the output layer of Distance Matrix behaves strange: if you try to sort it by clicking on the column title, the sort order behaves as if it were a string: 1, 10, 11, [...], 2, 20, 21, [...], 3, 30, 31, [...], even though the field type is shown as integer. That's why the join does not work. I'm not sure if the problem is connected with the fact that Distance Matrix generates a multipoint layer, not a single point layer.

However, the solution is simple: as stated you can save the output (define an output path in the Distance Matrix dialog or make the created temporary layer permanent).

Another option (if you want it to keep only as temporary layer) would be to create a new field join_id using the field calculator with the following expression: to_int(InputID). With this new field, joining works perfectly.

Answer 2

There is even a direct way to create a new field in your layer, without the workaround of using Distance matrix. Let's say you have a layer points with many points and a layer center with just one point. In the layer points, create a new attribute with the distance of each point to the center using field calculator with this expression:

distance (
    $geometry,
    geometry (get_feature_by_id ('center', 1))
)