How GeoLibre Integrates Apache Sedona (CereusDB) into Its SQL Workspace

GeoLibre's SQL Workspace leverages Apache Sedona (CereusDB) as a Rust-backed spatial engine by converting GeoJSON layers into temporary database views, executing spatial SQL queries via a FastAPI backend, and returning results as both tabular data and GeoJSON for map rendering.

The open-source GeoLibre desktop application provides a dedicated SQL Workspace where users can execute spatial queries against loaded vector data. This feature is powered by Apache Sedona (CereusDB), the single-node, Rust-based engine of the Apache Sedona project. The integration bridges the frontend TypeScript client with the Rust database through a Python FastAPI sidecar, enabling seamless spatial analysis without external database dependencies.

Architecture Overview

The integration spans three distinct layers: a FastAPI backend that exposes HTTP endpoints, a Python operations module that manages the Sedona runtime, and a TypeScript frontend that orchestrates user interactions.

Backend FastAPI Layer

The backend exposes two primary endpoints under /sql/* in backend/geolibre_server/geolibre_server/app/sql.py. The /sql/status endpoint checks whether the optional apache-sedona[db] package is available by calling sedona_ops.sedonadb_import_error(). The /sql/run endpoint accepts POST requests containing SQL strings and layer definitions, delegates execution to sedona_ops.run_sql, and maps exceptions to appropriate HTTP status codes.

Sedona Operations Module

backend/geolibre_server/geolibre_server/sedona_ops.py contains the core integration logic. It lazily imports sedona.db via _import_sedona(), converts incoming GeoJSON layers into GeoPandas GeoDataFrames, and registers them as named views within a CereusDB connection. The module enforces a MAX_FEATURES limit of 50,000 features per layer and validates view names against the ^[a-z_][a-z0-9_]*$ regex to prevent injection attacks.

Frontend TypeScript Client

The frontend implementation in apps/geolibre-desktop/src/lib/sedona-workspace.ts handles user input, serializes map layers as GeoJSON FeatureCollections, and POSTs the payload to /sql/run. It processes the SqlQueryResult response to populate the data grid and render result geometries on the map canvas.

End-to-End Query Flow

When a user runs a spatial query, the system executes the following pipeline:

1. Frontend Request Construction: The TypeScript client gathers active layers and constructs a JSON payload containing the SQL string and layer definitions.

   {
     "sql": "SELECT name, ST_Area(geom) AS area FROM parcels WHERE area > 1000",
     "layers": [
       {
         "name": "parcels",
         "geojson": {
           "type": "FeatureCollection",
           "features": [
             { "type": "Feature", "properties": { "name": "Block A" }, "geometry": {...} }
           ]
         }
       }
     ]
   }

2. HTTP Transport: sedona-workspace.ts transmits this payload via POST to the /sql/run endpoint.

3. View Registration: In sedona_ops.run_sql(), each layer is converted to a GeoDataFrame and registered as a temporary view using connection.create_data_frame(gdf).to_view(name).

4. SQL Execution: The function executes the query via connection.sql(sql) against the Rust-backed CereusDB engine.

5. Result Formatting: The module converts results to a Pandas DataFrame, extracts geometry as both WKT (for tabular display) and GeoJSON (for mapping), and returns a structured JSON response.

6. Frontend Rendering: The client parses the columns, rows, and geojson fields to update the workspace grid and add the result layer to the map.

Safety Mechanisms and Limits

GeoLibre implements several safeguards to ensure robust operation:

• Lazy Import Strategy: The sedona.db module is imported only when _import_sedona() is first called, allowing the server to start normally if the optional dependency is missing. The /sql/status endpoint surfaces any import errors to inform the UI.

• Feature Count Guard: The system enforces MAX_FEATURES = 50000 (mirroring vector_ops.MAX_FEATURES). Exceeding this limit raises SqlInputTooLarge, triggering an HTTP 413 Payload Too Large response.

• View Name Validation: Only identifiers matching ^[a-z_][a-z0-9_]*$ are accepted (_SAFE_VIEW_NAME). This regex validation prevents SQL injection through malicious layer names.

Implementation Examples

Backend Python Usage

You can invoke the Sedona integration directly from Python code:

from geolibre_server import sedona_ops

# Define layers as GeoJSON FeatureCollections

layers = [
    {
        "name": "roads",
        "geojson": {
            "type": "FeatureCollection",
            "features": [
                {
                    "type": "Feature",
                    "properties": {"id": 1, "name": "Main St"},
                    "geometry": {"type": "LineString", "coordinates": [[0, 0], [1, 1]]}
                }
            ]
        }
    }
]

# Execute spatial SQL

sql = "SELECT id, ST_Length(geom) AS length FROM roads WHERE ST_Length(geom) > 500"
result = sedona_ops.run_sql(sql, layers)

# Access results

print(result["columns"])  # ['id', 'length']

print(result["rows"][0]) # {'id': 1, 'length': 1.414...}

print(result["geojson"]["features"][0]["geometry"])  # GeoJSON geometry

Frontend TypeScript Usage

The TypeScript client constructs requests and handles responses:

import { post } from '@/utils/http';

interface LayerInfo {
  name: string;
  geojson: GeoJSON.FeatureCollection;
}

async function executeSpatialQuery(sql: string, layers: LayerInfo[]) {
  const response = await post('/sql/run', { sql, layers });
  return response.data; // Returns SqlQueryResult
}

// Example usage
const result = await executeSpatialQuery(
  'SELECT name, ST_Area(geom) AS area FROM parcels WHERE area > 1000',
  [{ name: 'parcels', geojson: currentMapLayers }]
);

// Update UI components
updateDataGrid(result.columns, result.rows);
addGeoJsonLayer(result.geojson);

Key Source Files

File Path	Purpose	GitHub Link
backend/geolibre_server/geolibre_server/app/sql.py	FastAPI routes for SQL status checks and query execution	View on GitHub
backend/geolibre_server/geolibre_server/sedona_ops.py	Core wrapper for Sedona DB operations, including lazy imports and view management	View on GitHub
apps/geolibre-desktop/src/lib/sedona-workspace.ts	Frontend client for building requests and processing query results	View on GitHub
backend/geolibre_server/tests/test_sql.py	Test suite verifying integration behavior and error handling	View on GitHub

Summary

• GeoLibre integrates Apache Sedona (CereusDB) through a three-tier architecture: FastAPI backend, Python operations module, and TypeScript frontend client.
• The sedona_ops.run_sql function in backend/geolibre_server/geolibre_server/sedona_ops.py converts GeoJSON layers to temporary views and executes spatial SQL against the Rust engine.
• Safety features include lazy importing of sedona.db, a 50,000-feature limit, and regex-based view name validation (^[a-z_][a-z0-9_]*$).
• The /sql/run endpoint accepts layer payloads and returns structured results with both tabular data and GeoJSON geometries for immediate visualization.

Frequently Asked Questions

What is Apache Sedona CereusDB?

Apache Sedona CereusDB is the single-node, Rust-based execution engine within the Apache Sedona ecosystem. It provides high-performance spatial SQL capabilities without requiring a distributed cluster, making it ideal for desktop GIS applications like GeoLibre.

How does GeoLibre handle large datasets in the SQL Workspace?

GeoLibre enforces a MAX_FEATURES limit of 50,000 features per layer in the SQL Workspace. If a user attempts to query a layer exceeding this threshold, the system raises a SqlInputTooLarge exception and returns an HTTP 413 Payload Too Large status, preventing memory exhaustion in the Rust-backed engine.

Can I use the SQL Workspace without installing Apache Sedona?

Yes, but with limited functionality. The backend uses lazy imports to allow the server to start without apache-sedona[db] installed. The /sql/status endpoint reports availability, and the frontend disables SQL Workspace features when the dependency is missing. To enable full functionality, install the optional apache-sedona[db] extra in the Python environment.

How are SQL injections prevented in the SQL Workspace?

GeoLibre prevents injection through strict validation of view names using the _SAFE_VIEW_NAME regex (^[a-z_][a-z0-9_]*$). Only lowercase letters, numbers, and underscores are permitted, and names must start with a letter or underscore. User-supplied SQL runs within the sandboxed CereusDB connection with no access to the host filesystem or network.

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