5

I have 2 layers of polygons in QGIS which are layer1 and layer2. they both have column id.

The goal is to select items in layer1 that not appear in layer2 using unique field id.

For example:

layer 1 has id: 1, 2, 3, 4, 5

layer 2 has id: 1, 2, 3, 4

by using expressions like

id not in layer2

How can I write the expression to have item 5 in layer 1 selected?

eth4io
  • 376
  • 1
  • 8
  • You could try attribute joining by id then select the ones that have no match https://www.qgistutorials.com/en/docs/performing_table_joins.html – Michael Stimson Jan 09 '20 at 06:24
  • Look at the aggregate and array functions. For example array_agg("id") should give a list of all id's in the current layer. To check if the current feature's id is in the list of id's in another layer it would be something like: array_contains( aggregate('layer2','array',"id"), "id"). I haven't tested this expression so it may have errors. – csk Jan 09 '20 at 17:15

2 Answers2

6

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

Snaileater
  • 5,743
  • 1
  • 15
  • 26
  • 2
    Let me add: the NOT IN construct will return an empty set when either side of the comparison contains a single NULL value (docs)! Either add WHERE ID IS NOT NULL in the sub-select, or use the (more performant) WHERE NOT EXISTS construct. – geozelot Jan 09 '20 at 11:18
5

You could also filter by expression using the Advanced Filter in the Attribute Table (of layer1):

get_feature_by_id('layer2',$id) IS NULL

This will filter out the features of layer1 whose $id is not found in layer2.

Note: $id refers to the current feature's id, as assigned automatically within QGIS. As noted by @Mike D, you may want to use another attribute as identifier, in which case you need to put double-quotes around its name (single-quotes point to an attribute, double-quotes point to its value). For the example below, we could have written the filter expression as follows

get_feature_by_id('layer2',"CODE_OBJ") IS NULL

Advanced Filter

onietosi
  • 559
  • 6
  • 7
  • 1
    I liked and used this method. For me it was get_feature_by_id( 'layer2',"OBJECTID") IS NULL – Mike D Apr 06 '20 at 20:49
  • Indeed, you can use any other object identifier and in that case it should be written with double-quotes around its name. I have added this as an edit to my answer. Thanks! – onietosi Apr 07 '20 at 08:22