This is an automated email from the ASF dual-hosted git repository.
jiayu 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 6c07657 docs: add queries notebook (#26)
6c07657 is described below
commit 6c07657a1b64abe2f8e860fe37648651248a4379
Author: Matthew Powers <[email protected]>
AuthorDate: Mon Sep 22 17:43:24 2025 -0400
docs: add queries notebook (#26)
* docs: add queries notebook
* install sedonadb from pip
* update sql
* remove duplicate requirements file
---
docs/queries.ipynb | 741 ++++++++++++++++++++++++++++++++++++++++++++++++++
docs/requirements.txt | 7 +-
mkdocs.yml | 4 +-
requirements-docs.txt | 8 -
4 files changed, 750 insertions(+), 10 deletions(-)
diff --git a/docs/queries.ipynb b/docs/queries.ipynb
new file mode 100644
index 0000000..d5ea175
--- /dev/null
+++ b/docs/queries.ipynb
@@ -0,0 +1,741 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "cf23cfd2-c45c-4ca3-9dcb-70541f3cc47d",
+ "metadata": {},
+ "source": [
+ "# SpatialBench Queries"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 22,
+ "id": "9c1fe889-d035-4ed9-85a6-f7a8ecb891ad",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "import sedona.db"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 23,
+ "id": "96cf6ad5-e106-48f7-9097-9f3f740e9e7f",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "sd = sedona.db.connect()"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 24,
+ "id": "16a147f4-4a12-4050-a1bb-934834b4a6bc",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+
"sd.read_parquet(f\"/Users/matthewpowers/data/sf1-parquet/building.parquet\").to_view(\"building\")\n",
+
"sd.read_parquet(f\"/Users/matthewpowers/data/sf1-parquet/customer.parquet\").to_view(\"customer\")\n",
+
"sd.read_parquet(f\"/Users/matthewpowers/data/sf1-parquet/driver.parquet\").to_view(\"driver\")\n",
+
"sd.read_parquet(f\"/Users/matthewpowers/data/sf1-parquet/trip.parquet\").to_view(\"trip\")\n",
+
"sd.read_parquet(f\"/Users/matthewpowers/data/sf1-parquet/vehicle.parquet\").to_view(\"vehicle\")\n",
+
"sd.read_parquet(f\"/Users/matthewpowers/data/sf1-parquet/zone.parquet\").to_view(\"zone\")"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "5e243bca-ca97-4f72-bc72-46ace7495019",
+ "metadata": {},
+ "source": [
+ "## Q1: Find trips starting within 50km of Sedona city center, ordered by
distance\n",
+ "\n",
+ "**Real-life scenario:** Identify and rank trips by proximity to a city
center for urban planning and transportation analysis.\n",
+ "\n",
+ "This query finds all taxi or rideshare trips that started within 50
kilometers of downtown Sedona, Arizona. For each qualifying trip, it shows the
trip ID, pickup coordinates, pickup time, and calculates the exact distance
from the pickup location to Sedona's city center. The results are sorted to
show the trips that picked up closest to downtown Sedona first, making it easy
to see which rides originated nearest to the city center.\n",
+ "\n",
+ "**Spatial query characteristics tested:**\n",
+ "\n",
+ "1. Distance-based spatial filtering (ST_DWithin)\n",
+ "2. Distance calculation to a fixed point\n",
+ "3. Coordinate extraction (ST_X, ST_Y)\n",
+ "4. Ordering by spatial distance"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 25,
+ "id": "f121f799-08f9-4679-a772-fb5f5470986f",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+
"┌───────────┬────────────────┬──────────────┬──────────────────────────────┬──────────────────────┐\n",
+ "│ t_tripkey ┆ pickup_lon ┆ pickup_lat ┆ t_pickuptime
┆ distance_to_center │\n",
+ "│ int64 ┆ float64 ┆ float64 ┆ timestamp(millisecond,
none) ┆ float64 │\n",
+
"╞═══════════╪════════════════╪══════════════╪══════════════════════════════╪══════════════════════╡\n",
+ "│ 1451371 ┆ -111.791052127 ┆ 34.826733457 ┆ 1998-08-12T06:47:01
┆ 0.05243333056935386 │\n",
+
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 2047835 ┆ -111.706967009 ┆ 34.883889472 ┆ 1992-04-08T07:36:09
┆ 0.055865062714050374 │\n",
+
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 3936870 ┆ -111.827619221 ┆ 34.882950924 ┆ 1998-11-10T13:32:07
┆ 0.06792427838042854 │\n",
+
"└───────────┴────────────────┴──────────────┴──────────────────────────────┴──────────────────────┘\n"
+ ]
+ }
+ ],
+ "source": [
+ "sd.sql(\"\"\"\n",
+ "SELECT\n",
+ " t.t_tripkey, ST_X(ST_GeomFromWKB(t.t_pickuploc)) AS pickup_lon,
ST_Y(ST_GeomFromWKB(t.t_pickuploc)) AS pickup_lat, t.t_pickuptime,\n",
+ " ST_Distance(ST_GeomFromWKB(t.t_pickuploc), ST_GeomFromText('POINT
(-111.7610 34.8697)')) AS distance_to_center\n",
+ "FROM trip t\n",
+ "WHERE ST_DWithin(ST_GeomFromWKB(t.t_pickuploc), ST_GeomFromText('POINT
(-111.7610 34.8697)'), 0.45) -- 50km radius around Sedona center\n",
+ "ORDER BY distance_to_center ASC, t.t_tripkey ASC\n",
+ "\"\"\").show(3)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "d1e2a458-fc72-4a21-889e-9099f9ad6fb7",
+ "metadata": {},
+ "source": [
+ "## Q2: Count trips starting within Coconino County (Arizona) zone\n",
+ "\n",
+ "**Real-life scenario:** Count all trips originating within a specific
administrative boundary (county) for regional transportation statistics.\n",
+ "\n",
+ "This query counts how many taxi or rideshare trips started within
Coconino County, Arizona. It does this by checking if each trip's pickup
location falls inside the county's geographic boundaries. The result is a
simple count showing the total number of trips that originated anywhere within
Coconino County.\n",
+ "\n",
+ "**Spatial query characteristics tested:**\n",
+ "\n",
+ "1. Point-in-polygon spatial filtering (ST_Intersects)\n",
+ "2. Subquery with spatial geometry selection\n",
+ "3. Simple aggregation on spatially filtered data"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 26,
+ "id": "cc219172-0cfb-4f60-a728-e4ec5cc5c21e",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "┌───────────────────────────────┐\n",
+ "│ trip_count_in_coconino_county │\n",
+ "│ int64 │\n",
+ "╞═══════════════════════════════╡\n",
+ "│ 541 │\n",
+ "└───────────────────────────────┘\n"
+ ]
+ }
+ ],
+ "source": [
+ "sd.sql(\"\"\"\n",
+ "SELECT COUNT(*) AS trip_count_in_coconino_county\n",
+ "FROM trip t\n",
+ "WHERE ST_Intersects(ST_GeomFromWKB(t.t_pickuploc), (SELECT
ST_GeomFromWKB(z.z_boundary) FROM zone z WHERE z.z_name = 'Coconino County'
LIMIT 1))\n",
+ "\"\"\").show(3)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "998daae7-31e4-4e93-823d-70d4cee00605",
+ "metadata": {},
+ "source": [
+ "## Q3: Monthly trip statistics within a 15km radius of the Sedona city
center\n",
+ "\n",
+ "**Real-life scenario:** Track monthly travel trends and performance
metrics in a metropolitan area with seasonal analysis.\n",
+ "\n",
+ "This query analyzes taxi and rideshare trip patterns around Sedona,
Arizona, by grouping trips into monthly summaries. It looks at all trips that
started within a 15-kilometer area around Sedona (a 10km box plus 5km buffer)
and calculates key statistics for each month, including total number of trips,
average trip distance, average trip duration, and average fare. The results are
organized chronologically by month, allowing you to see seasonal trends and
changes in ride patterns ove [...]
+ "\n",
+ "**Spatial query characteristics tested:**\n",
+ "\n",
+ "1. Distance-based spatial filtering (ST_DWithin) with buffer\n",
+ "2. Temporal grouping (monthly aggregation)\n",
+ "3. Multiple statistical aggregations on spatially filtered data"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 27,
+ "id": "eb5d6347-47dd-48af-adb5-929838954ac4",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+
"┌──────────────────────┬─────────────┬───────────────────┬─────────────────────┬───────────────────┐\n",
+ "│ pickup_month ┆ total_trips ┆ avg_distance ┆
avg_duration ┆ avg_fare │\n",
+ "│ timestamp(milliseco… ┆ int64 ┆ decimal128(19, 9) ┆
duration(milliseco… ┆ decimal128(19, 9) │\n",
+
"╞══════════════════════╪═════════════╪═══════════════════╪═════════════════════╪═══════════════════╡\n",
+ "│ 1992-04-01T00:00:00 ┆ 2 ┆ 0.000020000 ┆ 0 days 1
hours 23 … ┆ 0.000075000 │\n",
+
"├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 1992-07-01T00:00:00 ┆ 1 ┆ 0.000010000 ┆ 0 days 0
hours 58 … ┆ 0.000040000 │\n",
+
"├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 1994-02-01T00:00:00 ┆ 2 ┆ 0.000020000 ┆ 0 days 1
hours 23 … ┆ 0.000050000 │\n",
+
"└──────────────────────┴─────────────┴───────────────────┴─────────────────────┴───────────────────┘\n"
+ ]
+ }
+ ],
+ "source": [
+ "sd.sql(\"\"\"\n",
+ "SELECT\n",
+ " DATE_TRUNC('month', t.t_pickuptime) AS pickup_month,
COUNT(t.t_tripkey) AS total_trips,\n",
+ " AVG(t.t_distance) AS avg_distance, AVG(t.t_dropofftime -
t.t_pickuptime) AS avg_duration,\n",
+ " AVG(t.t_fare) AS avg_fare\n",
+ "FROM trip t\n",
+ "WHERE ST_DWithin(\n",
+ " ST_GeomFromWKB(t.t_pickuploc),\n",
+ " ST_GeomFromText('POLYGON((-111.9060 34.7347, -111.6160 34.7347,
-111.6160 35.0047, -111.9060 35.0047, -111.9060 34.7347))'), -- 10km bounding
box around Sedona\n",
+ " 0.045 -- Additional 5km buffer\n",
+ ")\n",
+ "GROUP BY pickup_month\n",
+ "ORDER BY pickup_month\n",
+ "\"\"\").show(3)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "b516ed52-8cf2-4c17-bc33-00002e22b287",
+ "metadata": {},
+ "source": [
+ "## Q4: Zone distribution of top 1000 trips by tip amount\n",
+ "\n",
+ "**Real-life scenario:** Analyze the geographic distribution of high-value
trips (by tip amount) to understand premium service areas.\n",
+ "\n",
+ "This query identifies which neighborhoods or zones produced the most
generous tippers by analyzing the top 1000 highest-tipping trips. It first
finds the 1000 trips with the largest tips, then determines which geographic
zones or neighborhoods those pickup locations fall within, and counts how many
of these high-tip trips originated from each area. The results show a ranking
of zones by the number of big tippers they produced, helping identify the most
lucrative pickup areas for dri [...]
+ "\n",
+ "**Spatial query characteristics tested:**\n",
+ "\n",
+ "1. Subquery with ordering and limiting\n",
+ "2. Point-in-polygon spatial join (ST_Within)\n",
+ "3. Aggregation on spatially joined results\n",
+ "4. Multi-step query with spatial filtering and grouping"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 28,
+ "id": "f8dcf328-a7f2-49be-9383-afa11766f871",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "┌───────────┬─────────────────────────────────┬────────────┐\n",
+ "│ z_zonekey ┆ z_name ┆ trip_count │\n",
+ "│ int64 ┆ utf8view ┆ int64 │\n",
+ "╞═══════════╪═════════════════════════════════╪════════════╡\n",
+ "│ 65008 ┆ Ndélé ┆ 35 │\n",
+ "├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 147530 ┆ 乐山市 ┆ 27 │\n",
+ "├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 150276 ┆ 锡林郭勒盟 ᠰᠢᠯᠢ ᠶᠢᠨ ᠭᠣᠣᠯ ᠠᠶᠢᠮᠠᠭ ┆ 19 │\n",
+ "└───────────┴─────────────────────────────────┴────────────┘\n"
+ ]
+ }
+ ],
+ "source": [
+ "sd.sql(\"\"\"\n",
+ "SELECT z.z_zonekey, z.z_name, COUNT(*) AS trip_count\n",
+ "FROM\n",
+ " zone z\n",
+ " JOIN (\n",
+ " SELECT t.t_pickuploc\n",
+ " FROM trip t\n",
+ " ORDER BY t.t_tip DESC, t.t_tripkey ASC\n",
+ " LIMIT 1000 -- Replace 1000 with x (how many top tips you want)\n",
+ " ) top_trips ON ST_Within(ST_GeomFromWKB(top_trips.t_pickuploc),
ST_GeomFromWKB(z.z_boundary))\n",
+ "GROUP BY z.z_zonekey, z.z_name\n",
+ "ORDER BY trip_count DESC, z.z_zonekey ASC\n",
+ "\"\"\").show(3)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "ad294223-742c-4b6b-a9ec-036723e67a1f",
+ "metadata": {},
+ "source": [
+ "## Q5: Monthly travel patterns for repeat customers (convex hull of
dropoff locations)\n",
+ "\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 [...]
+ "\n",
+ "**Spatial query characteristics tested:**\n",
+ "\n",
+ "1. Spatial aggregation (ST_Collect/ARRAY_AGG)\n",
+ "2. Convex hull computation (ST_ConvexHull)\n",
+ "3. Area calculation on complex geometries\n",
+ "4. Temporal and customer-based grouping with spatial operations"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 29,
+ "id": "8c446f9a-3734-4a8d-87a8-0d9d6d34fd5c",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+
"┌───────────┬────────────────────┬────────────────────────────┬────────────────────┬───────────────┐\n",
+ "│ c_custkey ┆ customer_name ┆ pickup_month ┆
monthly_travel_hul ┆ dropoff_count │\n",
+ "│ int64 ┆ utf8view ┆ timestamp(millisecond, no… ┆
l_area… ┆ int64 │\n",
+
"╞═══════════╪════════════════════╪════════════════════════════╪════════════════════╪═══════════════╡\n",
+ "│ 12049 ┆ Customer#000012049 ┆ 1997-10-01T00:00:00 ┆
22290.72518508209 ┆ 18 │\n",
+
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 12091 ┆ Customer#000012091 ┆ 1997-08-01T00:00:00 ┆
18901.827089935185 ┆ 18 │\n",
+
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 13231 ┆ Customer#000013231 ┆ 1994-09-01T00:00:00 ┆
24930.673726244015 ┆ 18 │\n",
+
"└───────────┴────────────────────┴────────────────────────────┴────────────────────┴───────────────┘\n"
+ ]
+ }
+ ],
+ "source": [
+ "sd.sql(\"\"\"\n",
+ "SELECT\n",
+ " c.c_custkey, c.c_name AS customer_name,\n",
+ " DATE_TRUNC('month', t.t_pickuptime) AS pickup_month,\n",
+ " ST_Area(ST_ConvexHull(ST_Collect(ST_GeomFromWKB(t.t_dropoffloc)))) AS
monthly_travel_hull_area,\n",
+ " COUNT(*) as dropoff_count\n",
+ "FROM trip t JOIN customer c 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 for meaningful
hulls\n",
+ "ORDER BY dropoff_count DESC, c.c_custkey ASC\n",
+ "\"\"\").show(3)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "8501daf5-5e4a-46d5-86c0-c3f6ea8673ee",
+ "metadata": {},
+ "source": [
+ "## Q6: Zone statistics for trips within a 50km radius of the Sedona city
center\n",
+ "\n",
+ "**Real-life scenario:** Analyze trip patterns in zones within a
metropolitan area around a specific city center.\n",
+ "\n",
+ "This query analyzes ride activity across all neighborhoods and zones
within a 50-kilometer area around Sedona, Arizona. It identifies which zones
had the most pickup activity by counting total trips that started in each
region. Also, it calculates the average trip cost and duration for rides
originating from each zone. The results are ranked by pickup volume, showing
which neighborhoods or areas generate the most ride demand and their typical
trip characteristics within the greater [...]
+ "\n",
+ "**Spatial query characteristics tested:**\n",
+ "\n",
+ "1. Polygon containment check (ST_Contains) with bounding box\n",
+ "2. Point-in-polygon spatial join (ST_Within)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 30,
+ "id": "f168c302-5187-45f9-8d87-427bbd68c942",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+
"┌───────────┬─────────────────┬───────────────┬───────────────────┬────────────────────────────────┐\n",
+ "│ z_zonekey ┆ z_name ┆ total_pickups ┆ avg_distance ┆
avg_duration │\n",
+ "│ int64 ┆ utf8view ┆ int64 ┆ decimal128(19, 9) ┆
duration(millisecond) │\n",
+
"╞═══════════╪═════════════════╪═══════════════╪═══════════════════╪════════════════════════════════╡\n",
+ "│ 30084 ┆ Coconino County ┆ 541 ┆ 0.000085323 ┆ 0
days 1 hours 45 mins 16.591… │\n",
+
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 30083 ┆ Yavapai County ┆ 292 ┆ 0.000076643 ┆ 0
days 1 hours 36 mins 43.647… │\n",
+
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 29488 ┆ Gila County ┆ 39 ┆ 0.000065641 ┆ 0
days 1 hours 16 mins 59.769… │\n",
+
"└───────────┴─────────────────┴───────────────┴───────────────────┴────────────────────────────────┘\n"
+ ]
+ }
+ ],
+ "source": [
+ "sd.sql(\"\"\"\n",
+ "SELECT\n",
+ " z.z_zonekey, z.z_name,\n",
+ " COUNT(t.t_tripkey) AS total_pickups, AVG(t.t_totalamount) AS
avg_distance,\n",
+ " AVG(t.t_dropofftime - t.t_pickuptime) AS avg_duration\n",
+ "FROM trip t, zone z\n",
+ "WHERE ST_Intersects(ST_GeomFromText('POLYGON((-112.2110 34.4197,
-111.3110 34.4197, -111.3110 35.3197, -112.2110 35.3197, -112.2110 34.4197))'),
ST_GeomFromWKB(z.z_boundary)) -- 50km bounding box around Sedona\n",
+ " AND ST_Within(ST_GeomFromWKB(t.t_pickuploc),
ST_GeomFromWKB(z.z_boundary))\n",
+ "GROUP BY z.z_zonekey, z.z_name\n",
+ "ORDER BY total_pickups DESC, z.z_zonekey ASC\n",
+ "\"\"\").show(3)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "fe58f65b-1403-4f91-81a3-e81784bfd3b4",
+ "metadata": {},
+ "source": [
+ "## Q7: Detect potential route detours by comparing reported vs. geometric
distances\n",
+ "\n",
+ "**Real-life scenario:** Identify suspicious trips where the reported
distance significantly exceeds the straight-line distance, potentially
indicating fare manipulation.\n",
+ "\n",
+ "This query analyzes how much taxi and rideshare trips deviate from the
most direct route by comparing the actual reported trip distance to the
straight-line distance between pickup and dropoff points. It calculates a
\"detour ratio\" that shows how much longer the actual route was compared to
flying in a straight line. For example, a ratio of 1.5 means the trip was 50%
longer than the direct path. The results are sorted to show the trips with the
highest detour ratios first, helpin [...]
+ "\n",
+ "**Spatial query characteristics tested:**\n",
+ "\n",
+ "1. Line geometry creation (ST_MakeLine)\n",
+ "2. Length calculation (ST_Length)\n",
+ "3. Coordinate system conversion and distance calculations\n",
+ "4. Ratio-based filtering on geometric vs. reported measurements"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 31,
+ "id": "3a8cddde-c04b-49d4-ab69-3d75d9a535ff",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+
"┌───────────┬─────────────────────┬────────────────────┬──────────────────────┐\n",
+ "│ t_tripkey ┆ reported_distance_m ┆ line_distance_m ┆
detour_ratio │\n",
+ "│ int64 ┆ decimal128(15, 5) ┆ float64 ┆ float64
│\n",
+
"╞═══════════╪═════════════════════╪════════════════════╪══════════════════════╡\n",
+ "│ 4688563 ┆ 0.00010 ┆ 11111.126052681648 ┆
8.99998789734414e-9 │\n",
+
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 2380123 ┆ 0.00010 ┆ 11111.126095065882 ┆
8.999987863013003e-9 │\n",
+
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 3077131 ┆ 0.00010 ┆ 11111.126138581423 ┆
8.99998782776551e-9 │\n",
+
"└───────────┴─────────────────────┴────────────────────┴──────────────────────┘\n"
+ ]
+ }
+ ],
+ "source": [
+ "sd.sql(\"\"\"\n",
+ "WITH trip_lengths AS (\n",
+ " SELECT\n",
+ " t.t_tripkey,\n",
+ " t.t_distance AS reported_distance_m,\n",
+ " ST_Length(\n",
+ " ST_MakeLine(\n",
+ " ST_GeomFromWKB(t.t_pickuploc),\n",
+ " ST_GeomFromWKB(t.t_dropoffloc)\n",
+ " )\n",
+ " ) / 0.000009 AS line_distance_m -- 1 meter = 0.000009 degree\n",
+ " FROM trip t\n",
+ ")\n",
+ "SELECT\n",
+ " t.t_tripkey,\n",
+ " t.reported_distance_m,\n",
+ " t.line_distance_m,\n",
+ " t.reported_distance_m / NULLIF(t.line_distance_m, 0) AS
detour_ratio\n",
+ "FROM trip_lengths t\n",
+ "ORDER BY detour_ratio DESC NULLS LAST, reported_distance_m DESC,
t_tripkey ASC\n",
+ "\"\"\").show(3)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "3040b853-100b-4c9d-8447-5b9bf4c4a895",
+ "metadata": {},
+ "source": [
+ "## Q8: Count nearby pickups for each building within a 500m radius\n",
+ "\n",
+ "**Real-life scenario:** Count how many trips start within 500 meters of
each building.\n",
+ "\n",
+ "This query identifies which buildings generate the most taxi and
rideshare pickup activity by counting trips that started within 500 meters of
each building. It analyzes the relationship between specific buildings (like
hotels, shopping centers, airports, or office buildings) and ride demand in
their immediate vicinity. The results are ranked to show which buildings are
the biggest trip generators, helping identify key pickup hotspots and
understand how different types of buildings [...]
+ "\n",
+ "**Spatial query characteristics tested:**\n",
+ "\n",
+ "1. Distance spatial join between points and polygons\n",
+ "2. Aggregation on spatial join result"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 32,
+ "id": "58ac6e6a-2e3d-4dd7-bb03-1b16f55fc186",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "┌───────────────┬──────────┬─────────────────────┐\n",
+ "│ b_buildingkey ┆ b_name ┆ nearby_pickup_count │\n",
+ "│ int64 ┆ utf8view ┆ int64 │\n",
+ "╞═══════════════╪══════════╪═════════════════════╡\n",
+ "│ 3779 ┆ linen ┆ 42 │\n",
+ "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 19135 ┆ misty ┆ 36 │\n",
+ "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 4416 ┆ sienna ┆ 26 │\n",
+ "└───────────────┴──────────┴─────────────────────┘\n"
+ ]
+ }
+ ],
+ "source": [
+ "sd.sql(\"\"\"\n",
+ "SELECT b.b_buildingkey, b.b_name, COUNT(*) AS nearby_pickup_count\n",
+ "FROM trip t\n",
+ "JOIN building b\n",
+ "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",
+ "\"\"\").show(3)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "eee8c5bc-1af6-484c-af91-b6c266ecb7c1",
+ "metadata": {},
+ "source": [
+ "## Q9: Building Conflation (duplicate/overlap detection via IoU)\n",
+ "\n",
+ "**Real-life scenario:** Detect duplicate or overlapping building
footprints in GIS datasets to identify data quality issues.\n",
+ "\n",
+ "This query identifies overlapping buildings by calculating how much their
footprints intersect with each other. For every pair of buildings that touch or
overlap, it measures the total area of each building and the area where they
overlap, then calculates an \"Intersection over Union\" (IoU) score that ranges
from 0 to 1. The results are sorted by IoU score to show the most significantly
overlapping building pairs first, which could help identify data quality
issues, adjacent struct [...]
+ "\n",
+ "**Spatial query characteristics tested:**\n",
+ "\n",
+ "1. Self-join with spatial intersection (ST_Intersects)\n",
+ "2. Area calculations (ST_Area)\n",
+ "3. Geometric intersection operations (ST_Intersection)\n",
+ "4. Complex geometric ratio calculations (IoU - Intersection over Union)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 34,
+ "id": "baae3710-6008-47cf-a31f-db2813f9fca3",
+ "metadata": {},
+ "outputs": [
+ {
+ "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"
+ ]
+ }
+ ],
+ "source": [
+ "sd.sql(\"\"\"\n",
+ "WITH b1 AS (\n",
+ " SELECT b_buildingkey AS id, ST_GeomFromWKB(b_boundary) AS geom\n",
+ " FROM building\n",
+ "),\n",
+ " b2 AS (\n",
+ " SELECT b_buildingkey AS id, ST_GeomFromWKB(b_boundary) AS geom\n",
+ " FROM building\n",
+ " ),\n",
+ " pairs AS (\n",
+ " SELECT\n",
+ " b1.id AS building_1,\n",
+ " b2.id AS building_2,\n",
+ " ST_Area(b1.geom) AS area1,\n",
+ " ST_Area(b2.geom) AS area2,\n",
+ " ST_Area(ST_Intersection(b1.geom, b2.geom)) AS overlap_area\n",
+ " FROM b1\n",
+ " JOIN b2\n",
+ " ON b1.id < b2.id\n",
+ " AND ST_Intersects(b1.geom, b2.geom)\n",
+ " )\n",
+ "SELECT\n",
+ " building_1,\n",
+ " building_2,\n",
+ " area1,\n",
+ " area2,\n",
+ " overlap_area,\n",
+ " CASE\n",
+ " WHEN overlap_area = 0 THEN 0.0\n",
+ " WHEN (area1 + area2 - overlap_area) = 0 THEN 1.0\n",
+ " ELSE overlap_area / (area1 + area2 - overlap_area)\n",
+ " END AS iou\n",
+ "FROM pairs\n",
+ "ORDER BY iou DESC, building_1 ASC, building_2 ASC\n",
+ "\"\"\").show(3)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "5ba7497e-cff3-47f6-9834-3beed648fd68",
+ "metadata": {},
+ "source": [
+ "## Q10: Zone statistics for trips starting within each zone\n",
+ "\n",
+ "**Real-life scenario:** Analyze trip patterns and performance metrics for
each administrative zone (like city districts or neighborhoods).\n",
+ "\n",
+ "This query analyzes trip patterns across all geographic zones by
calculating average trip duration, distance, and volume for rides originating
from each area. It uses a left join to include all zones in the results, even
those with no pickup activity, showing which neighborhoods generate longer
trips on average versus shorter local rides. The results are sorted by average
trip duration to identify zones where people tend to take longer journeys,
which could indicate more isolated ar [...]
+ "\n",
+ "**Spatial query characteristics tested:**\n",
+ "\n",
+ "1. Point-in-polygon spatial join (ST_Within)\n",
+ "2. Aggregation with multiple metrics (average duration, distance,
count)\n",
+ "3. LEFT JOIN to include zones with no trips"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 35,
+ "id": "bd264220-b2dd-49a1-a8f9-0267ffb1371c",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+
"┌───────────┬────────────────┬────────────────────────────────────┬───────────────────┬───────────┐\n",
+ "│ z_zonekey ┆ pickup_zone ┆ avg_duration ┆
avg_distance ┆ num_trips │\n",
+ "│ int64 ┆ utf8view ┆ duration(millisecond) ┆
decimal128(19, 9) ┆ int64 │\n",
+
"╞═══════════╪════════════════╪════════════════════════════════════╪═══════════════════╪═══════════╡\n",
+ "│ 31558 ┆ 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",
+
"├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ 59928 ┆ Ndhiwa ┆ 2 days 4 hours 19 mins 39.000 secs ┆
0.001040000 ┆ 1 │\n",
+
"└───────────┴────────────────┴────────────────────────────────────┴───────────────────┴───────────┘\n"
+ ]
+ }
+ ],
+ "source": [
+ "sd.sql(\"\"\"\n",
+ "SELECT\n",
+ " z.z_zonekey, z.z_name AS pickup_zone, AVG(t.t_dropofftime -
t.t_pickuptime) AS avg_duration,\n",
+ " AVG(t.t_distance) AS avg_distance, COUNT(t.t_tripkey) AS num_trips\n",
+ "FROM zone z LEFT JOIN trip t ON ST_Within(ST_GeomFromWKB(t.t_pickuploc),
ST_GeomFromWKB(z.z_boundary))\n",
+ "GROUP BY z.z_zonekey, z.z_name\n",
+ "ORDER BY avg_duration DESC NULLS LAST, z.z_zonekey ASC\n",
+ "\"\"\").show(3)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "cf6050f4-3968-4fd9-a701-1c43d81fa4f5",
+ "metadata": {},
+ "source": [
+ "## Q11: Count trips that cross between different zones\n",
+ "\n",
+ "**Real-life scenario:** Identify inter-district or inter-city trips to
understand cross-boundary travel patterns.\n",
+ "\n",
+ "This query counts how many trips crossed zone boundaries by starting in
one geographic zone and ending in a different zone. It identifies each trip's
pickup and dropoff zones, then filters to only include trips where the pickup
zone is different from the dropoff zone. The result shows the total number of
inter-zone trips, helping measure how much travel occurs between different
neighborhoods, districts, or areas rather than staying within the same local
zone.\n",
+ "\n",
+ "**Spatial query characteristics tested:**\n",
+ "\n",
+ "1. Multiple point-in-polygon spatial joins\n",
+ "2. Filtering based on spatial relationship results"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 36,
+ "id": "64bf9632-b4ca-4862-9443-41e90b82bee4",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "┌───────────────────────┐\n",
+ "│ cross_zone_trip_count │\n",
+ "│ int64 │\n",
+ "╞═══════════════════════╡\n",
+ "│ 176391 │\n",
+ "└───────────────────────┘\n"
+ ]
+ }
+ ],
+ "source": [
+ "sd.sql(\"\"\"\n",
+ "SELECT COUNT(*) AS cross_zone_trip_count\n",
+ "FROM\n",
+ " trip t\n",
+ " JOIN zone pickup_zone ON ST_Within(ST_GeomFromWKB(t.t_pickuploc),
ST_GeomFromWKB(pickup_zone.z_boundary))\n",
+ " JOIN zone dropoff_zone ON ST_Within(ST_GeomFromWKB(t.t_dropoffloc),
ST_GeomFromWKB(dropoff_zone.z_boundary))\n",
+ "WHERE pickup_zone.z_zonekey != dropoff_zone.z_zonekey\n",
+ "\"\"\").show(3)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "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",
+ "\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 [...]
+ "\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"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "0878af06-c518-44f3-9b45-83651cc37d04",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "sd.sql(\"\"\"\n",
+ "WITH trip_with_geom AS (\n",
+ " SELECT t_tripkey, t_pickuploc, ST_GeomFromWKB(t_pickuploc) as
pickup_geom\n",
+ " FROM trip\n",
+ "),\n",
+ "building_with_geom AS (\n",
+ " SELECT b_buildingkey, b_name, b_boundary, ST_GeomFromWKB(b_boundary)
as boundary_geom\n",
+ " FROM building\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 JOIN building_with_geom b\n",
+ "ON ST_KNN(t.pickup_geom, b.boundary_geom, 5, FALSE)\n",
+ "ORDER BY distance_to_building ASC, b.b_buildingkey ASC\n",
+ "\"\"\").show(3)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "id": "6bdfeffb-5015-4f34-ae73-a8e59af7d4f4",
+ "metadata": {},
+ "outputs": [],
+ "source": []
+ }
+ ],
+ "metadata": {
+ "kernelspec": {
+ "display_name": "Python 3 (ipykernel)",
+ "language": "python",
+ "name": "python3"
+ },
+ "language_info": {
+ "codemirror_mode": {
+ "name": "ipython",
+ "version": 3
+ },
+ "file_extension": ".py",
+ "mimetype": "text/x-python",
+ "name": "python",
+ "nbconvert_exporter": "python",
+ "pygments_lexer": "ipython3",
+ "version": "3.12.4"
+ }
+ },
+ "nbformat": 4,
+ "nbformat_minor": 5
+}
diff --git a/docs/requirements.txt b/docs/requirements.txt
index 500f80c..13a8918 100644
--- a/docs/requirements.txt
+++ b/docs/requirements.txt
@@ -6,4 +6,9 @@ mkdocs-material
mkdocstrings[python]
nbconvert
pyproj
-ruff
\ No newline at end of file
+ruff
+mkdocs-jupyter
+jupyter
+notebook
+jupyterlab
+apache-sedona[db]
\ No newline at end of file
diff --git a/mkdocs.yml b/mkdocs.yml
index ecf3270..d2bd5ef 100644
--- a/mkdocs.yml
+++ b/mkdocs.yml
@@ -22,6 +22,7 @@ nav:
- SpatialBench: index.md
- Overview and Methodology: overview-methodology.md
- Datasets and Generators: datasets-generators.md
+ - Queries: queries.ipynb
- Blog: "https://sedona.apache.org/latest/blog/";
- Community: "https://sedona.apache.org/latest/community/contact/";
- Apache Software Foundation: "https://sedona.apache.org/latest/asf/asf/";
@@ -105,6 +106,7 @@ markdown_extensions:
custom_checkbox: true
- pymdownx.tilde
plugins:
+ - mkdocs-jupyter
- search
- macros
- git-revision-date-localized:
@@ -145,4 +147,4 @@ plugins:
show_symbol_type_toc: true
signature_crossrefs: true
extra:
- paths: ['python']
\ No newline at end of file
+ paths: ['python']
diff --git a/requirements-docs.txt b/requirements-docs.txt
deleted file mode 100644
index 20fa4a8..0000000
--- a/requirements-docs.txt
+++ /dev/null
@@ -1,8 +0,0 @@
-mkdocs
-mkdocs-material
-mkdocs-macros-plugin
-mkdocs-glightbox
-mkdocs-git-revision-date-localized-plugin
-mkdocstrings[python]
-mike
-mkdocs-jupyter
