Geo QA

Overview

GeoQA is a geospatial question-answering system: a user asks a question in natural language (Indonesian) about land cover or administrative boundaries, and the system answers with a written response plus a map. The study area is Aceh Tamiang regency, whose Sentinel-2 land-cover data is stored as raster in PostGIS. Unlike conventional text-to-SQL over row-and-column tables, this system must generate spatial SQL that operates on pixel grids using PostGIS raster functions.

The core of the system is a Text-to-GeoSQL pipeline with self-correction. It has three stages: (1) generate a spatial query from the question, (2) execute it and, on failure, automatically loop through correction attempts, and (3) compose a natural-language answer from the execution result. The pipeline is protected by two guards: an anti-identical-SQL guard that stops retrying a query it has already tried, and a schema-based rejection mechanism that refuses to answer anything outside the data's scope.

The same pipeline powers two consumers: the interactive chat application (/chat/stream) and the evaluation harness (/evaluation/run/stream) that runs a benchmark of 80 questions across 17 models for the thesis analysis.

Tech Stack & Rationale

Architecture

The flow below shows a single question's journey through run_sql_pipeline(). Both the chat endpoint and the evaluation harness call this identical function, so the app and the benchmark always exercise the same correction loop.

The flow runs: Natural question → Generate GeoSQL → Execute SQL → Compose answer + map; Natural question → Generate GeoSQL → Execute SQL → Self-correction loop (max attempts).

1. Generate — the question is sent to an LLM with a system prompt that describes the PostGIS raster schema (land-cover classes, the Aceh Tamiang boundary, and available spatial functions).
2. Execute — the generated SQL is run against PostGIS with a uniform query timeout. Raster-heavy operations (ST_MapAlgebra, ST_DumpAsPolygons, ST_Reclass) get the same budget as lighter ones.
3. Self-correct — if execution throws or times out, the error is fed back to the model to produce a corrected query. The loop repeats up to MAX_SQL_CORRECTIONS attempts. Two guards bound the loop: the anti-identical-SQL guard hard-stops after three consecutive identical queries, and the schema-based rejection refuses to compose an answer outside the data scope (this also keeps hallucination near zero).
4. Compose — the result set is turned into a natural-language answer and an optional map event (map_data) that the frontend renders as a Leaflet layer.

A second helper, _inject_polygon_merge, automatically rewrites queries that produce fragmented polygons from ST_DumpAsPolygons by injecting ST_Union/ST_Dump CTEs, so adjacent same-value pixels merge into a single geometry.

Folder Structure

geoqa-hybrid/
├── CLAUDE.md                 # project router (thesis vs. app context)
├── Latex/                    # thesis: /thesis, /journal, /article (ICITEE 2026)
├── docker-compose.yml        # frontend + backend + PostGIS
├── package.json              # pnpm workspace scripts (dev/build/lint/test)
└── apps/
    ├── backend/              # FastAPI + PostGIS + evaluation harness
    │   ├── app/
    │   │   ├── api/v1/       # routers: chat, tiles, boundaries, evaluation, auth, debug
    │   │   ├── core/         # config, database, auth, logging, SSE helpers
    │   │   ├── models/       # pydantic (chat, spatial) + SQLAlchemy (db/)
    │   │   ├── repositories/ # per-table DB access
    │   │   ├── services/
    │   │   │   ├── pipeline/ # sql_pipeline.py, prompts.py, sql_parser.py (core)
    │   │   │   ├── chat/     # chat orchestration, LLM client, handlers, session
    │   │   │   ├── spatial/  # spatial functions + tile_service.py
    │   │   │   └── codegen/  # parameter extraction helpers
    │   │   └── constants/    # land-cover & spatial constants
    │   ├── evaluation/       # benchmark harness (ground_truth, pipeline, benchmark)
    │   └── main.py
    ├── frontend/             # Next.js 16 + Leaflet chat UI
    │   └── src/
    │       ├── app/          # page.tsx (chat), evaluation/, evaluation/summary/
    │       ├── components/   # chatbot/, map/, landcover/, evaluation/, shared/, ui/
    │       └── lib/          # api/, auth/, hooks/, types/, utils/, constant/
    └── docs/                 # ARSITEKTUR_SISTEM.md, BELAJAR_KOMPONEN.md, TEKNIK_PROMPTING.md

Database Design

PostGIS stores the Sentinel-2 land-cover raster (served as tiles), while SQLAlchemy models back the application and evaluation tables:

The benchmark dataset itself (evaluation/ground_truth/dataset.json) holds 80 questions across 8 categories, covering land-cover queries (class transitions, counts, areas) and hallucination probes used to measure schema-based rejection.

Impact & Results

• Published research: the thesis evaluates 17 models (6 architecture families) on an 80-question benchmark — the strongest model (glm-5.1) reached 88.6% accuracy and schema-based rejection held hallucinations to ~0.2% of 1,344 judgments.
• Both the chat application and the evaluation harness run the exact same pipeline, so every interactive answer is produced by the same machinery the thesis measured.

My Contributions

This was built as a master's thesis, single-author (all 42 commits). Key work:

• Designed and implemented the Text-to-GeoSQL pipeline (run_sql_pipeline) with the self-correction loop, anti-identical-SQL guard, and schema-based rejection.
• Built the FastAPI backend (tile/boundary/chat/evaluation routers, spatial functions, SSE streaming) and the Next.js frontend (chatbot, Leaflet map, evaluation pages).
• Created the evaluation harness: the 80-question benchmark, the (question × model) iterator, the LLM judge, and the bootstrap / cross-judge analysis scripts that produced the thesis findings.
• Wrote the thesis document (LaTeX) and the ICITEE 2026 paper.

References

[1] A. S. Shina, "geoqa-hybrid — Text-to-GeoSQL with self-correction for land-cover spatial queries," GitHub repository, 2026. [Online]. Available: https://github.com/ave-shina/geoqa-hybrid

[2] PostGIS Development Team, "PostGIS — spatial and geographic objects for PostgreSQL," postgis.net, 2026. [Online]. Available: https://postgis.net

[3] FastAPI, "FastAPI documentation," fastapi.tiangolo.com, 2026. [Online]. Available: https://fastapi.tiangolo.com

[4] European Space Agency, "Copernicus Sentinel-2 mission," esa.int, 2026. [Online]. Available: https://sentinels.copernicus.eu/web/sentinel/missions/sentinel-2