QGIS Select – Select Values That Are the Same in Another Column in Another Layer

qgisselectselect-by-attribute

I'm working n QGIS 3.10.7-A Coruña. I have two layers: point.shp (670 features) and line.shp (4500 features). Both of them have a column called "code" with string values. I want to select from line.shp the 670 features that are the same in column "code" for both layers (so they exist in point.shp)
I need to select by expression as far as I understand. How can I reference a column in a different layer? What should be the expression?

Best Answer

The aggregate() function in QGIS is both ridiculously powerful and also difficult to understand, but it's exactly what you need.

Conceptually this is what we're going to do:

First you need to create a list (an array in this case) of all the "code" values that appear in point.shp
Then you're going to have the Select By Expression tool check if that array contains the "code" value for each feature in line.shp

So select line.shp and open Select By Expression, and use the following expression:

array_contains(aggregate('point', 'array_agg', "code"), "code")

That should return what you're looking for.

If you want to understand that more then first just add this piece in the middle:

aggregate('point', 'array_agg', "code")

You will see the Preview at the bottom of the dialogue returns the array of the code values. It really helps if you find the aggregate function in the list of functions and then write the expression out manually while looking at the documentation on the right:

That will help you to understand what each part of the expression is doing. Then you can do the same for the array_contains part of the expression.

Good luck!

Related Solutions

arcpy – Troubleshooting Column Not Exist Error in Select Layer By Attribute Tool

Tested this on a shapefile with an integer field called GRIDCODE.

Ensure this is the actual field name in your shapefile.

Shapefiles use double quotes for field names

Use:

query = '"{0}" = {1}'.format(field_name, value)

You should be able to remove the create new field steps and just query GRIDCODE Working script below

def calc(fc):
arcpy.env.overwriteOutput = True
fcfields = arcpy.ListFields(fc)
field_name = 'gridvalue'
# Your code was trying to add a field each time the field name did not match...
#This is better
names = [fcfield.name.lower() for fcfield in fcfields]
if field_name in names:
    print "Field gridvalue already exists."
else:
    arcpy.AddField_management(fc, 'gridvalue', 'SHORT')

arcpy.CalculateField_management(fc, field_name, "!GRIDCODE!", 'PYTHON')
arcpy.MakeFeatureLayer_management(fc, "splyr")
print "Layer created for " + fc  
query = '"{0}" = {1}'.format(field_name, 1)

arcpy.SelectLayerByAttribute_management("splyr", selection_type='NEW_SELECTION', where_clause=query)

QGIS – How to Select Entries Not in Another Layer

You can achieve that using an SQL Query in the DB Manager of QGIS.

You choose Database / Database Manager / Database Manager then Virtual Layers / Qgis Layers you can then try queries like the following :

select ID from layer1 where ID not in (select ID from layer2)

Depending on the size of your tables it can be a bit time consuming ...

Best Answer

Related Solutions

arcpy – Troubleshooting Column Not Exist Error in Select Layer By Attribute Tool

QGIS – How to Select Entries Not in Another Layer

Related Question