TransWikia.com

Select by expression based on common ID

Geographic Information Systems Asked on April 13, 2021

Background

I need to select features (parcels) from an official source based on a list with no geometries provided by a client. Both data sets have a common, unique ID (parcel number). There are about 1,000 elements in the list provided by the client, and 3.15 million features in the official data set, saved as a geopackage.

Idea

Since my SQL is too rusty, I choose to use the IN-operator of the field calculator to select all features in the official data which are on the list provided by the client and then export the selection to a new file.

Expression

"FID" IN (aggregate('List-layer','concatenate_unique',"FID2",concatenator:=','))

Problem

The expression doesn’t throw an error, but also doesn’t select anything, since the concatenate_unique doesn’t put ' around the single entries in the list it creates, hence IN treats the list of unique IDs like one single ID.

Question

How can I use the common ID to select features in the official data? A solution using SQL/virtual layers would be feasible, too.

Example data

Example parcels (using EPSG25833):

wkt_geom;FID
Polygon ((398804.51099999999860302 5802561.93099999986588955, 398821.91100000002188608 5802514.11299999989569187, 398974.50099999998928979 5802569.6380000002682209, 398957.10100000002421439 5802617.45500000007450581, 398804.51099999999860302 5802561.93099999986588955));12290700300377______          
Polygon ((398778.91999999998370185 5802632.25899999961256981, 398798.36300000001210719 5802578.82799999974668026, 398798.6909999999916181 5802578.9469999996945262, 398804.92399999999906868 5802581.21499999985098839, 398805.6190000000060536 5802581.46800000034272671, 398902.97600000002421439 5802616.89499999955296516, 398926.28899999998975545 5802625.37799999956041574, 398932.89100000000325963 5802627.78000000026077032, 398949.26500000001396984 5802633.73900000005960464, 398950.46999999997206032 5802634.17700000014156103, 398950.9529999999795109 5802634.35199999995529652, 398931.51000000000931323 5802687.78399999998509884, 398931.02799999999115244 5802687.60800000000745058, 398854.35800000000745058 5802659.70899999979883432, 398779.24800000002142042 5802632.37799999956041574, 398778.91999999998370185 5802632.25899999961256981));12290700300378______

The entry from the list then has the same FID, though the column is named differently (e.g. FID2).

One Answer

You can build the query using pyqgis. List the ids from the table using getFeatures, convert to a tuple (from [1,2,3] to (1,2,3)) and use the query in Select by expression:

listlyr = QgsProject.instance().mapLayersByName('sample')[0]
list_field = 'rutanum'
parcellyr = QgsProject.instance().mapLayersByName('tk_rutnat')[0]
parcel_field = 'rutanum'

parcels_to_select = tuple([f[list_field] for f in listlyr.getFeatures()])
exp = """"{0}" IN{1}""".format(parcel_field, parcels_to_select)
print(exp)

processing.run("qgis:selectbyexpression", {'INPUT':parcellyr,'EXPRESSION':exp,'METHOD':0})

enter image description here

Correct answer by BERA on April 13, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP