Geosql
A geospatial analytics skill for AI agents like Claude, Codex, and Copilot, enabling map-based queries on PostGIS, BigQuery, Snowflake.
dekart-xyz · 已有 AI 审核记录
安装与使用
运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。
npx skills add dekart-xyz/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:
dekartfor running BigQuery, DuckDB, Snowflake, Wherobots, and Postgres SQL and rendering mapsbqto run BigQuery SQL whendekartBigQuery integration is not available.snowto run Snowflake SQL whendekartSnowflake 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
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_INTERSECTSagainst the real geometry fromdivision_areafor 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
LIMITfor 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.
- Cost / safety gate.
- BigQuery: dry run to check estimated bytes. If using
dekart, the mandatorydekart call --name update_queryprepare step is also the dry-run gate; readdry_run.validanddry_run.estimated_bytes_processedfrom that response before runningdekart run-query. If usingbqCLI, run the query withbq query --use_legacy_sql=false --dry_run --format=json '<SQL>'and parse the JSON output fortotalBytesProcessed. - 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.
- BigQuery: dry run to check estimated bytes. If using
- Validate row count: execute
COUNT(*)(or equivalent) to confirm count is reasonable. If count is zero, debug before presenting. - Validate geometry magnitude when possible:
- If output includes polygonal
GEOGRAPHY, compute total area (for exampleSUM(ST_AREA(geometry))) and return units in square meters (and optionally km²). - If output includes line
GEOGRAPHY, compute total length (for exampleSUM(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, explicitly state area/length validation is not applicable.
- If output includes polygonal
- Sanity-check whether row count and area/length are reasonable for the place and feature type (use domain knowledge). If nu