Query a SpatiaLite Database in PyQGIS

Not every project needs a database server. SpatiaLite adds spatial types and functions to SQLite, giving a full spatial SQL database in a single file that can be emailed, versioned or carried on a laptop to the field. GeoPackage is built on the same SQLite foundation, and QGIS can run SpatiaLite's spatial SQL against GeoPackage tables too. For one-person projects, offline work and data exchange with analysis attached, a single-file database often beats both shapefiles and a server.

This recipe belongs to PostGIS & Database Workflows. It loads SpatiaLite tables, runs spatial SQL and loads query results as layers, uses spatial indexes correctly in queries, creates views and tables, and compares the single-file approach with PostGIS.

SQLite, SpatiaLite and GeoPackageSQLite is a file-based SQL database. SpatiaLite extends it with spatial geometry types, hundreds of spatial SQL functions and R-tree spatial indexes. GeoPackage is an OGC standard built on SQLite with its own geometry encoding and metadata tables. QGIS reads both, and with the SpatiaLite extension loaded can run spatial SQL against GeoPackage tables as well.One file, full spatial SQLSQLitefile-based SQLSpatiaLitespatial types + functionsR-tree indexesspatialite providerGeoPackageOGC standardown geometry encodingogr provider

Prerequisites

  • QGIS 3.34 LTR or newer, or the QGIS 4 series; both include SpatiaLite support.
  • A SpatiaLite .sqlite database or a GeoPackage. QGIS can create either: GeoPackage through any "save as", SpatiaLite through the Browser or QgsVectorFileWriter with the SpatiaLite driver.

Load tables from a SpatiaLite file

SpatiaLite tables load through the spatialite provider with a URI naming the file, table and geometry column. QgsDataSourceUri builds it safely.

from qgis.core import QgsDataSourceUri, QgsVectorLayer, QgsProject

db = "/data/field/survey_2026.sqlite"
uri = QgsDataSourceUri()
uri.setDatabase(db)
uri.setDataSource("", "sample_points", "geom")
points = QgsVectorLayer(uri.uri(), "sample points", "spatialite")
print(points.isValid(), points.featureCount(), points.crs().authid())
QgsProject.instance().addMapLayer(points)

Breakdown: SpatiaLite has no schemas, so the schema argument is empty. The geometry column name must match the one registered in the database's geometry_columns table; list it with the connections API as shown below if unsure. The same file can also be opened with the ogr provider, but the spatialite provider is the one that supports SQL query layers and SpatiaLite-specific features cleanly.

Explore the database with the connections API

The provider connections API works for SpatiaLite files as for PostGIS, which makes exploring an unfamiliar database quick.

from qgis.core import QgsProviderRegistry

md = QgsProviderRegistry.instance().providerMetadata("spatialite")
conn = md.createConnection(db, {})
for t in conn.tables():
    cols = t.geometryColumnTypes()
    print(t.tableName(), [(c.crs.authid()) for c in cols] or "no geometry")

version = conn.executeSql("SELECT spatialite_version(), sqlite_version()")[0]
print("SpatiaLite", version[0], "on SQLite", version[1])

Breakdown: createConnection wraps a file directly without saving it in the Browser. Listing tables shows which have geometry and in which CRS. spatialite_version() confirms the spatial extension is active; if it raises an error, the file is plain SQLite without SpatiaLite metadata. Everything in using the database connections API applies here too.

Run spatial SQL

SpatiaLite provides several hundred spatial functions with names familiar from PostGIS: ST_Buffer, ST_Intersects, ST_Area, ST_Transform. Queries run through executeSql and return rows.

rows = conn.executeSql("""
    SELECT h.habitat, count(*) AS n, round(avg(p.species_count), 1) AS mean_species
    FROM sample_points AS p
    JOIN habitats AS h ON ST_Within(p.geom, h.geom)
    WHERE p.ROWID IN (
        SELECT ROWID FROM SpatialIndex
        WHERE f_table_name = 'sample_points' AND search_frame = h.geom)
    GROUP BY h.habitat ORDER BY mean_species DESC
""")
for habitat, n, mean in rows:
    print(f"{habitat:<20} {n:>4} samples, mean {mean} species")

Breakdown: The join finds each sample point within each habitat polygon. The subquery against the virtual SpatialIndex table is SpatiaLite's way of using its R-tree index: unlike PostGIS, SpatiaLite does not use spatial indexes automatically in joins, so without it the query compares every point with every polygon. With it, only candidates whose bounding boxes intersect are tested. This one difference explains most "SpatiaLite is slow" experiences.

Using the spatial index explicitlyA spatial join without the SpatialIndex subquery tests every point against every polygon, so cost grows with the product of their counts. With the subquery, SpatiaLite first retrieves only points whose bounding boxes intersect each polygon's frame, then tests those exactly. PostGIS uses its indexes automatically; SpatiaLite needs to be told.In SpatiaLite, ask for the indexno index subqueryevery point × every polygonminutes on modest dataSpatialIndex subquerybbox candidates firstseconds

Load a query as a layer

A query with a geometry column can be loaded as a layer — a live view of the result, re-run as the map is drawn — through the provider URI's SQL syntax.

query = """
    SELECT p.ROWID AS uid, p.sample_id, p.species_count, h.habitat, p.geom
    FROM sample_points AS p
    JOIN habitats AS h ON ST_Within(p.geom, h.geom)
    WHERE p.species_count >= 20
"""
quri = QgsDataSourceUri()
quri.setDatabase(db)
quri.setDataSource("", f"({query})", "geom", "", "uid")
rich = QgsVectorLayer(quri.uri(), "species-rich samples", "spatialite")
print(rich.isValid(), rich.featureCount())
QgsProject.instance().addMapLayer(rich)

Breakdown: Wrapping the query in parentheses as the "table" and naming a unique key column (uid from ROWID) lets the provider treat the result as a read-only layer. It updates whenever the data changes, which is convenient for derived layers in a field project. For large results that are drawn often, materialising the result into a table is faster than re-running the query on every pan. The equivalent technique for PostGIS is in loading a PostGIS query layer.

Create views and tables

Persistent derived data belongs in the database. A view keeps the logic; a table stores the result. Both must be registered for SpatiaLite to recognise their geometry.

conn.executeSql("""
    CREATE TABLE IF NOT EXISTS habitat_summary AS
    SELECT h.habitat, h.geom, count(p.ROWID) AS samples
    FROM habitats AS h LEFT JOIN sample_points AS p ON ST_Within(p.geom, h.geom)
    GROUP BY h.ROWID;
""")
conn.executeSql("SELECT RecoverGeometryColumn('habitat_summary', 'geom', 25832, 'MULTIPOLYGON', 'XY')")
conn.executeSql("SELECT CreateSpatialIndex('habitat_summary', 'geom')")
print([t.tableName() for t in conn.tables()])

Breakdown: CREATE TABLE … AS SELECT stores the result, but SpatiaLite does not know the new geom column is a geometry until RecoverGeometryColumn registers it with its SRID, type and dimensions; skip that and QGIS sees a table without geometry. CreateSpatialIndex builds the R-tree for fast drawing and joins. Views need an entry in views_geometry_columns instead — use QGIS's DB Manager or the spatialite command-line tool for view registration if you use them often.

Create a SpatiaLite database from QGIS layers

Starting a single-file project database from layers already loaded in QGIS takes one writer call per layer. The first call creates the file with SpatiaLite metadata; later calls add tables to it.

from qgis.core import QgsVectorFileWriter, QgsCoordinateTransformContext

out_db = "/data/field/new_project.sqlite"
layers = [QgsProject.instance().mapLayersByName(n)[0] for n in ("plots", "transects", "habitats")]
for i, lyr in enumerate(layers):
    opts = QgsVectorFileWriter.SaveVectorOptions()
    opts.driverName = "SQLite"
    opts.layerName = lyr.name()
    opts.datasourceOptions = ["SPATIALITE=YES"]
    opts.actionOnExistingFile = (QgsVectorFileWriter.CreateOrOverwriteFile if i == 0
                                 else QgsVectorFileWriter.CreateOrOverwriteLayer)
    err, msg, *_ = QgsVectorFileWriter.writeAsVectorFormatV3(
        lyr, out_db, QgsCoordinateTransformContext(), opts)
    print(lyr.name(), "ok" if err == QgsVectorFileWriter.NoError else msg)

Breakdown: The SQLite driver with SPATIALITE=YES creates a real SpatiaLite database rather than a plain SQLite file, so the spatial metadata tables and functions are available. Creating the file on the first layer and adding layers afterwards collects everything in one file. GDAL creates a spatial index for each geometry table by default. The result opens with the spatialite provider and works with every query technique above; for a GeoPackage instead, change the driver to GPKG and drop the datasource option.

SpatiaLite SQL on GeoPackage

GeoPackage files are SQLite databases too, and QGIS's OGR provider exposes SpatiaLite functions on them through GDAL's SQLite dialect — so the same spatial SQL works on the format most people exchange.

Spatial SQL on a GeoPackageA GeoPackage opened through the connections API with the ogr provider accepts SQL that uses SpatiaLite functions such as ST_Area and ST_Buffer, because GDAL loads the SpatiaLite extension for its SQLite dialect. Geometry columns use GeoPackage encoding, which the functions read transparently. Results come back like any SQL result.GeoPackage + SpatiaLite functions via GDALGeoPackagegpkg geometryGDAL SQLite dialectSpatiaLite loadedspatial SQLST_Area, ST_Buffer

gpkg = QgsProviderRegistry.instance().providerMetadata("ogr").createConnection(
    "/data/projects/parcels.gpkg", {})
rows = gpkg.executeSql("""
    SELECT land_use, round(sum(ST_Area(geom)) / 10000, 1) AS ha
    FROM parcels GROUP BY land_use ORDER BY ha DESC
""")
print(rows[:5])

Breakdown: The OGR connection runs the query through GDAL, which understands GeoPackage geometry and provides SpatiaLite functions when GDAL is built with SpatiaLite support — as it is in QGIS's distributions. This makes a GeoPackage a self-contained analysis database: data, styles and the SQL that summarises it travel in one file. Function availability depends on the GDAL build; test a simple SELECT ST_Area(geom) FROM … LIMIT 1 first.

SpatiaLite or PostGIS?

Single-file databases excel for one user or a small team working in turns, offline field work, data exchange and reproducible project archives. They struggle with many simultaneous editors (SQLite locks the file during writes), very large datasets with heavy concurrent querying, and fine-grained permissions. PostGIS is the answer for shared, multi-user, server-side data; a common pattern is PostGIS as the master and GeoPackage or SpatiaLite exports for field and exchange copies, as in writing a vector layer to GeoPackage.

QGIS version compatibility

The spatialite provider, connections API and SQL query layers work on QGIS 3.34 LTR, 3.40 LTR and QGIS 4. Bundled SpatiaLite is version 5 on current installers, which includes RecoverGeometryColumn, CreateSpatialIndex and the SpatialIndex virtual table.

Troubleshooting

  • Spatial joins take forever. The SpatialIndex subquery is missing, or the index was never created.
  • A new table has no geometry in QGIS. It was not registered with RecoverGeometryColumn.
  • "database is locked". Another process is writing; SQLite allows one writer at a time — close other editors.
  • Spatial functions are unknown on a GeoPackage. The GDAL build lacks SpatiaLite; use the SpatiaLite provider on a .sqlite file instead.

Conclusion

Load SpatiaLite tables with the spatialite provider, explore and query through the connections API, use the SpatialIndex virtual table explicitly in spatial joins, load queries as layers with a unique key, register new geometry tables and index them, run the same spatial SQL on GeoPackages through GDAL, and move to PostGIS when many people edit at once.

Frequently Asked Questions

Should I choose SpatiaLite or GeoPackage for new projects? GeoPackage for exchange and broad tool support; SpatiaLite when you rely heavily on its SQL functions and tooling. Both are SQLite underneath.

Can several people read the same file at once? Yes. Concurrent reads are fine; writes are serialised.

How big can a SpatiaLite database get? Many gigabytes work well; performance depends more on indexes and queries than size.

Can I use SpatiaLite from Python without QGIS? Yes — load the mod_spatialite extension into Python's sqlite3.