---
title: 'GeoSQL-Eval: PostGIS NL2GeoSQL Evaluation'
url: https://www.emergentmind.com/topics/geosql-eval
type: topic
---

# GeoSQL-Eval: PostGIS NL2GeoSQL Evaluation

Searching arXiv for recent papers on GeoSQL-Eval and related geospatial SQL evaluation benchmarks.
GeoSQL-Eval is an end-to-end automated evaluation framework for **NL2GeoSQL**, the natural-language-to-GeoSQL task of mapping natural-language questions to executable PostGIS SQL that involves spatial functions, spatial types, and spatial execution semantics. Built upon Webb’s Depth of Knowledge (DOK) model, it defines four cognitive dimensions, five capability levels, and twenty task categories, and is paired with **GeoSQL-Bench**, a benchmark dataset comprising **14,178 questions** that span **three task types**, **340 PostGIS functions**, and **82 domain-specific databases** [2509.25264]. Its central purpose is to evaluate not only parser-level SQL validity but also schema alignment, function invocation, SRID handling, geometry-aware result correctness, and robustness under ambiguity.

## 1. Position within NL2SQL and geospatial evaluation

GeoSQL-Eval addresses a gap left by conventional NL2SQL benchmarks. In the framework’s formulation, NL2GeoSQL extends traditional NL2SQL by requiring correct use of PostGIS-specific constructs, including spatial functions and operators such as `ST_Contains`, `ST_DWithin`, `ST_Transform`, `ST_Union`, `ST_LineLocatePoint`, `ST_Equals`, `ST_SnapToGrid`, `&&`, and `~=`; geometry and geography types including `GEOMETRY` and `GEOGRAPHY`; WKT, WKB, and EWKT encodings; explicit SRID casting through `ST_SetSRID`; projection transforms through `ST_Transform`; geometry/geography conversions; and GiST-based spatial indexing [2509.25264].

The framework also treats **execution semantics** as a first-class evaluation target. In this formulation, correctness extends beyond parser acceptance to encompass function availability, overload resolution, parameter order and type constraints, numeric tolerances for geometric equality, dimensionality in 2D and 3D, and environment-dependent behavior via PROJ. This design is intended to distinguish superficially well-formed SQL from executable and semantically aligned GeoSQL.

GeoSQL-Eval is explicitly positioned against relational NL2SQL resources such as Spider, WikiSQL, and BIRD, which do not cover spatial types, SRIDs, geometry/geography semantics, spatial operators, or topology-aware result validation. It is also distinguished from Google Earth Engine evaluation frameworks and from GeoQueryJP, which concerns place-name disambiguation for general SQL rather than GeoSQL. Within that landscape, GeoSQL-Eval is presented as the **first end-to-end automated evaluation framework for PostGIS query generation** and is coupled to a public leaderboard for ongoing submissions and comparison [2509.25264].

## 2. Framework architecture and task taxonomy

GeoSQL-Eval adopts Webb’s DOK model and maps it to four cognitive dimensions: **DOK1 Recall → Conceptual Understanding**, **DOK2 Skill/Concept → Structured SQL Generation**, **DOK3 Strategic Thinking → Semantic Alignment & Invocation**, and **DOK4 Extended Thinking → Generalization & Robust Reasoning**. Operationally, the framework uses five capability levels: **Knowledge acquisition**, **Syntax-level SQL generation**, **Semantic alignment & invocation**, **Execution & result accuracy**, and **Robust generalization & reasoning** [2509.25264].

The twenty task categories span conceptual, syntactic, semantic, executional, and robustness-oriented assessment. Conceptual Understanding contains **Function Purpose**, **Parameter Check**, **Return Type**, and **General Rule Compliance**. Structured SQL Generation contains **Syntax-valid SQL Generation** and **Underspecified SQL Generation**. Table Schema Retrieval contains **Explicit Prompt** and **Underspecified Prompt**. Semantic Alignment & Invocation contains **Table Hit Rate (THR)**, **Field Hit Rate (FHR)**, **Function Name Rate (FNR)**, and **Argument Match Accuracy (AMA)**. Execution & Result Accuracy contains **Overall Accuracy (ACC_ALL)**, **Geometric Accuracy (ACC_Geo)**, and **Other-Type Accuracy (ACC_Other)**. Robust Generalization & Reasoning contains **Pass@n Accuracy Metrics**, **Structural Stability Metrics**, and **Result Stability Metrics**, while **Syntax Accuracy (SA)** and **Execution Pass Rate (EPR)** are additional procedural categories used in aggregation and reporting.

| Layer | Categories | Representative outputs |
|---|---|---|
| Conceptual Understanding | function_purpose, parameter_check, return_type, general_rule | strict MCQ/T-F accuracy |
| Structured SQL / Schema Retrieval | explicit and underspecified SQL generation | SA, EPR |
| Semantic Alignment & Invocation | THR, FHR, FNR, AMA | AST-derived structure matching |
| Execution & Robustness | ACC_ALL, ACC_Geo, ACC_Other, pass@k, CV, SAA | result correctness and stability |

A recurrent theme in the framework is that **syntax correctness alone does not guarantee execution**. The framework therefore separates AST parse success from execution success and then further separates execution success from semantic correctness. This decomposition is especially important in GeoSQL because a query may parse correctly while still failing through nonexistent functions, incorrect overloads, argument mismatches, SRID conflicts, or invalid spatial semantics [2509.25264].

## 3. GeoSQL-Bench: corpus design and content

GeoSQL-Bench is the benchmark substrate used by GeoSQL-Eval. It is reported as comprising **14,178 tasks** spanning three types and covering **340 PostGIS 3.5 functions** collected from the official manual. Each function is modeled as a 5-tuple \( f_i = (\mathrm{Sig}_i, \mathrm{Desc}_i, \mathrm{Std}_i, \mathrm{Hist}_i, \mathrm{Ex}_i) \), capturing synopsis, description, standards, history, and example information [2509.25264].

The paper reports three principal task inventories. First, **Multiple Choice & True/False** includes **2,380 items**, consisting of **680 function purpose MCQ**, **680 parameter check T/F**, **340 return type MCQ**, and **680 general rule T/F**. Second, **Syntax-level SQL Generation** includes **3,744 items**, built from **756 function-example entries** and **two natural-language prompt variants per example**. Third, **Table Schema Retrieval** includes **2,155 items** with explicit and underspecified variants, each accompanied by schema, sample rows, and `INSERT` statements guaranteeing non-empty results [2509.25264].

The benchmark’s database layer contains **82 domain-specific databases** grouped into **eight expert-interpreted theme clusters** derived through **Word2Vec embeddings**, **Euclidean distances**, **Ward linkage**, and a **silhouette criterion**. The themes combine UN-GGIM and ISO 19115-1 `MD_TopicCategoryCode` into clusters such as **Urban Planning & Land Use**, **Buildings, Facilities & Infrastructure**, **Population & Social Services**, **Environmental Protection & Disaster Response**, **Remote Sensing & Imagery**, **Geographic Names & Spatial Location**, **Natural Resources & Geology & Soils**, and **Ecosystems & Water/Climate**. Each database contains **1–6 tables**; each table contains **3–5 fields**; at least one spatial field is required; and schemas include realistic English sample values, primary and foreign keys, and constraints embedded in type strings such as `"INTEGER PRIMARY KEY"` and `"INTEGER REFERENCES other_table(id)"` [2509.25264].

GeoSQL-Bench uses a wide range of geometry types, including `POINT`, `LINESTRING`, `POLYGON`, `MULTI*`, and occasional Z-enabled geometries, with **SRID=4326 commonly used** and task construction enforcing SRID consistency. The curation pipeline is function-driven, uses GPT-4o as a formatting tool rather than a knowledge source, and applies dual review plus single adjudication by three experts, followed by runtime verification for executability and non-empty results [2509.25264].

## 4. Automated evaluation pipeline and metrics

The evaluation pipeline standardizes prompting, generation, parsing, execution, result validation, and robustness measurement. Knowledge tasks require strict single-token outputs such as `A/B/C/D` or `True/False`. Syntax-level tasks provide only the natural-language prompt and require a single executable SQL statement. Schema-level tasks add schema and sample records, again requiring a single SQL statement. For robustness, the framework performs **five independent generations per item** [2509.25264].

Syntactic verification is performed by **pglast**, which records `SyntaxState` and `SyntaxTree`. Semantic extraction traverses the AST and extracts tables from `RangeVar.relname`, columns from `ColumnRef.fields`, function names from `FuncCall.funcname`, and ordered arguments from `FuncCall.args`. Execution is then performed in PostGIS, with `ExecState`, `ExecResult`, and `ResultType` recorded. The result type is classified as geometry, geography, numeric, text, or Boolean [2509.25264].

The foundational and execution-oriented metrics are defined explicitly:

$$
\mathrm{Accuracy\_Foundational} = \frac{1}{N}\sum_{i=1}^{N}\mathbf{1}(\hat{a}_i = a_i)
$$

$$
\mathrm{EPR} = \frac{1}{N}\sum_{i=1}^{N}\mathbf{1}(\mathrm{ExecState}_i = 1)
\qquad
\mathrm{SA} = \frac{1}{N}\sum_{i=1}^{N}\mathbf{1}(\mathrm{SyntaxState}_i = 1)
$$

For semantic alignment, the framework uses per-task table and field hit rates, a function-name indicator, and ordered argument matching:

$$
\mathrm{FNR}_j = \mathbf{1}(\mathrm{Func}_j \in E\_\mathrm{FunctionName}_j)
\qquad
\mathrm{AMA}_j = \frac{1}{n}\sum_{k=1}^{n}\mathbf{1}(a_k=b_k)
$$

For robustness, it reports:

$$
\mathrm{pass@}n = 1 - \frac{C_n}{N},
\qquad
\mathrm{CV} = \frac{\sigma}{\mu},
\qquad
\mathrm{SAA} = \frac{\mathrm{pass@}5}{1+\mathrm{CV}}
$$

Composite scores are aggregated with the **entropy weight method (EWM)**:

$$
S_i = \sum_{j=1}^{n}\frac{(1-e_j)}{\sum_{j'=1}^{n}(1-e_{j'})}X_{ij}
$$

$$
e_j = -\frac{1}{\ln m}\sum_{i=1}^{m}P_{ij}\ln P_{ij},
\qquad
P_{ij} = \frac{X_{ij}}{\sum_{i=1}^{m}X_{ij}}
$$

Geometry-aware validation is a defining feature. Geometry results are normalized to **SRID 4326** and accepted either by exact **EWKT** equality or by **topology equality** after `ST_SnapToGrid` with tolerance \(10^{-5}\), together with a **Z-check** using tolerance \(10^{-6}\); geography is coerced to geometry for uniform comparison. Text answers are normalized by lowercasing and whitespace removal. This design encodes a central methodological claim of the framework: GeoSQL evaluation requires type-aware result comparison rather than plain string matching [2509.25264].

## 5. Empirical results, model rankings, and error structure

GeoSQL-Eval evaluates **24 models across six categories**: **General Non-Reasoning Models (5)**, **General Reasoning-Enhanced Models (7)**, **General Code Generation Models (3)**, **Geospatial Code Generation Model (1)**, **General SQL Generation Models (6)**, and **GeoSQL Generation Strategies (2)**. The evaluated systems include models such as Claude3.7-Sonnet, DeepSeek-V3-0324, GPT-4.1, GPT-5, o4-mini, Qwen-3-Thinking 32B, Code-Llama-13B, GeoCode-GPT-7B, XiYan-SQL, Monkuu, and SpatialSQL [2509.25264].

In **Conceptual Understanding**, the top performers are **DeepSeek-R1-0528 (AVG=0.913)**, **Claude3.7-Sonnet (0.899)**, **GPT-5 (0.894)**, and **GPT-4.1 (0.886)**. The paper also notes that **rule compliance** is the weakest conceptual category across models, including top-ranked ones; for example, **DeepSeek-R1-0528** reaches **0.809** on `ACC_RULE`. This indicates that factual knowledge of function purpose does not eliminate difficulty with standards, defaults, dimensionality retention, and version-sensitive behavior.

In **Structured SQL**, syntax-level `EPR` leaders are **Claude3.7-Sonnet (0.877)**, **DeepSeek-V3-0324 (0.873)**, and **GPT-5 (0.856)**, while syntax accuracy for the strongest general models is near **0.99**. For schema-level generation, `EPR` leaders are **DeepSeek-R1-0528 (0.919)**, **SpatialSQL (0.863)**, and **XiYan-SQL-32B (0.834)**, with syntax accuracy often at or above **0.98**. The framework explicitly interprets this gap between `SA` and `EPR` as evidence that parser-level correctness is an insufficient proxy for PostGIS competence [2509.25264].

Semantic alignment results reinforce the same point. On syntax-level tasks, mean `FNR` is reported as slightly above **0.5**, while `AMA` is around **0.25**; Claude3.7-Sonnet and DeepSeek-V3-0324 lead this setting, and XiYan-SQL-32B and SpatialSQL are above the mean. On schema-level tasks, `THR` and `FHR` cluster near **0.97–0.99** for many models, but `FNR` still differentiates leaders such as **o4-mini** and **GPT-5**. In other words, table and field retrieval can be strong even when core spatial-function choice remains unreliable.

Execution accuracy is lower still. On schema-level tasks, **o4-mini** ranks first in `ACC_ALL` with **0.4612**, **DeepSeek-R1-0528** exceeds **0.44**, and **GPT-5** obtains the highest `ACC_Geo` at **0.9293**. On syntax-level tasks, general non-reasoning models lead overall, geometric accuracy is generally at or above **0.70**, and **o4-mini** and **GPT-5** are strong in both `ACC_ALL` and `ACC_Geo`. Robustness results show that on syntax-level tasks the `pass@5` leaders are **Gemini2.5-Flash-0520 (89.86%)**, **o4-mini (89.8%)**, and **GPT-5 (88.04%)**; on schema-level tasks, the leaders are **o4-mini (59.91%)**, **DeepSeek-R1-0528 (57.31%)**, and **SpatialSQL (51.69%)**. Underspecified prompts produce substantial degradation, including reported drops for **o4-mini** of **−0.354** on syntax-level tasks and **−0.247** on schema-level tasks [2509.25264].

The error distribution is concentrated. Roughly **70% combined** comes from **PostGIS Function Errors** and **SQL Syntax Errors**, while **SRID/Dimension mismatch** and **Geometry Parsing** become more frequent on schema-level tasks. In efficiency terms, general non-reasoning models are reported as strongest, **o4-mini** is noted for balancing accuracy and efficiency, **GPT-5** and **Gemini2.5-Flash-0520** adapt reasoning length, **Qwen3-32B-Thinking** consumes the most tokens, and **GPT-OSS-20B** is slow. The overall score distribution is summarized with **mean = 67.77**, **std = 28.32**, **skewness = -0.83**, and **kurtosis = -0.75** [2509.25264].

## 6. Related benchmarks, adjacent evaluation paradigms, and prospective extensions

GeoSQL-Eval sits within a broader ecosystem of geospatial QA and spatial-engine evaluation. **GS-QA** defines an extensible geospatial QA benchmark with **2,800 question–answer pairs across 28 templates** over OpenStreetMap and Wikipedia, covering **nearest neighbor**, **range**, **directional filter**, **towards filter**, and **intersects**, as well as outputs such as entity names, locations, distances, directions, counts, and aggregated areas or lengths; its evaluation combines normalized text measures with **distance error** and **angular error** [2605.22811]. **MapQA** provides **3,154 QA pairs** across **nine geospatial question types** in **Southern California** and **Illinois**, includes the geometries of geo-entities referenced in the questions, and evaluates retrieval and Text-to-SQL systems with **Recall@k**, execution accuracy, malformed SQL rate, and a **100 m** tolerance for distance answers [2503.07871]. **FloodSQL-Bench** contributes **443 question–SQL pairs across six levels of difficulty** in flood management, with **key-based**, **spatial**, and **hybrid joins**, and evaluates LLMs under a unified metadata-driven **RAG** protocol using SQL embedding cosine similarity as its primary metric [2512.12084]. **Spatter** introduces **Affine Equivalent Inputs (AEI)** as an oracle for spatial database correctness and reports **34 previously unknown and unique bugs**, **30 confirmed**, and **18 already fixed** across PostGIS, DuckDB Spatial, MySQL, and SQL Server [2410.12496].

These benchmarks emphasize dimensions that GeoSQL-Eval only partly addresses. GS-QA and MapQA foreground natural-language geospatial reasoning, output-type-sensitive metrics, and multi-source or junction-style reasoning; FloodSQL-Bench foregrounds heterogeneous multi-table spatial joins under a fixed RAG protocol; and Spatter foregrounds engine-level logical correctness under affine invariance and equivariance. This suggests that GeoSQL-Eval occupies a distinct position: it is centered on **PostGIS query generation quality**, not on one-shot QA performance or cross-engine metamorphic testing.

The framework’s own stated limitations are correspondingly specific. It currently emphasizes **vector processing modules**, while **raster**, **network analysis**, and some **advanced spatial operations** are underrepresented; it focuses on **PostGIS** rather than cross-DBMS GeoSQL evaluation; and future work includes **raster/vector integration**, **network analysis**, **advanced topology**, **multi-hop spatial reasoning**, **dynamic SRID handling**, **3D/temporal geometries**, **cross-DBMS GeoSQL evaluation**, **unified multi-modality/platform evaluation**, and a **long-term open platform for continuous benchmarking** [2509.25264]. A plausible implication is that future versions could combine GeoSQL-Eval’s PostGIS-specific semantic and execution analysis with GS-QA-style output-aware scoring, FloodSQL-Bench-style multi-table geospatial retrieval protocols, and AEI-style correctness oracles for spatial predicate invariance.

Source: https://www.emergentmind.com/topics/geosql-eval