Selecting elements by grouping with one attribute and specific rule using QGIS

Question

I have a question about a specific query for a Shapefile-Layer in QGIS 3.4.

I have a Point-Shapefile with the name point.shp. The Shapefile has a lot of different points with a large amount of attributes. But two attributes are interesting for us. The first attribute has the name 'Zone', and the second is called 'Direction'. Both attributes have the data type string.

This picture visualize the structure of the Shape:

Now, we have a rule: in every zone, the direction number must be unique. In the example, all points in zone 9 an 22 are legit. But we have a problem in zone 10. Direction 1 occurs twice. Because of that, all points in zone 10 are invalid.

Can I select all the points, where one zone has multiple directions with identical numbers?

Maybe, when I selected the points with the incorrect rule, I can reverse the selection to export the valid values.

Answer 1

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 zone, direction, count (*) as total from my_table
group by zone
having total>1

The result will show all zones having more than one direction (= those not respecting the rule)

To better answer your question you can try a double grouping like the following :

select zone, direction, count (*) as total from my_table
group by zone, direction
having total>1

Then if you want to automatically select or delete the cases like the double "zone 10 - direction 1" you can use the result of this second query, join it to your original data (using zone AND direction) and choose to keep only the first of each joined records (if that's the rule you want to apply ...)