QGIS Expression Lookup Value – Using Expression to Lookup Field Value in Another Layer in QGIS

expressionfield-calculatorqgis

I am mapping out the electrical grid infrastructure of my country. I have a Substations layer (with fid and name fields) and a Existing_Lines layer (with fid, substation_start, substation_end and label layers.

substation_start and substation_end represent the nodes to which the power line connects, so the example in the attached image shows the power line starting at "Botesland" substation and ending at "Welterede" substation. Both fields are related to the Substations layer as shown below (rows 11 and 12).

I want to include the field label as a label to display on the map next to the power lines showing the route of the power line. The label of the example should display "Botesland – Weltevrede" but when I use the expression concat("substation_start", " - ", "substation_end") in the calculator for the label field in the Existing_Lines layer, QGIS returns the string 1 - 2 as a value in the field.

I understand why it's doing this – the fields substation_start and substation_end contain integers with the fid value of the features it references in the Substations layer…

Therefore, I need an expression that will "Lookup" the value in the name field for the fid of the feature on the other layer…

Can anyone please help me find the right expression?

Best Answer

You can get a feature from another layer in an expression, and then query a particular attribute of that feature.

The feature is retrieved with the get_feature function like so:

get_feature( layer,  field,  field_value_to_match)

The attribute of interest is queried by passing the feature to the attribute function.

attribute(get_feature( layer,  field,  field_value_to_match), attribute_of_interest)

The following expression is used on the Existing lines layer.

concat(
    attribute(get_feature('substations', 'fid', "substation_start"), 'name'),
    '-',
    attribute(get_feature('substations', 'fid', "substation_end"), 'name')
)

Here I get the feature from substations where its fid is equal to the value in the substation_start field of the Existing_lines layer. Then I get the attribute value of the name field from that feature. Likewise with substation_end, and then concat them, with which you are already familiar.

Be careful with single and double quotes. "substation_start" in double quotes signifies the attribute value in the current layer (Existing lines). The single quotes around the other field names are required for the functions to work. You are passing the field name as an argument, rather than the attribute value.

Related Solutions

[GIS] How to label only the odd or even integers of a field using python in the label expression in arcmap

Label expressions are similar to field calculator: you don't need to use a for loop as the expression is already applied to every feature. Also, you'll get an error with your code as per the help:

Field values are automatically cast to text strings. Therefore, if you wish to use a numeric value in an arithmetic operation, or when making a comparison, you will need to cast it back to a numeric data type. The examples below add two integer fields

Here it is displaying odd values:

def FindLabel([Frame_Number]):
  return [Frame_Number] if int([Frame_Number]) % 2 else "whatever you want here"

Alternatively, you can use a SQL expression in Label Manager to accomplish the same:

MOD(Frame_Number, 2) = 1

QGIS – Creating ‘fid’ Column Using Field Calculator

Hacky solution using the field calculator:

array_find(
  array_agg(
    expression := to_int(substr("Row name", 5))*10*maximum("Col")+"Col",
    order_by   := to_int(substr("Row name", 5))*10*maximum("Col")+"Col"
  ),
  to_int(substr(attributes()['Row name'], 5))*10*maximum(attributes()['Col'])+attributes()['Col']
) + 1

Probably the most sane solution:

drop column fid
within the DB Manager navigate to Virtual Layers | Project layers

run

SELECT ROW_NUMBER() OVER(ORDER BY CAST(SUBSTR("Row name", 5) AS INT), CAST("Col" AS INT)) AS fid, *
FROM   <your_layer_name>
;

create new Virtual Layer from the result
Save as... your preferred file type

Best Answer

Related Solutions

[GIS] How to label only the odd or even integers of a field using python in the label expression in arcmap

QGIS – Creating ‘fid’ Column Using Field Calculator

Related Question