Spatialite is a powerful C library that extends SQLite's capabilities to support geospatial data. If you're working with an SQLite database and need geospatial data support, Spatialite is an excellent choice. In this blog post, we'll explore how to integrate Spatialite with SQLite in Node.js. We'll be using Spatialite version 4 as the npm package we are using has only Spatialite 4 as last update.

We'll focus on using Spatialite with raw SQL queries in SQLite. In a later blog we will see how we can integrate Spatialite with Sequelize and avoid using raw queries and use Sequelize to query tables.

To begin, create a new npm project and install the sqlite3 and spatialite and wkx npm packages.

npm i sqlite3 spatialite wkx

The first step in using Spatialite is to load the Spatialite extension into SQLite. This allows SQLite to store, analyze, and process geospatial data. To do this, we need to initialize Spatialite, which equips SQLite with the necessary data types and tables for geospatial data support. This can be achieved by calling the InitSpatialMetaData() function. If you're using Spatialite version 5 or later, you should use InitSpatialMetaDataFull(1) instead.

const sqlite = require('spatialite');
const db = new sqlite.Database('spatialite.sqlite'); // filepath for database file, if file dosent exists it will create one

const query = "SELECT AsGeoJSON(Centroid(GeomFromText('POLYGON ((30 10, 10 20, 20 40, 40 40, 30 10))'))) AS geojson;";

db.spatialite(function (err) { // first load spatialite extension before any geospacial query
    db.each(query, function (err, row) {
        // if you get the output means you have successfully loaded spatialite extenstion
        console.log(row.geojson);  // {"type":"Point","coordinates":[25.45454545454545,26.96969696969697]}

        // initialize spatial metadata, this is needed only first time when you create a new database
        db.run("SELECT InitSpatialMetaData();", function (err) {
            if (err) {
                console.log(err);
            }
            console.log('InitSpatialMetaData() executed');
        });
    });
});

It is also possible to initialize Spatialite using a GUI, but we will not get into that here as it requires some C and C++ compilation.

Once Spatialite is initialized, you can create a column with the geometry type to store geospatial data. This is done using the AddGeometryColumn() SQL function provided by Spatialite. You can find its documentation here.

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL
);

-- geometry column needs to be added separatly
SELECT AddGeometryColumn('users', 'location', 4326, 'POINT', 'XY');
-- the polygon of users area where he live
SELECT AddGeometryColumn('users', 'area_polygon', 4326, 'POLYGON', 'XY');

With these steps completed, you're ready to use all the Spatialite functions to analyze and process geospatial data. You can find a list of the functions provided by Spatialite here.

Example Spatialite query

-- data insert
INSERT INTO users (name, email, location, area_polygon) VALUES (
    'John Doe',
    'john@example.com',
    ST_GeomFromText('POINT(73.78291978700082 20.06382394128455)', 4326),
    ST_GeomFromText('POLYGON((73.78291978700082 20.153655469696503,73.76425171766915 20.151928401225437,73.74630224935409 20.146813678146447,73.72976218439238 20.138508175402713,73.71526782425902 20.127331545866948,73.70337641384222 20.11371386322068,73.69454469155285 20.09817900852825,73.68911137479127 20.0813244521561,73.6872842478228 20.063798221107906,73.68913233201987 20.04627394788734,73.69458341547501 20.029424966938404,73.70342700911033 20.013898456461558,73.71532258821522 20.00029061649653,73.72981277972077 19.989123829650488,73.74634097336151 19.98082667114284,73.76427267495802 19.975717523507555,73.78291978700082 19.9739924128726,73.8015668990436 19.975717523507555,73.81949860064013 19.98082667114284,73.83602679428087 19.989123829650488,73.85051698578643 20.00029061649653,73.86241256489132 20.013898456461558,73.87125615852663 20.029424966938404,73.87670724198178 20.04627394788734,73.87855532617884 20.063798221107906,73.87672819921036 20.0813244521561,73.8712948824488 20.09817900852825,73.86246316015941 20.11371386322068,73.85057174974263 20.127331545866948,73.83607738960926 20.138508175402713,73.81953732464756 20.146813678146447,73.80158785633247 20.151928401225437,73.78291978700082 20.153655469696503))', 4326)
);

You can also insert data with JavaScript, below is the example to insert Point and Polygon.

import circleToPolygon from 'circle-to-polygon';
import wkx from 'wkx';

const coordinates = [73.78291978700082, 20.06382394128455]; // longitude, latitude
const point = {type: "Point", coordinates: coordinates};
const radius = 10000; // in meters

const polygon = circleToPolygon(coordinates, radius, 32); // 32 is the number of edges
const pointForQuery = wkx.Geometry.parseGeoJSON(point).toWkt();
const polygonForQuery = wkx.Geometry.parseGeoJSON(polygon).toWkt();

const insert  = `
INSERT INTO users (name, email, location, area_polygon) VALUES (
    'John Doe',
    'john@example.com',
    ST_GeomFromText('${pointForQuery}', 4326),
    ST_GeomFromText('${polygonForQuery}', 4326)
);
`;

Now let's check if a coordinate is within a polygon and get the nearest users from a location, for this we can use a Spatialite functions ST_Within and ST_Distance

-- check if a point is within a polygon
SELECT id, name, email FROM users WHERE ST_Within(ST_GeomFromText('POINT(73.78291978700082 20.06382394128455)', 4326), area_polygon);

-- Find distance of all users from a particular point, can be used to get nearest or farthest users from a point
SELECT id, name, email, ST_Distance(location, ST_GeomFromText('POINT(73.78291978700082 20.06382394128455)', 4326)) as distance FROM users ORDER BY distance;

We can also use other functions as well, check all functions list available here.

After processing we also want to retrieve data from SQLite, for that we need to first convert data to JSON string and then JSON parse it, because Spatialite stores data in binary format.

SELECT id, name, email, AsGeoJSON(location) as location, AsGeoJSON(area_polygon) as area_polygon FROM users;

The above query will return location and area_polygon as string, that we need to JSON.parse to convert to JSON.

const selectquery = `SELECT id, name, email, AsGeoJSON(location) as location, AsGeoJSON(area_polygon) as area_polygon FROM users;`;

db.spatialite(function (err) { 
    db.all(selectquery, function (err, rows) {
        if (err) {
            console.log(err);
            return;
        } 
        rows.forEach(row => {
            row.location = JSON.parse(row.location);
            row.area_polygon = JSON.parse(row.area_polygon);
        });
        console.log(rows);
    });
});

If you get problems reading Saptialite docs or don't understand it, then you can also refer PostGIS docs for external reference, both Spatilaite and PostGIS have a similar API and Function names.

If you need more help, you can also join the google group of Spatialite. The group is active, and you can get answers quickly there.

You can always refer to Spatialite Docs for more advanced and complete tutorial. Apart from that this document provides more examples and use cases.

In the next blog I show how integrate Saptialite in Sequelize ORM, for a better developer experience and use all Sequelize features.