GeoQuery AI — Conversational Spatial SQL Engine via MCP Server

Ask questions in plain English ("hospitals within 2km of downtown Seattle") and get live PostGIS query results rendered as GeoJSON on a map. A Claude agent, embedded in a Next.js app, translates the question into tool calls against a custom Node.js MCP server (@modelcontextprotocol/sdk) that executes validated spatial SQL.

Architecture

• apps/web — Next.js (App Router) chat UI + MapLibre GL JS map. Hosts the Claude tool-use agent loop (app/api/chat/route.ts) that acts as an MCP client.

• services/mcp-server — Node MCP server exposing three tools: spatial_buffer, isochrone_query, and postgis_raw_sql, backed by a read-only Postgres role.

• db — PostGIS schema (OSM POIs + Census block groups) and a city-agnostic ingestion pipeline (flag-driven: --region, --pbf, --tiger-shp, --acs-csv).

Related MCP server: PostGIS Yukon MCP

Status

The full pipeline is wired end to end and has been run for real against a real database: chat input in apps/web → /api/chat → the Claude agent loop → MCP tool calls against PostGIS → GeoJSON streamed back → rendered on the map, with a toggle list to show/hide results from earlier turns and popups on both point and polygon features. The demo city (bellingham-wa) is ingested and verified (see db/ingest/README.md); what's left is the one piece that needs a paid ANTHROPIC_API_KEY this project hasn't used yet (deliberately — see below). See apps/web/README.md and services/mcp-server/README.md for how each half is built, DEMO.md for a scripted set of example prompts against the real demo data, and WRITEUP.md for the architectural decisions and real bugs found worth highlighting (the isochrone approximation tradeoff, MCP/Anthropic schema reuse, the layered SQL security model, and what building this against real data actually surfaced that mocks couldn't).

Setup

1. Database

cp .env.example .env   # fill in POSTGRES_PASSWORD, MCP_RO_PASSWORD, ANTHROPIC_API_KEY
docker compose up -d
docker compose exec postgis pg_isready -U geoquery_admin -d geoquery   # wait for "accepting connections"

On a fresh volume, Docker auto-applies everything in db/schema/ via docker-entrypoint-initdb.d (extensions, tables, indexes, and the read-only geoquery_ro role). If you're re-provisioning an existing database instead (schema changes after the first run), apply them manually:

./db/migrate.sh

migrate.sh tracks applied migrations in a schema_migrations table, so it's safe to run any time — it only applies files that haven't run yet.

Re-running docker compose up does not re-apply db/schema/ — those init scripts only fire on a brand-new volume. To wipe and start over: docker compose down -v (destroys all data). To apply new schema files without losing data, use ./db/migrate.sh instead.

2. Data ingestion

City-agnostic and flag-driven — nothing about a specific city is hardcoded in any ingestion script; see db/ingest/config/example-city.env for where to download a PBF/TIGER shapefile/ACS CSV and what shape they need.

pip install -r db/ingest/requirements.txt   # needed for census/join_acs.py

./db/ingest/ingest.sh \
  --region seattle-wa \
  --pbf ./data/seattle.osm.pbf \
  --tiger-shp ./data/tl_2023_53_bg.shp \
  --acs-csv ./data/seattle_acs.csv \
  --db-url "$DATABASE_URL"

Requires osm2pgsql (OSM loading) and ogr2ogr/GDAL (TIGER shapefile loading) installed locally. --acs-csv is optional — without it, block groups load with geometry only and NULL demographic columns. Re-running ingest.sh for the same --region is safe (each step deletes that region's existing rows before re-inserting); re-running for a different --region just adds more rows alongside it, so multiple cities can coexist.

3. Running the app

The MCP server (services/mcp-server) is feature-complete for this phase — all three tools (spatial_buffer, isochrone_query, postgis_raw_sql) are registered and can be built/run/tested standalone; see its README for how to exercise them manually with the MCP Inspector.

The Next.js app (apps/web) is now a real, working chat client — type a question and it genuinely calls /api/chat, which runs the Claude agent loop against the MCP server above:

npm run --workspace @geoquery/web dev   # http://localhost:3000

This needs your own ANTHROPIC_API_KEY in .env and a database with data in it (steps 1-2 above) to produce real answers — without those, you'll see real error messages in the chat (an auth error, or the model telling you it has no data) rather than a crash, which is itself part of what's been verified.

Verified vs. user-verified

Some parts of this project (Docker provisioning, real OSM/Census data ingestion, live end-to-end chat queries) require a local machine with Docker and downloaded data extracts, and can't be verified in a sandboxed dev environment.

• Verified so far: shell script syntax (bash -n) for all .sh files; Python syntax (py_compile) for join_acs.py; SQL DDL reviewed against PostGIS/Postgres documentation; services/mcp-server actually builds and runs — confirmed fail-fast behavior on missing config, and used a real MCP client to confirm all three tools' Zod schemas convert to JSON Schema correctly over the wire and that DB-dependent calls fail cleanly as an isError result against an unreachable Postgres rather than crashing. services/mcp-server also has a real automated test suite now (48 tests, npm run --workspace services/mcp-server test, no database needed — see its README): the postgis_raw_sql SQL guard's 34 cases (stacked queries, comment-hidden payloads, CTE-disguised writes, disallowed tables), and spatial_buffer/isochrone_query's query-construction and GeoJSON assembly logic against a mocked database. apps/web is also verified with a real headless browser (Playwright driving system Chrome), not just typecheck/build/curl: the MapLibre map actually renders and fills its pane, attribution text is correct, clicking a point feature opens a popup with the right content while clicking a polygon doesn't, and (after finding and fixing a Turbopack/maplibre-gl worker-loading issue along the way — see apps/web/README.md) there are no console errors left besides an unrelated, pre-existing favicon.ico 404. lib/mcp-client.ts and lib/anthropic-tools.ts are verified against the real running MCP server (not mocked): a real spawn + connect, a second getMcpClient() call reusing the same client (confirmed by reference equality, not just "it didn't crash"), all three tools' schemas correctly mapped to Anthropic's shape, and a real callTool() round trip. lib/agent-loop.ts is verified the same way, minus the one piece needing a paid API key (none available in this sandbox): a minimal fake Anthropic client drives the loop through a tool_use turn then an end_turn turn while the MCP side is entirely real (actual server spawn, actual callTool()) — confirming the tool_result block is correctly threaded back into the next API call with the right tool_use_id/is_error/content, text deltas forward through the event callbacks, and the final message history is exactly what it should be. app/api/chat/route.ts is verified against a real dev server: all four request-validation failure cases return the correct 400s, and a valid request returns the correct streaming headers and a genuine authentication_error from Anthropic's real API (no key available here) arrives as a clean error event rather than a crash — which also proves the MCP server spawn and listTools() succeeded first, since the loop only reaches the Anthropic call after that. Caught and fixed a real bug along the way: a NodeNext-style .js relative import habit carried over from services/mcp-server that typecheck accepted but Turbopack couldn't resolve, surfacing only once the file was actually built into a route. Finally, ChatPanel → /api/chat → MapView is verified as one real pipeline in a real browser: since no Anthropic key exists in this sandbox, the /api/chat network call itself was mocked (Playwright route interception) with a realistic ndjson stream, which still exercises all of the real client-side code (fetch, stream parsing, history reconciliation, map wiring) — confirmed the user bubble appears immediately, two assistant turns from one exchange render as separate bubbles once the authoritative done history lands (not merged, even though they're concatenated during live streaming), the mechanical tool_result message never renders as a chat bubble, a second turn resends the full prior history plus the new message (proving the stateless client-owns-history design actually works across turns, not just once), and a single-point GeoJSON result really does fly the map in and render the marker. The layer-toggle list and the new polygon popups are also verified in a real browser: clicking a polygon now shows a populated popup with a kind-aware heading and formatted values (previously nothing happened — an intentional change from Session 10's scope, not a regression), and — checked deterministically via a throwaway page exposing the map instance directly, rather than relying on real tile-loading timing in screenshots — unchecking a layer removes exactly that layer's source and rendered layer while a second, untouched layer is unaffected, and re-checking restores it.

• Verified for real (Session 17): docker compose up provisioning PostGIS end to end on a real machine (including a real port-conflict fix — POSTGRES_HOST_PORT — when 5432 was already taken by an existing local Postgres). Real osm2pgsql 1.6.0 ingestion against the full Washington state Geofabrik extract (49.6M nodes, 5.28M ways) — this surfaced and fixed two real bugs the flex Lua API docs didn't make obvious: there is no object:as_point()/as_polygon() method at all (geometry auto-populates on add_row() only when the table's ids.type matches the column's geometry type), and osm2pgsql --create drops and recreates any table its Lua script defines, even a pre-provisioned one with a different schema — fixed by routing all osm2pgsql writes through disposable staging tables, merged into the real osm_pois schema via SQL (negating way-derived ids to avoid colliding with node ids in the shared primary key). Real ogr2ogr TIGER shapefile ingestion (5,311 Washington block groups) worked on the first try, confirming the long-assumed field-name convention (statefp/countyfp/tractce/ blkgrpce/geoid). All three MCP tools (spatial_buffer, isochrone_query, postgis_raw_sql) called for real against this 282k-row database via a scripted MCP client: correct GeoJSON, correct distance ordering, the isochrone's approximation caveat present, and the SQL guard rejecting a real DELETE attempt. EXPLAIN ANALYZE against real data found and fixed a real indexing bug: ST_DWithin(geom::geography, ...) on osm_pois was not using the plain geometry-typed GIST index (osm_pois_geom_gix) at all — it fell back to a parallel sequential scan (~170ms over 282k rows) because the bounding-box check happens in geography space, not geometry space. A functional GIST index on the geography cast (db/schema/005_osm_pois_geog_index.sql) fixed it (~8ms, confirmed via the real MCP tool call, not just raw SQL) — the opposite of what an earlier, unverified code comment in queries.ts had assumed. census_block_groups' ST_Intersects query needed no such fix — it compares geometry to geometry and used cbg_geom_gix correctly from the start (confirmed, not assumed).

• Verified for real (Session 18): the project's actual demo city, bellingham-wa (DEFAULT_REGION in .env), ingested via a real, full run of the ingest.sh orchestrator itself (not just its sub-scripts individually) — see db/ingest/README.md for the exact commands and why it's derived from the already-ingested Washington-state data rather than a separate city PBF (Geofabrik has no city-level extracts; BBBike's don't include Bellingham). This real run found and fixed a genuine bug: census/load_tiger.sh's merge had no ON CONFLICT handling (unlike osm/load_osm.sh's), so the first time two regions' real data actually overlapped in geoid space it crashed — and because the DELETE/INSERT weren't transactional, the DELETE had already committed, silently dropping that region's 64 existing rows before the crash. Fixed with ON CONFLICT (geoid) DO NOTHING plus an explicit BEGIN/COMMIT (and applied the same transactional wrap to load_osm.sh's merge for consistency). Re-running afterward correctly restored all rows — confirmed by querying both tables.

• Verified for real (Session 19): a documentation/polish pass across apps/web found and fixed a real UI bug via a read-only audit agent, then confirmed the fix in a real browser (Playwright, mocked /api/chat route): an error arriving mid-turn (no done event ever sent) used to silently discard whatever text had already streamed in, leaving only the error banner. ChatPanel.tsx's failTurn now persists it as a real message first. Also added (and real-browser-verified) a "Loading map…" overlay for the previously-blank pane while the basemap style/tiles are still loading. See apps/web/README.md for the full env-var-validation audit (every process.env read, which ones fail fast and which don't, and why ANTHROPIC_API_KEY is the deliberate exception).

• Verified for real (Session 20): a final whole-codebase bug-hunt pass (not a diff review — everything was already committed) found and fixed four more real bugs: lib/mcp-client.ts getting permanently wedged after one transient connection failure (??= doesn't reassign on a rejected, non-null promise); postgis_raw_sql's LIMIT-detection regex missing a parameterized LIMIT $n (exactly what the tool's own description tells callers to use), producing a guaranteed syntax error; the agent loop having no iteration cap and silently returning a reply truncated at max_tokens with no indication anything was cut off; and the client silently losing a successful tool call's history whenever a later round in the same turn failed, since done (the only event that syncs history) was never sent on failure. See apps/web/README.md and services/mcp-server/README.md for details and how each was verified (real browser tests for the frontend fixes, a new regression test for the SQL guard). Also added DEMO.md (a scripted prompt sequence grounded in real bellingham-wa data) and WRITEUP.md (portfolio notes on the isochrone tradeoff, MCP/Anthropic schema reuse, and the layered SQL security model).

• Not verified — needs your machine: real ACS demographics for bellingham-wa — the Census Bureau's public API now requires a free API key even for small anonymous requests (it didn't previously), so census/join_acs.py is still only unit-logic-verified, never run against a real export; both demo regions' block groups have NULL population/median_income/housing_units (see db/ingest/README.md for the exact command once a key is available). And the one remaining gap now that every piece of the pipeline individually works for real — an end-to-end run with an actual ANTHROPIC_API_KEY and this real ingested data, asking a real question and getting a real model-reasoned answer with real GeoJSON on the map. Every piece of that chain has been verified in isolation (real MCP server against real data, real Anthropic request shape and error handling, real streaming/map wiring); what's never happened is all of them firing together with real credentials.