spatial

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

You are answering spatial questions using DuckDB's spatial extension and, when needed, Overture Maps as a free global data source.

Question or file:

$0

Additional context:

${1:-}

您将使用DuckDB的空间扩展，必要时结合Overture Maps这一免费全球数据源来解答空间相关问题。

问题或文件：

$0

额外上下文：

${1:-}

Step 1 — Understand what the user needs

步骤1 — 理解用户需求

Classify the question:

Pattern	Data source	Key functions
"Find X near Y" (no user file)	Overture Maps on S3	`ST_Distance_Spheroid` , bbox filtering
"How far between A and B"	Geocode or user data	`ST_Distance_Spheroid`
"Which points fall inside polygons"	User files	`ST_Contains`
"Analyze this GeoJSON/Shapefile/GPX"	User file	`ST_Read` , measurement functions
"Show density/hotspots"	User or Overture data	H3 hex binning
"Convert to GeoJSON/GeoPackage"	User file	`COPY TO (FORMAT GDAL)`
"Count buildings/roads in area"	Overture Maps	bbox filtering + aggregation

If the question involves real-world places, POIs, buildings, roads, or boundaries and the user hasn't provided a file, use Overture Maps — read

references/overture.md

for S3 paths and schema.

For spatial function syntax, read

references/functions.md

对问题进行分类：

模式	数据源	核心功能
“查找Y附近的X”（无用户文件）	S3上的Overture Maps	`ST_Distance_Spheroid` 、边界框过滤
“A和B之间的距离是多少”	地理编码或用户数据	`ST_Distance_Spheroid`
“哪些点落在多边形内”	用户文件	`ST_Contains`
“分析此GeoJSON/Shapefile/GPX”	用户文件	`ST_Read` 、测量函数
“展示密度/热点区域”	用户数据或Overture数据	H3六边形网格划分
“转换为GeoJSON/GeoPackage”	用户文件	`COPY TO (FORMAT GDAL)`
“统计区域内的建筑/道路数量”	Overture Maps	边界框过滤 + 聚合

如果问题涉及现实世界地点、兴趣点（POI）、建筑、道路或边界，且用户未提供文件，请使用Overture Maps——查看

references/overture.md

获取S3路径和模式。

关于空间函数语法，请查看

references/functions.md

。

Step 2 — Write and run the query

步骤2 — 编写并运行查询

Always start with:

sql

LOAD spatial;
SET geometry_always_xy = true;

Add extensions as needed:

Overture/remote data:

LOAD httpfs; CREATE SECRET (TYPE S3, PROVIDER config, REGION 'us-west-2');

H3 hex binning:
```
INSTALL h3 FROM community; LOAD h3;
```

始终从以下代码开始：

sql

LOAD spatial;
SET geometry_always_xy = true;

根据需要添加扩展：

Overture/远程数据：

LOAD httpfs; CREATE SECRET (TYPE S3, PROVIDER config, REGION 'us-west-2');

H3六边形网格划分：
```
INSTALL h3 FROM community; LOAD h3;
```

Key principles

核心原则

bbox filtering first — When querying Overture, always filter on

bbox.xmin/xmax/ymin/ymax

before any spatial function. This uses Parquet predicate pushdown and avoids downloading the full dataset.

Always set
geometry_always_xy = true
— This ensures all spatial functions interpret coordinates as longitude, latitude (the standard for Overture, GeoJSON, and most data sources). Without it, spheroid functions assume latitude first and return wrong results.

Use spheroid functions for real-world distances —

ST_Distance_Spheroid

returns meters on the WGS84 ellipsoid. Plain

ST_Distance

uses planar coordinates and gives meaningless results for lat/lng. Important: spheroid functions (

ST_Distance_Spheroid

ST_Area_Spheroid

, etc.) require

POINT_2D

inputs, not generic

GEOMETRY

. Overture geometry columns are typed

GEOMETRY('OGC:CRS84')

and cannot be cast directly. Extract coordinates first:

sql

ST_Point(ST_X(geometry), ST_Y(geometry))::POINT_2D

CSV with lat/lng needs conversion —

ST_Point(longitude, latitude)

(longitude first). This is the most common gotcha.

Run the query in a single bash call:

bash

duckdb -c "
LOAD spatial;
<ADDITIONAL_SETUP>
<YOUR_QUERY>
"

先进行边界框过滤——查询Overture时，务必在调用任何空间函数前先过滤

bbox.xmin/xmax/ymin/ymax

。这会利用Parquet谓词下推，避免下载完整数据集。

始终设置
geometry_always_xy = true
——这确保所有空间函数将坐标解读为经度、纬度（Overture、GeoJSON及大多数数据源的标准格式）。如果不设置，球体函数会默认先纬度后经度，导致结果错误。

使用球体函数计算真实世界距离——

ST_Distance_Spheroid

返回基于WGS84椭球体的米数。普通的

ST_Distance

使用平面坐标，对经纬度数据来说结果毫无意义。**重要提示：**球体函数（

ST_Distance_Spheroid

、

ST_Area_Spheroid

等）要求输入为

POINT_2D

类型，而非通用的

GEOMETRY

类型。Overture的几何列类型为

GEOMETRY('OGC:CRS84')

，无法直接转换。需先提取坐标：

sql

ST_Point(ST_X(geometry), ST_Y(geometry))::POINT_2D

含经纬度的CSV需要转换——使用

ST_Point(longitude, latitude)

（经度在前）。这是最常见的易错点。

通过单个bash命令运行查询：

bash

duckdb -c "
LOAD spatial;
<ADDITIONAL_SETUP>
<YOUR_QUERY>
"

Step 3 — Present results

步骤3 — 呈现结果

For tabular results: show the data directly
For spatial results: consider exporting to GeoJSON for visualization (
```
COPY TO 'result.geojson' WITH (FORMAT GDAL, DRIVER 'GeoJSON')
```
)
For distance/area results: use human-readable units (km for large distances, m for small)
For density/hotspot results: describe the pattern and offer to export for visualization

If the query fails:

duckdb: command not found
→ delegate to

/duckdb-skills:install-duckdb

Missing extension →

INSTALL spatial; LOAD spatial;

INSTALL h3 FROM community; LOAD h3;

S3 access denied → suggest checking AWS credentials
No results with Overture → widen the bbox, check the category spelling, or try a broader search

对于表格结果：直接展示数据
对于空间结果：考虑导出为GeoJSON以便可视化（
```
COPY TO 'result.geojson' WITH (FORMAT GDAL, DRIVER 'GeoJSON')
```
）
对于距离/面积结果：使用易读的单位（大距离用千米，小距离用米）
对于密度/热点区域结果：描述模式并提供导出可视化的选项

如果查询失败：

duckdb: command not found
→ 调用

/duckdb-skills:install-duckdb

缺少扩展 → 执行

INSTALL spatial; LOAD spatial;

或

INSTALL h3 FROM community; LOAD h3;

S3访问被拒绝 → 建议检查AWS凭证
使用Overture无结果 → 扩大边界框、检查类别拼写或尝试更宽泛的搜索