Attribute Queries and Selection: Finding the Features You Need

What is an attribute table?

A vector layer has two parts. The geometry says where a feature is. The attribute table says what it is. Each row is one feature, such as a park or a hospital, and each column is a field, such as name, category or visitors_per_day.

A field has a data type. Text fields hold names and categories. Integer and decimal fields hold counts and measurements. Date fields hold dates. The type decides which tests make sense: you can ask whether a number is greater than 5,000, but not whether a name is.

What is a query?

A query is a question written in a form the software can test against every row. The answer for each feature is true or false, and the features with a true answer are the result.

"category" = 'park' AND "visitors_per_day" > 5000

This reads: the category is park, and the visitor count is above 5,000. Field names go in double quotes, text values in single quotes, and numbers have no quotes.

Selecting and filtering

Both actions use a query, and they do different things.

A filter is easy to forget. If you filter a layer to lakes and count its features a week later, the count is wrong and nothing warns you. Look for the filter icon beside the layer name before you trust a number.

Building a query

Comparisons

The usual operators are equal to, not equal to, greater than, less than, and the two combined with equal to. Text can also be matched with LIKE, where % stands for any run of characters. "name" LIKE 'Cubbon%' finds every name that starts with Cubbon.

Combining conditions

AND needs both conditions to be true. OR needs at least one. Brackets set the order, so use them whenever you mix the two.

("category" = 'park' OR "category" = 'lake') AND "visitors_per_day" > 3000

Lists

When you would write many OR conditions on one field, use IN:

"category" IN ('park', 'lake', 'heritage')

Missing values

An empty field holds a value called NULL. It is neither equal to anything nor different from anything, so "name" = NULL is never true, even for an empty name. Write "name" IS NULL to find empty values and "name" IS NOT NULL to exclude them.

This also affects other tests. A query such as "visitors_per_day" <> 5000 leaves out every row where the field is empty, which can surprise you.

Attribute queries and spatial queries

An attribute query asks about the values in the table. A spatial query asks about where features are, such as parks within 500 metres of a lake or hospitals inside a ward. Both give a true or false answer for each feature, and they can be combined. A common workflow is to select by attribute first to narrow the layer, then run a spatial test on what is left.

Example: parks that draw crowds

Take a layer of ten places in Bengaluru, with a joined table that gives each place a category and an estimated number of visitors per day. The question is which parks have more than 5,000 visitors a day.

1. Select by expression with "category" = 'park' AND "visitors_per_day" > 5000.
2. Check how many features are highlighted and look at them on the map.
3. Open the statistics panel with "selected features only" ticked to read the mean and the sum.
4. Export the selection to a new layer if you want to keep it.

Step 2 is the one people skip. A query that returns zero features, or all of them, usually has a typing error, such as a capital letter in a text value or a number stored as text.

Common mistakes

• Treating a selection as a filter and expecting tools to ignore the unselected rows.
• Leaving a filter on a layer and trusting counts and statistics afterwards.
• Comparing a text field with a number, or the reverse.
• Writing = NULL where IS NULL is needed.
• Mixing AND and OR without brackets.
• Forgetting that text matching can be case sensitive in some data sources.

Glossary

Practise it

The tutorial Attribute queries and selection in QGIS covers Select by Expression, Select by Value, the Query Builder and attribute table filters on real data.

Test yourself

Quick quiz

Key takeaways