跳至正文
OpenAgentSkill
浏览已开放 · 目录记录日期 2026-11-21 · 自动更新和账户服务暂时关闭。
数据与分析

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:

  • 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

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, explicitly state area/length validation is not applicable.
  4. Sanity-check whether row count and area/length are reasonable for the place and feature type (use domain knowledge). If nu
阅读完整来源 ↗