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

> Explore GeoLibre's SQL Workspace, integrating DuckDB, PGlite (PostGIS), and Apache Sedona for browser-based spatial query execution directly on map vector layers.

- Repository: [Open Geospatial Solutions/GeoLibre](https://github.com/opengeos/GeoLibre)
- Tags: deep-dive
- Published: 2026-08-03

---

**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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/packages/plugins/src/plugins/maplibre-duckdb.ts), while the processing layer executes statements through [`packages/processing/src/runner.ts`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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:

```typescript
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:

```typescript
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:

```typescript
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:

```tsx
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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/packages/processing/src/runner.ts)** – Dispatches user SQL to appropriate back-ends based on view name metadata.

- **[`packages/plugins/src/plugins/maplibre-duckdb.ts`](https://github.com/opengeos/GeoLibre/blob/main/packages/plugins/src/plugins/maplibre-duckdb.ts)** – Bridges host `DuckDBControl` into the MapLibre layer system.

- **[`packages/core/src/constants.ts`](https://github.com/opengeos/GeoLibre/blob/main/packages/core/src/constants.ts)** – Stores the `SQL_QUERY_SOURCE_KIND` constant used throughout the codebase.

- **[`tests/sql-query-layer.test.ts`](https://github.com/opengeos/GeoLibre/blob/main/tests/sql-query-layer.test.ts)** – Verifies round-tripping of SQL queries through layer metadata.

- **[`tests/sql-completion.test.ts`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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.