SQL Workspace Capabilities in GeoLibre: DuckDB, PostGIS (PGlite), and Apache Sedona Integration

GeoLibre's SQL Workspace unifies DuckDB-WASM, PGlite (PostGIS), and Apache Sedona (CereusDB) into a single browser-based SQL environment, allowing users to execute spatial queries across vector layers using ST_ functions without leaving the map interface.*

The opengeos/GeoLibre project provides a comprehensive SQL Workspace that transforms geospatial projects into interactive relational databases. This capability enables analysts to run complex spatial SQL queries directly in the browser using multiple back-end engines. Understanding these SQL Workspace capabilities in GeoLibre helps developers choose the right engine for vector processing, spatial analysis, and large-scale distributed operations.

The Three SQL Back-Ends in GeoLibre

DuckDB-WASM with Spatial Extension

The primary engine is DuckDB-WASM, an in-browser SQLite-compatible database with the Spatial extension enabled. According to the source code in packages/processing/src/types.ts, GeoLibre creates a queryable DuckDB source identified by the SQL_QUERY_SOURCE_KIND constant. This back-end supports fast SQL-over-vector-layers, geometry functions like ST_Union and ST_Buffer, H3 hexagon generation, and on-the-fly file conversion into temporary DuckDB tables. The host injects a DuckDBControl instance via packages/plugins/src/plugins/maplibre-duckdb.ts, while the processing layer executes statements through packages/processing/src/runner.ts.

PGlite (PostGIS) for Advanced Spatial Functions

For operations requiring true PostGIS capabilities, GeoLibre integrates PGlite, a WebAssembly-compiled PostgreSQL with PostGIS extensions. As implemented in packages/processing/src/sidecar-client.ts (lines 1020-1088), the system registers a PGlite driver within the DuckDB Spatial stack. When queries reference the reserved view name pglite, the side-car translates statements to a PGlite session, executes them, and streams results back as temporary tables. This is essential when you need PostGIS-specific functions that DuckDB Spatial does not expose.

Apache Sedona (CereusDB) for Distributed Processing

For large-scale operations, Apache Sedona via CereusDB provides a distributed spatial-SQL engine built on Spark. The side-car client in packages/processing/src/sidecar-client.ts exposes runSedonaSQL and getSedonaStatus helpers (lines 1050-1086). The UI creates Sedona SQL layers using the same SQL_QUERY_SOURCE_KIND metadata, but execution routes through an HTTP side-car rather than the browser-based engine. This architecture enables massive spatial joins and raster-vector operations without loading entire datasets into browser memory.

How the SQL Workspace Architecture Works

The SQL Workspace follows a four-stage pipeline defined across packages/processing/src/runner.ts and related modules.

1. Layer Registration – When adding vector files, GeoLibre registers them as temporary DuckDB tables or PGlite views, storing table names in layer metadata under SQL_QUERY_SOURCE_KIND (defined in packages/core/src/constants.ts).

2. SQL Editor Interface – The UI provides a text editor bound to the active project. Users write SQL referencing registered tables, calling ST_* geometry functions, or invoking the pglite view. The editor dispatches statements to the processing runner.

3. Execution Dispatch – The runner checks the query's view name metadata. If it ends with :pglite, it forwards to the PGlite driver; if it contains the sedona: prefix, it calls runSedonaSQL; otherwise, it executes via DuckDB-WASM.

4. Result Handling – Result sets materialize as new temporary tables (DuckDB or Sedona) and automatically render as new vector layers via addGeoJsonLayer.

All three engines implement a common capability interface defined in packages/processing/src/types.ts. When capabilities are missing (e.g., Sedona side-car unreachable), the UI disables corresponding menu entries for graceful degradation.

Implementing SQL Queries in GeoLibre

Querying DuckDB-Backed Layers

To execute spatial aggregations using DuckDB-WASM:

import { runSQLQuery } from "@geolibre/processing";

// "roads" layer automatically registered as DuckDB table
const sql = `
  SELECT
    ST_Union(geom) AS geom,
    COUNT(*) AS road_cnt
  FROM roads
  WHERE highway = 'primary'
  GROUP BY ST_Buffer(geom, 0.01)
`;
const result = await runSQLQuery(sql);
// Returns temporary DuckDB table, displayed as new layer

Executing PostGIS Functions via PGlite

Access PostGIS-specific functions using the pglite view prefix:

import { runSQLQuery } from "@geolibre/processing";

const sql = `
  SELECT
    ST_ClosestPoint(geom, ST_MakePoint(-122.45, 37.75)) AS nearest,
    ST_Distance(geom, ST_MakePoint(-122.45, 37.75)) AS dist_m
  FROM pglite.parcels   -- Forces PGlite engine execution
  WHERE ST_Contains(geom, ST_MakePoint(-122.45, 37.75))
`;
const result = await runSQLQuery(sql);

Running Distributed Jobs with Apache Sedona

For large-scale spatial joins using the Sedona side-car:

import { runSedonaSQL } from "@geolibre/processing";

const sql = `
  SELECT
    ST_Intersection(a.geom, b.geom) AS intersect_geom,
    a.id AS a_id,
    b.id AS b_id
  FROM parcels a, parcels b
  WHERE a.id <> b.id AND ST_Intersects(a.geom, b.geom)
`;
const sedonaResult = await runSedonaSQL(sql);
// Results streamed back and added as vector layer

Adding SQL Layers from the UI

Integrate SQL Workspace functionality into React components:

import { useAddLayer } from "@geolibre/ui";

function AddSQLLayerButton() {
  const addLayer = useAddLayer();
  const handleClick = async () => {
    const sql = "SELECT * FROM my_table WHERE population > 10000";
    await addLayer({ sourceKind: "SQL_QUERY", sqlQuery: sql });
  };
  return <button onClick={handleClick}>Add SQL Layer</button>;
}

Key Source Files and Implementation Details

The SQL Workspace implementation spans several critical modules:

• packages/processing/src/types.ts – Defines SQL_QUERY_SOURCE_KIND and the DuckDB-WASM capability interface (lines 46-62).

• packages/processing/src/sidecar-client.ts – Implements HTTP side-car for Sedona SQL and PGlite driver integration (lines 1020-1088 for PGlite, lines 1050-1086 for Sedona).

• packages/processing/src/runner.ts – Dispatches user SQL to appropriate back-ends based on view name metadata.

• packages/plugins/src/plugins/maplibre-duckdb.ts – Bridges host DuckDBControl into the MapLibre layer system.

• packages/core/src/constants.ts – Stores the SQL_QUERY_SOURCE_KIND constant used throughout the codebase.

• tests/sql-query-layer.test.ts – Verifies round-tripping of SQL queries through layer metadata.

• tests/sql-completion.test.ts – Tests autocomplete for DuckDB-Spatial and PostGIS functions.

Summary

• GeoLibre's SQL Workspace provides unified access to DuckDB-WASM, PGlite (PostGIS), and Apache Sedona (CereusDB) through a common interface.
• The system automatically registers vector layers as temporary SQL tables using the SQL_QUERY_SOURCE_KIND metadata constant.
• DuckDB-WASM handles standard spatial operations and H3 hexagon generation entirely within the browser.
• PGlite offers true PostGIS functions when DuckDB Spatial capabilities are insufficient, accessed via the pglite view prefix.
• Apache Sedona enables distributed processing of massive datasets via HTTP side-car proxy, invoked through runSedonaSQL.
• Execution dispatch in packages/processing/src/runner.ts routes queries based on view name prefixes (:pglite or sedona:).

Frequently Asked Questions

What is the difference between DuckDB and PGlite in GeoLibre?

DuckDB-WASM provides an in-browser, SQLite-compatible engine optimized for fast analytical queries and basic spatial functions via the Spatial extension. PGlite is a WebAssembly-compiled PostgreSQL with full PostGIS support, enabling advanced spatial operations like ST_ClosestPoint that DuckDB may not implement. Use DuckDB for standard operations and PGlite when you require specific PostGIS functions or PostgreSQL syntax.

How does GeoLibre handle large datasets with Apache Sedona?

GeoLibre delegates large-scale processing to Apache Sedona through an HTTP side-car implemented in packages/processing/src/sidecar-client.ts. The runSedonaSQL function proxies queries to a Spark-based backend (CereusDB), streaming results back without loading entire datasets into browser memory. This architecture supports massive spatial joins and raster-vector operations that would crash a browser-based engine.

Can I mix DuckDB and PostGIS functions in the same query?

No, you cannot mix functions in a single query statement. GeoLibre routes queries to specific engines based on the view name prefix. Queries referencing pglite.tablename execute in the PGlite engine, while standard table references process through DuckDB-WASM. However, you can chain operations by saving intermediate results from one engine as temporary tables, then querying them from the other.

Is the SQL Workspace available offline?

DuckDB-WASM and PGlite both function entirely within the browser using WebAssembly, making them available offline once the application is loaded. However, Apache Sedona requires an active connection to the HTTP side-car proxy (CereusDB), which runs on a remote server. If the Sedona side-car is unreachable, the UI disables Sedona-specific menu entries while maintaining full DuckDB and PGlite functionality.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}