spatial-lakehouse-mcp

Iceberg-native geospatial MCP server powered by DuckDB.

Provides 18 tools for catalog discovery, spatial queries, analysis, and data management over an Apache Iceberg lakehouse. An LLM agent connects via the Model Context Protocol (MCP) and can explore schemas, run spatial SQL, perform point-in-polygon aggregation, export GeoJSON, and more — all through a single DuckDB connection.

Architecture

MCP Client (LLM)
    ↕  (Streamable HTTP, port 8082)
spatial-lakehouse-mcp
    ↕
DuckDB (in-process, :memory:)
    ├── iceberg extension  → LakeKeeper REST Catalog (port 8181)
    ├── httpfs extension   → Garage S3 (port 3900)
    └── spatial extension  → ST_* geospatial functions

Key design decision: DuckDB v1.4+ natively supports ATTACH to Iceberg REST Catalogs. Once attached, the catalog appears as a regular DuckDB database — SHOW ALL TABLES, DESCRIBE, iceberg_snapshots(), time travel, and full SQL (including spatial functions) all work through a single connection. No pyiceberg dependency.

Infrastructure Stack

Component	Technology	Default Port
Object Storage	Garage (S3-compatible)	3900
Iceberg Catalog	LakeKeeper (REST)	8181
Catalog Metadata	PostgreSQL	5432
MCP Server	This project (FastMCP + DuckDB)	8082

Tools (18 total)

Catalog Discovery

Tool	Description
list_namespaces	List schemas in the Iceberg catalog
list_tables	List tables, optionally filtered by namespace
describe_table	Column names, types, geometry detection
table_snapshots	Snapshot history for time-travel queries
search_tables	Search tables by name, column, or geometry presence

Spatial Queries

Tool	Description
query	Read-only SQL with spatial functions
spatial_filter	Structured spatial predicates (intersects, within, bbox, within_distance)
nearest_features	K-nearest-neighbor search
get_bbox	Bounding box / spatial extent
time_travel_query	Query at a specific Iceberg snapshot or timestamp
multi_table_query	Cross-table analytics with safety rails

Spatial Analysis

Tool	Description
spatial_join	Join two tables on spatial predicates (intersects, contains, dwithin, etc.)
aggregate_within	Point-in-polygon aggregation (count, sum, avg, min, max, stddev)
buffer_analysis	Buffer zones with optional dissolve (union)

Data Management

Tool	Description
sample_data	Preview rows from a table
table_stats	Row counts, column stats, geometry summary
export_geojson	Export as GeoJSON FeatureCollection

System

Tool	Description
health_check	Connection, extension, and catalog status

Project Structure

spatial-lakehouse-mcp/
├── pyproject.toml
├── Dockerfile
├── .env.example
├── validate_stack.py              # Pre-flight infrastructure validation
├── src/
│   └── spatial_lakehouse_mcp/
│       ├── __init__.py
│       ├── server.py              # FastMCP server + 18 tool definitions
│       ├── config.py              # Pydantic settings (SLM_ env prefix)
│       ├── engine.py              # DuckDB connection + catalog attachment
│       └── validators.py          # SQL safety + input validation
└── tests/
    ├── conftest.py                # Shared fixtures (local DuckDB test data)
    └── test_tools.py              # 21 tests across all phases

Quick Start (Local Development)

Prerequisites

• Python 3.11+
• A running lakehouse stack (LakeKeeper + Garage + PostgreSQL)

1. Install

git clone https://github.com/aoneil42/Spatial-Lakehouse-MCP.git
cd Spatial-Lakehouse-MCP

python -m venv .venv
source .venv/bin/activate
pip install -e ".[dev]"

2. Configure

cp .env.example .env

Edit .env with your infrastructure credentials:

# Required — Garage S3 credentials
GARAGE_KEY_ID=your_garage_key
GARAGE_SECRET_KEY=your_garage_secret
SLM_S3_ENDPOINT=localhost:3900

# Required — LakeKeeper catalog
SLM_CATALOG_URI=http://localhost:8181
SLM_CATALOG_WAREHOUSE=lakehouse

# Auth (leave empty for LakeKeeper allowall dev mode)
SLM_CATALOG_TOKEN=

3. Validate Stack Connectivity

Before running the server, verify the MCP server can reach your infrastructure:

python validate_stack.py

Expected output:

DuckDB Python version: 1.4.4

  [1/3 Garage S3 + httpfs] PASS
      5 files found, first file has 4096 rows

  [2/3 LakeKeeper ATTACH] PASS
      ATTACH OK, 7 table(s) found, lakehouse.colorado.lines has 30000 rows

  [3/3 Extension coexistence] PASS
      All 3 extensions loaded, ST_Point → POINT (-104.99 39.74)

Results: 3 passed, 0 failed

4. Run Tests

pytest -v

Tests run against local in-memory DuckDB tables (no infrastructure required).

5. Start the Server

python -m spatial_lakehouse_mcp.server

The server starts on http://0.0.0.0:8082 using Streamable HTTP transport.

Docker Deployment

docker build -t spatial-lakehouse-mcp .
docker run --env-file .env -p 8082:8082 spatial-lakehouse-mcp

Or add to an existing docker-compose stack:

services:
  mcp-server:
    build: .
    ports:
      - "8082:8082"
    environment:
      GARAGE_KEY_ID: "${GARAGE_KEY_ID}"
      GARAGE_SECRET_KEY: "${GARAGE_SECRET_KEY}"
      SLM_S3_ENDPOINT: garage:3900
      SLM_CATALOG_URI: http://lakekeeper:8181
      SLM_CATALOG_WAREHOUSE: lakehouse
    depends_on:
      lakekeeper:
        condition: service_healthy

Configuration Reference

All environment variables use the SLM_ prefix (Spatial Lakehouse MCP), except Garage credentials which use GARAGE_ for compatibility.

Variable	Default	Description
SLM_CATALOG_URI	http://localhost:8181	LakeKeeper REST endpoint
SLM_CATALOG_WAREHOUSE	warehouse	Warehouse name in LakeKeeper
SLM_CATALOG_ALIAS	lakehouse	DuckDB alias for the attached catalog
SLM_CATALOG_TOKEN	(empty)	Bearer token for LakeKeeper auth
SLM_CATALOG_CLIENT_ID	(empty)	OAuth2 client ID
SLM_CATALOG_CLIENT_SECRET	(empty)	OAuth2 client secret
SLM_CATALOG_OAUTH2_SCOPE	(empty)	OAuth2 scope
SLM_CATALOG_OAUTH2_SERVER_URI	(empty)	OAuth2 token endpoint
GARAGE_KEY_ID	(empty)	Garage S3 access key ID
GARAGE_SECRET_KEY	(empty)	Garage S3 secret access key
SLM_S3_ENDPOINT	localhost:3900	S3 endpoint (host:port, no scheme)
SLM_S3_REGION	garage	S3 region
SLM_S3_USE_SSL	false	Use HTTPS for S3
SLM_S3_URL_STYLE	path	S3 URL style (path or vhost)
SLM_MAX_RESULT_ROWS	100	Max rows returned per query
SLM_QUERY_TIMEOUT_SECONDS	30	Query timeout
SLM_SERVER_PORT	8082	MCP server port

Important Notes

ACCESS_DELEGATION_MODE

When running the MCP server outside Docker (on the host), the ATTACH statement uses ACCESS_DELEGATION_MODE 'none'. This bypasses LakeKeeper's remote signing, which otherwise returns S3 URLs containing Docker-internal hostnames (e.g., garage:3900 instead of localhost:3900). The server's local S3 secret provides the correct host-accessible endpoint.

SQL Safety

All user-supplied queries are validated as read-only:

• Only SELECT and WITH (CTE) queries are allowed
• Dangerous keywords (DROP, DELETE, INSERT, ALTER, etc.) are rejected
• Multi-statement queries (; in the middle) are blocked
• Table/column identifiers are validated against injection