Spatial SQL on the desktop: using SpatiaLite with Chwilio

SQLite plus SpatiaLite is a complete, OGC-compliant spatial database in a single file. You get geometry types, hundreds of spatial functions, an R*Tree spatial index, and on-the-fly reprojection between coordinate systems — most of what you’d reach to PostGIS for, with nothing to run but a .sqlite file you can email.

There’s just one catch, and it’s the reason this post exists.

SQLite can do GIS, if your tools let it

SpatiaLite ships as a loadable SQLite extension (mod_spatialite). And loading an extension is exactly the thing almost no GUI SQLite tool will do. Extensions are native code, so most browsers disable loading entirely, which is why, on the desktop, Spatial SQL has been a command-line-only affair. You could build a whole spatial database, but the moment you wanted to look at it, you were back in a terminal.

I built Chwilio around the opposite idea. Loading a trusted extension should be a normal, point-and-click thing, so SpatiaLite becomes something you load once and then use like any other SQL — autocomplete, a result grid, EXPLAIN, and export. (Here’s how Chwilio loads extensions safely.)

Everything below is a real run against a small spatial-demo.sqlite (ten world cities and two region polygons), captured straight from the app.

What SpatiaLite gives you

• Geometry types. POINT, LINESTRING, POLYGON, their multi- variants, and collections, each tagged with an SRID (coordinate system).
• The ST_* function family. Measurement (ST_Distance, ST_Area, GLength), relationships (ST_Within, ST_Contains, ST_Intersects), construction (MakePoint, GeomFromText, ST_Buffer), and readers (AsText, AsGeoJSON).
• A real spatial index. An R*Tree you create with one function call and query through a virtual table.
• Reprojection. Transform() converts geometry between coordinate systems using PROJ (e.g. WGS84 ↔ Web Mercator).
• Virtual tables. KNN, SpatialIndex, shapefile and spreadsheet readers, and more.

It’s native code, which is why a browser has to deliberately choose to load it. Chwilio does that deliberately — on macOS behind an architecture check and a helper-process test load first — then keeps it loaded for every connection.

Step 1: Load mod_spatialite in Chwilio

SpatiaLite is the awkward case for extension loading, because it isn’t one file. It’s one library plus a whole tree of dependencies — GEOS, PROJ, freexl, libxml2 and friends — and how you get that tree to resolve differs by platform. Pick your side.

macOS: make it self-contained (recommended)

brew install libspatialite puts mod_spatialite.dylib in /opt/homebrew/lib. You can load it straight from there, but it’s worth taking one extra step first.

Homebrew’s mod_spatialite.dylib references its dependencies (GEOS, PROJ, freexl, libxml2, and friends) by absolute /opt/homebrew/... paths. That ties it to your exact Homebrew install: move it, ship it to a colleague, or load it from anywhere else and dyld can’t find the chain, so the load fails with “Library not loaded”. dylibbundler (brew install dylibbundler) copies the whole dependency tree into one folder and rewrites every reference to @loader_path/, a path relative to the dylib itself, making it a portable unit that loads from any location:

mkdir -p ~/spatialite-fixed && cd ~/spatialite-fixed
cp /opt/homebrew/lib/mod_spatialite.dylib .

dylibbundler -of -cd -b \
  -x ./mod_spatialite.dylib \
  -d ./ \
  -p @loader_path/

The flags: -x is the file to fix, -d where the copied dependencies go, -p the path written into the references, -of overwrite, -cd create the destination dir, -b bundle the dependencies. Then verify the rewrite:

otool -L mod_spatialite.dylib

Every non-system line should now read @loader_path/... with no /opt/homebrew left. You now have a self-contained ~/spatialite-fixed/mod_spatialite.dylib you can move, back up, or share.

Windows: download the official bundle, and that’s it

There’s no relinking step, because the project already ships a self-contained build. Grab mod_spatialite-<version>-win-amd64.7z from gaia-gis.it and extract it anywhere — C:\sqlite-ext\spatialite\ is as good as anywhere. Inside you’ll find mod_spatialite.dll sitting next to 27 dependency DLLs.

That count is the interesting part. Windows resolves a DLL’s dependencies starting from the application’s directory, never from the directory the DLL itself lives in — so the usual advice for loading SpatiaLite on Windows is to put the folder on your PATH and restart the app. That’s the step everyone hits, and it’s why “just point the tool at the DLL” normally doesn’t work.

Chwilio doesn’t need it. It puts the extension’s own folder on the DLL search path for the duration of the load, so the 27 siblings resolve from where they already are. Point it at mod_spatialite.dll and press Test Now:

Tested OK   Loaded successfully into the current database.

Nothing on PATH, nothing copied next to the app, nothing to restart. (Leaving it off PATH is also the safer choice: that bundle ships its own libcurl-4.dll, and a stray copy of that on PATH is exactly the kind of thing that breaks other software in confusing ways.)

Load it in Chwilio

Open Extension ▸ Extension Manager (⇧⌘E on macOS, Ctrl+Shift+E on Windows), click Load Extension…, and point it at your mod_spatialite.dylib or mod_spatialite.dll. Leave the entry point on Automatic (SQLite detects sqlite3_modspatialite_init itself) and set the database scope to All Databases.

macOSWindows

Either way, Chwilio turns the platform’s unhelpful error into something you can act on. On macOS it also preflights the load before any native code goes near your real connection: an architecture check (an arm64/x86_64 mismatch is reported, not crashed) plus a separate helper-process load, and dyld’s cryptic “Library not loaded / image not found” rewritten into a hint — exactly the failure the dylibbundler step above prevents. On Windows there is no separate preflight pass; what you get is the diagnosis — telling apart the two things that both surface as “The specified module could not be found”: the file isn’t there, versus the file is there but something it depends on isn’t. Once it’s in, the connection bar reports the extension is ready and the full ST_* family autocompletes in the SQL editor.

-- sqlite3 CLI: you reload it by hand, every single session
.load ~/spatialite-fixed/mod_spatialite

-- Chwilio: load it once in the Extension Manager, leave the entry
-- point on "Automatic", scope it to All Databases — and every
-- connection you open from then on already has SpatiaLite ready.

Step 2: Build a spatial database

A geometry column isn’t a plain blob you stuff coordinates into. SpatiaLite tracks its type and SRID in metadata tables and guards it with triggers. The setup is a few function calls:

-- 1. Seed the spatial metadata (spatial_ref_sys, geometry_columns, …)
SELECT InitSpatialMetadata(1);

-- 2. A normal table…
CREATE TABLE cities (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  country    TEXT NOT NULL,
  population INTEGER
);

-- 3. …gets a typed, SRID-4326 (WGS84 lon/lat) POINT column
SELECT AddGeometryColumn('cities', 'geom', 4326, 'POINT', 'XY');

-- 4. Insert points with MakePoint(longitude, latitude, srid)
INSERT INTO cities (name, country, population, geom) VALUES
  ('London', 'UK', 8982000, MakePoint(-0.1276, 51.5074, 4326)),
  ('Paris',  'FR', 2161000, MakePoint( 2.3522, 48.8566, 4326)),
  ('Tokyo',  'JP', 13960000, MakePoint(139.6917, 35.6895, 4326));
  -- …and so on

-- 5. Build the R*Tree spatial index on the geometry column
SELECT CreateSpatialIndex('cities', 'geom');

Run that in Chwilio’s SQL editor and the Structure tab shows the result: a geom column typed POINT, alongside the spatial-constraint triggers SpatiaLite created to keep the geometry and its index consistent.

macOSWindows

A note for CLI refugees: in the bare sqlite3 shell these inserts fail with “unsafe use of GeometryConstraints()” unless you remember PRAGMA trusted_schema=ON. In Chwilio they just work: one less papercut.

Step 3: Read the geometry

Geometry is stored as a compact binary blob, so browsing the table the normal way shows you exactly that, an opaque BLOB:

macOSWindows

To read it, ask SpatiaLite to format it. AsText returns WKT; AsGeoJSON returns GeoJSON you can drop straight onto a map:

SELECT name, country, AsText(geom) AS wkt, AsGeoJSON(geom) AS geojson
FROM cities
ORDER BY name
LIMIT 6;

macOSWindows

Step 4: Spatial queries you can actually use

This is where Spatial SQL earns its keep. Every query below is plain SQL you run in the editor; the spatial part is just functions.

Nearest neighbour: “what’s near me?”

The bread and butter of every “stores near you” feature. ST_Distance with the ellipsoidal flag (1) returns metres, so ordering by it gives you the closest rows:

-- 5 cities nearest to Brussels (lon 4.3517, lat 50.8503), distance in km
SELECT name, country,
       ROUND(ST_Distance(geom, MakePoint(4.3517, 50.8503, 4326), 1) / 1000.0, 1) AS km
FROM cities
ORDER BY ST_Distance(geom, MakePoint(4.3517, 50.8503, 4326), 1)
LIMIT 5;

macOSWindows

Paris at 264 km, London at 322 km, Berlin at 653 km: real great-circle distances, computed in the database. Swap the LIMIT for a WHERE and you have a radius search instead:

-- Everything within 1500 km of Brussels
SELECT name, ROUND(ST_Distance(geom, MakePoint(4.3517, 50.8503, 4326), 1) / 1000.0, 1) AS km
FROM cities
WHERE ST_Distance(geom, MakePoint(4.3517, 50.8503, 4326), 1) <= 1500000
ORDER BY km;

Point-in-polygon: “which region is this in?”

Geofencing, territory assignment, “which delivery zone does this address fall in”: all the same question, really. Is a point inside a polygon? ST_Within answers it, and you can join straight on it:

-- Which region polygon contains each city?
SELECT c.name AS city, c.country, r.name AS region
FROM cities c
JOIN regions r ON ST_Within(c.geom, r.geom)
ORDER BY r.name, c.name;

macOSWindows

The European cities resolve to the Europe polygon and the US cities to North America, while Tokyo, Sydney, and Cairo, which fall in neither box, simply drop out of the join.

Measure and reproject

Areas and lengths (ellipsoidal, in m² and metres), buffers, and coordinate-system conversions are one function call each:

-- Area (km²) and perimeter (km) of each region, on the ellipsoid
SELECT name,
       ROUND(ST_Area(geom, 1) / 1000000.0)  AS area_km2,
       ROUND(Perimeter(geom, 1) / 1000.0)   AS perimeter_km
FROM regions;

-- Reproject WGS84 (lon/lat) → Web Mercator (the projection web maps use)
SELECT name, AsText(Transform(geom, 3857)) AS web_mercator
FROM cities
WHERE name = 'London';

Step 5: Make it fast with the spatial index

CreateSpatialIndex built an R*Tree, but SQLite won’t use it automatically. You opt in by filtering row IDs through the SpatialIndex virtual table with a bounding box (BuildCircleMbr here builds one around a point):

SELECT c.name, c.country
FROM cities c
WHERE c.ROWID IN (
  SELECT ROWID FROM SpatialIndex
  WHERE f_table_name = 'cities'
    AND search_frame = BuildCircleMbr(2.3522, 48.8566, 6.0, 4326)
);

Does it actually hit the index? Chwilio’s EXPLAIN QUERY PLAN answers that without you leaving the editor:

macOSWindows

SCAN SpatialIndex VIRTUAL TABLE INDEX is the R*Tree doing its job: instead of measuring distance to every row, SpatiaLite narrows to candidates inside the bounding box first. On ten cities it’s academic; on a few million points it’s the difference between instant and unusable.

What Chwilio adds

Nothing above is exotic SQL, but you couldn’t run any of it from a desktop GUI before, because none of them load the extension. With Chwilio you get the whole loop in one window:

• Extension loading as a real feature. One of the very few macOS SQLite browsers that loads extensions at all, with preflight checks and explicit load states rather than a crash or a silent failure.
• ST_* autocomplete in the editor, a result grid for inspecting output, and EXPLAIN QUERY PLAN to confirm the index is used.
• AsGeoJSON → export. Copy or export a spatial query’s results as GeoJSON/CSV and drop them onto a map.
• Change-set-safe edits. Edit spatial data through Chwilio’s reviewable, preview-first change set instead of firing raw UPDATEs.

Where Spatial SQL helps SQLite users

If you’ve never reached for geometry in SQLite, here’s the kind of thing it unlocks, all from a single file, with no server and no PostGIS:

• “Near me” / store locators: nearest-neighbour over your own data.
• Geofencing and territory rules: point-in-polygon to assign zones, regions, or sales territories.
• Logistics and routing prep: distances, buffers, and catchment areas for planning.
• Offline and mobile maps: SQLite is the on-device database, and SpatiaLite makes it spatial, fully offline.
• Geospatial data science: join coordinates to business data and query it in plain SQL.
• Asset, fleet, and IoT tracking: store positions, ask which ones are inside a region or within range.
• Real-estate and catchment analysis: what’s within X km, which district a parcel sits in.

The whole thing is still one .sqlite file you can commit to a repo or email to a colleague. The piece that was missing on the desktop — actually opening it and running the queries — is the part Chwilio fills in.

You can try this right now, for free. Chwilio is in private beta, and the beta build unlocks every feature, including loading your own extensions, for the whole beta period at no cost. Grab a beta invite, then brew install libspatialite and load it from the Extension Manager. When the beta period ends, access reverts to the free trial, and keeping these features needs a license. Questions about a specific extension? Get in touch.