bigquery-overture-skill

name: bigquery-overture-skill description: Build and optionally execute cost-safe Overture Maps SQL through the BigQuery bq CLI, with mandatory dry-run cost checks and visualization handoff. Use when a user needs map-ready Overture Maps queries in BigQuery, wants SQL-only output or executed numeric results, and needs budget enforcement with clear over-budget fallback options.

BigQuery Overture Cost Aware

Use this skill for Overture Maps work in BigQuery with strict cost controls.

Non-Installation Rule

Never install software automatically. If dependencies are missing, report exact prerequisite commands for the user to run.

Preferred Execution Path

Use the bundled script for deterministic behavior:

./scripts/run_cost_checked_query.sh --query-file /path/to/query.sql --mode sql_only

Or:

./scripts/run_cost_checked_query.sh --query "SELECT ..." --mode execute

Script location:

• scripts/run_cost_checked_query.sh

The script already handles:

• Optional .env loading from current working directory
• BQ_PROJECT_ID fallback to gcloud config get-value project
• BQ_LOCATION optional passthrough
• BQ_MAX_BYTES_BILLED safe default 10737418240 (10 GiB)
• Optional auth env support: GOOGLE_APPLICATION_CREDENTIALS, BIGQUERY_CREDENTIALS_BASE64
• Mandatory dry run and bytes budget gate before execution

Inputs

Collect or infer:

• mode: infer from user intent (sql_only for query drafting/verification, execute when user asks for actual numbers/results)
• User intent: dataset/theme, filters, output columns, aggregation, map vs numeric output
• Optional bounds: bbox, date/time, row limit
• Optional explicit over-budget override

Guardrails

Apply all guardrails every time:

1. Run dry run before execution.
2. Enforce maximum_bytes_billed.
3. Prefer bounded SQL by default (bbox/date/limit, minimal selected columns).
4. If estimated bytes exceed budget and no explicit override: do not execute.
5. If over budget: provide lower-cost SQL variants.

Query Construction Rules

1. Select only required columns; avoid SELECT *.
2. Prefer filtered Overture tables and partition-friendly predicates.
3. Add default limits when user omitted bounds:

• Spatial bounding constraints for map requests
• Row cap (LIMIT) for previews

4. Separate heavy geometry retrieval from numeric aggregation when practical.

H3 Aggregation Support

Use H3 when requested by the user, or when spatial aggregation/binning is needed (heatmaps, density summaries, cell-based rollups).

H3 function namespace by location

• Use jslibs.h3.* (US convenience namespace) for US/default location queries.
• Use jslibs.eu_h3.* for EU location queries.
• jslibs.us_h3.* is equivalent to US behavior when explicit namespace is preferred.

H3 usage patterns

1. Point/cell index:

jslibs.h3.ST_H3(<geography_point>, <resolution>)

2. Polygon fill to cells:

jslibs.h3.ST_H3_POLYFILLFROMGEOG(<geography_polygon>, <resolution>)

3. Cell boundary for visualization:

jslibs.h3.ST_H3_BOUNDARY(<h3_index>)

H3 safety/cost rules

1. Apply selective WHERE + hardcoded bbox first, then compute H3.
2. For aggregation queries, return h3 + aggregate metrics before adding boundaries.
3. Use COUNT(*) previews before geometry-heavy ST_H3_BOUNDARY output.
4. Keep resolution reasonable by default (7-9 city scale) unless user requests otherwise.
5. If over budget, lower resolution or narrow bbox/date filters before retrying.

Schema Discovery Patterns

List Overture tables:

SELECT table_name
FROM `bigquery-public-data.overture_maps.INFORMATION_SCHEMA.TABLES`
ORDER BY table_name;

List columns for one table:

SELECT
  column_name,
  data_type,
  is_nullable,
  ordinal_position
FROM `bigquery-public-data.overture_maps.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'division_area'
ORDER BY ordinal_position;

Querying Overture Safely (Small/Fast Only)

Rules:

• Always start with selective WHERE filters.
• Always include a hard-coded bbox prefilter on bbox.xmin/xmax/ymin/ymax for large feature tables (for example, segment, building, place). This prevents full table scans by enabling partition pruning.
• bbox does not replace real boundaries. When the user asks about a named area (city, district, country), always resolve its actual geometry from division_area and clip with ST_INTERSECTS. bbox is only a fast pre-filter gate — it is rectangular and overshoots real boundaries.
• Never omit ST_INTERSECTS for named-area queries. Always use bbox + ST_INTERSECTS together: bbox gates the scan cost, ST_INTERSECTS ensures geographic correctness.
• Add LIMIT for exploration.
• Prefer aggregate previews (COUNT(*)) before geometry-heavy pulls.
• Avoid full table scans and large result sets.

Example (optimized rail query pattern):

WITH city AS (
  SELECT geometry
  FROM `bigquery-public-data.overture_maps.division_area`
  WHERE country = 'DE'
    AND region = 'DE-BE'
    AND subtype = 'region'
    AND class = 'land'
  LIMIT 1
)
SELECT
  s.id,
  s.geometry
FROM `bigquery-public-data.overture_maps.segment` s
CROSS JOIN city c
WHERE s.subtype = 'rail'
  -- hardcoded city bbox (Berlin in this example)
  AND s.bbox.xmax >= 13.08834457397461
  AND s.bbox.xmin <= 13.761162757873535
  AND s.bbox.ymax >= 52.33823776245117
  AND s.bbox.ymin <= 52.67551040649414
  -- exact clip after bbox gate
  AND ST_INTERSECTS(s.geometry, c.geometry)
LIMIT 1000;

Recommended Agent Workflow

1. Discover table and column metadata via INFORMATION_SCHEMA.
2. Resolve the target area's actual geometry from division_area. Extract its bbox numeric constants and hardcode them into the query as the scan gate.
3. Draft the query with bbox prefilter first (scan gate), then ST_INTERSECTS against the real boundary geometry second (exact clip). Both are required for named-area queries.
4. Validate output shape and types with LIMIT or COUNT(*).
5. Iterate in small steps; do not run broad/full extraction queries unless explicitly requested.

Mode Behavior

Decide mode automatically unless user explicitly requests one.

sql_only (default)

• Build optimized SQL
• Run mandatory dry run
• Return SQL + estimated bytes + budget pass/fail + visualization handoff

execute

• Build optimized SQL
• Run mandatory dry run
• Execute only if user asked for results and estimate is within budget (or explicit user override)
• Return rows/aggregates preview + SQL + visualization handoff

Failure Handling

If bq unavailable or auth fails:

• Return exact fix commands only (no auto-install/no auto-auth side effects).

If over budget:

• Keep status=blocked_over_budget
• Do not execute query
• Return at least one cheaper SQL variant

If query invalid:

• Return corrected SQL draft and rerun dry-run logic

Output Contract

Always return:

• mode
• status (dry_run_only | executed | blocked_over_budget)
• project_id
• location
• estimated_bytes
• max_bytes_billed
• query_sql
• result_preview (if executed)
• visualization_handoff (dekart, bigquery_studio)
• next_steps

Visualization Handoff

Always include both handoffs:

• dekart: open Dekart, connect to same project, paste query_sql, map geometry fields.
• bigquery_studio: open https://console.cloud.google.com/bigquery?project=<PROJECT_ID> and paste query_sql in SQL workspace.

Response Quality Rules

1. Be explicit about budget pass/fail.
2. Show the final SQL used for dry run.
3. Keep previews concise.
4. If blocked, include cheaper alternatives.