File size: 14,608 Bytes
969891d | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 | import json
import logging
import math
import os
import tempfile
import threading
from typing import Dict, Any, Optional, List
import duckdb
from backend.core.data_catalog import get_data_catalog, describe_table
logger = logging.getLogger(__name__)
class ResultTooLargeError(Exception):
"""
Raised when a spatial result has more geometries than can be rendered.
Carries the counts so the caller can tell the agent exactly how far over it
is, which is what lets it rewrite the query rather than guess.
"""
def __init__(self, row_count: int, limit: int):
self.row_count = row_count
self.limit = limit
super().__init__(
f"Query matched {row_count:,} geometries, above the {limit:,} that can be "
f"rendered. Aggregate (GROUP BY a region, or average per hexagon across "
f"time) or filter (one season, one week, one country) and try again."
)
class GeoEngine:
"""
In-memory DuckDB Spatial engine.
Catalog tables are exposed as lazily-created VIEWs over their source files
(zero-copy, so startup stays fast and memory use stays low), while layers
produced by the agent are materialized as real tables so they can be
referenced by follow-up spatial operations.
"""
_instance = None
# Results are NOT truncated — a query returns every row it matched.
#
# There is still a physical limit: each row becomes a Python dict, then JSON,
# then an SVG path in the browser, so a few hundred thousand geometries will
# stall the page. Rather than silently dropping rows (which quietly makes an
# answer wrong), a result above this size is refused with an explanation, and
# the agent responds by aggregating or filtering — the query gets better
# instead of the answer getting quietly worse. Non-spatial results are exempt:
# they are cheap and never rendered as geometry.
RENDER_REFUSAL_THRESHOLD = 400_000
# Column names recognized as the geometry column in a result set.
GEOMETRY_COLUMNS = ('geom', 'geometry')
def __new__(cls):
if cls._instance is None:
cls._instance = super(GeoEngine, cls).__new__(cls)
cls._instance.initialized = False
return cls._instance
def __init__(self):
if self.initialized:
return
logger.info("Initializing GeoEngine (DuckDB)...")
try:
self.con = duckdb.connect(database=':memory:')
self.con.install_extension('spatial')
self.con.load_extension('spatial')
logger.info("GeoEngine initialized with Spatial extension.")
except Exception as e:
logger.error(f"Failed to initialize GeoEngine: {e}")
raise
self.layers: Dict[str, str] = {} # layer_id -> table_name
self.catalog = get_data_catalog()
# A single DuckDB connection is shared process-wide, but it is NOT safe to
# use from several threads at once. The agent runs tool calls concurrently
# and sub-agents run in parallel, so every statement goes through this
# lock. Without it, concurrent DESCRIBE/SELECT calls deadlock and the
# request hangs with no error. Re-entrant because several methods here
# call one another (ensure_table_loaded -> is_table_loaded).
self._lock = threading.RLock()
self.initialized = True
def fetch_all(self, sql: str) -> List[tuple]:
"""Run a read query and return all rows, holding the connection lock."""
with self._lock:
return self.con.execute(sql).fetchall()
def fetch_one(self, sql: str) -> Optional[tuple]:
"""Run a read query and return the first row, holding the connection lock."""
with self._lock:
return self.con.execute(sql).fetchone()
def describe_columns(self, table_name: str) -> List[tuple]:
"""Column metadata for a table, holding the connection lock."""
with self._lock:
return self.con.execute(f'DESCRIBE "{table_name}"').fetchall()
def is_table_loaded(self, table_name: str) -> bool:
"""Check whether a table or view already exists in this connection."""
with self._lock:
try:
self.con.execute(f'DESCRIBE "{table_name}"')
return True
except duckdb.Error:
return False
def ensure_table_loaded(self, table_name: str) -> bool:
"""
Ensure a catalog table is available in DuckDB, creating a view over its
source file if needed. Returns True if the table is queryable.
"""
if self.is_table_loaded(table_name):
return True
file_path = self.catalog.get_file_path(table_name)
if not file_path or not file_path.exists():
logger.warning(f"Table {table_name} not found in catalog or file missing.")
return False
metadata = self.catalog.get_table_metadata(table_name)
if not metadata:
logger.warning(f"No metadata found for {table_name}")
return False
try:
logger.info(f"Lazy loading table: {table_name}")
# DuckDB's spatial extension reads GeoParquet geometry metadata
# directly, so both Parquet variants use the same reader.
if file_path.suffix == '.parquet' or metadata.get('format') in ('parquet', 'geoparquet'):
reader = f"read_parquet('{file_path}')"
else:
reader = f"ST_Read('{file_path}')"
with self._lock:
self.con.execute(f'CREATE OR REPLACE VIEW "{table_name}" AS SELECT * FROM {reader}')
return True
except Exception as e:
logger.error(f"Failed to load {table_name}: {e}")
return False
def get_table_schemas(self) -> str:
"""Get the schema of every currently loaded table, for LLM context."""
result = "Currently Loaded Tables:\n\n"
try:
with self._lock:
tables = self.con.execute("SHOW TABLES").fetchall()
except duckdb.Error as e:
logger.error(f"Error listing tables: {e}")
return result
for (table_name,) in tables:
result += self._describe_table(table_name)
return result
def get_table_schemas_for_tables(self, table_names: List[str]) -> str:
"""
Get the schema for specific tables, including the catalog's semantic
description so the LLM knows what each table actually contains.
"""
if not table_names:
return "No tables available in the current scope.\n"
result = "Available Tables:\n\n"
for table_name in table_names:
result += self._describe_table(table_name, include_description=True)
return result
def _describe_table(self, table_name: str, include_description: bool = False) -> str:
"""Render one table's schema as LLM context. Returns "" if unavailable."""
try:
with self._lock:
columns = self.con.execute(f'DESCRIBE "{table_name}"').fetchall()
row_count = self.con.execute(f'SELECT COUNT(*) FROM "{table_name}"').fetchone()[0]
except duckdb.Error:
return "" # Not loaded yet
out = f"### {table_name} ({row_count} rows)\n"
if include_description:
meta = self.catalog.get_table_metadata(table_name)
if meta:
desc = describe_table(meta)
if desc and desc != "No description":
out += f"**Description**: {desc}\n"
out += "Columns:\n"
for col_name, col_type, *_ in columns:
if col_name in self.GEOMETRY_COLUMNS:
out += f" - {col_name}: GEOMETRY (spatial data)\n"
else:
out += f" - {col_name}: {col_type}\n"
return out + "\n"
def register_layer(self, layer_id: str, geojson: Dict[str, Any]) -> str:
"""
Register a GeoJSON FeatureCollection as a queryable DuckDB table so the
agent can reference it in later spatial operations. Returns the table name.
"""
table_name = f"layer_{layer_id.replace('-', '_')}"
def json_serial(obj):
if hasattr(obj, 'isoformat'):
return obj.isoformat()
raise TypeError(f"Type {type(obj)} not serializable")
tmp_path = None
try:
with self._lock:
self.con.execute(f'DROP TABLE IF EXISTS "{table_name}"')
# ST_Read needs a file, so round-trip through a temp file rather than
# hand-unpacking nested GeoJSON into columns.
with tempfile.NamedTemporaryFile(mode='w', suffix='.json', delete=False) as tmp:
json.dump(geojson, tmp, default=json_serial)
tmp_path = tmp.name
with self._lock:
self.con.execute(f'CREATE TABLE "{table_name}" AS SELECT * FROM ST_Read(\'{tmp_path}\')')
self.layers[layer_id] = table_name
logger.info(f"Registered layer {layer_id} as table {table_name}")
return table_name
except Exception as e:
logger.error(f"Error registering layer {layer_id}: {e}")
raise
finally:
if tmp_path and os.path.exists(tmp_path):
os.unlink(tmp_path)
def execute_spatial_query(self, sql: str) -> Dict[str, Any]:
"""
Execute a SELECT and return the result as a GeoJSON FeatureCollection.
Handles both spatial results (a geometry column is present) and plain
aggregates (no geometry, features carry properties only). Every matching
row is returned; nothing is truncated.
"""
logger.info(f"Executing Spatial SQL: {sql}")
# Held across the whole method: the result is staged in a temp table with
# a fixed name, so two concurrent queries would overwrite each other's
# results and return the wrong rows.
with self._lock:
return self._execute_spatial_query_locked(sql)
def _execute_spatial_query_locked(self, sql: str) -> Dict[str, Any]:
# DuckDB Spatial has no FeatureCollection writer, so the result is
# staged in a temp table and assembled row by row below.
self.con.execute(f"CREATE OR REPLACE TEMP TABLE query_result AS {sql}")
columns = self.con.execute("DESCRIBE query_result").fetchall()
col_names = [c[0] for c in columns]
geom_col = next((c for c in col_names if c in self.GEOMETRY_COLUMNS), None)
total_count = self.con.execute("SELECT COUNT(*) FROM query_result").fetchone()[0]
properties: Dict[str, Any] = {"row_count": total_count}
# Refuse before materializing: DuckDB holds the result cheaply, and the
# expensive part is building hundreds of thousands of Python dicts. The
# caller turns this into actionable guidance for the agent.
if geom_col is not None and total_count > self.RENDER_REFUSAL_THRESHOLD:
raise ResultTooLargeError(total_count, self.RENDER_REFUSAL_THRESHOLD)
if geom_col is None:
# Non-spatial result (e.g. a COUNT or GROUP BY aggregate).
rows = self.con.execute("SELECT * FROM query_result").fetchall()
features = [
{
"type": "Feature",
"geometry": None,
"properties": {
name: self._coerce_value(value)
for name, value in zip(col_names, row)
},
}
for row in rows
]
return {"type": "FeatureCollection", "features": features, "properties": properties}
other_cols = [c for c in col_names if c != geom_col]
# Quote identifiers so columns like OSM's "generator:source" survive.
select_clause = ", ".join(
[f'ST_AsGeoJSON("{geom_col}")'] + [f'"{c}"' for c in other_cols]
)
rows = self.con.execute(f"SELECT {select_clause} FROM query_result").fetchall()
features = []
for row in rows:
features.append({
"type": "Feature",
"geometry": json.loads(row[0]) if row[0] else None,
"properties": {
name: self._coerce_value(value)
for name, value in zip(other_cols, row[1:])
},
})
return {"type": "FeatureCollection", "features": features, "properties": properties}
@staticmethod
def _coerce_value(value: Any) -> Any:
"""Make a DuckDB value JSON-serializable (Parquet/WKB columns yield bytes)."""
if isinstance(value, bytes):
try:
return value.decode('utf-8')
except UnicodeDecodeError:
return str(value)
# NaN/Infinity are legal floats but illegal JSON. They arise routinely
# from analytics (AVG of an empty group, area of a degenerate geometry),
# and emitting them produces a payload that the API and the browser both
# reject with an opaque parse error. null is the honest encoding.
if isinstance(value, float) and (math.isnan(value) or math.isinf(value)):
return None
return value
def get_table_name(self, layer_id: str) -> Optional[str]:
return self.layers.get(layer_id)
def get_user_layers(self) -> List[str]:
"""Return the table names of all agent- and user-registered layers."""
return list(self.layers.values())
def drop_layer(self, layer_id: str) -> bool:
"""
Drop a registered layer from DuckDB and internal tracking.
Returns True if it was found and dropped.
"""
table_name = self.layers.get(layer_id)
if not table_name:
logger.warning(f"Layer {layer_id} not found in GeoEngine")
return False
try:
with self._lock:
self.con.execute(f'DROP TABLE IF EXISTS "{table_name}"')
del self.layers[layer_id]
logger.info(f"Dropped layer {layer_id} (table: {table_name})")
return True
except duckdb.Error as e:
logger.error(f"Failed to drop layer {layer_id}: {e}")
return False
_geo_engine = None
def get_geo_engine() -> GeoEngine:
global _geo_engine
if _geo_engine is None:
_geo_engine = GeoEngine()
return _geo_engine
|