A quick Sqlite3 and Spatialite primer

5 min readSep 13, 2021

Sqlite3 is a command-line based tool for working with Sqlite databases.

Spatialite is an extension for Sqlite that enables spatial things, such as spatially-aware functions and indexes.

This article will give you a brief overview of working with a spatial database in Sqlite3. It is written with Macs in mind.

Before we begin, we need to deal with a quirk.

Gotcha: Sqlite3 on Mac doesn’t support extensions

Macs come with sqlite3 built in, but using a version that does not support loading extensions. This means that we won’t be able to make use of Spatialite’s features (because Spatialite is an extension).

Thanks to this article for the extra steps required for getting Spatialite working on your Mac, which I’ll cover briefly here.

Installing Sqlite3 and Spatialite from brew

First, install sqlite3 and libspatialite using Brew:

brew install sqlite3 libspatialite

Now, we’ll need to locate the newly-installed Sqlite3 binary, so that we can run that, instead of the built-in version.

Use this command to locate the Sqlite3 binary we installed with Brew:

find /usr/local -path "*sqlite3" | grep -e "sqlite3$" | grep "/bin"

If you have multiple versions installed, pick the most recent version.

On my machine, the Sqlite3 binary installed by Brew was found here:

/usr/local/Cellar/sqlite/3.36.0/bin/sqlite3

Once you’ve found your binary, execute it to open an interactive Sqlite3 prompt.

Loading a database

For these examples, I’m using the db_small.sqlite database from osf.io.

To load a database, you can either specify it as an argument when you run sqlite3, like this:

/usr/local/Cellar/sqlite/3.36.0/bin/sqlite3 db_small.sqlite

Or, you can run sqlite3 and then open the database from within the interactive terminal:

sqlite> .open db_small.sqlite

Loading the Spatialite extension

To load the Spatialite extension, you can execute this statement:

sqlite> select load_extension(“mod_spatialite”);

Update 13 Jan 2022: the above command no longer works. I’m not sure whether it’s due to a newer version of SQLite (3.37.2) or because I upgraded macOS to Monterey.

In any event, the correct command is now:

sqlite> .load mod_spatialite

If all goes well, you should receive a blank result.

If you have trouble, refer to this article for the extra steps required for getting Spatialite working on your Mac.

The .tables command

You can use the .tables command to see all of the tables in the database:

This is a Spatialite database, so there’s a lot of extra tables in here, meaning the above output is hard to read! But luckily the author of the database has told us the tables names of interest:

Available data tables (name_type):
* buildings_points
* landuse_polygon
* pois_polygons
* roads_lines
* waterways_lines

The .schema command

We can use the .schema command to view the full schema of a table, e.g.:

Querying the data

Querying the data is as simple as executing an SQL statement:

sqlite> select * from landuse_polygon limit 5;

Which writes the output to the terminal:

1|506298017|7218|grass|
2|362422274|7208|meadow|
3|506299179|7218|grass|
4|362422273|7215|orchard|
5|506299172|7218|grass|

The results can be made easier to read by tweaking some settings, as described below.

Use .headers and .mode to tidy up the results

Let’s turn on headers and fixed-width columns, and then re-run the same query:

sqlite> .headers on
sqlite> .mode column
sqlite> select * from landuse_polygon limit 5;

Which gives us results that are much easier to read:

PK_UID osm_id code fclass Geometry
 — — — — — — — — — — — — — — — — — — 
1 506298017 7218 grass
2 362422274 7208 meadow
3 506299179 7218 grass
4 362422273 7215 orchard
5 506299172 7218 grass

Side note: the Geometry column is blank… but why? We’ll cover that below!

These results are easier to read, but what if we want to explore them a bit? For that, we can send the results to Excel!

View the results in Excel using the .excel command

You can use the .excel command to send the results of the next query to Excel.

For example, if you run these two commands:

sqlite> .excel
sqlite> select fclass,count(*) from landuse_polygon group by fclass;

The results are opened immediately in Excel:

You could now explore the data, create charts, etc. Excel is tops.

Now, all of the above is very interesting, but it’s a Spatialite database, so let’s dive in to some spatial stuff.

The AsText() function

You may notice that the Geometry column is not displayed in the results. This is because it is stored in an internal, not-human-readable, format.

But, we can transform it into various readable formats.

We can use the Spatialite AsText() function to turn the geometry, like this:

sqlite> select osm_id,AsText(Geometry) from buildings_points limit 5;

Which shows us the geometry in Well Known Text format:

osm_id AsText(Geometry)
 — — — — — — — — — — — — — — — — — — — — — — 
9389303 POINT(564802.614679 245098.270118)
676176350 POINT(569484.338867 248277.530192)
676358972 POINT(557675.069139 235316.008554)
676358966 POINT(557562.505012 235488.056404)
676358964 POINT(557561.476416 235514.474072)

Gotcha: if the AsText() column values are still blank, then it may be because you’re using a GeoPackage database, which you can resolve as described in this article.

The AsGeoJson() function

The AsGeoJson() function is similar to the AsText() function, but outputs the geometry as GeoJson:

sqlite> select osm_id,AsGeoJson(Geometry) from buildings_points limit 5;

Which results in:

osm_id AsGeoJson(Geometry)
 — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
9389303 {“type”:”Point”,”coordinates”:[564802.61467,24509.2701181]}
676176350 {“type”:”Point”,”coordinates”:[569484.33886,248277.53019]}
676358972 {“type”:”Point”,”coordinates”:[557675.06913,235316.00855]}
676358966 {“type”:”Point”,”coordinates”:[557562.50501,235488.05640]}
676358964 {“type”:”Point”,”coordinates”:[557561.47641,235514.47407]}

OK, so now we can see our geometry, but what about messing with it?

Spatialite functions to manipulate geometry

Spatialite provides us with functions we can use to manipulate geometry.

For example, we can group the landuse_polygon table by fclass and then calculate the bounding envelope for each group with the Envelope() function:

sqlite> select fclass,AsText(Envelope(Geometry)) from landuse_polygon group by fclass;

Which gives us this result:

The bounding envelopes of each `fclass` group

Or, we could use the Buffer() function to “buffer” some points and turn them into circular polygons:

sqlite> select AsText(Geometry),AsText(Buffer(Geometry,10)) from buildings_points limit 5;