crossbind
GitHub

SpatiaLite for Windows

v5.1.0Windows

SpatiaLite 5.1.0 for native Node.js addons on Windows, precompiled for x64 and arm64, Windows 10 or later as @crossbind/port-spatialite-win32.

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

Install

shell
npm install @crossbind/port-spatialite-win32@beta
npm install --save-dev crossbind@beta @crossbind/core-embind-napi@beta
crossbind.config.js
import spatialiteWin32 from '@crossbind/port-spatialite-win32/crossbind.config.js';
 
export default {
dependencies: [spatialiteWin32],
paths: { config: import.meta.url },
};
shell
npx crossbind build -p win32

The addons and their loader land in dist; the Node.js playbook has the whole flow.

Usage

The examples the WebAssembly page runs, as Windows compiles them: the same headers and the same calls. They are checked on the WebAssembly build.

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)
`));
PRINTS
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));
PRINTS
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`);
PRINTS
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)}`);
PRINTS
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));
PRINTS
{"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]]}}]}

What is different on Windows

  • crossbind build -p win32 links one .node addon per architecture into dist, next to a loader, dist/<name>.native.cjs, that require and import both load. A plain crossbind build skips it.
  • await initNative() once, then call the classes: calls are synchronous, and no Worker is involved.
  • The library and the C++ runtime are linked into the addon statically; it needs Windows 10 or later and only DLLs that ship with Windows.
  • There is no m.FS: the C++ reads real paths, and data such as GDAL_DATA or proj.db is copied to dist/data.
  • The build runs in Docker on any host, a Mac included. worker_threads are not supported yet.

Other platforms

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
cURLExpatGDALGEOSGeoTIFFiconvLERClibjpeg-turbolibTIFFOpenSSLPROJSQLiteWebPzlibZstandard
Type to search every guide page and section.
↑↓ navigate↵ openesc close