Add Spatial Capabilities in SQLite

A few days ago, I wanted to add spatial features to my backend. I wanted to store the GPS positions of users and return the closest people to them (like a dating app).

Usually, people use PostgreSQL and PostGIS, an extension that enables spatial capabilities, but that’s not something I want. My backend is using SQLite because I’m not expecting more than 10 users a day.

Fortunately, there is an extension that we can use called Spatialite.

This article is for Linux. It should work on Windows and macOS if you follow some special build steps.

Building the Extension

First, we have to build the extension. The extension is not directly available as a shared library. To build it, go here, download the source code libspatialite-5.1.0.tar.gz, and decompress it.

Alternatively, you can run this command:

wget "https://www.gaia-gis.it/gaia-sins/libspatialite-sources/libspatialite-5.1.0.tar.gz" -O libspatialite-5.1.0.tar.gz && tar -xzvf libspatialite-5.1.0.tar.gz

Then go inside the libspatialite-5.1.0 directory. We are going to compile the extension, but for that, you may need to install some packages:

sudo apt install binutils libproj-dev gdal-bin

Then:

./configure --enable-freexl=no

The flag --enable-freexl=no is used to exclude FreeXL. It’s a tool to import data from Microsoft Excel format (.xls suffix). I don’t think it’s something we want, but feel free to adapt the script!

Now we compile:

make

When it’s done, you can find the library in src/.libs/. It’s called mod_spatialite.so.8.1.0. Copy and paste it into your project folder.

Now let’s write some Dart!

name: use_spatialite
description: Example of using Spatialite with SQLite in Dart.

environment:
  sdk: '>=3.0.0 <4.0.0'

dependencies:
  sqlite3: ^2.4.6

import 'dart:ffi';
import 'dart:io';

import 'package:sqlite3/sqlite3.dart';

DynamicLibrary loadSpatialiteExtension() {
  final libraryNextToScript = File("assets/mod_spatialite.so.8.1.0");

  return DynamicLibrary.open(libraryNextToScript.path);
}

void main() {
  final spatialiteLibrary = loadSpatialiteExtension();

  sqlite3.ensureExtensionLoaded(
    SqliteExtension.inLibrary(spatialiteLibrary, "sqlite3_modspatialite_init"),
  );

  final db = sqlite3.openInMemory();
}

The code opens the Spatialite dynamic library. Then it passes it to sqlite3 via the ensureExtensionLoaded method. The name is explicit: it loads the extension. Finally, we define the entry point of the extension with sqlite3_modspatialite_init.

It’s Not Working?

If it’s not working and there are no errors, the problem could be the SQLite3 library. Either it’s too old or it hasn’t been built to support extensions. To fix that, you also have to build SQLite.

wget "https://www.sqlite.org/2024/sqlite-autoconf-3460100.tar.gz" -O sqlite-autoconf.tar.gz && tar -xzvf sqlite-autoconf.tar.gz

./configure
make

More information here.

Let’s Play With It

Let’s create a table and fill it with some data:

  db.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    position POINT
);

INSERT INTO users (name, position) VALUES ('Paris', GeomFromText('POINT(2.3522 48.8566)', 4326));
INSERT INTO users (name, position) VALUES ('Marseille', GeomFromText('POINT(5.3698 43.2965)', 4326));
INSERT INTO users (name, position) VALUES ('London', GeomFromText('POINT(-0.1278 51.5074)', 4326));
INSERT INTO users (name, position) VALUES ('Berlin', GeomFromText('POINT(13.4050 52.5200)', 4326));
INSERT INTO users (name, position) VALUES ('Rome', GeomFromText('POINT(12.4964 41.9028)', 4326));
INSERT INTO users (name, position) VALUES ('Madrid', GeomFromText('POINT(-3.7038 40.4168)', 4326));
INSERT INTO users (name, position) VALUES ('Barcelona', GeomFromText('POINT(2.1734 41.3851)', 4326));
INSERT INTO users (name, position) VALUES ('Amsterdam', GeomFromText('POINT(4.9041 52.3676)', 4326));
INSERT INTO users (name, position) VALUES ('Vienna', GeomFromText('POINT(16.3738 48.2082)', 4326));
INSERT INTO users (name, position) VALUES ('Warsaw', GeomFromText('POINT(21.0122 52.2297)', 4326));
INSERT INTO users (name, position) VALUES ('Dublin', GeomFromText('POINT(-6.2603 53.3498)', 4326));
INSERT INTO users (name, position) VALUES ('Lisbon', GeomFromText('POINT(-9.1393 38.7223)', 4326));
INSERT INTO users (name, position) VALUES ('Stockholm', GeomFromText('POINT(18.0686 59.3293)', 4326));
INSERT INTO users (name, position) VALUES ('Athens', GeomFromText('POINT(23.7275 37.9838)', 4326));
INSERT INTO users (name, position) VALUES ('Budapest', GeomFromText('POINT(19.0402 47.4979)', 4326));
''');

It’s a bunch of European cities with their GPS positions. The position column has the POINT type, meaning it will only accept geometries of type POINT (latitude, longitude).

Now you can insert the following code:

  final closestToParis = db.select('''
SELECT name, 
    ST_Distance(
        position,
        GeomFromText('POINT(2.3522 48.8566)', 4326),
        true
    ) / 1000 AS distance
FROM users
WHERE ST_Distance(
        position,
        GeomFromText('POINT(2.3522 48.8566)', 4326),
        true
    ) <= 1200000 -- 1200km
ORDER BY distance DESC;
''');

  for (final city in closestToParis) {
    print("${city['name']} at ${city['distance']}km");
  }

This code is very simple. We select the name of the cities and their distance to the point POINT(2.3522 48.8566) (Paris). Then we filter the results to only include cities within a 1200km radius. Finally, we order by distance in descending order.

An important point about the ST_Distance function: if you don’t set the last argument to true, the function computes the Euclidean distance instead of the correct spherical distance.

And that’s it! You can now use geographic features with SQLite in Dart!

Just in case, I’ve included the libraries I compiled:

mod_spatialite.so.8.1.0
libsqlite3.so.8.1.0