Query Layers with Virtual Layer SQL in PyQGIS

A virtual layer is a SQL view over other layers. The layers can be anything QGIS opens — a shapefile, a CSV, a PostGIS table, a WFS — and the query runs in an embedded SQLite engine with SpatiaLite functions, so you can join a spreadsheet to a GeoPackage and filter the result by a buffer in one statement without first copying everything into a database. The result is a live layer: change the underlying data and the view changes with it.

That convenience has a cost, and knowing where it starts is most of the skill. This recipe belongs to Layer Data Sources & Formats in PyQGIS. It builds virtual layers from Python, covers geometry and id declarations, shows the spatial index trick that keeps joins fast, and compares the alternatives.

One query across three providersOn the left, three sources: wards from a GeoPackage, readings from a CSV, and stations from PostGIS. Each is registered as a named table in the virtual layer definition. An embedded SQLite and SpatiaLite engine in the middle runs a SELECT with a JOIN and a spatial predicate. On the right the result is a read-only layer that updates when any source changes.SQL over layers that live in different placeswardsGeoPackage · polygonsreadingsCSV · no geometrystationsPostGIS · pointsSQLite + SpatiaLiteSELECT w.name, avg(r.pm25)JOIN … ON r.station = s.idWHERE ST_Within(s.geom, …)runs when the layer is readvirtual layerread-only viewlive: follows sourceseach source is read through its own provider — nothing is copied up front

Prerequisites

  • QGIS 3.40 LTR or newer, or the QGIS 4 series.
  • The layers you want to query, loaded in the project or reachable by path.
  • Familiarity with basic SQL. The dialect is SQLite, with SpatiaLite's ST_ functions for geometry.

Build a virtual layer from project layers

Layers already in the project can be referenced by name directly in the query — the provider finds them. Building the definition with QgsVirtualLayerDefinition rather than hand-writing the URI keeps quoting and encoding correct.

from qgis.core import Qgis, QgsProject, QgsVectorLayer, QgsVirtualLayerDefinition

sql = """
SELECT w.fid AS ward_id,
       w.ward_code,
       w.ward_name,
       count(s.station_id) AS stations,
       w.geometry
FROM wards AS w
LEFT JOIN stations AS s
       ON ST_Within(s.geometry, w.geometry)
GROUP BY w.fid
"""

definition = QgsVirtualLayerDefinition()
definition.setQuery(sql)
definition.setUid("ward_id")
definition.setGeometryField("geometry")
definition.setGeometryWkbType(Qgis.WkbType.Polygon)
definition.setGeometrySrid(27700)

summary = QgsVectorLayer(definition.toString(), "stations per ward", "virtual")
if not summary.isValid():
    raise RuntimeError(summary.dataProvider().error().message()
                       if summary.dataProvider() else "invalid virtual layer")
QgsProject.instance().addMapLayer(summary)

Breakdown: Table names in the FROM clause are layer names from the project; if a name has spaces, quote it in double quotes. The geometry column of a referenced layer is exposed as geometry whatever the source calls it. Declaring the uid, geometry field, type and SRID is optional — the provider will otherwise inspect the query result to guess — but guessing needs to run the query once just to open the layer, and a grouped or joined query can make that noticeably slow. setUid must name a column with unique integer values — hence the fid AS ward_id alias, since a ward code like E05001234 is text, which is what gives every feature a stable id; without one, feature ids are row numbers and change whenever the result order changes.

On releases before 3.30 the WKB type constant is spelled QgsWkbTypes.Polygon; everything else in the definition is unchanged.

Declare what you can, so nothing is guessedTwo panels. Undeclared: the provider runs the query at open time to discover the geometry column, type and CRS, and feature ids are row positions that change when results reorder, which breaks selections and edits. Declared: setUid gives stable ids and setGeometryField, setGeometryWkbType and setGeometrySrid let the layer open without executing the query.Guessing costs a query and stable idsleft to the providerquery executed just to open the layergeometry type read from first rowsfeature id = row positionselection jumps after a refreshfine for a quick lookdeclared in the definitionsetUid("ward_id")setGeometryField("geometry")setGeometryWkbType(…)setGeometrySrid(27700)what a script should do

Reference layers that are not in the project

In a standalone script there may be no project layers at all. addSource registers a table by name, provider and source string, so the definition carries everything it needs.

definition = QgsVirtualLayerDefinition()
definition.addSource("wards", "/data/boundaries.gpkg|layername=wards", "ogr")
definition.addSource(
    "readings",
    "file:///data/readings.csv?type=csv&delimiter=,&detectTypes=yes&geomType=none",
    "delimitedtext",
)
definition.setQuery("""
SELECT r.station_id, date(r.read_at) AS day, max(r.pm25) AS peak
FROM readings AS r
WHERE r.pm25 > 35
GROUP BY r.station_id, day
""")

peaks = QgsVectorLayer(definition.toString(), "daily peaks", "virtual")
print(peaks.isValid(), peaks.featureCount())
for f in peaks.getFeatures():
    print(f["station_id"], f["day"], f["peak"])

Breakdown: A query with no geometry column produces a geometryless layer — a table you can iterate, join or export like any other. The CSV source string is the same one covered in loading a CSV as a point layer, with geomType=none because the readings table has no location of its own. Here no uid is set on purpose: an aggregate like this has no natural integer key, and the result is read once rather than selected or edited, so row-number ids are harmless.

Keep spatial joins fast

A spatial predicate in a JOIN compares every row with every row unless it is told otherwise. For two layers of a few thousand features that is millions of geometry tests, which is the usual reason a virtual layer "hangs". The provider exposes each source's spatial index through a hidden _search_frame_ column: constraining it to the other feature's bounding box turns the join into an indexed lookup.

The _search_frame_ shortcutLeft: 5,000 points times 600 polygons means three million exact ST_Within tests. Right: the search frame constraint asks each point layer's spatial index for candidates inside a polygon's bounding box, leaving a few thousand exact tests. The query result is identical; only the work differs.Same answer, a thousandth of the geometry testsplain ST_Within joinevery point tested: 5,000 × 600with _search_frame_only points inside the box are tested

sql = """
SELECT w.fid AS ward_id, w.ward_code, count(*) AS incidents, w.geometry
FROM wards AS w
JOIN incidents AS i
  ON i._search_frame_ = w.geometry
 AND ST_Within(i.geometry, w.geometry)
GROUP BY w.fid
"""
definition = QgsVirtualLayerDefinition()
definition.setQuery(sql)
definition.setUid("ward_id")
definition.setGeometryField("geometry")
definition.setGeometrySrid(27700)
fast = QgsVectorLayer(definition.toString(), "incidents per ward", "virtual")

Breakdown: i._search_frame_ = w.geometry reads oddly but means "use the index on incidents to fetch rows whose bounding box intersects the ward's bounding box". The exact ST_Within test then runs only on those candidates. Both layers must be in the same CRS — the virtual engine does no reprojection, so a mismatch produces an empty result rather than an error. If the source layer has no spatial index (a delimited text file loaded without spatialIndex=yes, for instance), the frame falls back to a scan and you are back to brute force.

Read back and change an existing definition

A virtual layer saved in a project stores its whole definition in the source string. To inspect or alter it — change a threshold, add a column, point it at a renamed table — parse the source back into a definition rather than editing the URL by hand.

from qgis.PyQt.QtCore import QUrl
from qgis.core import QgsDataProvider

layer = QgsProject.instance().mapLayersByName("incidents per ward")[0]
definition = QgsVirtualLayerDefinition.fromUrl(QUrl.fromEncoded(layer.source().encode()))
print(definition.query())
print([s.name() for s in definition.sourceLayers()])

definition.setQuery(definition.query().replace(
    "JOIN incidents AS i", "JOIN incidents_2026 AS i"))

layer.setDataSource(definition.toString(), layer.name(), "virtual",
                    QgsDataProvider.ProviderOptions())
print(layer.isValid(), layer.featureCount())

Breakdown: fromUrl reverses toString, so the query, uid, geometry declarations and any explicit sources come back as objects you can read and set. sourceLayers() lists only the sources added with addSource; tables referenced by project layer name are resolved at open time and do not appear there, which is worth knowing before you assume a definition is self-contained. Applying the change with setDataSource rather than creating a new layer keeps the style, the layer id and any layout references — the same technique as repointing any other layer.

String replacement on SQL is fine for a one-off change you can eyeball. For anything driven by user input — a plugin dialog that lets someone pick a year, say — build the whole query from validated parts instead. The virtual provider has no bound parameters, so a value pasted straight into the SQL is an injection vector in exactly the way it would be against a database, and a stray quote in a place name is enough to make the layer invalid.

Execute SQL, a database, or Processing?

Virtual layers are lazy: every render, every identify and every attribute table refresh re-runs the query. That is what makes them live, and it is why a heavy query on a layer you pan around feels sluggish. When you only need the answer once, materialise it.

import processing

result = processing.run("qgis:executesql", {
    "INPUT_DATASOURCES": [wards, incidents],
    "INPUT_QUERY": """
        SELECT w.fid AS ward_id, w.ward_code, count(*) AS incidents, w.geometry
        FROM input1 AS w JOIN input2 AS i
          ON ST_Within(i.geometry, w.geometry)
        GROUP BY w.fid
    """,
    "INPUT_UID_FIELD": "ward_id",
    "INPUT_GEOMETRY_FIELD": "geometry",
    "OUTPUT": "/data/work/incidents_per_ward.gpkg",
})

Breakdown: Execute SQL uses the same engine but names the inputs input1, input2 in the order given, and writes a real output instead of a view. For a spatial join that Processing already offers as a dedicated algorithm — joining attributes by location, or counting points in polygons — the dedicated algorithm is usually faster still. And when every source already lives in PostGIS, write the query there, as executing SQL on PostGIS shows; pulling rows into SQLite to join them is strictly slower than letting the database do it.

QGIS version compatibility

The virtual layer provider and QgsVirtualLayerDefinition have been stable since QGIS 3.0, and _search_frame_ since 2.14. qgis:executesql kept its qgis: prefix through 3.44; check QgsApplication.processingRegistry().algorithmById("qgis:executesql") on the QGIS 4 series in case it has moved to the native: provider in your build. The SpatiaLite function set follows the SpatiaLite version bundled with your installer.

Troubleshooting

  • "no such table". The table name does not match a layer name, or the layer is not in the project and no addSource was given.
  • Empty result from a spatial join. The layers are in different CRSs. Reproject one first.
  • The layer takes minutes to open. No geometry declarations, so the query runs at open time; add them, and add _search_frame_ to spatial joins.
  • Geometry column not recognised. Aliased as something other than the declared geometry field, or an expression result that SQLite returns as a blob without SRID — wrap it in SetSRID(…, 27700).
  • Feature selection jumps around. No uid declared.

Conclusion

Use a virtual layer when you want a live SQL view across layers from different providers, declare the uid and geometry so nothing is guessed, and add _search_frame_ to every spatial join. When you need the answer rather than the view, run Execute SQL or push the query into the database that already holds the data.

Frequently Asked Questions

Can I edit a virtual layer? No. It is read-only; edit the sources and the view reflects the change.

Does the virtual layer save with the project? Yes — the definition, including the query and any sources, is stored in the project file.

Can I use QGIS expression functions in the SQL? Only SQLite and SpatiaLite functions are available inside the query. Apply QGIS expressions afterwards with a field calculation or a filter on the resulting layer.

How do I reference a layer whose name contains spaces or dots? Quote it with double quotes in the SQL, or register it under a simple alias with addSource.