2

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).

Improve this question
4
  • Cant you join and select features where there is a match? Or construct the query using python
    – BERA
    Sep 7, 2020 at 13:08
  • I think a join would take a lot of time, since there are said 3 million entries. But sure, I could and didn't think of it. My python skills are very rudimentary @BERA
    – Erik
    Sep 7, 2020 at 13:10
  • How does that list look like?
    – Taras
    Sep 7, 2020 at 13:17
  • @Taras basically it's the same as the attribute table of the parcels, just without the geometry.
    – Erik
    Sep 7, 2020 at 13:21

1 Answer 1

Reset to default
2

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

Improve this answer
1
  • 1
    Works like a charm, and is faster than the "join by attribute" method.
    – Erik
    Sep 8, 2020 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.