GeoChat
Ask questions in plain English, get back maps. GeoChat translates natural language into PostGIS SQL using Claude's tool-use API and the Model Context Protocol, then renders the results as interactive GeoJSON layers on a map.
Platform
geochat.afterrealism.com
GeoChat
NL2GeoSQL with Maps
By the Numbers
PostGIS Tables
Sydney Records
Suburb Polygons
MCP Tools
The Problem
Querying geospatial databases requires knowing SQL, PostGIS functions, and the schema. Analysts spend time writing ST_Contains, ST_DWithin, and spatial joins instead of asking the question they actually care about. Non-technical stakeholders can't access the data at all without someone writing queries for them.
The Solution
GeoChat lets you ask questions like "show me all schools within 2km of a train station in Surry Hills" and get back a map with the results. Claude generates the SQL, the MCP server validates and executes it against PostGIS, and the frontend renders the GeoJSON response on MapLibre GL JS — all in a single conversational turn.
How It Works
A multi-step agent loop that classifies the question, generates SQL, validates it, self-corrects on failure, and renders the spatial result — all through the Model Context Protocol.
MCP Agent Loop
Claude receives the full database schema upfront and has access to 5 MCP tools: list_tables, get_table_schema, get_column_values, execute_sql, and postgis_help. The LLM calls tools iteratively until it has the answer, classifying queries into SHOW, LOCATE, IDENTIFY, or ANALYSE taxonomies.
Self-Correcting SQL
Queries go through two-stage validation: static parsing with sqlglot, then EXPLAIN against the live database. If execution fails, Claude sees a structured error with a taxonomy-based hint (column, table, syntax, type_mismatch, spatial, or aggregate) and retries — up to 5 iterations.
GeoJSON Map Rendering
The LLM is prompted to include ST_AsGeoJSON(geom) as a column alias. The backend separates geometry from properties into a FeatureCollection. The frontend renders Points, LineStrings, and Polygons on MapLibre GL JS with click-to-zoom and a feature list sidebar.
SQL Safety Layer
All queries are read-only with a 10-second timeout. validate_sql() blocks non-SELECT statements, forbidden keywords (DROP, DELETE, INSERT, UPDATE), and multi-statement queries. Results are automatically capped with LIMIT 500.
Architecture
The request flows from SvelteKit through a BFF proxy to FastAPI, which runs the MCP agent loop against Claude. The MCP server validates and executes SQL against PostGIS. In production, the entire backend stack runs in a single Cloudflare Container with the geodata baked into the image.
User → SvelteKit (Pages) → BFF proxy (+server.ts) → FastAPI (8000) → MCP Server (8001) → PostGIS (5432)
↕
Claude API
(tool-use loop)Frontend
SvelteKit 5 with split-pane layout — ChatPanel for conversation and SQL display, MapPanel with MapLibre GL JS for GeoJSON rendering. BFF proxy forwards to backend via Cloudflare service binding.
Backend
FastAPI with async agent loop. Connects to MCP server on startup, caches schema and tools. Converts MCP tool format to Anthropic tool format for Claude API calls.
MCP Server
Standalone FastMCP server with 5 tools, 3 resources, and 3 prompts. Streamable HTTP transport. SQL validation with sqlglot + EXPLAIN. Reusable by Claude Desktop.
Database
PostgreSQL 17 with PostGIS 3.5 and pgvector. 15 tables of Sydney geodata, 840 suburb polygons with property market data. All geometries SRID 4326.
Cloudflare Deployment
The frontend runs on Cloudflare Pages at the edge. The backend — PostgreSQL, MCP server, and FastAPI — runs as a single Cloudflare Container with geodata baked into the Docker image at build time. Cold starts just start PostgreSQL (~3s), no data loading needed.
Pages (Edge)
SvelteKit frontend with adapter-cloudflare. BFF proxy uses service binding for zero-latency Worker-to-Worker calls to the backend.
Container (All-in-One)
Single Docker container runs PostgreSQL 17 + PostGIS 3.5 + FastMCP + FastAPI. initdb and data loading happen at build time — the data directory is baked into the image layer.
Chat History (D1)
Every query and response is persisted to Cloudflare D1 via waitUntil — non-blocking writes so they don't slow down the response. Tracks SQL, row count, latency, tokens, and errors.
Technology Stack
Sydney Geodata
15 Point & Polygon Tables
- Schools, hospitals, libraries, parks, playgrounds
- Train stations, ferry wharves, transport stops
- Emergency services, pharmacies, restaurants
- Supermarkets, gyms, shopping centres, air quality stations
840 Suburb Boundaries
- Official Geoscape suburb polygons for ST_Contains queries
- Domain.com.au property market data (prices, rents, demographics)
- Affordability tier classification per suburb
- Enables "features in suburb X" without hardcoded coordinates
Example Queries
Natural language in, spatial results out. The agent classifies each query and generates the appropriate PostGIS SQL.
“Show me all hospitals in the Eastern Suburbs”
“Find schools within 1km of Bondi Beach”
“Which suburb has the most parks per square km?”
“What train stations are near Surry Hills police station?”
“Show restaurants in suburbs where median rent is under $600/week”
“Find the nearest hospital to each fire station”
Ask a question, get a map
Try querying Sydney's geodata with natural language. The AI generates PostGIS SQL, validates it, and renders the results on an interactive map.
Launch GeoChat