Writing advanced queries

We discussed earlier that basic SQL expressions in GIS allow us to select features in only one layer. However, QGIS offers us numerous advanced options for querying even between layers. Unlike filtering, the query dialog allows us to use this extended functionality. These operations are functions which require some arguments as input and return values as output. As we can deal with many kinds of return values, let's discuss how queries work in QGIS.

First, we build an expression. QGIS runs the expression on every feature in the queried layer. If the expression returns true for the given feature, it becomes selected or processed. We can test this behavior by opening the query dialog and simply typing TRUE. Every feature gets selected in the layer as when this static value is evaluated, it yields to true for every feature. Following this analogy, if we type FALSE, none of the features get selected. What happens when we get a non-Boolean type as a return value? Well, it depends. If we get 0 or an empty string for a feature, it gets excluded, while if it is evaluated to another number, or a string, it gets selected. If we get an object as a result, that too counts as false.

If we use the advanced functionality of the query builder, we get access to numerous variables besides functions. Some of these variables, starting with $, represent something from the current feature. For example, $geometry represents the geometry of the processed feature, while $area represents the area of the geometry. Others, starting with @, store global values. Under the Variables menu, we can find a lot of these variables. Although they do not show the @ character in the middle panel, they will if we double click on them.

Let's create a query which does the same as our last one. We have to select every feature in our study area from the GeoNames layer which has a population value higher than 0. Under the Geometry menu in the middle panel, we can access a lot of spatial functions. The one we need in our case is intersects. We can see in the help panel that it requires two geometries and it returns true if the two geometries intersect. Accessing the geometries of the point features is easy as we have a variable for that. So far, our query looks like this:

    intersects($geometry, )

Here comes the tricky part. We have to access a single constant geometry from another layer. If we browse through the available functions, we can almost instantly bump into the geometry function, which returns a geometry of a feature:

    intersects($geometry, geometry( ))

As geometry can only process features, the last step is to extract the correct feature from the administrative boundaries layer. Under the Record menu, we can see the most convenient function for this task--get_feature. The function requires three arguments--the name or ID of a layer, the attribute column, and an attribute value. It's just a basic query in a functional form. After passing the required arguments, our query looks similar to the following:

    intersects($geometry, geometry(get_feature('HUN_adm1', 'NAME_1',
      'Baranya')))

Now we have a constant geometry, the geometries of the point features one by one, and a function checking for their intersections. The only thing left to supply is the population part. We can easily join that criterion with a logical operator as follows:

    intersects($geometry, geometry(get_feature(
      'HUN_adm1', 'NAME_1', 'Baranya'))) AND "population" > 0

For a visual example of the query, consider the following screenshot: