Copilot commented on code in PR #131: URL: https://github.com/apache/sedona-spatialbench/pull/131#discussion_r3651876527
########## docs/geography-queries.zh.md: ########## @@ -0,0 +1,418 @@ +--- +title: 地理查询 +--- + +<!--- + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + http://www.apache.org/licenses/LICENSE-2.0 + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. +--> + +# 地理（Geography）查询套件 + +SpatialBench 的[主查询套件](queries.md)是一个 **geometry（几何）** 基准：WKB 列通过 +`ST_GeomFromWKB` 解码，谓词使用平面（二维欧几里得）边，所有距离阈值都是以度为单位的角度。 + +本页定义这同样 12 条查询的 **geography（地理）** 版本。数据本身没有变化——仍是同样的 +EPSG:4326 经纬度 WKB 列——但列改用 `ST_GeogFromWKB` 解码，因此边被解释为球面上的大地线， +所有度量结果都以真实世界的单位返回。 + +## 为什么需要第二套查询 + +geometry 套件不得不伪造真实世界的单位。它用 `ST_DWithin(..., 0.45)` 筛选 Sedona “50 公里内” +的行程，用 `1 米 = 0.000009 度` 把米换算成度，并以平方度报告建筑物的重叠面积。 + +一个纬度差大致在各处都约等于 111 公里，但一个经度差会随纬度的余弦而缩小。因此在 Sedona +（北纬 34.87°），`0.45` 度约为 50 公里高、却只有约 41 公里宽；在北纬 60° 处仅约 25 公里宽。 +由于 SpatialBench 的数据生成在八个大陆级别的范围框内、跨越南纬 56° 到北纬 78°，以度为基准的 +半径在数据集的每个区域都对应着不同的真实形状。 + +geography 套件把这些全部替换为米和平方米： + +| | geometry 套件 | geography 套件 | +|---|---|---| +| 点/面构造函数 | `ST_GeomFromWKB`、`ST_GeomFromText` | `ST_GeogFromWKB`、`ST_GeogFromWKT` | +| 边的解释 | 平面上的直线 | 球面上的大地线 | +| `ST_DWithin` 阈值 | 度（`0.45`、`0.045`、`0.0045`） | 米（`50000`、`5000`、`500`） | +| `ST_Distance`、`ST_Length` | 度 | 米 | +| `ST_Area` | 平方度 | 平方米 | +| SQL 中的单位换算 | `/ 0.000009` | 无需换算 | + +!!! note "两套查询的结果无法逐行比较" + + 0.45° 的平面半径与 50 000 米的大地线半径会选出不同的行程，因此 Q1 在两套查询中返回的 + 是不同的 100 行。这正是本套件的意义所在，而不是结果不一致：每套查询都有各自的标准答案， + 结果只应在同一套件内部进行比较。 + +## 运行方式 + +这些查询可通过查询模块以 SQL 形式获取，该模块会按指定方言打印整套查询： + +```bash +python3 spatialbench-queries/print_geography_queries.py SedonaDB +``` + +它们需要一个具备 `GEOGRAPHY` 类型、且其度量基于大地线的引擎——各引擎对具体函数的支持仍在逐步 +完善，请查阅所用引擎自身的参考文档以确认其实现范围。Q12 在下文中给出了定义，但尚未纳入该模块： +它是一个 K 最近邻连接，而 `ST_KNN` 目前还没有 geography 形式。 + +## Q1：查找从 Sedona 市中心 50 公里范围内出发的行程，并按距离排序 + +**实际场景：** 按照与市中心的距离识别并排序行程，用于城市规划与交通分析。 + +与 geometry 版本不同，这里的 50 公里是真正的大地线半径，而不是 0.45° 的角度半径，且 +`distance_to_center` 以米为单位。 + +**所考察的空间查询特性：** + +1. 基于大地线距离的空间过滤（`ST_DWithin`，米） +2. 到固定点的大地线距离计算 +3. 坐标提取（`ST_X`、`ST_Y`） +4. 按空间距离排序 + +```sql +SELECT + t.t_tripkey, + -- A point's coordinates are identical under either edge interpretation, so the lon/lat + -- projection stays on the geometry accessor, which is the more widely available one. + ST_X(ST_GeomFromWKB(t.t_pickuploc)) AS pickup_lon, + ST_Y(ST_GeomFromWKB(t.t_pickuploc)) AS pickup_lat, + t.t_pickuptime, + ST_Distance( + ST_GeogFromWKB(t.t_pickuploc), + ST_GeogFromWKT('POINT (-111.7610 34.8697)') + ) AS distance_to_center -- metres +FROM trip t +WHERE ST_DWithin( + ST_GeogFromWKB(t.t_pickuploc), + ST_GeogFromWKT('POINT (-111.7610 34.8697)'), + 50000 -- 50 km geodesic radius around Sedona center +) +ORDER BY distance_to_center ASC, t.t_tripkey ASC +LIMIT 100 -- Return only the 100 closest trips (bounded result set) +``` + +## Q2：统计从 Coconino County 出发的行程数 + +**实际场景：** 统计某一行政边界内的行程活动。 + +县级多边形来自 Overture 数据，其边为平面边；将它作为 geography 读取会把这些边重新解释为 +大地线，相对 geometry 套件可能使边界偏移几米，从而改变那些几乎正好落在边界上的上车点的计数。 + +**所考察的空间查询特性：** + +1. 球面上的点在多边形内谓词（`ST_Intersects`） +2. 由标量子查询产生查询用 geography + +```sql +SELECT COUNT(*) AS trip_count_in_coconino_county +FROM trip t +WHERE ST_Intersects( + ST_GeogFromWKB(t.t_pickuploc), + (SELECT ST_GeogFromWKB(z.z_boundary) FROM zone z WHERE z.z_name = 'Coconino County' LIMIT 1) +) +``` + +## Q3：Sedona 市中心 15 公里范围内的每月行程统计 + +**实际场景：** 跟踪某都市区每月的需求、收入与行程时长。 + +多边形字面量与 geometry 套件中的环相同，但这里它的边是大地线，因此它是一个球面四边形而不是 +平面矩形。缓冲距离为 5 000 米。 + +**所考察的空间查询特性：** + +1. 基于大地线的多边形缓冲过滤（`ST_DWithin`，米） +2. 使用 `DATE_TRUNC` 的时间分组 +3. 在空间过滤结果上进行多项聚合 + +```sql +SELECT + DATE_TRUNC('month', t.t_pickuptime) AS pickup_month, + COUNT(t.t_tripkey) AS total_trips, + AVG(t.t_distance) AS avg_distance, + AVG(t.t_dropofftime - t.t_pickuptime) AS avg_duration, + AVG(t.t_fare) AS avg_fare +FROM trip t +WHERE ST_DWithin( + ST_GeogFromWKB(t.t_pickuploc), + ST_GeogFromWKT('POLYGON((-111.9060 34.7347, -111.6160 34.7347, -111.6160 35.0047, -111.9060 35.0047, -111.9060 34.7347))'), -- 10km box around Sedona + 5000 -- Additional 5 km geodesic buffer +) +GROUP BY pickup_month +ORDER BY pickup_month +``` + +## Q4：小费最高的 1000 次行程的区域分布 + +**实际场景：** 找出小费最丰厚的街区。 + +**所考察的空间查询特性：** + +1. 球面上的点在多边形内空间连接（`ST_Within`） +2. 与 `LIMIT` 限定子查询的空间连接 +3. 在空间连接结果上进行聚合 + +```sql +SELECT z.z_zonekey, z.z_name, COUNT(*) AS trip_count +FROM zone z +JOIN ( + SELECT t.t_pickuploc + FROM trip t + ORDER BY t.t_tip DESC, t.t_tripkey ASC + LIMIT 1000 -- Replace 1000 with x (how many top tips you want) +) top_trips +ON ST_Within(ST_GeogFromWKB(top_trips.t_pickuploc), ST_GeogFromWKB(z.z_boundary)) +GROUP BY z.z_zonekey, z.z_name +ORDER BY trip_count DESC, z.z_zonekey ASC +``` + +## Q5：回头客的每月出行范围 + +**实际场景：** 通过下车点的凸包，衡量每位高频乘客在一个月内的出行覆盖范围有多大。 + +在 geography 套件中，`monthly_travel_hull_area` 是以平方米为单位的球面面积，且凸包本身也是 +用大地线边计算的。 + +**所考察的空间查询特性：** + +1. geography 聚合为集合（`ST_Collect_Agg`） Review Comment: Q5 这里写成了 `ST_Collect_Agg`，但 geography 套件的默认 SedonaSpark 方言使用的是 `ST_Collect(ARRAY_AGG(...))`，`ST_Collect_Agg` 是 SedonaDB 的特定写法。建议在此处注明两种方言，避免 SedonaSpark 读者直接复制后运行失败。 This issue also appears on line 199 of the same file. ########## docs/geography-queries.md: ########## @@ -0,0 +1,435 @@ +--- +title: Geography Queries +--- + +<!--- + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + http://www.apache.org/licenses/LICENSE-2.0 + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. +--> + +# The Geography Query Suite + +SpatialBench's [main query suite](queries.md) is a **geometry** benchmark: the WKB columns are +decoded with `ST_GeomFromWKB`, predicates use planar (2D Euclidean) edges, and every distance +threshold is an angle in degrees. + +This page defines the **geography** counterpart of the same 12 queries. The data is +unchanged — the same EPSG:4326 longitude/latitude WKB columns — but the columns are decoded +with `ST_GeogFromWKB`, so edges are interpreted as geodesics on a sphere and every measure +comes back in real-world units. + +## Why a second suite + +The geometry suite has to fake real-world units. It filters trips "within 50 km" of Sedona +using `ST_DWithin(..., 0.45)`, converts metres to degrees with `1 m = 0.000009 degree`, and +reports building overlap areas in square degrees. + +A degree of latitude is roughly 111 km everywhere, but a degree of longitude shrinks with the +cosine of the latitude. So `0.45` degrees is about 50 km tall and only about 41 km wide at +Sedona (34.87°N), and about 25 km wide at 60°N. Since SpatialBench data is generated across +eight continent-sized boxes spanning latitudes −56° to 78°, a degree-based radius is a +different real-world shape in every part of the dataset. + +The geography suite replaces all of that with metres and square metres: + +| | Geometry suite | Geography suite | +|---|---|---| +| Point/polygon constructor | `ST_GeomFromWKB`, `ST_GeomFromText` | `ST_GeogFromWKB`, `ST_GeogFromWKT` | +| Edge interpretation | straight lines in a plane | geodesics on a sphere | +| `ST_DWithin` threshold | degrees (`0.45`, `0.045`, `0.0045`) | metres (`50000`, `5000`, `500`) | +| `ST_Distance`, `ST_Length` | degrees | metres | +| `ST_Area` | square degrees | square metres | +| Unit conversion in SQL | `/ 0.000009` | none needed | + +!!! note "The two suites are not row-for-row comparable" + + A 0.45° planar radius and a 50 000 m geodesic radius select different trips, so Q1 + returns a different 100 rows in each suite. That is the point of the exercise, not a + discrepancy: each suite has its own ground truth, and results should only ever be + compared within a suite. + +## Running them + +The queries are available as SQL through the query module, which prints the suite for a given +dialect: + +```bash +python3 spatialbench-queries/print_geography_queries.py SedonaDB +``` + +They require an engine with a `GEOGRAPHY` type whose measures are geodesic — support for the +individual functions is still filling in across engines, so check your engine's own reference +for what it implements. Q12 is defined below but not yet carried in the module: it is a +K-nearest-neighbour join, and `ST_KNN` has no geography form yet. + +## Q1: Find trips starting within 50km of Sedona city center, ordered by distance + +**Real-life scenario:** Identify and rank trips by proximity to a city center for urban +planning and transportation analysis. + +Unlike the geometry version, the 50 km radius is a true geodesic radius rather than a 0.45° +angular one, and `distance_to_center` is in metres. + +**Spatial query characteristics tested:** + +1. Geodesic distance-based spatial filtering (`ST_DWithin`, metres) +2. Geodesic distance calculation to a fixed point +3. Coordinate extraction (`ST_X`, `ST_Y`) +4. Ordering by spatial distance + +```sql +SELECT + t.t_tripkey, + -- A point's coordinates are identical under either edge interpretation, so the lon/lat + -- projection stays on the geometry accessor, which is the more widely available one. + ST_X(ST_GeomFromWKB(t.t_pickuploc)) AS pickup_lon, + ST_Y(ST_GeomFromWKB(t.t_pickuploc)) AS pickup_lat, + t.t_pickuptime, + ST_Distance( + ST_GeogFromWKB(t.t_pickuploc), + ST_GeogFromWKT('POINT (-111.7610 34.8697)') + ) AS distance_to_center -- metres +FROM trip t +WHERE ST_DWithin( + ST_GeogFromWKB(t.t_pickuploc), + ST_GeogFromWKT('POINT (-111.7610 34.8697)'), + 50000 -- 50 km geodesic radius around Sedona center +) +ORDER BY distance_to_center ASC, t.t_tripkey ASC +LIMIT 100 -- Return only the 100 closest trips (bounded result set) +``` + +## Q2: Count trips starting within Coconino County + +**Real-life scenario:** Count trip activity inside an administrative boundary. + +The county polygon comes from Overture data with planar edges; reading it as a geography +reinterprets those edges as geodesics, which can move the boundary by a few metres relative +to the geometry suite and change the count for pickups that fall essentially on the border. + +**Spatial query characteristics tested:** + +1. Point-in-polygon spatial predicate on the sphere (`ST_Intersects`) +2. Scalar subquery producing the query geography + +```sql +SELECT COUNT(*) AS trip_count_in_coconino_county +FROM trip t +WHERE ST_Intersects( + ST_GeogFromWKB(t.t_pickuploc), + (SELECT ST_GeogFromWKB(z.z_boundary) FROM zone z WHERE z.z_name = 'Coconino County' LIMIT 1) +) +``` + +## Q3: Monthly trip statistics within 15km of Sedona city center + +**Real-life scenario:** Track monthly demand, revenue and trip duration in a metro area. + +The polygon literal is the same ring as in the geometry suite, but its edges are geodesics +here, so it is a spherical quadrilateral rather than a planar box. The buffer is 5 000 m. + +**Spatial query characteristics tested:** + +1. Geodesic buffered-polygon filtering (`ST_DWithin`, metres) +2. Temporal grouping with `DATE_TRUNC` +3. Multiple aggregations over a spatial filter + +```sql +SELECT + DATE_TRUNC('month', t.t_pickuptime) AS pickup_month, + COUNT(t.t_tripkey) AS total_trips, + AVG(t.t_distance) AS avg_distance, + AVG(t.t_dropofftime - t.t_pickuptime) AS avg_duration, + AVG(t.t_fare) AS avg_fare +FROM trip t +WHERE ST_DWithin( + ST_GeogFromWKB(t.t_pickuploc), + ST_GeogFromWKT('POLYGON((-111.9060 34.7347, -111.6160 34.7347, -111.6160 35.0047, -111.9060 35.0047, -111.9060 34.7347))'), -- 10km box around Sedona + 5000 -- Additional 5 km geodesic buffer +) +GROUP BY pickup_month +ORDER BY pickup_month +``` + +## Q4: Zone distribution of top 1000 trips by tip amount + +**Real-life scenario:** Find which neighbourhoods generate the most generous tips. + +**Spatial query characteristics tested:** + +1. Point-in-polygon spatial join on the sphere (`ST_Within`) +2. Spatial join against a `LIMIT`-bounded subquery +3. Aggregation on the spatial join result + +```sql +SELECT z.z_zonekey, z.z_name, COUNT(*) AS trip_count +FROM zone z +JOIN ( + SELECT t.t_pickuploc + FROM trip t + ORDER BY t.t_tip DESC, t.t_tripkey ASC + LIMIT 1000 -- Replace 1000 with x (how many top tips you want) +) top_trips +ON ST_Within(ST_GeogFromWKB(top_trips.t_pickuploc), ST_GeogFromWKB(z.z_boundary)) +GROUP BY z.z_zonekey, z.z_name +ORDER BY trip_count DESC, z.z_zonekey ASC +``` + +## Q5: Monthly travel patterns for repeat customers + +**Real-life scenario:** Measure how large an area each frequent customer travels across in a +month, from the convex hull of their dropoff locations. + +In the geography suite `monthly_travel_hull_area` is a spherical area in square metres, and +the hull itself is computed with geodesic edges. + +**Spatial query characteristics tested:** + +1. Geography aggregation into a collection (`ST_Collect_Agg`) Review Comment: Q5 is presented as using `ST_Collect_Agg`, but the geography suite’s default SedonaSpark dialect uses `ST_Collect(ARRAY_AGG(...))` (with `ST_Collect_Agg` being SedonaDB-specific). This bullet should reflect both dialects or the default one to avoid copy/paste failures on SedonaSpark. This issue also appears on line 209 of the same file. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
