How GeoLibre's SQL Workspace Supports DuckDB: In-Browser Spatial SQL Processing

GeoLibre's SQL Workspace leverages DuckDB-WASM with the Spatial extension to execute client-side spatial SQL queries, automatically rewriting cloud URLs, registering map layers as temporary tables, and converting geometries to GeoJSON for visualization.

GeoLibre is an open-source geospatial platform that brings advanced spatial analysis to both browser and desktop environments. The GeoLibre SQL Workspace provides a sandboxed DuckDB environment where users can query vector layers, remote Parquet files, and cloud storage without installing database software. This article examines how the opengeos/GeoLibre repository implements this architecture, referencing the actual source code in apps/geolibre-desktop/src/lib/sql-workspace.ts.

Architecture Overview

The SQL Workspace builds a sandboxed DuckDB instance per query session, loading the Spatial extension to handle geometry operations. This design enables zero-install data access while maintaining cross-platform parity between the web build and the Tauri desktop application.

The system processes queries through a nine-stage pipeline that handles everything from input sanitization to geometry conversion. Each stage is implemented as a discrete function in the core workspace file, ensuring robust error handling and recovery from known DuckDB-WASM limitations.

Query Processing Pipeline

Statement Cleaning and Safety Checks

Before execution, user-provided SQL undergoes strict validation. The cleanStatement function strips trailing semicolons and comments, while containsMultipleStatements verifies that only a single statement is submitted. This prevents batch injection attacks and ensures predictable behavior in the single-query execution model.

// Located in apps/geolibre-desktop/src/lib/sql-workspace.ts
cleanStatement(sql: string): string
containsMultipleStatements(sql: string): boolean

Cloud URL Normalization

Public cloud URLs using s3://, gs://, or az:// protocols are automatically rewritten to HTTPS equivalents. The rewriteCloudUrls function, assisted by cloudUrlToHttps, transforms these schemes so DuckDB can stream data via HTTP range requests rather than relying on native cloud protocol support.

// Lines 48-66 in sql-workspace.ts
rewriteCloudUrls(sql: string): string
cloudUrlToHttps(url: string): string

Bare Source Wrapping

Bare file paths or URLs following FROM or JOIN clauses are automatically wrapped in appropriate DuckDB table functions. The rewriteBareSources function uses the DATA_SOURCE_READERS registry to determine whether to apply read_parquet, read_csv_auto, read_json_auto, or ST_Read based on file extensions.

// Lines 29-38 and 6-12 in sql-workspace.ts
rewriteBareSources(sql: string): string
// DATA_SOURCE_READERS maps extensions to reader functions

Remote File Registration

For every remote reader call (e.g., read_parquet('https://...')), the workspace registers a Virtual File System (VFS) handle using db.registerFileURL. This implementation in registerRemoteSources enables byte-range streaming without downloading entire files, bypassing the unreliable in-WASM httpfs path that can trigger memory issues.

Spatial Extension Warm-Up

The ensureSpatialExtension function loads DuckDB's spatial capabilities before query execution. To prevent the "stoi: no conversion" bug that can poison a DuckDB-WASM instance, the workspace includes warm-up logic in runSqlStatementOnce that reads a tiny default Parquet file (SAMPLE_DATASET_URL) if the user's query does not already trigger a remote read.

Layer Table Registration

Every loaded vector layer containing an in-memory GeoJSON collection is registered as a temporary DuckDB table via ST_Read. The registerLayerTables function derives table names from layer names using assignTableNames and sanitizeTableName, making map data queryable as standard SQL tables.

-- Query a loaded layer automatically exposed as table 'countries'
SELECT name, continent, pop_est
FROM countries
ORDER BY pop_est DESC
LIMIT 10;

Query Description and Schema Detection

Before final execution, describeQuery runs a lightweight DESCRIBE SELECT * FROM (<statement>) probe to determine column order and detect geometry columns using isGeometryColumnType. This metadata gathering ensures proper handling of spatial data in subsequent processing stages.

Geometry Handling and Conversion

When geometry columns are detected, the workspace re-runs the query with specialized formatting functions. ST_AsText generates human-readable WKT for the UI grid, while ST_AsGeoJSON produces machine-readable geometry for the "Add as layer" functionality. The system uses a hidden GEOMETRY_JSON_COLUMN constant to manage this dual representation, filtering the raw GeoJSON from the grid display while using it to build valid GeoJSON FeatureCollections.

// Geometry wrapping logic in runSqlStatementOnce (lines 84-100)
ST_AsText(geom) // For display
ST_AsGeoJSON(geom) // For map integration

Result Normalization and Export

Query results undergo normalization via normalizeValue and normalizeRow to ensure JSON and CSV safety. The workspace provides resultToCsv for spreadsheet export and rowsToFeatureCollection for converting tabular results with geometries into standard GeoJSON FeatureCollections suitable for map visualization.

Error Recovery and Robustness

If a remote-read query triggers the "stoi: no conversion" error—a known DuckDB-WASM bug that renders the instance unusable—the workspace implements automatic recovery. The runSqlQuery function catches this specific error, invokes resetSqlDatabase to instantiate a fresh DuckDB connection, and retries the query exactly once. This ensures users experience seamless query execution even when underlying WASM limitations occur.

Practical SQL Examples

The SQL Workspace supports querying remote files directly without download:

-- Query remote Parquet with automatic streaming
SELECT name, population, geom
FROM https://data.source.coop/giswqs/opengeos/countries.parquet
LIMIT 100;

Cloud storage URLs are rewritten automatically for HTTPFS compatibility:

-- S3 URLs are transparently converted to HTTPS
SELECT *
FROM read_parquet('s3://open-data-bucket/sample.parquet')
WHERE population > 1_000_000;

Local files and map layers coexist in the same query namespace:

-- Join a map layer with a remote CSV
SELECT a.name, b.elevation
FROM map_layer a
JOIN read_csv_auto('https://example.com/data.csv') b
ON a.id = b.site_id;

Summary

• Zero-install architecture: GeoLibre's SQL Workspace uses DuckDB-WASM to execute spatial SQL entirely in the browser or desktop client without external dependencies.
• Automatic URL handling: Cloud URLs (s3://, gs://, az://) are rewritten to HTTPS, and bare sources are wrapped in appropriate reader functions (read_parquet, ST_Read, etc.).
• Layer integration: Map layers are registered as temporary tables via registerLayerTables, enabling seamless SQL queries against active visualization data.
• Robust geometry processing: The workspace automatically detects geometry columns and dual-formats them as WKT for display and GeoJSON for map integration.
• Error resilience: Automatic detection and recovery from the "stoi: no conversion" DuckDB-WASM bug ensures query reliability through instance reset and retry logic.

Frequently Asked Questions

What file formats does GeoLibre SQL Workspace support via DuckDB?

GeoLibre supports Parquet, CSV, JSON, and GeoJSON files through automatic reader detection. The rewriteBareSources function in sql-workspace.ts maps file extensions to DuckDB functions including read_parquet, read_csv_auto, read_json_auto, and ST_Read for spatial formats.

How does GeoLibre handle cloud storage URLs in SQL queries?

The workspace automatically rewrites cloud-specific protocols (s3://, gs://, az://) to HTTPS equivalents using rewriteCloudUrls and cloudUrlToHttps. This enables DuckDB's HTTPFS extension to stream data via byte-range requests without requiring cloud SDK credentials in the client.

What happens if a DuckDB query fails in GeoLibre's SQL Workspace?

The system implements specific error recovery for the "stoi: no conversion" DuckDB-WASM bug. When detected in runSqlQuery, the workspace calls resetSqlDatabase to create a fresh DuckDB instance and retries the query once. Other SQL errors are returned to the user with standard DuckDB error messages.

Can I query existing map layers using SQL in GeoLibre?

Yes. The registerLayerTables function automatically exposes every loaded vector layer as a temporary DuckDB table using ST_Read. Table names are derived from layer names via sanitizeTableName, allowing you to reference active map data directly in SQL statements alongside remote files.

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