GeoSQL-Bench: PostGIS NL2GeoSQL Evaluation

• GeoSQL-Bench is a specialized benchmark dataset that evaluates models' ability to generate valid PostGIS queries from natural language.
• It comprises 14,178 questions spanning three task types and covers 340 PostGIS functions with realistic, domain-aware schemas.
• The GeoSQL-Eval framework employs layered cognitive metrics to assess conceptual understanding, SQL generation, schema grounding, and execution accuracy.

GeoSQL-Bench is a benchmark dataset for evaluating LLMs on natural language to GeoSQL in the PostGIS environment. It was introduced together with the GeoSQL-Eval framework as a response to the lack of systematic benchmarks tailored to spatial databases, in contrast to evaluations centered on general relational databases or Google Earth Engine code generation. The benchmark comprises 14,178 questions, spans three task types, covers 340 PostGIS functions, and uses 82 domain-specific databases, thereby targeting conceptual understanding, SQL generation, schema grounding, execution accuracy, and robustness within a single PostGIS-specific evaluation setting (Hou et al., 28 Sep 2025).

1. Position within NL2GeoSQL evaluation

GeoSQL-Bench is designed for the NL2GeoSQL problem: generating PostGIS queries from natural language under the constraints of spatial functions, geometric data types, and execution semantics. Its scope is narrower than general Text-to-SQL in database diversity, but deeper in spatial specificity. The benchmark is explicitly PostGIS-centered, with the stated aim of covering the richness of the PostGIS API rather than approximating it through generic SQL templates. In the associated framework, GeoSQL-Bench functions as the dataset substrate for GeoSQL-Eval, which is built upon Webb’s Depth of Knowledge (DOK) model, includes four cognitive dimensions, five proficiency levels, and twenty task categories, and evaluates model behavior in terms of knowledge acquisition, syntactic generation, semantic alignment, execution accuracy, and robustness (Hou et al., 28 Sep 2025).

A recurring misconception in spatial LLM evaluation is that strong performance on standard Text-to-SQL datasets is a reliable proxy for GeoSQL competence. GeoSQL-Bench is constructed precisely to challenge that assumption. The reported findings indicate that standard large Text-to-SQL datasets and benchmarks would have significantly overestimated LLM capability for spatial databases, because PostGIS-specific failures arise from function invocation, parameter typing, schema linking, geometry handling, and CRS-sensitive execution rather than from SQL surface form alone (Hou et al., 28 Sep 2025).

2. Construction methodology and source materials

The benchmark construction pipeline combines documentation-derived function coverage with domain-aware schema generation. The PostGIS 3.5 official manual serves as the primary source for spatial functions and operators. For domain themes, the benchmark uses UN-GGIM and the ISO 19115-1:2014 TopicCategoryCode, which are merged into realistic schema contexts.

Question generation uses a Self-Instruct paradigm, with OpenAI’s GPT-4o used only for question linguistic structure, NOT content, to avoid leakage and bias. The resulting material is then reviewed and curated by three experts in surveying, GIS, and data modeling under a dual-review, single-adjudication protocol. This expert validation is central to the benchmark’s claim of scientific rigor and distinguishes it from purely synthetic prompt-generation pipelines (Hou et al., 28 Sep 2025).

Database schemas are generated through semantic clustering of domain topics using Word2Vec and hierarchical clustering (Ward’s method, Euclidean distance). This process merges the UN and ISO themes into 8 core database clusters, from which 82 realistic, domain-aware databases are produced. The benchmark description states that each schema and task aligns field types and names with real PostGIS requirements and geospatial semantics. A plausible implication is that the benchmark is intended not merely to test lexical recall of function names, but to test whether models can map language to valid spatial operations under realistic schema constraints.

3. Internal organization and task taxonomy

GeoSQL-Bench is divided into three major task categories, each associated with a different difficulty profile and DOK level. The benchmark includes both explicit prompts and underspecified prompts, allowing it to evaluate standard performance and robustness under ambiguity.

Task type	Purpose	Count
Multiple Choice & True/False	Conceptual understanding of PostGIS functions and rules	2,380
Syntax-level SQL Generation	Valid PostGIS SQL generation from natural language	3,744
Table Schema Retrieval	Schema linking, function selection, execution-oriented SQL generation	2,155

The benchmark’s total size is 14,178 questions/tasks. The paper notes that the sum of the explicit/underspecified variants plus distractor configurations generates this total, which explains why the category subtotals do not by themselves equal the full task count (Hou et al., 28 Sep 2025).

The Multiple Choice & True/False component is intended for DOK1: Recall. It includes four subtypes: Function Purpose Recognition (MCQ), Parameter Matching (TF), Return Type Recognition (MCQ), and Behavioral/General Rule Compliance (TF). These tasks are derived from function synopsis, description, standards, history, and examples in the PostGIS documentation.

The Syntax-level SQL Generation component tests the generation of valid PostGIS SQL without external schema dependence. It is built from function examples rewritten into explicit prompts, which simulate expert users, and underspecified prompts, which simulate vague or naive users. This task type focuses on function usage and argument handling rather than table linking.

The Table Schema Retrieval component is the most complex. It combines natural-language understanding, schema linking, SQL generation, and execution. Inputs may include the natural-language question, schema or schemas, field samples, and sample data. The prompt design explicitly introduces field-name mismatches such as “building height” vs “elevation” and other ambiguous references. This structure is meant to probe whether a model can ground spatial language in realistic schema variants rather than relying on memorized templates (Hou et al., 28 Sep 2025).

4. PostGIS coverage and schema representation

GeoSQL-Bench systematically covers 340 PostGIS functions sourced from the official PostGIS 3.5 manual. These functions span major spatial operations, including geometric constructors, relationships, and analysis, with examples such as ST_Buffer, ST_Intersects, and ST_DWithin. The benchmark states that every function is involved in at least one task across different types and difficulty levels (Hou et al., 28 Sep 2025).

The 82 domain-specific PostGIS databases represent real-world entities and thematic domains such as urban planning, health, environment, and transportation. Example database names given in the summary include CrimeHotspotTracker, EcoGuardian, and MarineActivityMonitor. Each database contains 1–8 tables with an average of 4–6, and 3–5 fields per table, always including at least one geometry field with SRID and type information, along with realistic sample data and primary/foreign keys. The schema design is described as jointly driven by thematic context and PostGIS function requirements.

This benchmark structure places GeoSQL-Bench at the intersection of function-level testing and database-level grounding. It is therefore not limited to the canonical NL2SQL scenario of mapping a question to a single relational schema. Instead, it also tests whether a model can reconcile field semantics, geometry types, spatial predicates, and execution constraints in domain-aware spatial databases. This suggests that the benchmark is intended to approximate operational GeoSQL authoring more closely than benchmarks based solely on function trivia or isolated SQL snippets.

5. Evaluation logic and metric system

GeoSQL-Bench is evaluated through the GeoSQL-Eval framework, which maps tasks to a layered capability model: Conceptual Understanding, Structured SQL Generation, Semantic Alignment & Invocation, Execution & Result Accuracy, and Robust Generalization. These layers correspond to DOK progression from recall to generalization under underspecification (Hou et al., 28 Sep 2025).

For foundational MCQ/TF tasks, the framework uses an accuracy measure:

$$\text{Accuracy}_{\text{Foundational}} = \frac{1}{N}\sum_{i=1}^{N} 1(\hat{a}_i = a_i)$$

For SQL generation, the framework measures Execution Pass Rate (EPR) and Syntax Accuracy (SA):

$$EPR = \frac{1}{N}\sum_{i=1}^{N} 1(\text{ExecState}_i = 1)$$

$$SA = \frac{1}{N}\sum_{i=1}^{N} 1(\text{SyntaxState}_i = 1)$$

Semantic alignment is decomposed into Table hit rate (THR), Field hit rate (FHR), Function hit rate (FNR), and Argument Match Accuracy (AMA). The AMA term is defined as

$AMA_i = \frac{\sum_{j=1}^{n} 1(a_j = b_j)}{n}$

where $a_j$ and $b_j$ are argument types in the ground-truth and model-generated queries. This design makes semantic correctness explicitly separable from surface syntax. A query may be syntactically valid yet semantically misaligned because it invokes the wrong table, field, function, or argument order.

For outputs involving geometries, the framework performs EWKT string match and otherwise evaluates equivalence through snapped-to-grid comparison and Z-value tolerance. For scalar, boolean, and textual outputs, it applies normalized exact matching. Robustness is assessed with pass@n, coefficient of variation (CV), and SAA, and overall ranking uses the Entropy Weight Method (EWM):

$S_i = \sum_{j=1}^{n} w_j X_{ij}$

with entropy-derived weights. This ranking scheme is intended to aggregate heterogeneous metrics while preserving interpretability (Hou et al., 28 Sep 2025).

6. Empirical findings, limitations, and relation to adjacent benchmarks

The benchmark results emphasize a sharp difference between superficial SQL fluency and true GeoSQL competence. General non-reasoning and reasoning-enhanced LLMs, including GPT-5, o4-mini, and DeepSeek-R1-0528, are reported to outperform code-specialist and SQL-only models. On conceptual understanding (MCQ/TF), most advanced LLMs achieve more than 0.85 accuracy on function, parameter, and return-type questions, but most remain below 0.81 on rule/behavioral compliance. On SQL generation, syntax correctness is high (>0.97 in top models), yet executability and semantic alignment lag substantially; for syntax-level tasks, function and parameter matching hit rates are approximately 0.5 and approximately 0.25 on average, while table-schema retrieval is considerably harder and is often below 0.5 pass@5 on even best models (Hou et al., 28 Sep 2025).

Robustness degrades under ambiguity. Accuracy falls substantially for underspecified prompts, especially in syntax-level tasks, and outputs become less stable. The error distribution is also highly diagnostic: about 70% of all errors are PostGIS function errors—mainly argument, type, and count mismatches or hallucinated function names—and SQL syntax errors. Geometry parsing and SRID/CRS mismatches are described as the next most frequent error sources, particularly in schema-driven tasks. These findings make clear that GeoSQL-Bench is not only a leaderboard instrument but also an error-analysis framework for PostGIS-specific failure modes (Hou et al., 28 Sep 2025).

The benchmark’s limitations are explicit. The current version does not cover all PostGIS extensions, focusing mainly on vector processing and omitting raster, network, and advanced operations. All tasks are on PostGIS, with other spatial DBMS and plugins, including those for raster, 3D, and time series, identified as future work. This matters because GeoSQL-Bench is comprehensive within a PostGIS vector-processing regime, but not a universal benchmark for all spatial database paradigms.

Relative to adjacent geospatial benchmarks, GeoSQL-Bench occupies a distinct niche. GS-QA evaluates geospatial question answering over 2,800 question-answer pairs across 28 templates on OpenStreetMap and Wikipedia and focuses on spatial predicates, answer types, and multi-source reasoning rather than PostGIS query generation (Saeedan et al., 21 May 2026). FloodSQL-Bench is a domain-specific Text-to-SQL benchmark for flood-risk management, with 443 question-SQL pairs emphasizing key-based, spatial, and hybrid joins over ten heterogeneous datasets (Liu et al., 12 Dec 2025). At the systems level, “Towards an Application-Centric Benchmark Suite for Spatiotemporal Database Systems” argues that a comprehensive benchmark suite for spatiotemporal databases is still missing and sketches a modular architecture for such benchmarking (Rese et al., 8 Jul 2025). Taken together, these works suggest a layered landscape: GeoSQL-Bench targets PostGIS-centric NL2GeoSQL evaluation; FloodSQL-Bench targets application-grounded geospatial Text-to-SQL in a single high-stakes domain; GS-QA targets broader geospatial reasoning outputs; and application-centric spatiotemporal benchmarking addresses DBMS-level performance rather than query generation.