SpatiaLite

v5.1.0Database

SpatiaLite 5.1.0, spatial SQL extension for SQLite, packaged by crossbind as @crossbind/port-spatialite and one package per target. Only a variant that is actually on npm beta is listed as published.

npm install @crossbind/port-spatialite-wasm@beta

npm ↗Port recipe and licence files ↗Upstream source 5.1.0 ↗

LIVE · 3 APPS · RUNS IN THIS TAB

SpatiaLite in your browser: spatial SQL, routing and GeoPackage, with no server

SpatiaLite adds PostGIS-style spatial SQL to SQLite: spatial indexes, joins, reprojection and routing in one database file. These apps run SpatiaLite 5.1.0, compiled by crossbind with GEOS, PROJ and SQLite: a SQL playground that draws what each query returns, shortest paths around the streets you close, and a GeoPackage that GDAL and QGIS open. PROJ's database, which ST_Transform reads, is 10.2 MB of the download. This build leaves out RTTOPO, which would make SpatiaLite GPL-only, so ST_MakeValid is GeosMakeValid here. The first run downloads 23.8 MB of WebAssembly once; every app on this page shares it, and nothing is uploaded.

APP 01

Spatial SQL, with the answer drawn as a map

PostGIS-style SQL in a SpatiaLite database inside this tab, over 2,000 points of interest generated around twelve Turkish cities. Pick a query or write your own: a spatial join through the R*Tree index, nearest neighbours with KNN2, buffers merged with ST_Union, Voronoi cells, a transform to UTM. Every geometry the query returns is drawn above its rows.

QUERIES

SQL

Tables: pois (id, kind, city, geom), hexagons (id, geom) and cities (id, name, geom), all in EPSG:4326, with spatial indexes on pois and hexagons.

Runs SpatiaLite, licensed MPL-1.1, GPL-2.0+ or LGPL-2.1+ at your choice, with GEOS and GNU libiconv under the LGPL: source · licence · build recipe

Run a query to see its rows and draw its geometries.

SHOW THE CODE

src/native/spatial_playground.h

// src/native/spatial_playground.h (excerpt)
SpatialPlayground() : connection(":memory:") {
    // spatial::Connection opened SQLite, then called spatialite_alloc_connection
    // and spatialite_init_ex; the sample SQL runs InitSpatialMetaData(1),
    // creates cities, pois and hexagons and calls CreateSpatialIndex
    spatial::exec(connection.get(), sample::PLAYGROUND);
}
 
std::string run(const std::string& sql, int limit) {
    // ...
    while (rest < end) {   // every statement in turn
        sqlite3_prepare_v2(db, rest, static_cast<int>(end - rest), &raw, &rest);
        // ...
        if (sqlite3_column_count(raw) > 0) rows = writer.rows(raw, limit);
    }
    // ...
}
 
// src/support/spatial_sql.h: a geometry cell goes back as GeoJSON
geometry(prepare(db, "SELECT AsGeoJSON(?1, 6), GeometryType(?1), SRID(?1)"))

main.js

const m = await initNative();
const playground = await new m.SpatialPlayground();
const result = JSON.parse(await playground.run(`
    SELECT h.id, count(*) AS pois, h.geom
    FROM hexagons AS h JOIN pois AS p ON p.ROWID IN (
        SELECT ROWID FROM SpatialIndex
        WHERE f_table_name = 'pois' AND search_frame = h.geom)
    AND ST_Contains(h.geom, p.geom)
    GROUP BY h.id ORDER BY pois DESC, h.id`, 2000));
// result.total: 53 hexagons with points
// result.rows[0]: [164, 497, { geometry: { type: 'Polygon', ... }, type: 'POLYGON', srid: 4326 }]

APP 02

Shortest paths in SQL, around the streets you close

CreateRouting turns a table of streets into a routing network, and a SELECT on it returns the shortest path. This grid has 400 intersections and 718 two-way streets that take 1 to 2 minutes each; the darker a street, the slower. Click two intersections to route between them, click a street to close it: SpatiaLite rebuilds the network and the query runs again, all inside SQLite in this tab.

The first route runs from the bottom-left corner to the top-right one.

The solid line is the fastest route, by travel time; the dashed one passes the fewest streets. Closed streets are dashed and thin.

Runs SpatiaLite, licensed MPL-1.1, GPL-2.0+ or LGPL-2.1+ at your choice, with GEOS and GNU libiconv under the LGPL: source · licence · build recipe

Route to draw the street grid; then click intersections and streets.

SHOW THE CODE

src/native/route_planner.h

// src/native/route_planner.h (excerpt)
// the open streets go into roads, a table with a LINESTRING column; then
build("SELECT CreateRouting('roads_data', 'roads_net', 'roads', 'node_from', 'node_to', "
      "'geom', 'cost', NULL, 1, 1, NULL, NULL, 1)");
 
std::string route(int from, int to, bool fewest) {
    // the VirtualRouting table takes only NodeFrom and NodeTo in WHERE
    const auto query = spatial::prepare(db, fewest ? "SELECT ... FROM hops_net WHERE NodeFrom = ?1 AND NodeTo = ?2"
        : "SELECT RouteRow, Role, LinkRowid, NodeFrom, NodeTo, Cost FROM roads_net WHERE NodeFrom = ?1 AND NodeTo = ?2");
    sqlite3_bind_int(query.get(), 1, from);
    sqlite3_bind_int(query.get(), 2, to);
    return spatial::ResultWriter(db).rows(query.get(), 1000);
}

main.js

const m = await initNative();
const planner = await new m.RoutePlanner();
const route = JSON.parse(await planner.route(1, 400, false));
// route.rows[0]: [0, 'Route', null, 1, 400, 47.5], 47.5 minutes in all
// route.rows.length - 1: 38 streets, one row each: [n, 'Link', street, from, to, minutes]
 
await planner.closeStreets('[381, 401, 39]');   // CreateRouting runs again without them
JSON.parse(await planner.route(1, 400, false)).rows[0][5];   // 47.75

APP 03

A GeoPackage that QGIS and GDAL open, written in this tab

GeoPackage is the OGC's one-file format for vector data, which GDAL and QGIS open as it is. This writes one with SpatiaLite's gpkg functions, from the generated points, their count per hexagon and the pins you drop on the map, then opens the file again to check it. GDAL's validate_gpkg.py passes the files it writes. Nothing leaves the tab until you download.

LAYERS

pois: the 2,000 generated pointshexbins: 53 hexagons with the number of points in each

pins: click the map to drop some

NEXT PIN'S NAME

Runs SpatiaLite, licensed MPL-1.1, GPL-2.0+ or LGPL-2.1+ at your choice, with GEOS and GNU libiconv under the LGPL: source · licence · build recipe

Build the file to see the map, then drop pins on it and build again.

SHOW THE CODE

src/native/geopackage_builder.h

// src/native/geopackage_builder.h (excerpt)
spatial::Connection connection(path);   // a new file in /memfs
spatial::exec(db, "SELECT gpkgCreateBaseTables()");
// ...two fixes GDAL's validate_gpkg.py asks for, then one layer:
spatial::exec(db, "CREATE TABLE pins (fid INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)");
// ...registered in gpkg_contents first, then
spatial::exec(db, "SELECT gpkgAddGeometryColumn('pins', 'geom', 'POINT', 0, 0, 4326)");
spatial::exec(db, "SELECT gpkgAddGeometryTriggers('pins', 'geom')");
spatial::exec(db, "SELECT gpkgAddSpatialIndex('pins', 'geom')");
// the pins arrive as JSON: [{ name, lon, lat }, ...]
"INSERT INTO pins (name, geom) SELECT json_extract(value, '$.name'), "
"gpkgMakePoint(json_extract(value, '$.lon'), json_extract(value, '$.lat'), 4326) FROM json_each(?1)"

main.js

const m = await initNative();
const path = `${await m.getRandomPath('/memfs')}/field.gpkg`;
const pins = [{ name: 'Meeting point', lon: 29, lat: 41 }, { name: 'Camp', lon: 32.5, lat: 39.5 }];
const report = JSON.parse(await m.GeoPackageBuilder.build(path, true, true, JSON.stringify(pins)));
// report.check: 1, CheckGeoPackageMetaData()
// report.layers: hexbins 53 polygons, pins 2 points, pois 2,000 points, all epsg:4326
const bytes = await m.getFileBytes(path);   // the .gpkg QGIS and GDAL open

Usage

The calls most SpatiaLite code makes, each a small C++ header crossbind binds and the JavaScript that uses it. Every example runs here in WebAssembly and prints what the site build checked; the same headers and calls work on Android and iOS.

Each example also has a JavaScript only tab: the same task with no C++ file, calling SpatiaLite's own headers from @crossbind/port-spatialite directly. All 5 work that way.

Imported straight from JavaScript, the headers need this configuration today; its comments say why.

crossbind.overrides.js

import spatialiteWasm from '@crossbind/port-spatialite-wasm/crossbind.config.js';
import sqlite3Wasm from '@crossbind/port-sqlite3-wasm/crossbind.config.js';
 
// Importing sqlite3.h and spatialite.h binds every function they declare, and these
// are not in the published libraries: sqlite3_mutex_held and sqlite3_mutex_notheld
// exist only without NDEBUG, the other sqlite3 ones only on Windows or behind
// SQLITE_ENABLE_* options, load_zip_* and load_XL only with minizip and FreeXL, and
// spatialite_set_verbode_mode is a misspelling the library never defines.
// The lists live here, not on the dependencies in crossbind.config.js, because sqlite3
// also comes in through SpatiaLite and PROJ and those copies win over one listed there.
// A `replace` reaches every copy and hands each package back with
// export.ignoredDeclarations added, so nothing is rebuilt.
const ignore = (config, names) => ({
    replace: { ...config, export: { ...config.export, ignoredDeclarations: names } },
});
 
export default {
    '@crossbind/port-sqlite3-wasm': ignore(sqlite3Wasm, [
        'sqlite3_mutex_held',
        'sqlite3_mutex_notheld',
        'sqlite3_win32_set_directory',
        'sqlite3_win32_set_directory8',
        'sqlite3_win32_set_directory16',
        'sqlite3_unlock_notify',
        'sqlite3_stmt_scanstatus',
        'sqlite3_stmt_scanstatus_v2',
        'sqlite3_stmt_scanstatus_reset',
        'sqlite3_snapshot_get',
        'sqlite3_snapshot_open',
        'sqlite3_snapshot_free',
        'sqlite3_snapshot_cmp',
        'sqlite3_snapshot_recover',
        'sqlite3_carray_bind',
        'sqlite3_carray_bind_v2',
    ]),
    '@crossbind/port-spatialite-wasm': ignore(spatialiteWasm, [
        'spatialite_set_verbode_mode',
        'load_zip_shapefile',
        'load_zip_dbf',
        'load_XL',
    ]),
};

Store points and measure the distance between them

The calls every SpatiaLite program starts with: spatialite_alloc_connection and spatialite_init_ex register the spatial SQL functions on a SQLite connection, InitSpatialMetaData creates the metadata tables and AddGeometryColumn adds a geometry column. GeomFromText reads WKT, and ST_Distance(a, b, 1) measures on the WGS 84 ellipsoid.

src/native/spatial_database.h

#pragma once
 
// spatialite.h uses SQLite's types without including sqlite3.h, so sqlite3.h comes first.
#include <sqlite3.h>
#include <spatialite.h>
 
#include <stdexcept>
#include <string>
 
// An in-memory SQLite database with SpatiaLite's spatial SQL functions registered on it.
class SpatialDatabase {
public:
    SpatialDatabase() {
        if (sqlite3_open(":memory:", &handle) != SQLITE_OK) throw std::runtime_error("cannot open the database");
        cache = spatialite_alloc_connection();
        spatialite_init_ex(handle, cache, 0);
    }
 
    ~SpatialDatabase() {
        sqlite3_close(handle);
        spatialite_cleanup_ex(cache);
    }
 
    static std::string version() { return spatialite_version(); }
 
    void exec(const std::string& sql) {
        char* message = nullptr;
        if (sqlite3_exec(handle, sql.c_str(), nullptr, nullptr, &message) == SQLITE_OK) return;
        const std::string reason = message ? message : sqlite3_errmsg(handle);
        sqlite3_free(message);
        throw std::runtime_error(reason);
    }
 
    // The first column of the first row as text, or "" when the query returns no row.
    std::string scalar(const std::string& sql) {
        sqlite3_stmt* statement = nullptr;
        if (sqlite3_prepare_v2(handle, sql.c_str(), -1, &statement, nullptr) != SQLITE_OK) throw std::runtime_error(sqlite3_errmsg(handle));
        const int step = sqlite3_step(statement);
        const unsigned char* text = step == SQLITE_ROW ? sqlite3_column_text(statement, 0) : nullptr;
        const std::string value = text ? reinterpret_cast<const char*>(text) : "";
        const std::string error = step == SQLITE_ROW || step == SQLITE_DONE ? "" : sqlite3_errmsg(handle);
        sqlite3_finalize(statement);
        if (!error.empty()) throw std::runtime_error(error);
        return value;
    }
 
private:
    sqlite3* handle = nullptr;
    void* cache = nullptr;
};

main.js

import { initNative, SpatialDatabase } from './native/spatial_database.h';
 
await initNative();
const db = await new SpatialDatabase();
await db.exec(`
    SELECT InitSpatialMetaData(1, 'WGS84');
    CREATE TABLE cities (name TEXT NOT NULL);
    SELECT AddGeometryColumn('cities', 'geom', 4326, 'POINT', 'XY');
    INSERT INTO cities (name, geom) VALUES
        ('Istanbul', GeomFromText('POINT(28.9784 41.0082)', 4326)),
        ('Ankara', GeomFromText('POINT(32.8597 39.9334)', 4326)),
        ('Izmir', GeomFromText('POINT(27.1428 38.4237)', 4326));
`);
console.log(await SpatialDatabase.version(), await db.scalar('SELECT count(*) FROM cities'));
console.log(await db.scalar(`
    SELECT group_concat(name || ' ' || km || ' km', ', ' ORDER BY km)
    FROM (SELECT name, CAST(Round(ST_Distance(geom, MakePoint(28.9784, 41.0082, 4326), 1) / 1000) AS INTEGER) AS km FROM cities)
`));

PRINTSfirst run downloads 23.8 MB

5.1.0 3
Istanbul 0 km, Izmir 327 km, Ankara 350 km

The same calls spatial_database.h makes, on sqlite3.h and spatialite.h as the ports ship them. The sqlite3 ** and sqlite3_stmt ** out-parameters are a slot from allocPointer read back with readPointerAt, sqlite3_column_text returns a pointer that readCString reads, and SQLITE_OK and SQLITE_ROW are macros, which do not bind, so their values are written out. The build also needs this module's crossbind.overrides.js: the two headers declare 20 functions the published libraries do not define, such as sqlite3_snapshot_get and load_XL, and the build stops until export.ignoredDeclarations leaves them out.

main.js

import {
    initNative, spatialite_version, spatialite_alloc_connection, spatialite_init_ex,
    spatialite_cleanup_ex,
} from '@crossbind/port-spatialite/spatialite.h';
import {
    sqlite3_open, sqlite3_exec, sqlite3_errmsg, sqlite3_prepare_v2, sqlite3_step,
    sqlite3_column_text, sqlite3_finalize, sqlite3_close, allocPointer, readPointerAt, readCString,
} from '@crossbind/port-sqlite3/sqlite3.h';
 
await initNative();
const SQLITE_OK = 0;
const SQLITE_ROW = 100;
const slot = await allocPointer(1);
if ((await sqlite3_open(':memory:', slot)) !== SQLITE_OK) throw new Error('cannot open the database');
const db = await readPointerAt(slot, 0);
const cache = await spatialite_alloc_connection();
await spatialite_init_ex(db, cache, 0);
const exec = async (sql) => {
    if ((await sqlite3_exec(db, sql, null, null, null)) !== SQLITE_OK) throw new Error(await sqlite3_errmsg(db));
};
const scalar = async (sql) => {
    if ((await sqlite3_prepare_v2(db, sql, -1, slot, null)) !== SQLITE_OK) throw new Error(await sqlite3_errmsg(db));
    const statement = await readPointerAt(slot, 0);
    const text = (await sqlite3_step(statement)) === SQLITE_ROW ? await sqlite3_column_text(statement, 0) : null;
    const value = text === null ? '' : await readCString(text);
    await sqlite3_finalize(statement);
    return value;
};
 
await exec(`
    SELECT InitSpatialMetaData(1, 'WGS84');
    CREATE TABLE cities (name TEXT NOT NULL);
    SELECT AddGeometryColumn('cities', 'geom', 4326, 'POINT', 'XY');
    INSERT INTO cities (name, geom) VALUES
        ('Istanbul', GeomFromText('POINT(28.9784 41.0082)', 4326)),
        ('Ankara', GeomFromText('POINT(32.8597 39.9334)', 4326)),
        ('Izmir', GeomFromText('POINT(27.1428 38.4237)', 4326));
`);
console.log(await spatialite_version(), await scalar('SELECT count(*) FROM cities'));
console.log(await scalar(`
    SELECT group_concat(name || ' ' || km || ' km', ', ' ORDER BY km)
    FROM (SELECT name, CAST(Round(ST_Distance(geom, MakePoint(28.9784, 41.0082, 4326), 1) / 1000) AS INTEGER) AS km FROM cities)
`));
await sqlite3_close(db);
await spatialite_cleanup_ex(cache);

PRINTSfirst run downloads 24.1 MB

5.1.0 3
Istanbul 0 km, Izmir 327 km, Ankara 350 km

Find places in a map view and the nearest ones with a spatial index

CreateSpatialIndex keeps an R*Tree of the bounding boxes of a geometry column, and queries reach it through virtual tables: SpatialIndex returns the rows inside a box such as the map view, and KNN2 the rows nearest to a point with their distance in metres.

src/native/place_index.h

#pragma once
 
// spatialite.h uses SQLite's types without including sqlite3.h, so sqlite3.h comes first.
#include <sqlite3.h>
#include <spatialite.h>
 
#include <memory>
#include <stdexcept>
#include <string>
 
// Places in a SpatiaLite table with a spatial index: CreateSpatialIndex keeps an R*Tree of every
// geometry's bounding box, and queries reach it through the SpatialIndex and KNN2 virtual tables.
class PlaceIndex {
public:
    PlaceIndex() {
        if (sqlite3_open(":memory:", &db) != SQLITE_OK) throw std::runtime_error("cannot open the database");
        cache = spatialite_alloc_connection();
        spatialite_init_ex(db, cache, 0);
        run("SELECT InitSpatialMetaData(1, 'WGS84')");
        run("CREATE TABLE places (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
        run("SELECT AddGeometryColumn('places', 'geom', 4326, 'POINT', 'XY')");
        run("SELECT CreateSpatialIndex('places', 'geom')");
    }
 
    ~PlaceIndex() {
        sqlite3_close(db);
        spatialite_cleanup_ex(cache);
    }
 
    int add(const std::string& name, double lon, double lat) {
        Statement insert = prepare("INSERT INTO places (name, geom) VALUES (?1, MakePoint(?2, ?3, 4326))");
        sqlite3_bind_text(insert.get(), 1, name.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_double(insert.get(), 2, lon);
        sqlite3_bind_double(insert.get(), 3, lat);
        if (sqlite3_step(insert.get()) != SQLITE_DONE) throw std::runtime_error(sqlite3_errmsg(db));
        return static_cast<int>(sqlite3_last_insert_rowid(db));
    }
 
    // The places inside a longitude/latitude box, by name: the rows the R*Tree finds in the box.
    std::string inView(double west, double south, double east, double north) {
        Statement query = prepare(
            "SELECT name FROM places WHERE ROWID IN (SELECT ROWID FROM SpatialIndex "
            "WHERE f_table_name = 'places' AND search_frame = BuildMbr(?1, ?2, ?3, ?4, 4326)) ORDER BY name");
        sqlite3_bind_double(query.get(), 1, west);
        sqlite3_bind_double(query.get(), 2, south);
        sqlite3_bind_double(query.get(), 3, east);
        sqlite3_bind_double(query.get(), 4, north);
        return lines(query.get());
    }
 
    // The `count` places nearest to a point, with their distance on the WGS 84 ellipsoid. KNN2 ranks
    // the places inside a square of plus or minus `radius` degrees around the point, so the radius
    // has to reach the farthest of them.
    std::string nearest(double lon, double lat, int count, double radius) {
        Statement query = prepare(
            "SELECT p.name || ' ' || CAST(Round(k.distance_m / 1000) AS INTEGER) || ' km' FROM KNN2 AS k "
            "JOIN places AS p ON p.id = k.fid WHERE k.f_table_name = 'places' "
            "AND k.ref_geometry = MakePoint(?1, ?2, 4326) AND k.radius = ?3 AND k.max_items = ?4 ORDER BY k.pos");
        sqlite3_bind_double(query.get(), 1, lon);
        sqlite3_bind_double(query.get(), 2, lat);
        sqlite3_bind_double(query.get(), 3, radius);
        sqlite3_bind_int(query.get(), 4, count);
        return lines(query.get());
    }
 
private:
    using Statement = std::unique_ptr<sqlite3_stmt, int (*)(sqlite3_stmt*)>;
 
    Statement prepare(const char* sql) {
        sqlite3_stmt* statement = nullptr;
        if (sqlite3_prepare_v2(db, sql, -1, &statement, nullptr) != SQLITE_OK) throw std::runtime_error(sqlite3_errmsg(db));
        return Statement(statement, sqlite3_finalize);
    }
 
    void run(const char* sql) {
        char* message = nullptr;
        if (sqlite3_exec(db, sql, nullptr, nullptr, &message) == SQLITE_OK) return;
        const std::string reason = message ? message : sqlite3_errmsg(db);
        sqlite3_free(message);
        throw std::runtime_error(reason);
    }
 
    // The first column of every row, joined with commas.
    std::string lines(sqlite3_stmt* statement) {
        std::string joined;
        int result;
        while ((result = sqlite3_step(statement)) == SQLITE_ROW) {
            joined += (joined.empty() ? "" : ", ") + std::string(reinterpret_cast<const char*>(sqlite3_column_text(statement, 0)));
        }
        if (result != SQLITE_DONE) throw std::runtime_error(sqlite3_errmsg(db));
        return joined;
    }
 
    sqlite3* db = nullptr;
    void* cache = nullptr;
};

main.js

import { initNative, PlaceIndex } from './native/place_index.h';
 
await initNative();
const places = await new PlaceIndex();
const cities = [
    ['Istanbul', 28.9784, 41.0082],
    ['Ankara', 32.8597, 39.9334],
    ['Izmir', 27.1428, 38.4237],
    ['Bursa', 29.061, 40.1885],
    ['Antalya', 30.7133, 36.8969],
    ['Trabzon', 39.7168, 41.0027],
    ['Konya', 32.4846, 37.8746],
    ['Edirne', 26.5557, 41.6771],
];
for (const [name, lon, lat] of cities) await places.add(name, lon, lat);
console.log(await places.inView(26, 38, 31, 42));
const eskisehir = [30.5206, 39.7767];
console.log(await places.nearest(...eskisehir, 3, 5));

PRINTSfirst run downloads 23.8 MB

Bursa, Edirne, Istanbul, Izmir
Bursa 133 km, Istanbul 189 km, Ankara 201 km

Numbers bind as they are; text needs SQLITE_TRANSIENT, ((sqlite3_destructor_type)-1), a macro. The Number -1 fails with Cannot pass "-1" as a NativePointer, so the pointer is read back from a slot holding -1, and SQLite copies each name as it does for the C++. null (SQLITE_STATIC) is no stand-in: the binding frees its copy of a JavaScript string when the call returns, and three strings bound that way read back as two empty strings and garbage.

main.js

import {
    initNative, spatialite_alloc_connection, spatialite_init_ex, spatialite_cleanup_ex,
} from '@crossbind/port-spatialite/spatialite.h';
import {
    sqlite3_open, sqlite3_exec, sqlite3_errmsg, sqlite3_prepare_v2, sqlite3_bind_text,
    sqlite3_bind_double, sqlite3_bind_int, sqlite3_step, sqlite3_reset, sqlite3_column_text,
    sqlite3_finalize, sqlite3_close, allocPointer, readPointerAt, writeNumberAt, readCString,
} from '@crossbind/port-sqlite3/sqlite3.h';
 
await initNative();
// sqlite3.h's constants are macros without bindings. SQLITE_TRANSIENT is ((sqlite3_destructor_type)-1):
// a pointer with the address -1, which a fresh pointer slot holding -1 reads back.
const SQLITE_OK = 0;
const SQLITE_ROW = 100;
const SQLITE_DONE = 101;
const minusOne = await allocPointer(1);
await writeNumberAt(minusOne, 0, 'int32', -1);
const SQLITE_TRANSIENT = await readPointerAt(minusOne, 0);
const slot = await allocPointer(1);
if ((await sqlite3_open(':memory:', slot)) !== SQLITE_OK) throw new Error('cannot open the database');
const db = await readPointerAt(slot, 0);
const cache = await spatialite_alloc_connection();
await spatialite_init_ex(db, cache, 0);
const setup = `
    SELECT InitSpatialMetaData(1, 'WGS84');
    CREATE TABLE places (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
    SELECT AddGeometryColumn('places', 'geom', 4326, 'POINT', 'XY');
    SELECT CreateSpatialIndex('places', 'geom');
`;
if ((await sqlite3_exec(db, setup, null, null, null)) !== SQLITE_OK) throw new Error(await sqlite3_errmsg(db));
const prepare = async (sql) => {
    if ((await sqlite3_prepare_v2(db, sql, -1, slot, null)) !== SQLITE_OK) throw new Error(await sqlite3_errmsg(db));
    return readPointerAt(slot, 0);
};
const lines = async (statement) => {
    const values = [];
    while ((await sqlite3_step(statement)) === SQLITE_ROW) values.push(await readCString(await sqlite3_column_text(statement, 0)));
    await sqlite3_finalize(statement);
    return values.join(', ');
};
 
const cities = [
    ['Istanbul', 28.9784, 41.0082],
    ['Ankara', 32.8597, 39.9334],
    ['Izmir', 27.1428, 38.4237],
    ['Bursa', 29.061, 40.1885],
    ['Antalya', 30.7133, 36.8969],
    ['Trabzon', 39.7168, 41.0027],
    ['Konya', 32.4846, 37.8746],
    ['Edirne', 26.5557, 41.6771],
];
const insert = await prepare('INSERT INTO places (name, geom) VALUES (?1, MakePoint(?2, ?3, 4326))');
for (const [name, lon, lat] of cities) {
    await sqlite3_bind_text(insert, 1, name, -1, SQLITE_TRANSIENT);
    await sqlite3_bind_double(insert, 2, lon);
    await sqlite3_bind_double(insert, 3, lat);
    if ((await sqlite3_step(insert)) !== SQLITE_DONE) throw new Error(await sqlite3_errmsg(db));
    await sqlite3_reset(insert);
}
await sqlite3_finalize(insert);
 
const inView = await prepare(`
    SELECT name FROM places WHERE ROWID IN (SELECT ROWID FROM SpatialIndex
    WHERE f_table_name = 'places' AND search_frame = BuildMbr(?1, ?2, ?3, ?4, 4326)) ORDER BY name
`);
await sqlite3_bind_double(inView, 1, 26);
await sqlite3_bind_double(inView, 2, 38);
await sqlite3_bind_double(inView, 3, 31);
await sqlite3_bind_double(inView, 4, 42);
console.log(await lines(inView));
 
const nearest = await prepare(`
    SELECT p.name || ' ' || CAST(Round(k.distance_m / 1000) AS INTEGER) || ' km' FROM KNN2 AS k
    JOIN places AS p ON p.id = k.fid WHERE k.f_table_name = 'places'
    AND k.ref_geometry = MakePoint(?1, ?2, 4326) AND k.radius = ?3 AND k.max_items = ?4 ORDER BY k.pos
`);
const eskisehir = [30.5206, 39.7767];
await sqlite3_bind_double(nearest, 1, eskisehir[0]);
await sqlite3_bind_double(nearest, 2, eskisehir[1]);
await sqlite3_bind_double(nearest, 3, 5);
await sqlite3_bind_int(nearest, 4, 3);
console.log(await lines(nearest));
await sqlite3_close(db);
await spatialite_cleanup_ex(cache);

PRINTSfirst run downloads 24.1 MB

Bursa, Edirne, Istanbul, Izmir
Bursa 133 km, Istanbul 189 km, Ankara 201 km

Measure areas and distances in metres

SpatiaLite measures in the units of the coordinates, so ST_Area of a longitude/latitude polygon comes out in square degrees. ST_Transform to a projected CRS, here UTM zone 35N (EPSG:32635), gives square metres and metres, stretched the further a shape lies from the zone; ST_Distance(a, b, 1) measures on the ellipsoid with no projection at all.

src/native/measure.h

#pragma once
 
// spatialite.h uses SQLite's types without including sqlite3.h, so sqlite3.h comes first.
#include <sqlite3.h>
#include <spatialite.h>
 
#include <memory>
#include <stdexcept>
#include <string>
 
// Measurements of shapes written as WKT in longitude and latitude (EPSG:4326). SpatiaLite measures
// in the units of the coordinates, so the planar methods first transform the shape to the CRS they
// are given: a projected one such as UTM measures in metres.
class Measure {
public:
    Measure() {
        if (sqlite3_open(":memory:", &db) != SQLITE_OK) throw std::runtime_error("cannot open the database");
        cache = spatialite_alloc_connection();
        spatialite_init_ex(db, cache, 0);
        char* message = nullptr;
        if (sqlite3_exec(db, "SELECT InitSpatialMetaData(1, 'WGS84')", nullptr, nullptr, &message) == SQLITE_OK) return;
        const std::string reason = message ? message : sqlite3_errmsg(db);
        sqlite3_free(message);
        throw std::runtime_error(reason);
    }
 
    ~Measure() {
        sqlite3_close(db);
        spatialite_cleanup_ex(cache);
    }
 
    double area(const std::string& wkt, int srid) {
        Statement query = prepare("SELECT ST_Area(ST_Transform(GeomFromText(?1, 4326), ?2))");
        sqlite3_bind_text(query.get(), 1, wkt.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(query.get(), 2, srid);
        return number(query.get());
    }
 
    double distance(const std::string& a, const std::string& b, int srid) {
        Statement query = prepare("SELECT ST_Distance(ST_Transform(GeomFromText(?1, 4326), ?3), ST_Transform(GeomFromText(?2, 4326), ?3))");
        sqlite3_bind_text(query.get(), 1, a.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(query.get(), 2, b.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(query.get(), 3, srid);
        return number(query.get());
    }
 
    // On the WGS 84 ellipsoid, in metres, with no projection in between.
    double geodesicDistance(const std::string& a, const std::string& b) {
        Statement query = prepare("SELECT ST_Distance(GeomFromText(?1, 4326), GeomFromText(?2, 4326), 1)");
        sqlite3_bind_text(query.get(), 1, a.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(query.get(), 2, b.c_str(), -1, SQLITE_TRANSIENT);
        return number(query.get());
    }
 
private:
    using Statement = std::unique_ptr<sqlite3_stmt, int (*)(sqlite3_stmt*)>;
 
    Statement prepare(const char* sql) {
        sqlite3_stmt* statement = nullptr;
        if (sqlite3_prepare_v2(db, sql, -1, &statement, nullptr) != SQLITE_OK) throw std::runtime_error(sqlite3_errmsg(db));
        return Statement(statement, sqlite3_finalize);
    }
 
    // SpatiaLite answers NULL, not an error, for WKT it cannot read or an SRID it does not know.
    double number(sqlite3_stmt* statement) {
        if (sqlite3_step(statement) != SQLITE_ROW) throw std::runtime_error(sqlite3_errmsg(db));
        if (sqlite3_column_type(statement, 0) == SQLITE_NULL) throw std::invalid_argument("cannot measure: check the WKT and the SRID");
        return sqlite3_column_double(statement, 0);
    }
 
    sqlite3* db = nullptr;
    void* cache = nullptr;
};

main.js

import { initNative, Measure } from './native/measure.h';
 
await initNative();
const measure = await new Measure();
const block = 'POLYGON((28.97 41.00, 28.99 41.00, 28.99 41.01, 28.97 41.01, 28.97 41.00))';
console.log(`${(await measure.area(block, 4326)).toFixed(6)} square degrees, ${Math.round(await measure.area(block, 32635))} m²`);
const istanbul = 'POINT(28.9784 41.0082)';
const ankara = 'POINT(32.8597 39.9334)';
const projected = await measure.distance(istanbul, ankara, 32635);
const geodesic = await measure.geodesicDistance(istanbul, ankara);
console.log(`${Math.round(projected)} m in UTM zone 35N, ${Math.round(geodesic)} m on the ellipsoid`);

PRINTSfirst run downloads 23.8 MB

0.000200 square degrees, 1868349 m²
350462 m in UTM zone 35N, 350082 m on the ellipsoid

Each method of measure.h becomes a JavaScript function over the same calls, and the WKT stays a plain string, bound with SQLITE_TRANSIENT read from a slot holding -1. SQLITE_NULL is a macro as well, so the check for a shape SpatiaLite cannot measure spells out its value, 5.

main.js

import {
    initNative, spatialite_alloc_connection, spatialite_init_ex, spatialite_cleanup_ex,
} from '@crossbind/port-spatialite/spatialite.h';
import {
    sqlite3_open, sqlite3_exec, sqlite3_errmsg, sqlite3_prepare_v2, sqlite3_bind_text,
    sqlite3_bind_int, sqlite3_step, sqlite3_column_type, sqlite3_column_double, sqlite3_finalize,
    sqlite3_close, allocPointer, readPointerAt, writeNumberAt,
} from '@crossbind/port-sqlite3/sqlite3.h';
 
await initNative();
// sqlite3.h's constants are macros without bindings. SQLITE_TRANSIENT is ((sqlite3_destructor_type)-1):
// a pointer with the address -1, which a fresh pointer slot holding -1 reads back.
const SQLITE_OK = 0;
const SQLITE_ROW = 100;
const SQLITE_NULL = 5;
const minusOne = await allocPointer(1);
await writeNumberAt(minusOne, 0, 'int32', -1);
const SQLITE_TRANSIENT = await readPointerAt(minusOne, 0);
const slot = await allocPointer(1);
if ((await sqlite3_open(':memory:', slot)) !== SQLITE_OK) throw new Error('cannot open the database');
const db = await readPointerAt(slot, 0);
const cache = await spatialite_alloc_connection();
await spatialite_init_ex(db, cache, 0);
if ((await sqlite3_exec(db, "SELECT InitSpatialMetaData(1, 'WGS84')", null, null, null)) !== SQLITE_OK) throw new Error(await sqlite3_errmsg(db));
const prepare = async (sql) => {
    if ((await sqlite3_prepare_v2(db, sql, -1, slot, null)) !== SQLITE_OK) throw new Error(await sqlite3_errmsg(db));
    return readPointerAt(slot, 0);
};
const number = async (statement) => {
    if ((await sqlite3_step(statement)) !== SQLITE_ROW) throw new Error(await sqlite3_errmsg(db));
    if ((await sqlite3_column_type(statement, 0)) === SQLITE_NULL) throw new Error('cannot measure: check the WKT and the SRID');
    const value = await sqlite3_column_double(statement, 0);
    await sqlite3_finalize(statement);
    return value;
};
const area = async (wkt, srid) => {
    const statement = await prepare('SELECT ST_Area(ST_Transform(GeomFromText(?1, 4326), ?2))');
    await sqlite3_bind_text(statement, 1, wkt, -1, SQLITE_TRANSIENT);
    await sqlite3_bind_int(statement, 2, srid);
    return number(statement);
};
const distance = async (a, b, srid) => {
    const statement = await prepare('SELECT ST_Distance(ST_Transform(GeomFromText(?1, 4326), ?3), ST_Transform(GeomFromText(?2, 4326), ?3))');
    await sqlite3_bind_text(statement, 1, a, -1, SQLITE_TRANSIENT);
    await sqlite3_bind_text(statement, 2, b, -1, SQLITE_TRANSIENT);
    await sqlite3_bind_int(statement, 3, srid);
    return number(statement);
};
const geodesicDistance = async (a, b) => {
    const statement = await prepare('SELECT ST_Distance(GeomFromText(?1, 4326), GeomFromText(?2, 4326), 1)');
    await sqlite3_bind_text(statement, 1, a, -1, SQLITE_TRANSIENT);
    await sqlite3_bind_text(statement, 2, b, -1, SQLITE_TRANSIENT);
    return number(statement);
};
 
const block = 'POLYGON((28.97 41.00, 28.99 41.00, 28.99 41.01, 28.97 41.01, 28.97 41.00))';
console.log(`${(await area(block, 4326)).toFixed(6)} square degrees, ${Math.round(await area(block, 32635))} m²`);
const istanbul = 'POINT(28.9784 41.0082)';
const ankara = 'POINT(32.8597 39.9334)';
const projected = await distance(istanbul, ankara, 32635);
const geodesic = await geodesicDistance(istanbul, ankara);
console.log(`${Math.round(projected)} m in UTM zone 35N, ${Math.round(geodesic)} m on the ellipsoid`);
await sqlite3_close(db);
await spatialite_cleanup_ex(cache);

PRINTSfirst run downloads 24.1 MB

0.000200 square degrees, 1868349 m²
350462 m in UTM zone 35N, 350082 m on the ellipsoid

Reproject coordinates between EPSG codes

ST_Transform moves a geometry to another reference system through PROJ, which reads the proj.db the build preloads. InitSpatialMetaData(1) registers the 6,559 EPSG codes SpatiaLite knows in spatial_ref_sys, where their names are too.

src/native/reproject.h

#pragma once
 
// spatialite.h uses SQLite's types without including sqlite3.h, so sqlite3.h comes first.
#include <sqlite3.h>
#include <spatialite.h>
 
#include <memory>
#include <stdexcept>
#include <string>
 
// Coordinates from one EPSG code to another with ST_Transform, which runs PROJ. InitSpatialMetaData(1)
// fills spatial_ref_sys with the 6,559 reference systems SpatiaLite knows; PROJ reads its proj.db for
// how to get from one to the other.
class Reprojector {
public:
    Reprojector() {
        if (sqlite3_open(":memory:", &db) != SQLITE_OK) throw std::runtime_error("cannot open the database");
        cache = spatialite_alloc_connection();
        spatialite_init_ex(db, cache, 0);
        char* message = nullptr;
        if (sqlite3_exec(db, "SELECT InitSpatialMetaData(1)", nullptr, nullptr, &message) == SQLITE_OK) return;
        const std::string reason = message ? message : sqlite3_errmsg(db);
        sqlite3_free(message);
        throw std::runtime_error(reason);
    }
 
    ~Reprojector() {
        sqlite3_close(db);
        spatialite_cleanup_ex(cache);
    }
 
    std::string transform(const std::string& wkt, int from, int to) {
        Statement query = prepare("SELECT AsText(ST_Transform(GeomFromText(?1, ?2), ?3))");
        sqlite3_bind_text(query.get(), 1, wkt.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(query.get(), 2, from);
        sqlite3_bind_int(query.get(), 3, to);
        return text(query.get(), "cannot transform: check the WKT and both EPSG codes");
    }
 
    std::string srsName(int srid) {
        Statement query = prepare("SELECT ref_sys_name FROM spatial_ref_sys WHERE srid = ?1");
        sqlite3_bind_int(query.get(), 1, srid);
        return text(query.get(), "unknown EPSG code");
    }
 
private:
    using Statement = std::unique_ptr<sqlite3_stmt, int (*)(sqlite3_stmt*)>;
 
    Statement prepare(const char* sql) {
        sqlite3_stmt* statement = nullptr;
        if (sqlite3_prepare_v2(db, sql, -1, &statement, nullptr) != SQLITE_OK) throw std::runtime_error(sqlite3_errmsg(db));
        return Statement(statement, sqlite3_finalize);
    }
 
    // The first column of the only row; no row, or NULL, becomes `missing` as an exception.
    std::string text(sqlite3_stmt* statement, const char* missing) {
        const int result = sqlite3_step(statement);
        if (result != SQLITE_ROW && result != SQLITE_DONE) throw std::runtime_error(sqlite3_errmsg(db));
        const unsigned char* value = result == SQLITE_ROW ? sqlite3_column_text(statement, 0) : nullptr;
        if (!value) throw std::invalid_argument(missing);
        return reinterpret_cast<const char*>(value);
    }
 
    sqlite3* db = nullptr;
    void* cache = nullptr;
};

main.js

import { initNative, Reprojector } from './native/reproject.h';
 
await initNative();
const reprojector = await new Reprojector();
const istanbul = 'POINT(28.9784 41.0082)';
for (const srid of [3857, 32635]) console.log(`${await reprojector.srsName(srid)}: ${await reprojector.transform(istanbul, 4326, srid)}`);
const utm = await reprojector.transform(istanbul, 4326, 32635);
console.log(`back to ${await reprojector.srsName(4326)}: ${await reprojector.transform(utm, 32635, 4326)}`);

PRINTSfirst run downloads 23.8 MB

WGS 84 / Pseudo-Mercator: POINT(3225860.732004 5013551.237223)
WGS 84 / UTM zone 35N: POINT(666370.505017 4541552.487191)
back to WGS 84: POINT(28.9784 41.0082)

ST_Transform reads PROJ's proj.db from the data preloaded with the module, the same file the C++ build ships, with no configuration of its own. The WKT is bound with SQLITE_TRANSIENT, read from a slot holding -1, and every answer comes back from sqlite3_column_text through readCString.

main.js

import {
    initNative, spatialite_alloc_connection, spatialite_init_ex, spatialite_cleanup_ex,
} from '@crossbind/port-spatialite/spatialite.h';
import {
    sqlite3_open, sqlite3_exec, sqlite3_errmsg, sqlite3_prepare_v2, sqlite3_bind_text,
    sqlite3_bind_int, sqlite3_step, sqlite3_column_text, sqlite3_finalize, sqlite3_close,
    allocPointer, readPointerAt, writeNumberAt, readCString,
} from '@crossbind/port-sqlite3/sqlite3.h';
 
await initNative();
// sqlite3.h's constants are macros without bindings. SQLITE_TRANSIENT is ((sqlite3_destructor_type)-1):
// a pointer with the address -1, which a fresh pointer slot holding -1 reads back.
const SQLITE_OK = 0;
const SQLITE_ROW = 100;
const minusOne = await allocPointer(1);
await writeNumberAt(minusOne, 0, 'int32', -1);
const SQLITE_TRANSIENT = await readPointerAt(minusOne, 0);
const slot = await allocPointer(1);
if ((await sqlite3_open(':memory:', slot)) !== SQLITE_OK) throw new Error('cannot open the database');
const db = await readPointerAt(slot, 0);
const cache = await spatialite_alloc_connection();
await spatialite_init_ex(db, cache, 0);
if ((await sqlite3_exec(db, 'SELECT InitSpatialMetaData(1)', null, null, null)) !== SQLITE_OK) throw new Error(await sqlite3_errmsg(db));
const prepare = async (sql) => {
    if ((await sqlite3_prepare_v2(db, sql, -1, slot, null)) !== SQLITE_OK) throw new Error(await sqlite3_errmsg(db));
    return readPointerAt(slot, 0);
};
const text = async (statement, missing) => {
    const value = (await sqlite3_step(statement)) === SQLITE_ROW ? await sqlite3_column_text(statement, 0) : null;
    const result = value === null ? null : await readCString(value);
    await sqlite3_finalize(statement);
    if (result === null) throw new Error(missing);
    return result;
};
const transform = async (wkt, from, to) => {
    const statement = await prepare('SELECT AsText(ST_Transform(GeomFromText(?1, ?2), ?3))');
    await sqlite3_bind_text(statement, 1, wkt, -1, SQLITE_TRANSIENT);
    await sqlite3_bind_int(statement, 2, from);
    await sqlite3_bind_int(statement, 3, to);
    return text(statement, 'cannot transform: check the WKT and both EPSG codes');
};
const srsName = async (srid) => {
    const statement = await prepare('SELECT ref_sys_name FROM spatial_ref_sys WHERE srid = ?1');
    await sqlite3_bind_int(statement, 1, srid);
    return text(statement, 'unknown EPSG code');
};
 
const istanbul = 'POINT(28.9784 41.0082)';
for (const srid of [3857, 32635]) console.log(`${await srsName(srid)}: ${await transform(istanbul, 4326, srid)}`);
const utm = await transform(istanbul, 4326, 32635);
console.log(`back to ${await srsName(4326)}: ${await transform(utm, 32635, 4326)}`);
await sqlite3_close(db);
await spatialite_cleanup_ex(cache);

PRINTSfirst run downloads 24.1 MB

WGS 84 / Pseudo-Mercator: POINT(3225860.732004 5013551.237223)
WGS 84 / UTM zone 35N: POINT(666370.505017 4541552.487191)
back to WGS 84: POINT(28.9784 41.0082)

Read GeoJSON in and write a FeatureCollection out

GeomFromGeoJSON reads a GeoJSON geometry into a SpatiaLite geometry, and AsGeoJSON writes one back rounded to the decimals you ask for. SQLite's JSON functions wrap the rows into a FeatureCollection that Leaflet, MapLibre or OpenLayers load as it is.

src/native/geojson_layer.h

#pragma once
 
// spatialite.h uses SQLite's types without including sqlite3.h, so sqlite3.h comes first.
#include <sqlite3.h>
#include <spatialite.h>
 
#include <memory>
#include <stdexcept>
#include <string>
 
// Features that arrive as GeoJSON geometries, stored as SpatiaLite geometries and read back as one
// GeoJSON FeatureCollection, the shape web map libraries load.
class GeoJsonLayer {
public:
    GeoJsonLayer() {
        if (sqlite3_open(":memory:", &db) != SQLITE_OK) throw std::runtime_error("cannot open the database");
        cache = spatialite_alloc_connection();
        spatialite_init_ex(db, cache, 0);
        run("SELECT InitSpatialMetaData(1, 'WGS84')");
        run("CREATE TABLE features (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
        run("SELECT AddGeometryColumn('features', 'geom', 4326, 'GEOMETRY', 'XY')");
    }
 
    ~GeoJsonLayer() {
        sqlite3_close(db);
        spatialite_cleanup_ex(cache);
    }
 
    // GeomFromGeoJSON returns NULL for text that is not a GeoJSON geometry, so nothing is inserted
    // then and the call throws. GeoJSON is longitude and latitude, EPSG:4326.
    int add(const std::string& name, const std::string& geometry) {
        Statement insert = prepare(
            "INSERT INTO features (name, geom) SELECT ?1, shape FROM "
            "(SELECT SetSRID(GeomFromGeoJSON(?2), 4326) AS shape) WHERE shape IS NOT NULL");
        sqlite3_bind_text(insert.get(), 1, name.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(insert.get(), 2, geometry.c_str(), -1, SQLITE_TRANSIENT);
        if (sqlite3_step(insert.get()) != SQLITE_DONE) throw std::runtime_error(sqlite3_errmsg(db));
        if (sqlite3_changes(db) == 0) throw std::invalid_argument("not a GeoJSON geometry: " + name);
        return static_cast<int>(sqlite3_last_insert_rowid(db));
    }
 
    // Every feature in insertion order, coordinates rounded to `decimals`; SQLite's JSON functions
    // build the collection around the geometries AsGeoJSON writes.
    std::string featureCollection(int decimals) {
        Statement query = prepare(
            "SELECT json_object('type', 'FeatureCollection', 'features', json_group_array(json_object("
            "'type', 'Feature', 'properties', json_object('name', name), 'geometry', json(AsGeoJSON(geom, ?1))))) "
            "FROM (SELECT name, geom FROM features ORDER BY id)");
        sqlite3_bind_int(query.get(), 1, decimals);
        if (sqlite3_step(query.get()) != SQLITE_ROW) throw std::runtime_error(sqlite3_errmsg(db));
        return reinterpret_cast<const char*>(sqlite3_column_text(query.get(), 0));
    }
 
private:
    using Statement = std::unique_ptr<sqlite3_stmt, int (*)(sqlite3_stmt*)>;
 
    Statement prepare(const char* sql) {
        sqlite3_stmt* statement = nullptr;
        if (sqlite3_prepare_v2(db, sql, -1, &statement, nullptr) != SQLITE_OK) throw std::runtime_error(sqlite3_errmsg(db));
        return Statement(statement, sqlite3_finalize);
    }
 
    void run(const char* sql) {
        char* message = nullptr;
        if (sqlite3_exec(db, sql, nullptr, nullptr, &message) == SQLITE_OK) return;
        const std::string reason = message ? message : sqlite3_errmsg(db);
        sqlite3_free(message);
        throw std::runtime_error(reason);
    }
 
    sqlite3* db = nullptr;
    void* cache = nullptr;
};

main.js

import { initNative, GeoJsonLayer } from './native/geojson_layer.h';
 
await initNative();
const layer = await new GeoJsonLayer();
await layer.add('Galata Tower', '{"type":"Point","coordinates":[28.974128,41.025638]}');
await layer.add('Galata Bridge', '{"type":"LineString","coordinates":[[28.973364,41.019634],[28.971392,41.024021]]}');
console.log(await layer.featureCollection(5));

PRINTSfirst run downloads 23.8 MB

{"type":"FeatureCollection","features":[{"type":"Feature","properties":{"name":"Galata Tower"},"geometry":{"type":"Point","coordinates":[28.97413,41.02564]}},{"type":"Feature","properties":{"name":"Galata Bridge"},"geometry":{"type":"LineString","coordinates":[[28.97336,41.01963],[28.97139,41.02402]]}}]}

The same statements as geojson_layer.h, with sqlite3_changes telling a geometry GeomFromGeoJSON rejected from a stored one. The name and the GeoJSON are bound with SQLITE_TRANSIENT, read from a slot holding -1, and the FeatureCollection comes back from sqlite3_column_text through readCString.

main.js

import {
    initNative, spatialite_alloc_connection, spatialite_init_ex, spatialite_cleanup_ex,
} from '@crossbind/port-spatialite/spatialite.h';
import {
    sqlite3_open, sqlite3_exec, sqlite3_errmsg, sqlite3_prepare_v2, sqlite3_bind_text,
    sqlite3_bind_int, sqlite3_step, sqlite3_changes, sqlite3_column_text, sqlite3_finalize,
    sqlite3_close, allocPointer, readPointerAt, writeNumberAt, readCString,
} from '@crossbind/port-sqlite3/sqlite3.h';
 
await initNative();
// sqlite3.h's constants are macros without bindings. SQLITE_TRANSIENT is ((sqlite3_destructor_type)-1):
// a pointer with the address -1, which a fresh pointer slot holding -1 reads back.
const SQLITE_OK = 0;
const SQLITE_ROW = 100;
const SQLITE_DONE = 101;
const minusOne = await allocPointer(1);
await writeNumberAt(minusOne, 0, 'int32', -1);
const SQLITE_TRANSIENT = await readPointerAt(minusOne, 0);
const slot = await allocPointer(1);
if ((await sqlite3_open(':memory:', slot)) !== SQLITE_OK) throw new Error('cannot open the database');
const db = await readPointerAt(slot, 0);
const cache = await spatialite_alloc_connection();
await spatialite_init_ex(db, cache, 0);
const setup = `
    SELECT InitSpatialMetaData(1, 'WGS84');
    CREATE TABLE features (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
    SELECT AddGeometryColumn('features', 'geom', 4326, 'GEOMETRY', 'XY');
`;
if ((await sqlite3_exec(db, setup, null, null, null)) !== SQLITE_OK) throw new Error(await sqlite3_errmsg(db));
const prepare = async (sql) => {
    if ((await sqlite3_prepare_v2(db, sql, -1, slot, null)) !== SQLITE_OK) throw new Error(await sqlite3_errmsg(db));
    return readPointerAt(slot, 0);
};
const add = async (name, geometry) => {
    const insert = await prepare(`
        INSERT INTO features (name, geom) SELECT ?1, shape FROM
        (SELECT SetSRID(GeomFromGeoJSON(?2), 4326) AS shape) WHERE shape IS NOT NULL
    `);
    await sqlite3_bind_text(insert, 1, name, -1, SQLITE_TRANSIENT);
    await sqlite3_bind_text(insert, 2, geometry, -1, SQLITE_TRANSIENT);
    const error = (await sqlite3_step(insert)) === SQLITE_DONE ? null : await sqlite3_errmsg(db);
    await sqlite3_finalize(insert);
    if (error) throw new Error(error);
    if ((await sqlite3_changes(db)) === 0) throw new Error(`not a GeoJSON geometry: ${name}`);
};
 
await add('Galata Tower', '{"type":"Point","coordinates":[28.974128,41.025638]}');
await add('Galata Bridge', '{"type":"LineString","coordinates":[[28.973364,41.019634],[28.971392,41.024021]]}');
const collection = await prepare(`
    SELECT json_object('type', 'FeatureCollection', 'features', json_group_array(json_object(
    'type', 'Feature', 'properties', json_object('name', name), 'geometry', json(AsGeoJSON(geom, ?1)))))
    FROM (SELECT name, geom FROM features ORDER BY id)
`);
await sqlite3_bind_int(collection, 1, 5);
if ((await sqlite3_step(collection)) !== SQLITE_ROW) throw new Error(await sqlite3_errmsg(db));
console.log(await readCString(await sqlite3_column_text(collection, 0)));
await sqlite3_finalize(collection);
await sqlite3_close(db);
await spatialite_cleanup_ex(cache);

PRINTSfirst run downloads 24.1 MB

{"type":"FeatureCollection","features":[{"type":"Feature","properties":{"name":"Galata Tower"},"geometry":{"type":"Point","coordinates":[28.97413,41.02564]}},{"type":"Feature","properties":{"name":"Galata Bridge"},"geometry":{"type":"LineString","coordinates":[[28.97336,41.01963],[28.97139,41.02402]]}}]}

Add it to your project

One package per platform: install the ones you build for and list each in crossbind.config.js; crossbind compiles only the one that matches the build target. Your C++ goes in src/native, next to the headers it binds. Libraries explains the whole flow.

shell

npm install @crossbind/port-spatialite-wasm@beta

crossbind.config.js

import spatialiteWasm from '@crossbind/port-spatialite-wasm/crossbind.config.js';
 
export default {
    dependencies: [spatialiteWasm],
    paths: { config: import.meta.url },
};

Platforms

Platform	Runs in	Builds	Page
WebAssembly	browsers, Node.js and edge runtimes	`wasm32`, single-threaded and multi-threaded	SpatiaLite for WebAssembly
Android	React Native apps on Android	`arm64-v8a` devices and the `x86_64` emulator	SpatiaLite for Android
iOS	React Native apps on iOS	`arm64` devices and simulators	SpatiaLite for iOS
macOS	native Node.js addons and Electron on macOS	`arm64` and `x64`, macOS 11 or later	SpatiaLite for macOS
Linux	native Node.js addons on Linux	`x64` and `arm64`, glibc 2.28 or later	SpatiaLite for Linux
Windows	native Node.js addons on Windows	`x64` and `arm64`, Windows 10 or later	SpatiaLite for Windows
WASI	command-line programs under wasmtime	`wasm32-wasip3`, single-threaded	SpatiaLite for WASI

Packages

Target	Package	npm `beta`
Meta package	`@crossbind/port-spatialite`	`2.0.0-beta.62`
Web and Node.js	`@crossbind/port-spatialite-wasm`	`2.0.0-beta.62`
WASI library	`@crossbind/port-spatialite-wasi`	`2.0.0-beta.62`
Android	`@crossbind/port-spatialite-android`	`2.0.0-beta.62`
iOS	`@crossbind/port-spatialite-ios`	`2.0.0-beta.62`
macOS	`@crossbind/port-spatialite-darwin`	`2.0.0-beta.62`
Linux	`@crossbind/port-spatialite-linux`	`2.0.0-beta.62`
Linux (musl)	`@crossbind/port-spatialite-linuxmusl`	not published
Windows	`@crossbind/port-spatialite-win32`	`2.0.0-beta.62`

Licence

npm license field of @crossbind/port-spatialite: MPL-1.1 OR GPL-2.0-or-later OR LGPL-2.1-or-later.
The licence files that ship with the package, and the port recipe, are in the port directory.

Facts on this page come from the port manifests in the repository and from what npm served on beta when the site was built. See the Libraries guide for the full consumer flow.

MORE LIBRARIES

cURL Expat GDAL GEOS GeoTIFF iconv LERC libjpeg-turbo libTIFF OpenSSL PROJ SQLite WebP zlib Zstandard