[GIS] Joining attributes by field value not working

attribute-joinsqgisunique id

I cannot figure out why this join is failing when using the Join attribute be field value tool in QGIS 3.6.

Please see my image below which highlights the two layers attribute table and the criteria used in the tool:

I then get this error saying all features have failed to join:

Some background

The Autoincrimental layer had the PopID field created using the "Add autoincremental field" tool.
Both PopID fields are integers.
The PopID attribute values for the RF layer do not all have a value of zero. I have just ordered them from smallest to largest.

Please see below for the datasets

Dropbox link for test data

Best Answer

Problem is PopID fields are not of the same type.

You can see in layer properties that PopID in Autoincrement layer is "real" and in RF "integer".

Algorithm sees "integer" 1 differently than "real" 1 or "string" 1. So, You should convert one of the ID columns. You can do it in field calculator:

This will create new field that you should use to join.

2. Polygon layer, create the areas

Turn toggl editing on Open attribute table Select Field calculator Create new field, give name: Area Output Field type: Decimal number (real) Output Field Length: 2, Precision 2 I think if you set the precision to 2 it will correctly round the area to the length of the precision Expression: $area

There are errors this way of producing areas

You get the following in the attribute table

id ¦ area¦
0 ¦ 1.19 ¦
1 ¦ 3.74 ¦
2 ¦ 4.52 ¦
3 ¦ 2.81 ¦
ect...

Save the layer

2.b You can add the labels to the polygon layer which can be quicker if you have many to add.

3. Point layer

You need a point in the middle of each polygon. Take care with very small/close together polygons.

You can add the labels as you add the points or you can use Join by location function to add labels and areas from the polygon layer.

Run Vector -> Data Management Tools -> Join by Location Input vector layer: Points layer Intersect layer: Polygon layer

Output, press browse, go to where you want to save the shp file, give a file name like: Labels Tick Add result to canvas Press OK

Open the attribute table on the new layer: Labels, and enable toggle editing

Attribute table be like this:

id ¦ Name ¦ id ¦ area ¦
5 ¦ Label 1 ¦ 0 ¦ 1.19 ¦
6 ¦ Label 2 ¦ 1 ¦ 3.74 ¦
7 ¦ Label 3 ¦ 2 ¦ 4.52 ¦
8 ¦ Label 4 ¦ 3 ¦ 2.81 ¦
ect...

You can now combine and "wrap" + new line the columns together using functions in the field calculator

Open Field Calculator Tick Create a new field Output Field Name: Labels Output field type: Text (string) Output field length: 80

Expression:

"Name" ||'/n' || "area" || ' ha'

Name = attribute field with all the labels in it Area = attribute field with all the areas

(You can use another wrap symbol instead of /n)

Then press OK

This creates

Attribute table be like this:

id ¦ Name ¦ id2 ¦ area ¦ Labels ¦
5 ¦ Label 1 ¦ 0 ¦ 1.19 ¦ Label 1 ¦
¦ 1.19 ha ¦
6 ¦ Label 2 ¦ 1 ¦ 3.74 ¦ Label 2 ¦
¦ ¦ ¦ ¦ 3.74 ha ¦
7 ¦ Label 3 ¦ 2 ¦ 4.52 ¦ Label 3 ¦
¦ ¦ ¦ ¦ 4.52 ha ¦
8 ¦ Label 4 ¦ 3 ¦ 2.81 ¦ Label 4 ¦
¦ ¦ ¦ ¦ 2.81 ha ¦
ect... (Can't seem to indent)

Press save as

Double click on the layer, or right click and go to properties, Labels Select the drop down: show labels Then select the new field called: Labels Press Ok

The label + area should appear on the map

Then go to properties, Fields, select the id, Name, Id2 and area columns and delete these Then save the layer

How to add labels to the polygon layer first

Create a new attribute, make that a string (text) and you can select each polygon in the attribute table, this makes the polygon go yellow (default colour) you then add the name to relevant column in the attribute table

You can export and possible reimport the data using MMQGIS

You could combine and adjust the attribute table in excel then join and reimport but field calculator is easier.

QGIS – Joining Attributes from Points to Lines Using Spatial Join

I have tried to set up the example above. I hope I correctly noticed that your point layer consists of points located very close to each other. Looks like the output after using the 'Create points along lines' tool.

I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer....

Let's assume we have the following layers 'Layer_Points' and 'Layer_Lines', see image below. The data that has to be transferred from the points placed in the field "Data".

With the following query, it is possible to join points' attributes to lines appropriated through the beginning and the end of the line layer.

WITH start_points AS (
    SELECT
        l.id,
        p."data"
    FROM
        "Layer_Lines" AS l,
        "Layer_Points" AS p
    GROUP BY
        l.id
    ORDER BY
        MIN(ST_Length(ST_ShortestLine(start_point(l.geometry), p.geometry))) DESC
    ),

end_points AS (
    SELECT
        l.id,
        p."data"
    FROM
        "Layer_Lines" AS l,
        "Layer_Points" AS p
    GROUP BY
        l.id
    ORDER BY
        MIN(ST_Length(ST_ShortestLine(end_point(l.geometry), p.geometry))) DESC
    )

SELECT
    l.*,
    sp."data" AS "start_data",
    ep."data" AS "end_data"
FROM
    "Layer_Lines" AS l
JOIN start_points AS sp
    ON sp.id = l.id
JOIN end_points AS ep
    ON ep.id = l.id

The output Virtual Layer with its Attribute table will look as following

ST_ShortestLine()is the shortest line between two geometries. ST_StartPoint() defines the lines' starting points for connecting with points layer, see image below.

The same principle was applied for ST_EndPoint()

Best Answer

Related Solutions

[GIS] How to combine/merge attribute data from 1 layer to another, polygon to point, merge attributes and wrap/carriage return attributes

2. Polygon layer, create the areas

3. Point layer

How to add labels to the polygon layer first

You can export and possible reimport the data using MMQGIS

QGIS – Joining Attributes from Points to Lines Using Spatial Join

Related Question