Installing and loading spatialite on MacOS

Today I thought I’d try out a new tool which I’d never used before, spatialite, as part of a project I’m working on. Unfortunately, I found the steps to install and use the spatialite extensions lacking while following the tutorial, so I decided to document the steps I took to get it up and running. Hopefully it helps someone else, too.

It turns out the version of sqlite3 which comes with MacOS doesn’t support loading extensions, and spatialite is an sqlite3 extension, so we need to install a newer version of SQLite3 through homebrew (or compile the latest version yourself). We’ll also use brew to install the libspatialite library.

brew install sqlite3 libspatialite

Because MacOS comes with sqlite3, homebrew doesn’t automatically link it into your path— so you’ll need to call the new one explicitly. You can run this command to find binaries named like sqlite3 :

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

For me, the output looked like this:

/usr/local/Cellar/sqlite/3.24.0/bin/sqlite3
/usr/local/Cellar/sqlite/3.26.0_1/bin/sqlite3
/usr/local/Cellar/sqlite/3.27.1/bin/sqlite3

I chose to use /usr/local/Cellar/sqlite3/3.27.1/bin/sqlite3 because it’s the newest version I have available. Whichever you choose, open up a new terminal window and run /path/to/chosen/sqlite3 /path/to/database_file.db (obviously, replace with paths of your choosing). This will open up a new sqlite3 session and create the database file you specified, if it doesn’t already exist.

Next, we need to find the libspatialite library installed by homebrew. brew tends to link C/C++ libraries it installs into /usr/local/lib, so we can use find to find it:

find /usr/local/lib -path "*spatialite*"

My output looked like this:

/usr/local/lib/pkgconfig/spatialite.pc
/usr/local/lib/libspatialite.dylib
/usr/local/lib/mod_spatialite.dylib
/usr/local/lib/libspatialite.a
/usr/local/lib/libspatialite.7.dylib
/usr/local/lib/mod_spatialite.7.dylib

Although the file named like libspatialite.dylib looks promising, it turns out that’s not the right file to use. This issue on the Django issue tracker points out that we actually want to use mod_spatialite.dylib instead. So, back in our sqlite3 session, we’ll use the .load command to load the extension by specifying the full path to the mod_spatialite.dylib file:

.load /usr/local/lib/mod_spatialite.dylib

The command should run without an error, and the spatialite functions and types will be available throughout your session. But let’s make sure:

sqlite> .load /usr/local/lib/mod_spatialite.dylib
sqlite> create table test(id int, geom geometry);
sqlite> insert into test(id, geom) values (1, geomfromtext('POINT(0 0)'));
sqlite> select x(geom) from test;
0.0
sqlite> select intersects(geom, geomfromtext('POLYGON((-1 -1, 1 -1, 1 1, -1 1, -1 -1))')) FROM test;
1
sqlite> select astext(geom) from test;
POINT(0 0)

Looks good to me; we can use the x(GEOMETRY) function to get the X value of the point we put into our table, we get a 1 (for success) from our intersects query, and we can get back our input point as WKT.

Thanks for reading!