/geosql

name: geosql
description: Build cost-safe geospatial SQL for BigQuery, DuckDB, Snowflake, Wherobots, and Postgres and/or render results on an interactive Dekart map.
user-invocable: true

GeoSQL

Tools/CLI

This skill uses the following CLIs:

• `dekart` for running BigQuery, DuckDB, Snowflake, Wherobots, and Postgres SQL and rendering maps
• `bq` to run BigQuery SQL when `dekart` BigQuery integration is not available.
• `snow` to run Snowflake SQL when `dekart` Snowflake integration is not available.
• Wherobots and Postgres have no local CLI fallback; they are dekart-only in this skill.

Before using CLIs, verify availability if it was not done before:

for c in bq snow dekart; do command -v $c >/dev/null && echo $c=ok || echo $c=missing; done

If no CLI is available, suggest installing the dekart CLI: `pip install dekart && dekart init`. `dekart init` is an interactive command that must be run by the user.

The user has 3 options in `dekart init`:

• cloud.dekart.xyz (Dekart-hosted control plane, easier setup)
• localhost (`docker run -p 8080:8080 dekartxyz/dekart`, more secure, requires Docker)
• self-hosted

Help the user pick the best installation option for their needs.

If `dekart` is available, check available connectors:

dekart call --name list_connections --args '{}' --json

If dekart available but not init, ask user to `dekart init`

Dialect References

Use the matching reference for per-database SQL, CLI examples, and engine-specific caveats:

• BigQuery: `references/bigquery.md`
• DuckDB: `references/duckdb.md`
• Snowflake: `references/snowflake.md`
• Postgres / PostGIS: `references/postgres.md`
• Wherobots / Sedona: `references/wherobots.md`

Dekart map references:

• Map config (`update_report_map_config`): `references/map-config.md`
• Charts (widgets): `references/widgets.md`
• Styling: `references/map-styling.md`

Required Workflow

Follow these steps in order. Do NOT write a final query until steps 1-3 are complete. Combine each step with examples in matching dialect reference.

Step 1: Discover schema

Discover available data objects before writing any query: start with database/share availability (where applicable), then confirm schemas, tables, and columns. Always verify exact object names and column types from the warehouse metadata; do not assume from general knowledge.

When multiple tables match the entity, sample each candidate for attribute density and prefer the richer source. Richer attributes enable stronger visual encoding in Step 5 and a stronger map validation case.

Step 2: Resolve the target area

When the user asks about a named area (city, district, country), query `division_area` first to discover how it is actually stored (subtype, class, naming conventions). Do not assume from general knowledge.

Use the dialect reference for target-area SQL and fallbacks. Extract the exact bbox constants from the result. Use the full precision values returned by the query, do not round or truncate them.

Step 3: Draft the query

• Use hardcoded bbox from step 2 as a scan gate (fast partition pruning).
• Add `ST_INTERSECTS` against the real geometry from `division_area` for geographic correctness. bbox alone is rectangular and overshoots.
• Both are required for named-area queries. Never omit `ST_INTERSECTS`.
• Select only required columns; avoid `SELECT *`.
• Add `LIMIT` for exploration.

**bbox overlap filter direction.** The scan gate must use the OVERLAP pattern, not containment. The feature's bbox must overlap the target area:

-- CORRECT (overlap): feature extends into our area
AND bbox.xmax >= <area_xmin>   -- feature's right edge is east of area's left
AND bbox.xmin <= <area_xmax>   -- feature's left edge is west of area's right
AND bbox.ymax >= <area_ymin>   -- feature's top edge is above area's bottom
AND bbox.ymin <= <area_ymax>   -- feature's bottom edge is below area's top

-- WRONG (containment): do NOT use this
AND bbox.xmin >= <area_xmin>   -- WRONG
AND bbox.xmax <= <area_xmax>   -- WRONG

Use the dialect reference for draft query examples and engine-specific syntax. For map output, select a geometry column aliased exactly as lowercase `geometry`.

**cast 64-bit integer fields to 32-bit int or double.** Any numeric column bound to a Kepler visual channel (color, stroke width, size, height) must NOT be a 64-bit integer.

Step 4: Validate (mandatory)

Do NOT present the query to the user without validating it first.

1. Cost / safety gate.

• BigQuery: dry run to check estimated bytes. If using `dekart`, the mandatory `dekart call --name update_query` prepare step is also the dry-run gate; read `dry_run.valid` and `dry_run.estimated_bytes_processed` from that response before running `dekart run-query`.

If using `bq` CLI, run the query with `bq query --use_legacy_sql=false --dry_run --format=json '<SQL>'` and parse the JSON output for `totalBytesProcessed`.

• If dry run fails: read the bq error output. Common causes: string vs int type mismatch, missing backtick escaping, reserved keyword collision.
• If estimated bytes exceed budget: do NOT execute. Instead, rewrite the query to be cheaper (tighter bbox, more filters, lower H3 resolution, etc) and validate again.

2. Validate row count: execute `COUNT(*)` (or equivalent) to confirm count is reasonable. If count is zero, debug before presenting. 3. Validate geometry magnitude when possible:

• If output includes polygonal `GEOGRAPHY`, compute total area (for example `SUM(ST_AREA(geometry))`) and return units in square meters (and optionally km²).
• If output includes line `GEOGRAPHY`, compute total length (for example `SUM(ST_LENGTH(geometry))`) and return units in meters (and optionally km).
• If output includes `geometry`, measurements are CRS units. For EPSG:4326 geometry, do not report meters; transform/cast first or label the result as CRS units.
• If geometry is point-only or no geometry is selected, explic