Complexity-Tiered Text-to-SQL Performance of Large Language Models in Smart-City Decision Support

Abstract

High-value sensor data generated by smart-city observatories is often inaccessible to policymakers due to technical barriers. Large language models (LLMs) have been proposed as one way to reduce this bottleneck, but their ability to process real, complex sensor databases remained largely untested. This study benchmarked 29 LLMs across eleven model families using a real dataset from the Newcastle Urban Observatory, comprising 1,411 sensors, 112 variable types, and nearly 88,000 time-stamped readings. A five-tier SQL query complexity grading system was designed, pairing each level with a distinct decision-support scenario, from simple lookups to cross-theme analytics based on common table expressions. Across 4,246 scored model-question pairs, overall execution accuracy was 48.9%, falling from 76.8% in the simplest tier to 26.7% in the hardest (Spearman $\rho =-0.327$ ). Model accuracies ranged from 37.3% to 54.7% with overlapping confidence intervals, and a mixed-effects analysis showed that question-level heterogeneity was far larger than model-level heterogeneity. Among the errors, 78.1% were executable but semantically incorrect responses and 20.4% were one-second query timeouts. These results suggest that current LLMs can support basic smart-city data queries, but complex, high-stakes analysis requires additional safeguards and expert supervision.