SpatiaLite & GeoPackage Automation with Python

Scripting Batch Spatial Queries with the sqlite3 CLI

Feed a whole block of spatial SQL to one sqlite3 (or spatialite) process through a quoted heredoc — loading modspatialite, wrapping the statements in…

Feed a whole block of spatial SQL to one sqlite3 (or spatialite) process through a quoted heredoc — loading mod_spatialite, wrapping the statements in BEGIN;/COMMIT;, and redirecting results with .output — so a batch that would take minutes as separate invocations runs in a single transaction in seconds.

This page is part of the SpatiaLite CLI & Shell Automation guide. Its scope is query and SQL batching — running many spatial statements non-interactively and capturing their output — as distinct from format conversion, which the ogr2ogr guide covers.

Why This Matters

The slowest way to run a thousand spatial updates is a shell loop that invokes sqlite3 a thousand times. Each invocation starts a process, opens the file, commits its own transaction, and flushes to disk — so the fixed per-call overhead, not the SQL, dominates the runtime. Batching flips that: one process, one transaction, one flush at the end. On field gateways and CI runners where the same maintenance query runs nightly over a growing GeoPackage, that difference is the gap between a job that finishes before the next collection shift and one that is still running when the surveyors arrive.

Non-interactive batching is also what makes spatial SQL reproducible. A heredoc or a .read file is a versioned artifact you can diff, review, and replay, whereas commands typed into an interactive shell vanish with the terminal. Every technique here reads its SQL from standard input or a file and returns an exit code the calling script can act on.

Prerequisites

  • SQLite 3.37+ with the sqlite3 shell on PATH, or the spatialite shell from the SpatiaLite distribution
  • mod_spatialite reachable on the library search path if you use the plain sqlite3 shell for spatial functions
  • A GeoPackage or SpatiaLite file with at least one spatial layer to query
  • A POSIX shell for heredocs and set -euo pipefail
  • Familiarity with SpatiaLite metadata tables so batched schema changes register geometry columns correctly

Primary Method

The core pattern is a quoted heredoc piped into a single shell process. Loading the extension, enabling WAL mode, opening a transaction, running every statement, and committing all happen in one session against one open file handle.

bash
# One process, one transaction: a full batch of spatial SQL via a quoted heredoc
set -euo pipefail
DB=field.gpkg

sqlite3 "$DB" <<'SQL'
.load mod_spatialite
PRAGMA journal_mode=WAL;
BEGIN;
UPDATE parcels
   SET area_m2 = ST_Area(geom)
 WHERE area_m2 IS NULL;
UPDATE parcels
   SET geom = ST_MakeValid(geom)
 WHERE ST_IsValid(geom) = 0;
DELETE FROM parcels WHERE geom IS NULL;
COMMIT;
SELECT COUNT(*) AS remaining FROM parcels;
SQL

The quoted delimiter <<'SQL' passes the block to sqlite3 verbatim, so $ and backticks inside the SQL are not touched by the shell. BEGIN; and COMMIT; bracket every mutation into one durable transaction, and with set -e the script aborts if any statement raises an error before the commit.

Step-by-Step Walkthrough

1. Load spatial functions

The spatialite shell has spatial functions built in, so it needs no load step. The plain sqlite3 shell needs .load mod_spatialite as the first line before any ST_* call. Choose one and stay consistent within a script.

bash
# spatialite shell — functions preloaded, no .load needed
spatialite field.gpkg "SELECT ST_Area(geom) FROM parcels LIMIT 1;"

# sqlite3 shell — must load the extension first
sqlite3 field.gpkg ".load mod_spatialite" "SELECT ST_Area(geom) FROM parcels LIMIT 1;"

Loading mod_spatialite is the same extension mechanism documented in Extension Compatibility in Spatial SQLite; on most platforms the bare name resolves the correct .so, .dylib, or .dll.

2. Wrap the batch in a single transaction

Placing BEGIN; before the first statement and COMMIT; after the last collapses the batch into one durable write. Without it, each statement commits independently and pays a separate disk flush.

bash
# Every UPDATE inside one transaction commits once, not once per statement
sqlite3 field.gpkg <<'SQL'
BEGIN;
UPDATE parcels SET status = 'reviewed' WHERE district = 'north';
UPDATE parcels SET status = 'reviewed' WHERE district = 'south';
UPDATE parcels SET reviewed_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
 WHERE status = 'reviewed';
COMMIT;
SQL

3. Keep reusable SQL in a file and run it with .read

For SQL you version-control, keep it in a .sql file and run it with .read. The file can itself contain .load, BEGIN/COMMIT, and dot-commands, so the whole batch is self-describing.

sql
-- GeoPackage context — nightly_maintenance.sql, run via .read
.load mod_spatialite
BEGIN;
UPDATE parcels SET geom = ST_MakeValid(geom) WHERE ST_IsValid(geom) = 0;
DELETE FROM _sync_log WHERE changed_at < date('now', '-30 days');
COMMIT;
SELECT COUNT(*) AS parcels FROM parcels;
bash
# Execute the versioned SQL file non-interactively
sqlite3 field.gpkg ".read nightly_maintenance.sql"

4. Capture results into a file with .mode and .output

To write query results to a file, set the output format with .mode, turn on .headers, redirect with .output, run the query, then restore .output stdout. Everything printed between the two .output directives lands in the file.

bash
# Export a spatial query result to CSV from inside a heredoc
sqlite3 field.gpkg <<'SQL'
.load mod_spatialite
.headers on
.mode csv
.output large_parcels.csv
SELECT fid, ST_Area(geom) AS area_m2
  FROM parcels
 WHERE ST_Area(geom) > 10000
 ORDER BY area_m2 DESC;
.output stdout
SQL

For a single ad-hoc query, the command-line flags -csv -header do the same thing without a heredoc: sqlite3 -csv -header field.gpkg "SELECT ..." > out.csv.

5. Bulk-load rows with .import

When a batch needs to ingest many rows from a delimited file, .import loads them directly into a table far faster than generating per-row INSERT statements. Set .mode csv so commas and quoting are parsed correctly, and --skip 1 to drop a header line.

bash
# Bulk-load attributes into a staging table, then merge in one transaction
sqlite3 field.gpkg <<'SQL'
.mode csv
.import --skip 1 incoming_attrs.csv attr_staging
BEGIN;
UPDATE parcels
   SET status = (SELECT s.status FROM attr_staging s WHERE s.fid = parcels.fid)
 WHERE fid IN (SELECT fid FROM attr_staging);
COMMIT;
DROP TABLE attr_staging;
SQL

6. Check the exit code

sqlite3 returns non-zero when a statement fails, so set -euo pipefail makes the script abort automatically. When you need to branch on the outcome rather than abort, capture the result and test it.

bash
#!/usr/bin/env bash
# Fail the run if the batch did not leave the database in a valid state
set -euo pipefail
DB=field.gpkg

sqlite3 "$DB" ".read nightly_maintenance.sql"

if ! sqlite3 "$DB" "PRAGMA integrity_check;" | grep -q '^ok$'; then
    echo "post-batch integrity check failed" >&2
    exit 1
fi
echo "batch complete, database valid"

Verification

After a batch runs, confirm the database is intact and the intended rows changed. PRAGMA integrity_check reads the raw b-tree, and a targeted count confirms the batch touched what you expected.

bash
# Structural integrity plus a targeted result check
sqlite3 field.gpkg <<'SQL'
PRAGMA integrity_check;
SELECT COUNT(*) AS reviewed FROM parcels WHERE status = 'reviewed';
SELECT COUNT(*) AS invalid_geom FROM parcels WHERE ST_IsValid(geom) = 0;
SQL

An integrity_check that prints ok, the expected reviewed count, and zero invalid_geom rows together confirm the batch committed cleanly and repaired the geometries it was meant to. If invalid_geom is non-zero, the ST_MakeValid step either did not run or the extension was not loaded when it ran.

Alternative Approaches or Edge Cases

Splitting an oversized batch. One transaction is fastest, but a batch that modifies millions of rows holds a write lock for its entire duration and grows the -wal sidecar until it commits. When that lock duration is a problem on a live field database, chunk the work into several transactions of a few thousand rows each, checkpointing between them:

bash
# Chunked commits keep the WAL bounded and release the lock periodically
set -euo pipefail
for offset in 0 5000 10000 15000; do
    sqlite3 field.gpkg <<SQL
BEGIN;
UPDATE parcels SET area_m2 = ST_Area(geom)
 WHERE fid IN (SELECT fid FROM parcels ORDER BY fid LIMIT 5000 OFFSET $offset);
COMMIT;
PRAGMA wal_checkpoint(TRUNCATE);
SQL
done

Note this heredoc is unquoted (<<SQL) precisely because it needs the shell to substitute $offset; keep the interpolated value numeric to avoid injecting SQL.

Driving the same SQL from Python. When a batch needs branching logic, error handling per statement, or values computed in Python, run the identical SQL through the sqlite3 module instead of the shell — the approach in Native sqlite3 Spatial Extensions. The SQL is the same; only the harness differs.

Troubleshooting

Error: no such function: ST_Area

Cause: The spatial extension was not loaded in the session. In the plain sqlite3 shell, ST_* functions do not exist until mod_spatialite is loaded, and a .load line in one invocation does not carry over to a separate invocation.

Fix: Put .load mod_spatialite as the first line of the same heredoc or .sql file that calls the spatial functions, or switch to the spatialite shell, which preloads them. Confirm the load worked with SELECT spatialite_version(); at the top of the batch.

Error: database is locked

Cause: Another process — often an overlapping scheduled run of the same script — holds the write lock, or a long transaction in this batch has not committed.

Fix: Guard scheduled batches with an flock lock file so runs cannot overlap, and keep transactions short enough to release the lock promptly. Setting PRAGMA busy_timeout=5000; at the top of the batch makes sqlite3 wait up to five seconds for the lock instead of failing immediately. See Transaction Scoping & Rollback Strategies for retry patterns.

Error: near line 1: unrecognized token after variable expansion

Cause: An unquoted heredoc (<<SQL) let the shell expand a $ inside the SQL that was meant to be literal, producing malformed SQL by the time sqlite3 parsed it.

Fix: Quote the delimiter (<<'SQL') so the block is passed verbatim. Use the unquoted form only when you deliberately interpolate a shell value, and keep interpolated values simple and validated to avoid injection.

Frequently Asked Questions

Is a heredoc or a .read file better for batching SQL?

Use a .read file when the SQL is stable, reusable, and worth version-controlling — a nightly maintenance batch or a migration you review and replay. Use a heredoc when the SQL is generated or embedded in a one-off orchestration script and does not warrant its own file. Both run the same statements in the same session, so the choice is about maintainability, not performance. A common hybrid keeps stable logic in .read files and calls them from a heredoc that adds run-specific parameters.

How much faster is one transaction than separate statements?

The gain scales with how many separate commits you avoid, because each independent commit forces a disk flush that a single wrapping transaction pays only once. On a batch of thousands of small updates against local storage, consolidating into one BEGIN;/COMMIT; commonly turns a multi-minute run into a sub-second one. The improvement is even larger on network or removable storage, where each flush is slower, which is exactly the storage field devices tend to use.