You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@sedona.apache.org by ji...@apache.org on 2026/07/21 07:32:29 UTC
(sedona-spatialbench) branch main updated: Bound query result sizes to at most 100 rows (#124)
This is an automated email from the ASF dual-hosted git repository.
jiayuasu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/sedona-spatialbench.git
The following commit(s) were added to refs/heads/main by this push:
new b5dc5c9 Bound query result sizes to at most 100 rows (#124)
b5dc5c9 is described below
commit b5dc5c955e792c8e2b3762856fd14cb5e04d954d
Author: Jia Yu <ji...@apache.org>
AuthorDate: Tue Jul 21 00:32:24 2026 -0700
Bound query result sizes to at most 100 rows (#124)
* Bound query result sizes to at most 100 rows
Several queries previously returned result sets that grow with the scale
factor -- q7 returns one row per trip (6M at SF1), q10 one row per zone
(156K at SF1), q12 five rows per trip (30M at SF1), and q1/q5/q8/q9 also
scale unboundedly. Returning these to the driver measures driver-engine
bandwidth rather than spatial computation.
Bound q1, q5, q7, q8, q9, and q10 with LIMIT 100 after their existing
ORDER BY. This keeps the full scan, spatial-function evaluation, join, and
aggregation intact (the cost is upstream of the sort) while returning a
meaningful top-N that matches each query's scenario.
Redesign q12 to average each pickup's five nearest-building distances and
rank pickups by that average (most isolated first), so the LIMIT sits above
a GROUP BY aggregation and the full KNN join is still performed.
Add total-order tiebreakers so the top-N is reproducible across engines
(pickup_month for q5, t_tripkey for q12), and align q5 ordering on
dropoff_count across every implementation and the docs.
Applied consistently across the SedonaSpark/Databricks/DuckDB/SedonaDB SQL,
the GeoPandas/Spatial Polars/PyCanopy implementations, the queries notebook,
and the documentation.
Closes #122
* Rank Q5 by travel-hull area and make the docs example deterministic
Order Q5 by monthly_travel_hull_area DESC (keeping c_custkey and
pickup_month as tiebreakers) instead of dropoff_count. Ranking by the
plain count let a top-k optimizer take the top 100 groups by count and
skip the convex-hull computation for the rest; ranking by the hull area
forces the hull to be computed for every group, so the LIMIT cannot
bypass the spatial work the query is meant to measure.
Add a b_buildingkey ASC tiebreaker to the example query on the docs
landing page so its bounded top-100 is deterministic, matching Q8.
* Regenerate all sample outputs using SedonaDB (SF1)
Re-ran all 12 queries against a freshly generated SF1 dataset with
SedonaDB and refreshed the sample output in the docs and notebook. Several
outputs committed previously were stale relative to the current data
generator:
- Q4, Q6, Q10: zone keys shifted with the tiered zone-table rework.
- Q5: top customer-month by travel-hull area is Customer#000010609
(the previous sample was stale and omitted it).
- Q9: building-overlap IoU values changed with the building geometry.
- Q12: real output for the redesigned "average distance to the 5 nearest
buildings" ranking (most-isolated pickups).
Q1, Q2, Q3, Q7, Q8, and Q11 outputs were re-run and unchanged.
---
docs/index.md | 3 +-
docs/index.zh.md | 3 +-
docs/queries.md | 109 +++++++++++++-----------
docs/queries.zh.md | 109 +++++++++++++-----------
notebooks/queries.ipynb | 132 ++++++++++++++++--------------
spatialbench-queries/geopandas_queries.py | 47 ++++++-----
spatialbench-queries/print_queries.py | 68 ++++++++-------
spatialbench-queries/pycanopy_queries.py | 68 ++++++++++-----
spatialbench-queries/spatial_polars.py | 31 ++++---
9 files changed, 322 insertions(+), 248 deletions(-)
diff --git a/docs/index.md b/docs/index.md
index c33c007..fcd8973 100644
--- a/docs/index.md
+++ b/docs/index.md
@@ -81,7 +81,8 @@ FROM trip t
JOIN building b
ON ST_DWithin(t.t_pickup_loc, b.b_boundary, 500)
GROUP BY b.b_buildingkey, b.b_name
-ORDER BY nearby_pickup_count DESC;
+ORDER BY nearby_pickup_count DESC, b.b_buildingkey ASC
+LIMIT 100;
```
This query performs a distance join, followed by an aggregation. It's a great example of a query that's useful for performance benchmarking a spatial engine that can process vector geometries.
diff --git a/docs/index.zh.md b/docs/index.zh.md
index aebd1ee..e732dd5 100644
--- a/docs/index.zh.md
+++ b/docs/index.zh.md
@@ -79,7 +79,8 @@ FROM trip t
JOIN building b
ON ST_DWithin(t.t_pickup_loc, b.b_boundary, 500)
GROUP BY b.b_buildingkey, b.b_name
-ORDER BY nearby_pickup_count DESC;
+ORDER BY nearby_pickup_count DESC, b.b_buildingkey ASC
+LIMIT 100;
```
该查询先执行一个基于距离的连接，再进行聚合。它非常适合用于评测可处理矢量几何的空间引擎的性能。
diff --git a/docs/queries.md b/docs/queries.md
index 5edfd4a..c5d8b99 100644
--- a/docs/queries.md
+++ b/docs/queries.md
@@ -81,6 +81,7 @@ WHERE ST_DWithin(
0.45 -- 50km radius around Sedona center in degrees
)
ORDER BY distance_to_center ASC, t.t_tripkey ASC
+LIMIT 100 -- Return only the 100 closest trips (bounded result set)
""").show(3)
```
@@ -222,11 +223,11 @@ ORDER BY trip_count DESC, z.z_zonekey ASC
│ z_zonekey ┆ z_name ┆ trip_count │
│ int64 ┆ utf8 ┆ int64 │
╞═══════════╪═════════════════════════════════╪════════════╡
- │ 65008 ┆ Ndélé ┆ 35 │
+ │ 21286 ┆ Ndélé ┆ 35 │
├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 147530 ┆ 乐山市 ┆ 27 │
+ │ 80341 ┆ 乐山市 ┆ 27 │
├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 150276 ┆ 锡林郭勒盟 ᠰᠢᠯᠢ ᠶᠢᠨ ᠭᠣᠣᠯ ᠠᠶᠢᠮᠠᠭ ┆ 19 │
+ │ 88075 ┆ 锡林郭勒盟 ᠰᠢᠯᠢ ᠶᠢᠨ ᠭᠣᠣᠯ ᠠᠶᠢᠮᠠᠭ ┆ 19 │
└───────────┴─────────────────────────────────┴────────────┘
@@ -234,7 +235,7 @@ ORDER BY trip_count DESC, z.z_zonekey ASC
**Real-life scenario:** Analyze the geographic spread of travel patterns for frequent customers to understand their mobility behavior.
-This query analyzes the monthly travel patterns of frequent customers by measuring how much geographic area they cover with their trips. For each customer who took more than five trips in a month, it calculates the size of the "travel hull" - the area enclosed by connecting all their dropoff locations that month. The results reveal which customers have the most expansive travel patterns, helping to identify power users who cover large geographic areas versus those who stick to smaller, l [...]
+This query analyzes the monthly travel patterns of frequent customers by measuring how much geographic area they cover with their trips. For each customer who took more than five trips in a month, it calculates the size of the "travel hull" - the area enclosed by connecting all their dropoff locations that month. Results are ranked by travel-hull area (largest first) so the most geographically spread-out repeat customer-months appear first, and are bounded to the top 100. Ranking by the [...]
**Spatial query characteristics tested:**
@@ -259,7 +260,8 @@ JOIN customer c
ON t.t_custkey = c.c_custkey
GROUP BY c.c_custkey, c.c_name, pickup_month
HAVING dropoff_count > 5 -- Only include repeat customers
-ORDER BY monthly_travel_hull_area DESC, c.c_custkey ASC
+ORDER BY monthly_travel_hull_area DESC, c.c_custkey ASC, pickup_month ASC
+LIMIT 100 -- Return only the top 100 repeat customer-months by travel-hull area (bounded result set)
""").show(3)
```
@@ -267,11 +269,11 @@ ORDER BY monthly_travel_hull_area DESC, c.c_custkey ASC
│ c_custkey ┆ customer_name ┆ pickup_month ┆ monthly_travel_hul ┆ dropoff_count │
│ int64 ┆ utf8 ┆ timestamp ┆ l_area… ┆ int64 │
╞═══════════╪════════════════════╪═════════════════════╪════════════════════╪═══════════════╡
+ │ 10609 ┆ Customer#000010609 ┆ 1998-05-01T00:00:00 ┆ 36943.5600373373 ┆ 11 │
+ ├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ 25975 ┆ Customer#000025975 ┆ 1992-02-01T00:00:00 ┆ 34941.303419053635 ┆ 10 │
├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ 12061 ┆ Customer#000012061 ┆ 1997-03-01T00:00:00 ┆ 34607.53871953154 ┆ 14 │
- ├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 21418 ┆ Customer#000021418 ┆ 1993-08-01T00:00:00 ┆ 34465.32323910264 ┆ 9 │
└───────────┴────────────────────┴─────────────────────┴────────────────────┴───────────────┘
@@ -317,11 +319,11 @@ ORDER BY total_pickups DESC, z.z_zonekey ASC
│ z_zonekey ┆ z_name ┆ total_pickups ┆ avg_distance ┆ avg_duration │
│ int64 ┆ utf8 ┆ int64 ┆ decimal128 ┆ duration │
╞═══════════╪═════════════════╪═══════════════╪══════════════╪════════════════════════════════════╡
- │ 30084 ┆ Coconino County ┆ 541 ┆ 0.000030406 ┆ 0 days 1 hours 45 mins 16.591 secs │
+ │ 149106 ┆ Coconino County ┆ 541 ┆ 0.000030406 ┆ 0 days 1 hours 45 mins 16.591 secs │
├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 30083 ┆ Yavapai County ┆ 292 ┆ 0.000027157 ┆ 0 days 1 hours 36 mins 43.647 secs │
+ │ 132418 ┆ Yavapai County ┆ 292 ┆ 0.000027157 ┆ 0 days 1 hours 36 mins 43.647 secs │
├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 29488 ┆ Gila County ┆ 39 ┆ 0.000021282 ┆ 0 days 1 hours 16 mins 59.769 secs │
+ │ 82996 ┆ Gila County ┆ 39 ┆ 0.000021282 ┆ 0 days 1 hours 16 mins 59.769 secs │
└───────────┴─────────────────┴───────────────┴──────────────┴────────────────────────────────────┘
@@ -363,6 +365,7 @@ ORDER BY
detour_ratio DESC NULLS LAST,
reported_distance_m DESC,
t_tripkey ASC
+LIMIT 100 -- Return only the top 100 highest-detour trips (bounded result set)
""").show(3)
```
@@ -398,6 +401,7 @@ JOIN building b
ON ST_DWithin(ST_GeomFromWKB(t.t_pickuploc), ST_GeomFromWKB(b.b_boundary), 0.0045) -- ~500m
GROUP BY b.b_buildingkey, b.b_name
ORDER BY nearby_pickup_count DESC, b.b_buildingkey ASC
+LIMIT 100 -- Return only the top 100 busiest buildings (bounded result set)
""").show(3)
```
@@ -459,19 +463,20 @@ SELECT
END AS iou
FROM pairs
ORDER BY iou DESC, building_1 ASC, building_2 ASC
+LIMIT 100 -- Return only the top 100 most-overlapping building pairs (bounded result set)
""").show(3)
```
- ┌────────────┬────────────┬───┬───────────────────────┬────────────────────┐
- │ building_1 ┆ building_2 ┆ … ┆ overlap_area ┆ iou │
- │ int64 ┆ int64 ┆ ┆ float64 ┆ float64 │
- ╞════════════╪════════════╪═══╪═══════════════════════╪════════════════════╡
- │ 2285 ┆ 15719 ┆ … ┆ 2.3709162946727276e-6 ┆ 0.9056816071717889 │
- ├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 7562 ┆ 18534 ┆ … ┆ 5.855106543747764e-6 ┆ 0.8450437137796769 │
- ├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 2285 ┆ 13658 ┆ … ┆ 1.9770693222933237e-6 ┆ 0.737899157380637 │
- └────────────┴────────────┴───┴───────────────────────┴────────────────────┘
+ ┌────────────┬────────────┬───┬─────────────────────────┬────────────────────┐
+ │ building_1 ┆ building_2 ┆ … ┆ overlap_area ┆ iou │
+ │ int64 ┆ int64 ┆ ┆ float64 ┆ float64 │
+ ╞════════════╪════════════╪═══╪═════════════════════════╪════════════════════╡
+ │ 7562 ┆ 18534 ┆ … ┆ 0.000022785758541344757 ┆ 0.8415764980401906 │
+ ├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+ │ 2285 ┆ 15719 ┆ … ┆ 6.445979506828921e-6 ┆ 0.7022218100645453 │
+ ├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+ │ 13658 ┆ 15719 ┆ … ┆ 6.813826545713668e-6 ┆ 0.7019832720541139 │
+ └────────────┴────────────┴───┴─────────────────────────┴────────────────────┘
## Q10: Zone statistics for trips starting within each zone
@@ -503,6 +508,7 @@ FROM
)
GROUP BY z.z_zonekey, z.z_name
ORDER BY avg_duration DESC NULLS LAST, z.z_zonekey ASC
+LIMIT 100 -- Return only the top 100 zones by average trip duration (bounded result set)
""").show(3)
```
@@ -510,11 +516,11 @@ ORDER BY avg_duration DESC NULLS LAST, z.z_zonekey ASC
│ z_zonekey ┆ pickup_zone ┆ avg_duration ┆ avg_distance ┆ num_trips │
│ int64 ┆ utf8 ┆ duration ┆ decimal128 ┆ int64 │
╞═══════════╪════════════════╪════════════════════════════════════╪══════════════╪═══════════╡
- │ 31558 ┆ Benewah County ┆ 4 days 13 hours 3 mins 34.000 secs ┆ 0.002180000 ┆ 2 │
+ │ 13183 ┆ Benewah County ┆ 4 days 13 hours 3 mins 34.000 secs ┆ 0.002180000 ┆ 2 │
├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌┤
- │ 119540 ┆ Kreis Unna ┆ 2 days 4 hours 52 mins 44.000 secs ┆ 0.001050000 ┆ 1 │
+ │ 124002 ┆ Kreis Unna ┆ 2 days 4 hours 52 mins 44.000 secs ┆ 0.001050000 ┆ 1 │
├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌┤
- │ 59928 ┆ Ndhiwa ┆ 2 days 4 hours 19 mins 39.000 secs ┆ 0.001040000 ┆ 1 │
+ │ 52386 ┆ Castel di Leva ┆ 2 days 4 hours 19 mins 39.000 secs ┆ 0.001040000 ┆ 1 │
└───────────┴────────────────┴────────────────────────────────────┴──────────────┴───────────┘
@@ -557,16 +563,17 @@ WHERE pickup_zone.z_zonekey != dropoff_zone.z_zonekey
└───────────────────────┘
-## Q12: Find five nearest buildings to each trip pickup location using KNN join
-**Real-life scenario:** Identify the closest landmarks or buildings to each trip start point for location context and navigation.
+## Q12: Rank trip pickups by average distance to their five nearest buildings (KNN join)
+**Real-life scenario:** Identify the most isolated trip pickups — those in areas with the fewest nearby buildings — for coverage and location-context analysis.
-This query finds the 5 closest buildings to each trip pickup location using spatial nearest neighbor analysis. For every trip, it identifies the five buildings that are geographically closest to where the passenger was picked up and calculates the exact distance to each of those buildings. The results show which buildings are most commonly near pickup points, helping understand the relationship between trip origins and nearby landmarks, businesses, or residential structures that might in [...]
+This query finds the 5 closest buildings to each trip pickup location using spatial nearest neighbor analysis, then averages those five distances to produce a single "local building density" measure per pickup. Ranking pickups by this average (largest first) surfaces the most isolated trip origins — those farthest from any surrounding buildings — which can indicate rural areas, coverage gaps, or unusual pickup locations. The result is bounded to the top 100 pickups.
**Spatial query characteristics tested:**
1. K-nearest neighbor (KNN) spatial join
2. Distance calculations between points and polygons
-3. Ranking and limiting results based on spatial proximity
+3. Aggregation (average) over each pickup's k nearest neighbors
+4. Ranking and limiting the aggregated per-pickup results
```python
@@ -574,38 +581,40 @@ sd.sql("""
WITH trip_with_geom AS (
SELECT
t_tripkey,
- t_pickuploc,
ST_GeomFromWKB(t_pickuploc) as pickup_geom
FROM trip
),
building_with_geom AS (
SELECT
- b_buildingkey,
- b_name,
- b_boundary,
ST_GeomFromWKB(b_boundary) as boundary_geom
FROM building
+),
+knn AS (
+ SELECT
+ t.t_tripkey,
+ ST_Distance(t.pickup_geom, b.boundary_geom) AS distance_to_building
+ FROM trip_with_geom t
+ JOIN building_with_geom b
+ ON ST_KNN(t.pickup_geom, b.boundary_geom, 5, FALSE)
)
SELECT
- t.t_tripkey,
- t.t_pickuploc,
- b.b_buildingkey,
- b.b_name AS building_name,
- ST_Distance(t.pickup_geom, b.boundary_geom) AS distance_to_building
-FROM trip_with_geom t
-JOIN building_with_geom b
- ON ST_KNN(t.pickup_geom, b.boundary_geom, 5, FALSE)
-ORDER BY t.t_tripkey ASC, distance_to_building ASC, b.b_buildingkey ASC
+ t_tripkey,
+ AVG(distance_to_building) AS avg_distance_to_5_nearest
+FROM knn
+GROUP BY t_tripkey
+ORDER BY avg_distance_to_5_nearest DESC, t_tripkey ASC
+LIMIT 100 -- Return only the top 100 most-isolated pickups (bounded result set)
""").show(3)
```
- ┌───────────┬─────────────────────────────────┬───────────────┬───────────────┬────────────────────┐
- │ t_tripkey ┆ t_pickuploc ┆ b_buildingkey ┆ building_name ┆ distance_to_buildi │
- │ int64 ┆ binary ┆ int64 ┆ utf8 ┆ ng… │
- ╞═══════════╪═════════════════════════════════╪═══════════════╪═══════════════╪════════════════════╡
- │ 1 ┆ 01010000009f3c318dd43735405930… ┆ 15870 ┆ purple ┆ 0.984633987957188 │
- ├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 1 ┆ 01010000009f3c318dd43735405930… ┆ 6800 ┆ ghost ┆ 1.205725156670704 │
- ├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 1 ┆ 01010000009f3c318dd43735405930… ┆ 8384 ┆ lavender ┆ 1.4195012994942622 │
- └───────────┴─────────────────────────────────┴───────────────┴───────────────┴────────────────────┘
+ ┌───────────┬───────────────────────────┐
+ │ t_tripkey ┆ avg_distance_to_5_nearest │
+ │ int64 ┆ float64 │
+ ╞═══════════╪═══════════════════════════╡
+ │ 1397637 ┆ 82.40977530183955 │
+ ├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+ │ 4166482 ┆ 82.38756120900636 │
+ ├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+ │ 2976679 ┆ 82.36270091818895 │
+ └───────────┴───────────────────────────┘
+
diff --git a/docs/queries.zh.md b/docs/queries.zh.md
index 9ba5e05..31e035e 100644
--- a/docs/queries.zh.md
+++ b/docs/queries.zh.md
@@ -81,6 +81,7 @@ WHERE ST_DWithin(
0.45 -- Sedona 中心 50km 半径，以度为单位
)
ORDER BY distance_to_center ASC, t.t_tripkey ASC
+LIMIT 100 -- Return only the 100 closest trips (bounded result set)
""").show(3)
```
@@ -222,11 +223,11 @@ ORDER BY trip_count DESC, z.z_zonekey ASC
│ z_zonekey ┆ z_name ┆ trip_count │
│ int64 ┆ utf8 ┆ int64 │
╞═══════════╪═════════════════════════════════╪════════════╡
- │ 65008 ┆ Ndélé ┆ 35 │
+ │ 21286 ┆ Ndélé ┆ 35 │
├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 147530 ┆ 乐山市 ┆ 27 │
+ │ 80341 ┆ 乐山市 ┆ 27 │
├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 150276 ┆ 锡林郭勒盟 ᠰᠢᠯᠢ ᠶᠢᠨ ᠭᠣᠣᠯ ᠠᠶᠢᠮᠠᠭ ┆ 19 │
+ │ 88075 ┆ 锡林郭勒盟 ᠰᠢᠯᠢ ᠶᠢᠨ ᠭᠣᠣᠯ ᠠᠶᠢᠮᠠᠭ ┆ 19 │
└───────────┴─────────────────────────────────┴────────────┘
@@ -234,7 +235,7 @@ ORDER BY trip_count DESC, z.z_zonekey ASC
**真实场景：** 分析常客出行模式的地理分布范围，以了解他们的出行行为。
-该查询通过测量常客每月行程所覆盖的地理范围，分析他们的月度出行模式。对于每个在某个月内乘坐超过 5 次行程的客户，它会计算其“出行凸包”的面积——即连接当月所有下车点形成的区域的面积。结果可以揭示出哪些客户的出行覆盖范围最广，帮助识别覆盖大面积区域的“重度用户”以及那些只在较小的本地区域内活动的用户。
+该查询通过测量常客每月行程所覆盖的地理范围，分析他们的月度出行模式。对于每个在某个月内乘坐超过 5 次行程的客户，它会计算其“出行凸包”的面积——即连接当月所有下车点形成的区域的面积。结果按出行凸包面积降序排列，使出行范围最广的重复客户月份排在最前，并限制为前 100 行。按凸包面积（而非简单的行程计数）排序可确保每个分组都必须计算凸包，因此该限制无法被跳过空间计算的 top-k 优化所规避。
**被测试的空间查询特性：**
@@ -259,7 +260,8 @@ JOIN customer c
ON t.t_custkey = c.c_custkey
GROUP BY c.c_custkey, c.c_name, pickup_month
HAVING dropoff_count > 5 -- 仅保留重复客户
-ORDER BY monthly_travel_hull_area DESC, c.c_custkey ASC
+ORDER BY monthly_travel_hull_area DESC, c.c_custkey ASC, pickup_month ASC
+LIMIT 100 -- Return only the top 100 repeat customer-months by travel-hull area (bounded result set)
""").show(3)
```
@@ -267,11 +269,11 @@ ORDER BY monthly_travel_hull_area DESC, c.c_custkey ASC
│ c_custkey ┆ customer_name ┆ pickup_month ┆ monthly_travel_hul ┆ dropoff_count │
│ int64 ┆ utf8 ┆ timestamp ┆ l_area… ┆ int64 │
╞═══════════╪════════════════════╪═════════════════════╪════════════════════╪═══════════════╡
+ │ 10609 ┆ Customer#000010609 ┆ 1998-05-01T00:00:00 ┆ 36943.5600373373 ┆ 11 │
+ ├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ 25975 ┆ Customer#000025975 ┆ 1992-02-01T00:00:00 ┆ 34941.303419053635 ┆ 10 │
├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
│ 12061 ┆ Customer#000012061 ┆ 1997-03-01T00:00:00 ┆ 34607.53871953154 ┆ 14 │
- ├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 21418 ┆ Customer#000021418 ┆ 1993-08-01T00:00:00 ┆ 34465.32323910264 ┆ 9 │
└───────────┴────────────────────┴─────────────────────┴────────────────────┴───────────────┘
@@ -317,11 +319,11 @@ ORDER BY total_pickups DESC, z.z_zonekey ASC
│ z_zonekey ┆ z_name ┆ total_pickups ┆ avg_distance ┆ avg_duration │
│ int64 ┆ utf8 ┆ int64 ┆ decimal128 ┆ duration │
╞═══════════╪═════════════════╪═══════════════╪══════════════╪════════════════════════════════════╡
- │ 30084 ┆ Coconino County ┆ 541 ┆ 0.000030406 ┆ 0 days 1 hours 45 mins 16.591 secs │
+ │ 149106 ┆ Coconino County ┆ 541 ┆ 0.000030406 ┆ 0 days 1 hours 45 mins 16.591 secs │
├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 30083 ┆ Yavapai County ┆ 292 ┆ 0.000027157 ┆ 0 days 1 hours 36 mins 43.647 secs │
+ │ 132418 ┆ Yavapai County ┆ 292 ┆ 0.000027157 ┆ 0 days 1 hours 36 mins 43.647 secs │
├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 29488 ┆ Gila County ┆ 39 ┆ 0.000021282 ┆ 0 days 1 hours 16 mins 59.769 secs │
+ │ 82996 ┆ Gila County ┆ 39 ┆ 0.000021282 ┆ 0 days 1 hours 16 mins 59.769 secs │
└───────────┴─────────────────┴───────────────┴──────────────┴────────────────────────────────────┘
@@ -363,6 +365,7 @@ ORDER BY
detour_ratio DESC NULLS LAST,
reported_distance_m DESC,
t_tripkey ASC
+LIMIT 100 -- Return only the top 100 highest-detour trips (bounded result set)
""").show(3)
```
@@ -398,6 +401,7 @@ JOIN building b
ON ST_DWithin(ST_GeomFromWKB(t.t_pickuploc), ST_GeomFromWKB(b.b_boundary), 0.0045) -- 约 500m
GROUP BY b.b_buildingkey, b.b_name
ORDER BY nearby_pickup_count DESC, b.b_buildingkey ASC
+LIMIT 100 -- Return only the top 100 busiest buildings (bounded result set)
""").show(3)
```
@@ -459,19 +463,20 @@ SELECT
END AS iou
FROM pairs
ORDER BY iou DESC, building_1 ASC, building_2 ASC
+LIMIT 100 -- Return only the top 100 most-overlapping building pairs (bounded result set)
""").show(3)
```
- ┌────────────┬────────────┬───┬───────────────────────┬────────────────────┐
- │ building_1 ┆ building_2 ┆ … ┆ overlap_area ┆ iou │
- │ int64 ┆ int64 ┆ ┆ float64 ┆ float64 │
- ╞════════════╪════════════╪═══╪═══════════════════════╪════════════════════╡
- │ 2285 ┆ 15719 ┆ … ┆ 2.3709162946727276e-6 ┆ 0.9056816071717889 │
- ├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 7562 ┆ 18534 ┆ … ┆ 5.855106543747764e-6 ┆ 0.8450437137796769 │
- ├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 2285 ┆ 13658 ┆ … ┆ 1.9770693222933237e-6 ┆ 0.737899157380637 │
- └────────────┴────────────┴───┴───────────────────────┴────────────────────┘
+ ┌────────────┬────────────┬───┬─────────────────────────┬────────────────────┐
+ │ building_1 ┆ building_2 ┆ … ┆ overlap_area ┆ iou │
+ │ int64 ┆ int64 ┆ ┆ float64 ┆ float64 │
+ ╞════════════╪════════════╪═══╪═════════════════════════╪════════════════════╡
+ │ 7562 ┆ 18534 ┆ … ┆ 0.000022785758541344757 ┆ 0.8415764980401906 │
+ ├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+ │ 2285 ┆ 15719 ┆ … ┆ 6.445979506828921e-6 ┆ 0.7022218100645453 │
+ ├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+ │ 13658 ┆ 15719 ┆ … ┆ 6.813826545713668e-6 ┆ 0.7019832720541139 │
+ └────────────┴────────────┴───┴─────────────────────────┴────────────────────┘
## Q10：每个区域内起始行程的统计指标
@@ -503,6 +508,7 @@ FROM
)
GROUP BY z.z_zonekey, z.z_name
ORDER BY avg_duration DESC NULLS LAST, z.z_zonekey ASC
+LIMIT 100 -- Return only the top 100 zones by average trip duration (bounded result set)
""").show(3)
```
@@ -510,11 +516,11 @@ ORDER BY avg_duration DESC NULLS LAST, z.z_zonekey ASC
│ z_zonekey ┆ pickup_zone ┆ avg_duration ┆ avg_distance ┆ num_trips │
│ int64 ┆ utf8 ┆ duration ┆ decimal128 ┆ int64 │
╞═══════════╪════════════════╪════════════════════════════════════╪══════════════╪═══════════╡
- │ 31558 ┆ Benewah County ┆ 4 days 13 hours 3 mins 34.000 secs ┆ 0.002180000 ┆ 2 │
+ │ 13183 ┆ Benewah County ┆ 4 days 13 hours 3 mins 34.000 secs ┆ 0.002180000 ┆ 2 │
├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌┤
- │ 119540 ┆ Kreis Unna ┆ 2 days 4 hours 52 mins 44.000 secs ┆ 0.001050000 ┆ 1 │
+ │ 124002 ┆ Kreis Unna ┆ 2 days 4 hours 52 mins 44.000 secs ┆ 0.001050000 ┆ 1 │
├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌┤
- │ 59928 ┆ Ndhiwa ┆ 2 days 4 hours 19 mins 39.000 secs ┆ 0.001040000 ┆ 1 │
+ │ 52386 ┆ Castel di Leva ┆ 2 days 4 hours 19 mins 39.000 secs ┆ 0.001040000 ┆ 1 │
└───────────┴────────────────┴────────────────────────────────────┴──────────────┴───────────┘
@@ -557,16 +563,17 @@ WHERE pickup_zone.z_zonekey != dropoff_zone.z_zonekey
└───────────────────────┘
-## Q12：使用 KNN 连接找出每个行程上车点最近的 5 栋建筑物
-**真实场景：** 找出每个行程起点最近的地标或建筑物，为定位和导航提供上下文。
+## Q12：按到最近 5 栋建筑物的平均距离对行程上车点排序（KNN 连接）
+**真实场景：** 找出最孤立的行程上车点——即周边建筑物最少的位置——用于覆盖分析和定位上下文。
-该查询通过空间最近邻分析，找出每个行程上车点最近的 5 栋建筑物。对于每个行程，它会识别出与上车点地理距离最近的 5 栋建筑物，并计算到每栋建筑物的精确距离。结果可以揭示哪些建筑物常常出现在上车点附近，有助于理解行程起点与附近地标、商业设施或住宅建筑之间的关系，以及它们如何影响出行需求模式。
+该查询通过空间最近邻分析找出每个行程上车点最近的 5 栋建筑物，然后对这 5 个距离取平均，为每个上车点生成一个"局部建筑物密度"度量。按该平均值降序排序即可揭示最孤立的行程起点——即离周边建筑物最远的位置——这可能反映农村地区、覆盖盲区或异常的上车位置。结果限制为前 100 个上车点。
**被测试的空间查询特性：**
1. K 近邻（KNN）空间连接
2. 点和多边形之间的距离计算
-3. 基于空间邻近性进行排序与限制
+3. 对每个上车点的 k 个最近邻做聚合（求平均）
+4. 对聚合后的每个上车点结果进行排序与限制
```python
@@ -574,38 +581,40 @@ sd.sql("""
WITH trip_with_geom AS (
SELECT
t_tripkey,
- t_pickuploc,
ST_GeomFromWKB(t_pickuploc) as pickup_geom
FROM trip
),
building_with_geom AS (
SELECT
- b_buildingkey,
- b_name,
- b_boundary,
ST_GeomFromWKB(b_boundary) as boundary_geom
FROM building
+),
+knn AS (
+ SELECT
+ t.t_tripkey,
+ ST_Distance(t.pickup_geom, b.boundary_geom) AS distance_to_building
+ FROM trip_with_geom t
+ JOIN building_with_geom b
+ ON ST_KNN(t.pickup_geom, b.boundary_geom, 5, FALSE)
)
SELECT
- t.t_tripkey,
- t.t_pickuploc,
- b.b_buildingkey,
- b.b_name AS building_name,
- ST_Distance(t.pickup_geom, b.boundary_geom) AS distance_to_building
-FROM trip_with_geom t
-JOIN building_with_geom b
- ON ST_KNN(t.pickup_geom, b.boundary_geom, 5, FALSE)
-ORDER BY t.t_tripkey ASC, distance_to_building ASC, b.b_buildingkey ASC
+ t_tripkey,
+ AVG(distance_to_building) AS avg_distance_to_5_nearest
+FROM knn
+GROUP BY t_tripkey
+ORDER BY avg_distance_to_5_nearest DESC, t_tripkey ASC
+LIMIT 100 -- Return only the top 100 most-isolated pickups (bounded result set)
""").show(3)
```
- ┌───────────┬─────────────────────────────────┬───────────────┬───────────────┬────────────────────┐
- │ t_tripkey ┆ t_pickuploc ┆ b_buildingkey ┆ building_name ┆ distance_to_buildi │
- │ int64 ┆ binary ┆ int64 ┆ utf8 ┆ ng… │
- ╞═══════════╪═════════════════════════════════╪═══════════════╪═══════════════╪════════════════════╡
- │ 1 ┆ 01010000009f3c318dd43735405930… ┆ 15870 ┆ purple ┆ 0.984633987957188 │
- ├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 1 ┆ 01010000009f3c318dd43735405930… ┆ 6800 ┆ ghost ┆ 1.205725156670704 │
- ├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
- │ 1 ┆ 01010000009f3c318dd43735405930… ┆ 8384 ┆ lavender ┆ 1.4195012994942622 │
- └───────────┴─────────────────────────────────┴───────────────┴───────────────┴────────────────────┘
+ ┌───────────┬───────────────────────────┐
+ │ t_tripkey ┆ avg_distance_to_5_nearest │
+ │ int64 ┆ float64 │
+ ╞═══════════╪═══════════════════════════╡
+ │ 1397637 ┆ 82.40977530183955 │
+ ├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+ │ 4166482 ┆ 82.38756120900636 │
+ ├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+ │ 2976679 ┆ 82.36270091818895 │
+ └───────────┴───────────────────────────┘
+
diff --git a/notebooks/queries.ipynb b/notebooks/queries.ipynb
index fb8691b..0db0f4e 100644
--- a/notebooks/queries.ipynb
+++ b/notebooks/queries.ipynb
@@ -110,7 +110,7 @@
},
{
"cell_type": "code",
- "execution_count": 37,
+ "execution_count": null,
"id": "f121f799-08f9-4679-a772-fb5f5470986f",
"metadata": {},
"outputs": [
@@ -149,6 +149,7 @@
" 0.45 -- 50km radius around Sedona center in degrees\n",
")\n",
"ORDER BY distance_to_center ASC, t.t_tripkey ASC\n",
+ "LIMIT 100 -- Return only the 100 closest trips (bounded result set)\n",
"\"\"\").show(3)"
]
},
@@ -172,7 +173,7 @@
},
{
"cell_type": "code",
- "execution_count": 38,
+ "execution_count": null,
"id": "cc219172-0cfb-4f60-a728-e4ec5cc5c21e",
"metadata": {},
"outputs": [
@@ -225,7 +226,7 @@
},
{
"cell_type": "code",
- "execution_count": 39,
+ "execution_count": null,
"id": "eb5d6347-47dd-48af-adb5-929838954ac4",
"metadata": {},
"outputs": [
@@ -290,7 +291,7 @@
},
{
"cell_type": "code",
- "execution_count": 40,
+ "execution_count": null,
"id": "f8dcf328-a7f2-49be-9383-afa11766f871",
"metadata": {},
"outputs": [
@@ -302,11 +303,11 @@
"│ z_zonekey ┆ z_name ┆ trip_count │\n",
"│ int64 ┆ utf8 ┆ int64 │\n",
"╞═══════════╪═════════════════════════════════╪════════════╡\n",
- "│ 65008 ┆ Ndélé ┆ 35 │\n",
+ "│ 21286 ┆ Ndélé ┆ 35 │\n",
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
- "│ 147530 ┆ 乐山市 ┆ 27 │\n",
+ "│ 80341 ┆ 乐山市 ┆ 27 │\n",
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
- "│ 150276 ┆ 锡林郭勒盟 ᠰᠢᠯᠢ ᠶᠢᠨ ᠭᠣᠣᠯ ᠠᠶᠢᠮᠠᠭ ┆ 19 │\n",
+ "│ 88075 ┆ 锡林郭勒盟 ᠰᠢᠯᠢ ᠶᠢᠨ ᠭᠣᠣᠯ ᠠᠶᠢᠮᠠᠭ ┆ 19 │\n",
"└───────────┴─────────────────────────────────┴────────────┘\n"
]
}
@@ -343,7 +344,7 @@
"\n",
"**Real-life scenario:** Analyze the geographic spread of travel patterns for frequent customers to understand their mobility behavior. \n",
"\n",
- "This query analyzes the monthly travel patterns of frequent customers by measuring how much geographic area they cover with their trips. For each customer who took more than five trips in a month, it calculates the size of the \"travel hull\" - the area enclosed by connecting all their dropoff locations that month. The results reveal which customers have the most expansive travel patterns, helping to identify power users who cover large geographic areas versus those who stick to sma [...]
+ "This query analyzes the monthly travel patterns of frequent customers by measuring how much geographic area they cover with their trips. For each customer who took more than five trips in a month, it calculates the size of the \"travel hull\" - the area enclosed by connecting all their dropoff locations that month. Results are ranked by travel-hull area (largest first) so the most geographically spread-out repeat customer-months appear first, and are bounded to the top 100. Ranking [...]
"\n",
"**Spatial query characteristics tested:**\n",
"\n",
@@ -355,7 +356,7 @@
},
{
"cell_type": "code",
- "execution_count": 41,
+ "execution_count": null,
"id": "8c446f9a-3734-4a8d-87a8-0d9d6d34fd5c",
"metadata": {},
"outputs": [
@@ -367,11 +368,11 @@
"│ c_custkey ┆ customer_name ┆ pickup_month ┆ monthly_travel_hul ┆ dropoff_count │\n",
"│ int64 ┆ utf8 ┆ timestamp ┆ l_area… ┆ int64 │\n",
"╞═══════════╪════════════════════╪═════════════════════╪════════════════════╪═══════════════╡\n",
+ "│ 10609 ┆ Customer#000010609 ┆ 1998-05-01T00:00:00 ┆ 36943.5600373373 ┆ 11 │\n",
+ "├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
"│ 25975 ┆ Customer#000025975 ┆ 1992-02-01T00:00:00 ┆ 34941.303419053635 ┆ 10 │\n",
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
"│ 12061 ┆ Customer#000012061 ┆ 1997-03-01T00:00:00 ┆ 34607.53871953154 ┆ 14 │\n",
- "├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
- "│ 21418 ┆ Customer#000021418 ┆ 1993-08-01T00:00:00 ┆ 34465.32323910264 ┆ 9 │\n",
"└───────────┴────────────────────┴─────────────────────┴────────────────────┴───────────────┘\n"
]
}
@@ -391,7 +392,8 @@
" ON t.t_custkey = c.c_custkey\n",
"GROUP BY c.c_custkey, c.c_name, pickup_month\n",
"HAVING dropoff_count > 5 -- Only include repeat customers\n",
- "ORDER BY monthly_travel_hull_area DESC, c.c_custkey ASC\n",
+ "ORDER BY monthly_travel_hull_area DESC, c.c_custkey ASC, pickup_month ASC\n",
+ "LIMIT 100 -- Return only the top 100 repeat customer-months by travel-hull area (bounded result set)\n",
"\"\"\").show(3)"
]
},
@@ -414,7 +416,7 @@
},
{
"cell_type": "code",
- "execution_count": 42,
+ "execution_count": null,
"id": "f168c302-5187-45f9-8d87-427bbd68c942",
"metadata": {},
"outputs": [
@@ -426,11 +428,11 @@
"│ z_zonekey ┆ z_name ┆ total_pickups ┆ avg_distance ┆ avg_duration │\n",
"│ int64 ┆ utf8 ┆ int64 ┆ decimal128 ┆ duration │\n",
"╞═══════════╪═════════════════╪═══════════════╪══════════════╪════════════════════════════════════╡\n",
- "│ 30084 ┆ Coconino County ┆ 541 ┆ 0.000030406 ┆ 0 days 1 hours 45 mins 16.591 secs │\n",
+ "│ 149106 ┆ Coconino County ┆ 541 ┆ 0.000030406 ┆ 0 days 1 hours 45 mins 16.591 secs │\n",
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
- "│ 30083 ┆ Yavapai County ┆ 292 ┆ 0.000027157 ┆ 0 days 1 hours 36 mins 43.647 secs │\n",
+ "│ 132418 ┆ Yavapai County ┆ 292 ┆ 0.000027157 ┆ 0 days 1 hours 36 mins 43.647 secs │\n",
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
- "│ 29488 ┆ Gila County ┆ 39 ┆ 0.000021282 ┆ 0 days 1 hours 16 mins 59.769 secs │\n",
+ "│ 82996 ┆ Gila County ┆ 39 ┆ 0.000021282 ┆ 0 days 1 hours 16 mins 59.769 secs │\n",
"└───────────┴─────────────────┴───────────────┴──────────────┴────────────────────────────────────┘\n"
]
}
@@ -482,7 +484,7 @@
},
{
"cell_type": "code",
- "execution_count": 43,
+ "execution_count": null,
"id": "3a8cddde-c04b-49d4-ab69-3d75d9a535ff",
"metadata": {},
"outputs": [
@@ -527,6 +529,7 @@
" detour_ratio DESC NULLS LAST,\n",
" reported_distance_m DESC,\n",
" t_tripkey ASC\n",
+ "LIMIT 100 -- Return only the top 100 highest-detour trips (bounded result set)\n",
"\"\"\").show(3)"
]
},
@@ -549,7 +552,7 @@
},
{
"cell_type": "code",
- "execution_count": 44,
+ "execution_count": null,
"id": "58ac6e6a-2e3d-4dd7-bb03-1b16f55fc186",
"metadata": {},
"outputs": [
@@ -578,6 +581,7 @@
"ON ST_DWithin(ST_GeomFromWKB(t.t_pickuploc), ST_GeomFromWKB(b.b_boundary), 0.0045) -- ~500m\n",
"GROUP BY b.b_buildingkey, b.b_name\n",
"ORDER BY nearby_pickup_count DESC, b.b_buildingkey ASC\n",
+ "LIMIT 100 -- Return only the top 100 busiest buildings (bounded result set)\n",
"\"\"\").show(3)"
]
},
@@ -602,7 +606,7 @@
},
{
"cell_type": "code",
- "execution_count": 45,
+ "execution_count": null,
"id": "baae3710-6008-47cf-a31f-db2813f9fca3",
"metadata": {},
"outputs": [
@@ -610,16 +614,16 @@
"name": "stdout",
"output_type": "stream",
"text": [
- "┌────────────┬────────────┬───┬───────────────────────┬────────────────────┐\n",
- "│ building_1 ┆ building_2 ┆ … ┆ overlap_area ┆ iou │\n",
- "│ int64 ┆ int64 ┆ ┆ float64 ┆ float64 │\n",
- "╞════════════╪════════════╪═══╪═══════════════════════╪════════════════════╡\n",
- "│ 2285 ┆ 15719 ┆ … ┆ 2.3709162946727276e-6 ┆ 0.9056816071717889 │\n",
- "├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
- "│ 7562 ┆ 18534 ┆ … ┆ 5.855106543747764e-6 ┆ 0.8450437137796769 │\n",
- "├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
- "│ 2285 ┆ 13658 ┆ … ┆ 1.9770693222933237e-6 ┆ 0.737899157380637 │\n",
- "└────────────┴────────────┴───┴───────────────────────┴────────────────────┘\n"
+ "┌────────────┬────────────┬───┬─────────────────────────┬────────────────────┐\n",
+ "│ building_1 ┆ building_2 ┆ … ┆ overlap_area ┆ iou │\n",
+ "│ int64 ┆ int64 ┆ ┆ float64 ┆ float64 │\n",
+ "╞════════════╪════════════╪═══╪═════════════════════════╪════════════════════╡\n",
+ "│ 7562 ┆ 18534 ┆ … ┆ 0.000022785758541344757 ┆ 0.8415764980401906 │\n",
+ "├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 2285 ┆ 15719 ┆ … ┆ 6.445979506828921e-6 ┆ 0.7022218100645453 │\n",
+ "├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 13658 ┆ 15719 ┆ … ┆ 6.813826545713668e-6 ┆ 0.7019832720541139 │\n",
+ "└────────────┴────────────┴───┴─────────────────────────┴────────────────────┘\n"
]
}
],
@@ -655,6 +659,7 @@
" END AS iou\n",
"FROM pairs\n",
"ORDER BY iou DESC, building_1 ASC, building_2 ASC\n",
+ "LIMIT 100 -- Return only the top 100 most-overlapping building pairs (bounded result set)\n",
"\"\"\").show(3)"
]
},
@@ -678,7 +683,7 @@
},
{
"cell_type": "code",
- "execution_count": 46,
+ "execution_count": null,
"id": "bd264220-b2dd-49a1-a8f9-0267ffb1371c",
"metadata": {},
"outputs": [
@@ -690,11 +695,11 @@
"│ z_zonekey ┆ pickup_zone ┆ avg_duration ┆ avg_distance ┆ num_trips │\n",
"│ int64 ┆ utf8 ┆ duration ┆ decimal128 ┆ int64 │\n",
"╞═══════════╪════════════════╪════════════════════════════════════╪══════════════╪═══════════╡\n",
- "│ 31558 ┆ Benewah County ┆ 4 days 13 hours 3 mins 34.000 secs ┆ 0.002180000 ┆ 2 │\n",
+ "│ 13183 ┆ Benewah County ┆ 4 days 13 hours 3 mins 34.000 secs ┆ 0.002180000 ┆ 2 │\n",
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌┤\n",
- "│ 119540 ┆ Kreis Unna ┆ 2 days 4 hours 52 mins 44.000 secs ┆ 0.001050000 ┆ 1 │\n",
+ "│ 124002 ┆ Kreis Unna ┆ 2 days 4 hours 52 mins 44.000 secs ┆ 0.001050000 ┆ 1 │\n",
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌┤\n",
- "│ 59928 ┆ Ndhiwa ┆ 2 days 4 hours 19 mins 39.000 secs ┆ 0.001040000 ┆ 1 │\n",
+ "│ 52386 ┆ Castel di Leva ┆ 2 days 4 hours 19 mins 39.000 secs ┆ 0.001040000 ┆ 1 │\n",
"└───────────┴────────────────┴────────────────────────────────────┴──────────────┴───────────┘\n"
]
}
@@ -715,6 +720,7 @@
" )\n",
"GROUP BY z.z_zonekey, z.z_name\n",
"ORDER BY avg_duration DESC NULLS LAST, z.z_zonekey ASC\n",
+ "LIMIT 100 -- Return only the top 100 zones by average trip duration (bounded result set)\n",
"\"\"\").show(3)"
]
},
@@ -737,7 +743,7 @@
},
{
"cell_type": "code",
- "execution_count": 47,
+ "execution_count": null,
"id": "64bf9632-b4ca-4862-9443-41e90b82bee4",
"metadata": {},
"outputs": [
@@ -778,21 +784,22 @@
"id": "386e6a72-c825-4341-ab4a-8ea59b7a9439",
"metadata": {},
"source": [
- "## Q12: Find five nearest buildings to each trip pickup location using KNN join\n",
- "**Real-life scenario:** Identify the closest landmarks or buildings to each trip start point for location context and navigation.\n",
+ "## Q12: Rank trip pickups by average distance to their five nearest buildings (KNN join)\n",
+ "**Real-life scenario:** Identify the most isolated trip pickups — those in areas with the fewest nearby buildings — for coverage and location-context analysis.\n",
"\n",
- "This query finds the 5 closest buildings to each trip pickup location using spatial nearest neighbor analysis. For every trip, it identifies the five buildings that are geographically closest to where the passenger was picked up and calculates the exact distance to each of those buildings. The results show which buildings are most commonly near pickup points, helping understand the relationship between trip origins and nearby landmarks, businesses, or residential structures that mig [...]
+ "This query finds the 5 closest buildings to each trip pickup location using spatial nearest neighbor analysis, then averages those five distances to produce a single \"local building density\" measure per pickup. Ranking pickups by this average (largest first) surfaces the most isolated trip origins — those farthest from any surrounding buildings — which can indicate rural areas, coverage gaps, or unusual pickup locations. The result is bounded to the top 100 pickups.\n",
"\n",
"**Spatial query characteristics tested:**\n",
"\n",
"1. K-nearest neighbor (KNN) spatial join\n",
"2. Distance calculations between points and polygons\n",
- "3. Ranking and limiting results based on spatial proximity"
+ "3. Aggregation (average) over each pickup's k nearest neighbors\n",
+ "4. Ranking and limiting the aggregated per-pickup results"
]
},
{
"cell_type": "code",
- "execution_count": 48,
+ "execution_count": null,
"id": "0878af06-c518-44f3-9b45-83651cc37d04",
"metadata": {},
"outputs": [
@@ -800,16 +807,16 @@
"name": "stdout",
"output_type": "stream",
"text": [
- "┌───────────┬─────────────────────────────────┬───────────────┬───────────────┬────────────────────┐\n",
- "│ t_tripkey ┆ t_pickuploc ┆ b_buildingkey ┆ building_name ┆ distance_to_buildi │\n",
- "│ int64 ┆ binary ┆ int64 ┆ utf8 ┆ ng… │\n",
- "╞═══════════╪═════════════════════════════════╪═══════════════╪═══════════════╪════════════════════╡\n",
- "│ 1 ┆ 01010000009f3c318dd43735405930… ┆ 15870 ┆ purple ┆ 0.984633987957188 │\n",
- "├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
- "│ 1 ┆ 01010000009f3c318dd43735405930… ┆ 6800 ┆ ghost ┆ 1.205725156670704 │\n",
- "├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
- "│ 1 ┆ 01010000009f3c318dd43735405930… ┆ 8384 ┆ lavender ┆ 1.4195012994942622 │\n",
- "└───────────┴─────────────────────────────────┴───────────────┴───────────────┴────────────────────┘\n"
+ "┌───────────┬───────────────────────────┐\n",
+ "│ t_tripkey ┆ avg_distance_to_5_nearest │\n",
+ "│ int64 ┆ float64 │\n",
+ "╞═══════════╪═══════════════════════════╡\n",
+ "│ 1397637 ┆ 82.40977530183955 │\n",
+ "├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 4166482 ┆ 82.38756120900636 │\n",
+ "├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 2976679 ┆ 82.36270091818895 │\n",
+ "└───────────┴───────────────────────────┘\n"
]
}
],
@@ -818,28 +825,29 @@
"WITH trip_with_geom AS (\n",
" SELECT\n",
" t_tripkey,\n",
- " t_pickuploc,\n",
" ST_GeomFromWKB(t_pickuploc) as pickup_geom\n",
" FROM trip\n",
"),\n",
"building_with_geom AS (\n",
" SELECT\n",
- " b_buildingkey,\n",
- " b_name,\n",
- " b_boundary,\n",
" ST_GeomFromWKB(b_boundary) as boundary_geom\n",
" FROM building\n",
+ "),\n",
+ "knn AS (\n",
+ " SELECT\n",
+ " t.t_tripkey,\n",
+ " ST_Distance(t.pickup_geom, b.boundary_geom) AS distance_to_building\n",
+ " FROM trip_with_geom t\n",
+ " JOIN building_with_geom b\n",
+ " ON ST_KNN(t.pickup_geom, b.boundary_geom, 5, FALSE)\n",
")\n",
"SELECT\n",
- " t.t_tripkey,\n",
- " t.t_pickuploc,\n",
- " b.b_buildingkey,\n",
- " b.b_name AS building_name,\n",
- " ST_Distance(t.pickup_geom, b.boundary_geom) AS distance_to_building\n",
- "FROM trip_with_geom t\n",
- "JOIN building_with_geom b\n",
- " ON ST_KNN(t.pickup_geom, b.boundary_geom, 5, FALSE)\n",
- "ORDER BY t.t_tripkey ASC, distance_to_building ASC, b.b_buildingkey ASC\n",
+ " t_tripkey,\n",
+ " AVG(distance_to_building) AS avg_distance_to_5_nearest\n",
+ "FROM knn\n",
+ "GROUP BY t_tripkey\n",
+ "ORDER BY avg_distance_to_5_nearest DESC, t_tripkey ASC\n",
+ "LIMIT 100 -- Return only the top 100 most-isolated pickups (bounded result set)\n",
"\"\"\").show(3)"
]
}
diff --git a/spatialbench-queries/geopandas_queries.py b/spatialbench-queries/geopandas_queries.py
index 1cf3337..f4daa69 100644
--- a/spatialbench-queries/geopandas_queries.py
+++ b/spatialbench-queries/geopandas_queries.py
@@ -52,7 +52,7 @@ def q1(data_paths: dict[str, str]) -> DataFrame: # type: ignore[override]
"t_pickuptime",
"distance_to_center",
]
- ].reset_index(drop=True)
+ ].head(100).reset_index(drop=True) # Return only the 100 closest trips (bounded result set)
def q2(data_paths: dict[str, str]) -> DataFrame: # type: ignore[override]
@@ -202,10 +202,12 @@ def q5(data_paths: dict[str, str]) -> DataFrame: # type: ignore[override]
).convex_hull.area
result = (
- grouped.sort_values(["trip_count", "c_custkey"], ascending=[False, True])[
- ["c_custkey", "c_name", "pickup_month", "monthly_travel_hull_area"]
- ]
+ grouped.sort_values(
+ ["monthly_travel_hull_area", "c_custkey", "pickup_month"],
+ ascending=[False, True, True],
+ )[["c_custkey", "c_name", "pickup_month", "monthly_travel_hull_area"]]
.rename(columns={"c_name": "customer_name"})
+ .head(100) # Return only the top 100 repeat customer-months (bounded result set)
.reset_index(drop=True)
)
return result
@@ -336,6 +338,7 @@ def q7(data_paths: dict[str, str]) -> DataFrame: # type: ignore[override]
ascending=[False, False, True],
na_position="last",
)
+ .head(100) # Return only the top 100 highest-detour trips (bounded result set)
.reset_index(drop=True)
)
return result
@@ -368,6 +371,7 @@ def q8(data_paths: dict[str, str]) -> DataFrame: # type: ignore[override]
.sort_values(
["nearby_pickup_count", "b_buildingkey"], ascending=[False, True]
)
+ .head(100) # Return only the top 100 busiest buildings (bounded result set)
.reset_index(drop=True)
)
return result # type: ignore[no-any-return]
@@ -434,6 +438,7 @@ def q9(data_paths: dict[str, str]) -> DataFrame: # type: ignore[override]
.sort_values(
["iou", "building_1", "building_2"], ascending=[False, True, True]
)
+ .head(100) # Return only the top 100 most-overlapping building pairs (bounded result set)
.reset_index(drop=True)
)
return cast(DataFrame, result)
@@ -490,6 +495,7 @@ def q10(data_paths: dict[str, str]) -> DataFrame: # type: ignore[override]
na_position="last",
)
.rename(columns={"z_name": "pickup_zone"})
+ .head(100) # Return only the top 100 zones by average trip duration (bounded result set)
.reset_index(drop=True)
)
return result # type: ignore[no-any-return]
@@ -570,12 +576,13 @@ def q11(data_paths: dict[str, str]) -> DataFrame: # type: ignore[override]
def q12(data_paths: dict[str, str]) -> DataFrame: # type: ignore[override]
- """Q12 (GeoPandas): Find 5 nearest buildings to each trip pickup location (NLJ, memory-efficient).
+ """Q12 (GeoPandas): Rank trip pickups by average distance to their 5 nearest buildings (NLJ, memory-efficient).
Uses a Python loop (nested loop join) to avoid materializing the full cross join.
- Optionally uses STRtree to shortlist candidate buildings for each pickup point.
- For each pickup, computes distances to candidates, selects 5 closest (ties by building key ASC).
- Output columns: t_tripkey, t_pickuploc, b_buildingkey, building_name, distance_to_building
+ For each pickup, computes distances to buildings, selects the 5 closest (ties by building key ASC),
+ then averages those 5 distances to produce one row per trip. Ordered by that average descending
+ (most isolated pickups first) and bounded to the top 100.
+ Output columns: t_tripkey, avg_distance_to_5_nearest
"""
trips_df = pd.read_parquet(data_paths["trip"])
buildings_df = pd.read_parquet(data_paths["building"])
@@ -592,9 +599,9 @@ def q12(data_paths: dict[str, str]) -> DataFrame: # type: ignore[override]
)
pickup_geoms = trips_gdf["pickup_geom"].to_list()
+ trip_keys = trips_gdf["t_tripkey"].to_numpy()
building_geoms = buildings_gdf["boundary_geom"].to_list()
building_keys = buildings_gdf["b_buildingkey"].to_numpy()
- building_names = buildings_gdf["b_name"].to_numpy()
results = []
# Since geopandas doesn't support KNN join, we had to choose either a cross join + filter or a NLJ.
@@ -603,22 +610,20 @@ def q12(data_paths: dict[str, str]) -> DataFrame: # type: ignore[override]
# actually get the query to run.
for i, pt in enumerate(pickup_geoms):
dists = [pt.distance(geom) for geom in building_geoms]
- # Sort by distance, then building key
+ # 5 nearest buildings (ties by building key ASC), then average their distances
nearest_idx = np.lexsort((building_keys, dists))[:5]
- for idx in nearest_idx:
- results.append(
- {
- "t_tripkey": trips_gdf.iloc[i]["t_tripkey"],
- "t_pickuploc": trips_gdf.iloc[i]["t_pickuploc"],
- "b_buildingkey": building_keys[idx],
- "building_name": building_names[idx],
- "distance_to_building": dists[idx],
- }
- )
+ avg_distance = float(np.mean([dists[idx] for idx in nearest_idx]))
+ results.append(
+ {
+ "t_tripkey": trip_keys[i],
+ "avg_distance_to_5_nearest": avg_distance,
+ }
+ )
return (
pd.DataFrame(results)
.sort_values(
- ["distance_to_building", "b_buildingkey"], ascending=[True, True]
+ ["avg_distance_to_5_nearest", "t_tripkey"], ascending=[False, True]
)
+ .head(100) # Return only the top 100 most-isolated pickups (bounded result set)
.reset_index(drop=True)
)
diff --git a/spatialbench-queries/print_queries.py b/spatialbench-queries/print_queries.py
index f0ad605..a78f32c 100755
--- a/spatialbench-queries/print_queries.py
+++ b/spatialbench-queries/print_queries.py
@@ -71,6 +71,7 @@ SELECT
FROM trip t
WHERE ST_DWithin(ST_GeomFromWKB(t.t_pickuploc), ST_GeomFromText('POINT (-111.7610 34.8697)'), 0.45) -- 50km 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)
"""
@staticmethod
@@ -129,7 +130,8 @@ SELECT
FROM trip t JOIN customer c ON t.t_custkey = c.c_custkey
GROUP BY c.c_custkey, c.c_name, pickup_month
HAVING dropoff_count > 5 -- Only include repeat customers for meaningful hulls
-ORDER BY dropoff_count DESC, c.c_custkey ASC
+ORDER BY monthly_travel_hull_area DESC, c.c_custkey ASC, pickup_month ASC
+LIMIT 100 -- Return only the top 100 repeat customer-months by travel-hull area (bounded result set)
"""
@staticmethod
@@ -170,6 +172,7 @@ SELECT
t.reported_distance_m / NULLIF(t.line_distance_m, 0) AS detour_ratio
FROM trip_lengths t
ORDER BY detour_ratio DESC NULLS LAST, reported_distance_m DESC, t_tripkey ASC
+LIMIT 100 -- Return only the top 100 highest-detour trips (bounded result set)
"""
@staticmethod
@@ -180,6 +183,7 @@ SELECT b.b_buildingkey, b.b_name, COUNT(*) AS nearby_pickup_count
FROM trip t JOIN building b ON ST_DWithin(ST_GeomFromWKB(t.t_pickuploc), ST_GeomFromWKB(b.b_boundary), 0.0045) -- ~500m
GROUP BY b.b_buildingkey, b.b_name
ORDER BY nearby_pickup_count DESC, b.b_buildingkey ASC
+LIMIT 100 -- Return only the top 100 busiest buildings (bounded result set)
"""
@staticmethod
@@ -219,6 +223,7 @@ SELECT
END AS iou
FROM pairs
ORDER BY iou DESC, building_1 ASC, building_2 ASC
+LIMIT 100 -- Return only the top 100 most-overlapping building pairs (bounded result set)
"""
@staticmethod
@@ -231,6 +236,7 @@ SELECT
FROM zone z LEFT JOIN trip t ON ST_Within(ST_GeomFromWKB(t.t_pickuploc), ST_GeomFromWKB(z.z_boundary))
GROUP BY z.z_zonekey, z.z_name
ORDER BY avg_duration DESC NULLS LAST, z.z_zonekey ASC
+LIMIT 100 -- Return only the top 100 zones by average trip duration (bounded result set)
"""
@staticmethod
@@ -249,24 +255,30 @@ WHERE pickup_zone.z_zonekey != dropoff_zone.z_zonekey
def q12() -> str:
# There is some odd bug with missing columns in EMR. Using CTEs to work around it.
return """
--- Q12: Find 5 nearest buildings to each trip pickup location using KNN join
+-- Q12: Rank trip pickups by the average distance to their 5 nearest buildings (KNN join).
+-- The top results are the most isolated pickups (farthest from surrounding buildings).
WITH trip_with_geom AS (
- SELECT t_tripkey, t_pickuploc, ST_GeomFromWKB(t_pickuploc) as pickup_geom
+ SELECT t_tripkey, ST_GeomFromWKB(t_pickuploc) as pickup_geom
FROM trip
),
building_with_geom AS (
- SELECT b_buildingkey, b_name, b_boundary, ST_GeomFromWKB(b_boundary) as boundary_geom
+ SELECT ST_GeomFromWKB(b_boundary) as boundary_geom
FROM building
+ ),
+ knn AS (
+ SELECT
+ t.t_tripkey,
+ ST_Distance(t.pickup_geom, b.boundary_geom) AS distance_to_building
+ FROM trip_with_geom t JOIN building_with_geom b
+ ON ST_KNN(t.pickup_geom, b.boundary_geom, 5, FALSE)
)
SELECT
- t.t_tripkey,
- t.t_pickuploc,
- b.b_buildingkey,
- b.b_name AS building_name,
- ST_Distance(t.pickup_geom, b.boundary_geom) AS distance_to_building
-FROM trip_with_geom t JOIN building_with_geom b
- ON ST_KNN(t.pickup_geom, b.boundary_geom, 5, FALSE)
-ORDER BY distance_to_building ASC, b.b_buildingkey ASC
+ t_tripkey,
+ AVG(distance_to_building) AS avg_distance_to_5_nearest
+FROM knn
+GROUP BY t_tripkey
+ORDER BY avg_distance_to_5_nearest DESC, t_tripkey ASC
+LIMIT 100 -- Return only the top 100 most-isolated pickups (bounded result set)
"""
@@ -294,7 +306,8 @@ SELECT
FROM trip t JOIN customer c ON t.t_custkey = c.c_custkey
GROUP BY c.c_custkey, c.c_name, pickup_month
HAVING dropoff_count > 5 -- Only include repeat customers for meaningful hulls
-ORDER BY dropoff_count DESC, c.c_custkey ASC
+ORDER BY monthly_travel_hull_area DESC, c.c_custkey ASC, pickup_month ASC
+LIMIT 100 -- Return only the top 100 repeat customer-months by travel-hull area (bounded result set)
"""
@staticmethod
@@ -322,6 +335,7 @@ SELECT
t.reported_distance_m / NULLIF(t.line_distance_m, 0) AS detour_ratio
FROM trip_lengths t
ORDER BY detour_ratio DESC NULLS LAST, reported_distance_m DESC, t_tripkey ASC
+LIMIT 100 -- Return only the top 100 highest-detour trips (bounded result set)
"""
@staticmethod
@@ -329,18 +343,13 @@ ORDER BY detour_ratio DESC NULLS LAST, reported_distance_m DESC, t_tripkey ASC
return """
-- Q12 (Databricks): No KNN join, using cross join + ROW_NUMBER() window function instead.
-- Note: Databricks doesn't have cross join lateral support.
+-- Ranks trip pickups by the average distance to their 5 nearest buildings.
SELECT
t_tripkey,
- t_pickuploc,
- b_buildingkey,
- building_name,
- distance_to_building
+ AVG(distance_to_building) AS avg_distance_to_5_nearest
FROM (
SELECT
t.t_tripkey,
- t.t_pickuploc,
- b.b_buildingkey,
- b.b_name AS building_name,
ST_Distance(ST_GeomFromWKB(t.t_pickuploc), ST_GeomFromWKB(b.b_boundary)) AS distance_to_building,
ROW_NUMBER() OVER (
PARTITION BY t.t_tripkey
@@ -350,7 +359,9 @@ FROM (
JOIN building b
) AS ranked_buildings
WHERE rn <= 5
-ORDER BY distance_to_building ASC, b_buildingkey ASC
+GROUP BY t_tripkey
+ORDER BY avg_distance_to_5_nearest DESC, t_tripkey ASC
+LIMIT 100 -- Return only the top 100 most-isolated pickups (bounded result set)
"""
@@ -369,23 +380,21 @@ class DuckDBSpatialBenchBenchmark(SpatialBenchBenchmark):
def q12() -> str:
return """
-- Q12 (DuckDB): No KNN join, using cross join lateral instead.
+-- Ranks trip pickups by the average distance to their 5 nearest buildings.
SELECT
t.t_tripkey,
- t.t_pickuploc,
- nb.b_buildingkey,
- nb.building_name,
- nb.distance_to_building
+ AVG(nb.distance_to_building) AS avg_distance_to_5_nearest
FROM trip t
CROSS JOIN LATERAL (
SELECT
- b.b_buildingkey,
- b.b_name AS building_name,
ST_Distance(ST_GeomFromWKB(t.t_pickuploc), ST_GeomFromWKB(b.b_boundary)) AS distance_to_building
FROM building b
ORDER BY distance_to_building
LIMIT 5
) AS nb
-ORDER BY nb.distance_to_building, nb.b_buildingkey
+GROUP BY t.t_tripkey
+ORDER BY avg_distance_to_5_nearest DESC, t.t_tripkey ASC
+LIMIT 100 -- Return only the top 100 most-isolated pickups (bounded result set)
"""
@@ -413,7 +422,8 @@ SELECT
FROM trip t JOIN customer c ON t.t_custkey = c.c_custkey
GROUP BY c.c_custkey, c.c_name, pickup_month
HAVING dropoff_count > 5 -- Only include repeat customers for meaningful hulls
-ORDER BY dropoff_count DESC, c.c_custkey ASC
+ORDER BY monthly_travel_hull_area DESC, c.c_custkey ASC, pickup_month ASC
+LIMIT 100 -- Return only the top 100 repeat customer-months by travel-hull area (bounded result set)
"""
diff --git a/spatialbench-queries/pycanopy_queries.py b/spatialbench-queries/pycanopy_queries.py
index 00523a8..4446c20 100644
--- a/spatialbench-queries/pycanopy_queries.py
+++ b/spatialbench-queries/pycanopy_queries.py
@@ -44,6 +44,7 @@ def q1(data_paths: dict[str, str]) -> pl.DataFrame:
"distance_to_center",
)
.sort(["distance_to_center", "t_tripkey"])
+ .head(100) # Return only the 100 closest trips (bounded result set)
)
@@ -145,10 +146,12 @@ def q5(data_paths: dict[str, str]) -> pl.DataFrame:
areas = pc.Engine.group_convex_hull_areas(grouped["dxs"], grouped["dys"])
grouped = grouped.with_columns(
monthly_travel_hull_area=pl.Series("monthly_travel_hull_area", areas, dtype=pl.Float64)
- ).sort(["trip_count", "t_custkey"], descending=[True, False])
+ ).sort(["monthly_travel_hull_area", "t_custkey", "pickup_month"], descending=[True, False, False])
- return grouped.select(["t_custkey", "c_name", "pickup_month", "monthly_travel_hull_area"]).rename(
- {"t_custkey": "c_custkey", "c_name": "customer_name"}
+ return (
+ grouped.select(["t_custkey", "c_name", "pickup_month", "monthly_travel_hull_area"])
+ .rename({"t_custkey": "c_custkey", "c_name": "customer_name"})
+ .head(100) # Return only the top 100 repeat customer-months (bounded result set)
)
@@ -210,10 +213,14 @@ def q7(data_paths: dict[str, str]) -> pl.DataFrame:
.then(pl.col("reported_distance_m") / pl.col("line_distance_m"))
.otherwise(None)
)
- return df.select("t_tripkey", "reported_distance_m", "line_distance_m", "detour_ratio").sort(
- ["detour_ratio", "reported_distance_m", "t_tripkey"],
- descending=[True, True, False],
- nulls_last=True,
+ return (
+ df.select("t_tripkey", "reported_distance_m", "line_distance_m", "detour_ratio")
+ .sort(
+ ["detour_ratio", "reported_distance_m", "t_tripkey"],
+ descending=[True, True, False],
+ nulls_last=True,
+ )
+ .head(100) # Return only the top 100 highest-detour trips (bounded result set)
)
@@ -234,6 +241,7 @@ def q8(data_paths: dict[str, str]) -> pl.DataFrame:
.group_by(["b_buildingkey", "b_name"])
.agg(nearby_pickup_count=pc.agg.count())
.sort(["nearby_pickup_count", "b_buildingkey"], descending=[True, False])
+ .head(100) # Return only the top 100 busiest buildings (bounded result set)
)
@@ -253,14 +261,18 @@ def q9(data_paths: dict[str, str]) -> pl.DataFrame:
"iou": pl.Float64,
}
)
- return pairs.select(
- pl.col("b_buildingkey_1").alias("building_1"),
- pl.col("b_buildingkey_2").alias("building_2"),
- pl.col("area_left").alias("area1"),
- pl.col("area_right").alias("area2"),
- "overlap_area",
- "iou",
- ).sort(["iou", "building_1", "building_2"], descending=[True, False, False])
+ return (
+ pairs.select(
+ pl.col("b_buildingkey_1").alias("building_1"),
+ pl.col("b_buildingkey_2").alias("building_2"),
+ pl.col("area_left").alias("area1"),
+ pl.col("area_right").alias("area2"),
+ "overlap_area",
+ "iou",
+ )
+ .sort(["iou", "building_1", "building_2"], descending=[True, False, False])
+ .head(100) # Return only the top 100 most-overlapping building pairs (bounded result set)
+ )
def q10(data_paths: dict[str, str]) -> pl.DataFrame:
@@ -295,7 +307,9 @@ def q10(data_paths: dict[str, str]) -> pl.DataFrame:
.with_columns(num_trips=pl.col("num_trips").fill_null(0))
.rename({"z_name": "pickup_zone"})
)
- return result.sort(["avg_duration", "z_zonekey"], descending=[True, False], nulls_last=True)
+ return result.sort(
+ ["avg_duration", "z_zonekey"], descending=[True, False], nulls_last=True
+ ).head(100) # Return only the top 100 zones by average trip duration (bounded result set)
def q11(data_paths: dict[str, str]) -> pl.DataFrame:
@@ -330,20 +344,30 @@ def q11(data_paths: dict[str, str]) -> pl.DataFrame:
def q12(data_paths: dict[str, str]) -> pl.DataFrame:
- """Q12 (PyCanopy): The 5 nearest buildings to each trip pickup location."""
+ """Q12 (PyCanopy): Rank trip pickups by average distance to their 5 nearest buildings.
+
+ For each pickup, averages the distances to its 5 nearest buildings to produce one row per trip.
+ Ordered by that average descending (most isolated pickups first), bounded to the top 100.
+ Output columns: t_tripkey, avg_distance_to_5_nearest
+ """
k = 5
- buildings = pl.read_parquet(data_paths["building"], columns=["b_buildingkey", "b_name", "b_boundary"])
+ buildings = pl.read_parquet(data_paths["building"], columns=["b_buildingkey", "b_boundary"])
sf = pc.SpatialFrame.from_wkb_polygons(buildings, "b_boundary")
trip = pl.read_parquet(data_paths["trip"], columns=["t_tripkey", "t_pickuploc"])
qx, qy = pc.wkb_points_to_xy(trip["t_pickuploc"])
- query_df = trip.select("t_tripkey", "t_pickuploc").with_columns(pl.Series("qx", qx), pl.Series("qy", qy))
+ query_df = trip.select("t_tripkey").with_columns(pl.Series("qx", qx), pl.Series("qy", qy))
- return (
+ knn = (
sf.lazy()
.polygon_knn_join(query_df, "qx", "qy", k=k, sorted_output=True)
- .select("t_tripkey", "t_pickuploc", "b_buildingkey", "b_name", "distance_to_polygon")
+ .select("t_tripkey", "distance_to_polygon")
.collect()
- .rename({"b_name": "building_name", "distance_to_polygon": "distance_to_building"})
+ )
+ return (
+ knn.group_by("t_tripkey")
+ .agg(avg_distance_to_5_nearest=pl.col("distance_to_polygon").mean())
+ .sort(["avg_distance_to_5_nearest", "t_tripkey"], descending=[True, False])
+ .head(100) # Return only the top 100 most-isolated pickups (bounded result set)
)
diff --git a/spatialbench-queries/spatial_polars.py b/spatialbench-queries/spatial_polars.py
index 478d28b..211db56 100644
--- a/spatialbench-queries/spatial_polars.py
+++ b/spatialbench-queries/spatial_polars.py
@@ -52,6 +52,7 @@ def q1(data_paths: dict[str, str]) -> DataFrame:
"distance_to_center",
"t_tripkey",
)
+ .head(100) # Return only the 100 closest trips (bounded result set)
.collect(engine="streaming")
)
@@ -237,8 +238,10 @@ def q5(data_paths: dict[str, str]) -> DataFrame:
.sort(
pl.col("monthly_travel_hull_area"),
pl.col("c_custkey"),
- descending=[True, False],
+ pl.col("pickup_month"),
+ descending=[True, False, False],
)
+ .head(100) # Return only the top 100 repeat customer-months by travel-hull area (bounded result set)
.collect(engine="streaming")
)
@@ -349,6 +352,7 @@ def q7(data_paths: dict[str, str]) -> DataFrame:
descending=[True, True, False],
nulls_last=[True, False, False],
)
+ .head(100) # Return only the top 100 highest-detour trips (bounded result set)
.collect(engine="streaming")
)
@@ -384,6 +388,7 @@ def q8(data_paths: dict[str, str]) -> DataFrame:
pl.col("b_buildingkey"),
descending=[True, False],
)
+ .head(100) # Return only the top 100 busiest buildings (bounded result set)
)
@@ -453,6 +458,7 @@ def q9(data_paths: dict[str, str]) -> DataFrame:
pl.col("building_2"),
descending=[True, False, False],
)
+ .head(100) # Return only the top 100 most-overlapping building pairs (bounded result set)
)
@@ -497,6 +503,7 @@ def q10(data_paths: dict[str, str]) -> DataFrame:
descending=[True, False],
nulls_last=[True, False],
)
+ .head(100) # Return only the top 100 zones by average trip duration (bounded result set)
)
@@ -545,13 +552,14 @@ def q11(data_paths: dict[str, str]) -> DataFrame:
def q12(data_paths: dict[str, str]) -> DataFrame:
- """Q12 (Spatial Polars): Find 5 nearest buildings to each trip pickup location.
+ """Q12 (Spatial Polars): Rank trip pickups by average distance to their 5 nearest buildings.
Spatial polars uses scipy's KDtree for KNN joins. Scipy must be installed for this to work.
`pip install spatial-polars[knn]`
- For each pickup, computes distances to candidates, selects 5 closest (ties by building key ASC).
- Output columns: t_tripkey, t_pickuploc, b_buildingkey, building_name, distance_to_building
+ For each pickup, computes distances to the 5 nearest buildings and averages them to produce one
+ row per trip. Ordered by that average descending (most isolated pickups first), bounded to top 100.
+ Output columns: t_tripkey, avg_distance_to_5_nearest
"""
return (
@@ -564,8 +572,6 @@ def q12(data_paths: dict[str, str]) -> DataFrame:
.spatial.centroid_knn_join(
pl.scan_parquet(data_paths["building"])
.select(
- pl.col("b_buildingkey"),
- pl.col("b_name"),
pl.col("b_boundary").spatial.from_WKB().alias("boundary_geom"),
)
.collect(engine="streaming"),
@@ -576,18 +582,19 @@ def q12(data_paths: dict[str, str]) -> DataFrame:
.lazy()
.select(
pl.col("t_tripkey"),
- pl.col("pickup_geom").struct.field("wkb_geometry").alias("t_pickuploc"),
- pl.col("b_buildingkey"),
- pl.col("b_name").alias("building_name"),
pl.struct(("pickup_geom", "boundary_geom"))
.spatial.distance()
.alias("distance_to_building"),
)
+ .group_by("t_tripkey")
+ .agg(
+ pl.col("distance_to_building").mean().alias("avg_distance_to_5_nearest"),
+ )
.sort(
+ pl.col("avg_distance_to_5_nearest"),
pl.col("t_tripkey"),
- pl.col("distance_to_building"),
- pl.col("b_buildingkey"),
- descending=[False, False, True],
+ descending=[True, False],
)
+ .head(100) # Return only the top 100 most-isolated pickups (bounded result set)
.collect(engine="streaming")
)