This is an automated email from the ASF dual-hosted git repository.
github-bot pushed a commit to branch asf-site
in repository https://gitbox.apache.org/repos/asf/sedona-spatialbench.git
The following commit(s) were added to refs/heads/asf-site by this push:
new e559aa1 update documentation for main branch
e559aa1 is described below
commit e559aa1221d532c8f84869069b998b2e6ebe86b2
Author: GitHub Actions <[email protected]>
AuthorDate: Mon Sep 22 21:44:24 2025 +0000
update documentation for main branch
---
404.html | 23 +
datasets-generators/index.html | 43 +-
index.html | 25 +-
overview-methodology/index.html | 25 +-
queries/index.html | 2689 +++++++++++++++++++++++++++++++++++++++
requirements.txt | 7 +-
search/search_index.json | 2 +-
7 files changed, 2809 insertions(+), 5 deletions(-)
diff --git a/404.html b/404.html
index 4502e58..a861afc 100644
--- a/404.html
+++ b/404.html
@@ -440,6 +440,29 @@
+
+
+
+
+
+
+ <li class="md-nav__item">
+ <a href="/queries/" class="md-nav__link">
+
+
+
+ <span class="md-ellipsis">
+ SpatialBench Queries
+
+ </span>
+
+
+ </a>
+ </li>
+
+
+
+
</ul>
</nav>
diff --git a/datasets-generators/index.html b/datasets-generators/index.html
index 67da774..2e65cea 100644
--- a/datasets-generators/index.html
+++ b/datasets-generators/index.html
@@ -11,6 +11,8 @@
<link rel="prev" href="../overview-methodology/">
+ <link rel="next" href="../queries/">
+
<link rel="icon" href="../image/sedona_logo_symbol.png">
<meta name="generator" content="mkdocs-1.6.1, mkdocs-material-9.6.20">
@@ -555,6 +557,29 @@
+
+
+
+
+
+
+ <li class="md-nav__item">
+ <a href="../queries/" class="md-nav__link">
+
+
+
+ <span class="md-ellipsis">
+ SpatialBench Queries
+
+ </span>
+
+
+ </a>
+ </li>
+
+
+
+
</ul>
</nav>
@@ -970,7 +995,7 @@
<span class="md-icon" title="Last update">
<svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 24 24"><path d="M21
13.1c-.1 0-.3.1-.4.2l-1 1 2.1 2.1 1-1c.2-.2.2-.6
0-.8l-1.3-1.3c-.1-.1-.2-.2-.4-.2m-1.9 1.8-6.1 6V23h2.1l6.1-6.1zM12.5 7v5.2l4
2.4-1 1L11 13V7zM11 21.9c-5.1-.5-9-4.8-9-9.9C2 6.5 6.5 2 12 2c5.3 0 9.6 4.1 10
9.3-.3-.1-.6-.2-1-.2s-.7.1-1 .2C19.6 7.2 16.2 4 12 4c-4.4 0-8 3.6-8 8 0 4.1 3.1
7.5 7.1 7.9l-.1.2z"></path></svg>
</span>
- <span class="git-revision-date-localized-plugin
git-revision-date-localized-plugin-datetime" title="September 22, 2025 18:34:03
UTC">September 22, 2025 18:34:03</span>
+ <span class="git-revision-date-localized-plugin
git-revision-date-localized-plugin-datetime" title="September 22, 2025 21:43:24
UTC">September 22, 2025 21:43:24</span>
</span>
@@ -1022,6 +1047,22 @@
</a>
+
+ <a href="../queries/" class="md-footer__link md-footer__link--next"
aria-label="Next: SpatialBench Queries">
+ <div class="md-footer__title">
+ <span class="md-footer__direction">
+ Next
+ </span>
+ <div class="md-ellipsis">
+ SpatialBench Queries
+ </div>
+ </div>
+ <div class="md-footer__button md-icon">
+
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 24
24"><path d="M4 11v2h12l-5.5 5.5 1.42 1.42L19.84 12l-7.92-7.92L10.5 5.5 16
11z"></path></svg>
+ </div>
+ </a>
+
</nav>
diff --git a/index.html b/index.html
index ae57398..a1d5798 100644
--- a/index.html
+++ b/index.html
@@ -545,6 +545,29 @@
+
+
+
+
+
+
+ <li class="md-nav__item">
+ <a href="queries/" class="md-nav__link">
+
+
+
+ <span class="md-ellipsis">
+ SpatialBench Queries
+
+ </span>
+
+
+ </a>
+ </li>
+
+
+
+
</ul>
</nav>
@@ -817,7 +840,7 @@
<span class="md-icon" title="Last update">
<svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 24 24"><path d="M21
13.1c-.1 0-.3.1-.4.2l-1 1 2.1 2.1 1-1c.2-.2.2-.6
0-.8l-1.3-1.3c-.1-.1-.2-.2-.4-.2m-1.9 1.8-6.1 6V23h2.1l6.1-6.1zM12.5 7v5.2l4
2.4-1 1L11 13V7zM11 21.9c-5.1-.5-9-4.8-9-9.9C2 6.5 6.5 2 12 2c5.3 0 9.6 4.1 10
9.3-.3-.1-.6-.2-1-.2s-.7.1-1 .2C19.6 7.2 16.2 4 12 4c-4.4 0-8 3.6-8 8 0 4.1 3.1
7.5 7.1 7.9l-.1.2z"></path></svg>
</span>
- <span class="git-revision-date-localized-plugin
git-revision-date-localized-plugin-datetime" title="September 22, 2025 18:34:03
UTC">September 22, 2025 18:34:03</span>
+ <span class="git-revision-date-localized-plugin
git-revision-date-localized-plugin-datetime" title="September 22, 2025 21:43:24
UTC">September 22, 2025 21:43:24</span>
</span>
diff --git a/overview-methodology/index.html b/overview-methodology/index.html
index e70838c..d4136bd 100644
--- a/overview-methodology/index.html
+++ b/overview-methodology/index.html
@@ -569,6 +569,29 @@
+
+
+
+
+
+
+ <li class="md-nav__item">
+ <a href="../queries/" class="md-nav__link">
+
+
+
+ <span class="md-ellipsis">
+ SpatialBench Queries
+
+ </span>
+
+
+ </a>
+ </li>
+
+
+
+
</ul>
</nav>
@@ -862,7 +885,7 @@
<span class="md-icon" title="Last update">
<svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 24 24"><path d="M21
13.1c-.1 0-.3.1-.4.2l-1 1 2.1 2.1 1-1c.2-.2.2-.6
0-.8l-1.3-1.3c-.1-.1-.2-.2-.4-.2m-1.9 1.8-6.1 6V23h2.1l6.1-6.1zM12.5 7v5.2l4
2.4-1 1L11 13V7zM11 21.9c-5.1-.5-9-4.8-9-9.9C2 6.5 6.5 2 12 2c5.3 0 9.6 4.1 10
9.3-.3-.1-.6-.2-1-.2s-.7.1-1 .2C19.6 7.2 16.2 4 12 4c-4.4 0-8 3.6-8 8 0 4.1 3.1
7.5 7.1 7.9l-.1.2z"></path></svg>
</span>
- <span class="git-revision-date-localized-plugin
git-revision-date-localized-plugin-datetime" title="September 22, 2025 18:34:03
UTC">September 22, 2025 18:34:03</span>
+ <span class="git-revision-date-localized-plugin
git-revision-date-localized-plugin-datetime" title="September 22, 2025 21:43:24
UTC">September 22, 2025 21:43:24</span>
</span>
diff --git a/queries/index.html b/queries/index.html
new file mode 100644
index 0000000..88e6879
--- /dev/null
+++ b/queries/index.html
@@ -0,0 +1,2689 @@
+<!DOCTYPE html><html lang="en" class="no-js"><head>
+
+ <meta charset="utf-8">
+ <meta name="viewport" content="width=device-width,initial-scale=1">
+
+ <meta name="description" content="SpatialBench is a benchmark for
assessing geospatial SQL analytics query performance across database systems">
+
+
+
+
+ <link rel="prev" href="../datasets-generators/">
+
+
+
+ <link rel="icon" href="../image/sedona_logo_symbol.png">
+ <meta name="generator" content="mkdocs-1.6.1, mkdocs-material-9.6.20">
+
+
+
+ <title>SpatialBench Queries - SpatialBench</title>
+
+
+
+ <link rel="stylesheet"
href="../assets/stylesheets/main.e53b48f4.min.css">
+
+
+ <link rel="stylesheet"
href="../assets/stylesheets/palette.06af60db.min.css">
+
+
+
+
+
+
+
+
+
+
+
+
+ <link rel="preconnect" href="https://fonts.gstatic.com"; crossorigin>
+ <link rel="stylesheet"
href="https://fonts.googleapis.com/css?family=Roboto:300,300i,400,400i,700,700i%7CRoboto+Mono:400,400i,700,700i&display=fallback";>
+ <style>:root{--md-text-font:"Roboto";--md-code-font:"Roboto
Mono"}</style>
+
+
+
+ <link rel="stylesheet" href="../assets/_mkdocstrings.css">
+
+ <link rel="stylesheet" href="../stylesheets/extra.css">
+
+ <script>__md_scope=new
URL("..",location),__md_hash=e=>[...e].reduce(((e,_)=>(e<<5)-e+_.charCodeAt(0)),0),__md_get=(e,_=localStorage,t=__md_scope)=>JSON.parse(_.getItem(t.pathname+"."+e)),__md_set=(e,_,t=localStorage,a=__md_scope)=>{try{t.setItem(a.pathname+"."+e,JSON.stringify(_))}catch(e){}}</script>
+
+
+
+
+
+
+ <link href="../assets/stylesheets/glightbox.min.css"
rel="stylesheet"><script
src="../assets/javascripts/glightbox.min.js"></script><style
id="glightbox-style">
+ html.glightbox-open { overflow: initial; height: 100%; }
+ .gslide-title { margin-top: 0px; user-select: text; }
+ .gslide-desc { color: #666; user-select: text; }
+ .gslide-image img { background: white; }
+ .glightbox-clean .gslide-media { -webkit-box-shadow: none;
box-shadow: none; }
+ .gscrollbar-fixer { padding-right: 15px; }
+ .gdesc-inner { font-size: 0.75rem; }
+ body[data-md-color-scheme="slate"] .gdesc-inner { background:
var(--md-default-bg-color); }
+ body[data-md-color-scheme="slate"] .gslide-title { color:
var(--md-default-fg-color); }
+ body[data-md-color-scheme="slate"] .gslide-desc { color:
var(--md-default-fg-color); }
+ </style></head>
+
+
+
+
+
+
+
+ <body dir="ltr" data-md-color-scheme="default"
data-md-color-primary="custom" data-md-color-accent="green">
+
+
+ <input class="md-toggle" data-md-toggle="drawer" type="checkbox"
id="__drawer" autocomplete="off">
+ <input class="md-toggle" data-md-toggle="search" type="checkbox"
id="__search" autocomplete="off">
+ <label class="md-overlay" for="__drawer"></label>
+ <div data-md-component="skip">
+
+
+ <a href="#spatialbench-queries" class="md-skip">
+ Skip to content
+ </a>
+
+ </div>
+ <div data-md-component="announce">
+
+ </div>
+
+
+
+
+
+
+<header class="md-header md-header--shadow md-header--lifted"
data-md-component="header">
+ <nav class="md-header__inner md-grid" aria-label="Header">
+ <a href=".." title="SpatialBench" class="md-header__button md-logo"
aria-label="SpatialBench" data-md-component="logo">
+
+ <img src="../image/sedona_logo_symbol.svg" alt="logo">
+
+ </a>
+ <label class="md-header__button md-icon" for="__drawer">
+
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 24 24"><path d="M3
6h18v2H3zm0 5h18v2H3zm0 5h18v2H3z"></path></svg>
+ </label>
+ <div class="md-header__title" data-md-component="header-title">
+ <div class="md-header__ellipsis">
+ <div class="md-header__topic">
+ <span class="md-ellipsis">
+ SpatialBench
+ </span>
+ </div>
+ <div class="md-header__topic" data-md-component="header-topic">
+ <span class="md-ellipsis">
+
+ SpatialBench Queries
+
+ </span>
+ </div>
+ </div>
+ </div>
+
+
+
+
+
+
+
+
+ <label class="md-header__button md-icon" for="__search">
+
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 24 24"><path
d="M9.5 3A6.5 6.5 0 0 1 16 9.5c0 1.61-.59 3.09-1.56 4.23l.27.27h.79l5 5-1.5
1.5-5-5v-.79l-.27-.27A6.52 6.52 0 0 1 9.5 16 6.5 6.5 0 0 1 3 9.5 6.5 6.5 0 0 1
9.5 3m0 2C7 5 5 7 5 9.5S7 14 9.5 14 14 12 14 9.5 12 5 9.5 5"></path></svg>
+ </label>
+ <div class="md-search" data-md-component="search" role="dialog">
+ <label class="md-search__overlay" for="__search"></label>
+ <div class="md-search__inner" role="search">
+ <form class="md-search__form" name="search">
+ <input type="text" class="md-search__input" name="query"
aria-label="Search" placeholder="Search" autocapitalize="off" autocorrect="off"
autocomplete="off" spellcheck="false" data-md-component="search-query" required>
+ <label class="md-search__icon md-icon" for="__search">
+
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 24 24"><path
d="M9.5 3A6.5 6.5 0 0 1 16 9.5c0 1.61-.59 3.09-1.56 4.23l.27.27h.79l5 5-1.5
1.5-5-5v-.79l-.27-.27A6.52 6.52 0 0 1 9.5 16 6.5 6.5 0 0 1 3 9.5 6.5 6.5 0 0 1
9.5 3m0 2C7 5 5 7 5 9.5S7 14 9.5 14 14 12 14 9.5 12 5 9.5 5"></path></svg>
+
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 24 24"><path
d="M20 11v2H8l5.5 5.5-1.42 1.42L4.16 12l7.92-7.92L13.5 5.5 8 11z"></path></svg>
+ </label>
+ <nav class="md-search__options" aria-label="Search">
+
+ <a href="javascript:void(0)" class="md-search__icon md-icon"
title="Share" aria-label="Share" data-clipboard data-clipboard-text=""
data-md-component="search-share" tabindex="-1">
+
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 24 24"><path
d="M18 16.08c-.76 0-1.44.3-1.96.77L8.91
12.7c.05-.23.09-.46.09-.7s-.04-.47-.09-.7l7.05-4.11c.54.5 1.25.81 2.04.81a3 3 0
0 0 3-3 3 3 0 0 0-3-3 3 3 0 0 0-3 3c0 .24.04.47.09.7L8.04 9.81C7.5 9.31 6.79 9
6 9a3 3 0 0 0-3 3 3 3 0 0 0 3 3c.79 0 1.5-.31 2.04-.81l7.12
4.15c-.05.21-.08.43-.08.66 0 1.61 1.31 2.91 2.92 2.91s2.92-1.3 2.92-2.91A2.92
2.92 0 0 0 18 16.08"></path></svg>
+ </a>
+
+ <button type="reset" class="md-search__icon md-icon" title="Clear"
aria-label="Clear" tabindex="-1">
+
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 24 24"><path
d="M19 6.41 17.59 5 12 10.59 6.41 5 5 6.41 10.59 12 5 17.59 6.41 19 12 13.41
17.59 19 19 17.59 13.41 12z"></path></svg>
+ </button>
+ </nav>
+
+ <div class="md-search__suggest"
data-md-component="search-suggest"></div>
+
+ </form>
+ <div class="md-search__output">
+ <div class="md-search__scrollwrap" tabindex="0" data-md-scrollfix>
+ <div class="md-search-result" data-md-component="search-result">
+ <div class="md-search-result__meta">
+ Initializing search
+ </div>
+ <ol class="md-search-result__list" role="presentation"></ol>
+ </div>
+ </div>
+ </div>
+ </div>
+</div>
+
+
+
+ <div class="md-header__source">
+ <a href="https://github.com/apache/sedona-spatialbench/"; title="Go to
repository" class="md-source" data-md-component="source">
+ <div class="md-source__icon md-icon">
+
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 512 512"><!--! Font
Awesome Free 7.0.0 by @fontawesome - https://fontawesome.com License -
https://fontawesome.com/license/free (Icons: CC BY 4.0, Fonts: SIL OFL 1.1,
Code: MIT License) Copyright 2025 Fonticons, Inc.--><path d="M173.9 397.4c0
2-2.3 3.6-5.2 3.6-3.3.3-5.6-1.3-5.6-3.6 0-2 2.3-3.6 5.2-3.6 3-.3 5.6 1.3 5.6
3.6m-31.1-4.5c-.7 2 1.3 4.3 4.3 4.9 2.6 1 5.6 0
6.2-2s-1.3-4.3-4.3-5.2c-2.6-.7-5.5.3-6.2 2.3m44.2-1.7c-2.9.7-4.9 2. [...]
+ </div>
+ <div class="md-source__repository">
+ apache/sedona-spatialbench
+ </div>
+</a>
+ </div>
+
+ </nav>
+
+
+
+<nav class="md-tabs" aria-label="Tabs" data-md-component="tabs">
+ <div class="md-grid">
+ <ul class="md-tabs__list">
+
+
+
+
+
+
+
+
+
+
+ <li class="md-tabs__item md-tabs__item--active">
+ <a href=".." class="md-tabs__link">
+
+
+
+ Home
+
+ </a>
+ </li>
+
+
+
+
+
+
+
+
+
+ <li class="md-tabs__item">
+ <a href="https://sedona.apache.org/latest/blog/"; class="md-tabs__link">
+
+
+
+
+
+ Blog
+
+ </a>
+ </li>
+
+
+
+
+
+
+
+
+ <li class="md-tabs__item">
+ <a href="https://sedona.apache.org/latest/community/contact/";
class="md-tabs__link">
+
+
+
+
+
+ Community
+
+ </a>
+ </li>
+
+
+
+
+
+
+
+
+ <li class="md-tabs__item">
+ <a href="https://sedona.apache.org/latest/asf/asf/";
class="md-tabs__link">
+
+
+
+
+
+ Apache Software Foundation
+
+ </a>
+ </li>
+
+
+
+
+
+
+
+
+ <li class="md-tabs__item">
+ <a href="https://sedona.apache.org/latest/"; class="md-tabs__link">
+
+
+
+
+
+ Sedona Homepage
+
+ </a>
+ </li>
+
+
+
+ </ul>
+ </div>
+</nav>
+
+
+</header>
+
+ <div class="md-container" data-md-component="container">
+
+
+
+
+ <main class="md-main" data-md-component="main">
+ <div class="md-main__inner md-grid">
+
+
+
+ <div class="md-sidebar md-sidebar--primary"
data-md-component="sidebar" data-md-type="navigation">
+ <div class="md-sidebar__scrollwrap">
+ <div class="md-sidebar__inner">
+
+
+
+
+
+
+<nav class="md-nav md-nav--primary md-nav--lifted" aria-label="Navigation"
data-md-level="0">
+ <label class="md-nav__title" for="__drawer">
+ <a href=".." title="SpatialBench" class="md-nav__button md-logo"
aria-label="SpatialBench" data-md-component="logo">
+
+ <img src="../image/sedona_logo_symbol.svg" alt="logo">
+
+ </a>
+ SpatialBench
+ </label>
+
+ <div class="md-nav__source">
+ <a href="https://github.com/apache/sedona-spatialbench/"; title="Go to
repository" class="md-source" data-md-component="source">
+ <div class="md-source__icon md-icon">
+
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 512 512"><!--! Font
Awesome Free 7.0.0 by @fontawesome - https://fontawesome.com License -
https://fontawesome.com/license/free (Icons: CC BY 4.0, Fonts: SIL OFL 1.1,
Code: MIT License) Copyright 2025 Fonticons, Inc.--><path d="M173.9 397.4c0
2-2.3 3.6-5.2 3.6-3.3.3-5.6-1.3-5.6-3.6 0-2 2.3-3.6 5.2-3.6 3-.3 5.6 1.3 5.6
3.6m-31.1-4.5c-.7 2 1.3 4.3 4.3 4.9 2.6 1 5.6 0
6.2-2s-1.3-4.3-4.3-5.2c-2.6-.7-5.5.3-6.2 2.3m44.2-1.7c-2.9.7-4.9 2. [...]
+ </div>
+ <div class="md-source__repository">
+ apache/sedona-spatialbench
+ </div>
+</a>
+ </div>
+
+ <ul class="md-nav__list" data-md-scrollfix>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class="md-nav__item md-nav__item--active md-nav__item--section
md-nav__item--nested">
+
+
+
+ <input class="md-nav__toggle md-toggle " type="checkbox" id="__nav_1"
checked>
+
+
+ <label class="md-nav__link" for="__nav_1" id="__nav_1_label"
tabindex="">
+
+
+
+ <span class="md-ellipsis">
+ Home
+
+ </span>
+
+
+ <span class="md-nav__icon md-icon"></span>
+ </label>
+
+ <nav class="md-nav" data-md-level="1" aria-labelledby="__nav_1_label"
aria-expanded="true">
+ <label class="md-nav__title" for="__nav_1">
+ <span class="md-nav__icon md-icon"></span>
+ Home
+ </label>
+ <ul class="md-nav__list" data-md-scrollfix>
+
+
+
+
+
+
+
+ <li class="md-nav__item">
+ <a href=".." class="md-nav__link">
+
+
+
+ <span class="md-ellipsis">
+ SpatialBench
+
+ </span>
+
+
+ </a>
+ </li>
+
+
+
+
+
+
+
+
+
+
+ <li class="md-nav__item">
+ <a href="../overview-methodology/" class="md-nav__link">
+
+
+
+ <span class="md-ellipsis">
+ Overview and Methodology
+
+ </span>
+
+
+ </a>
+ </li>
+
+
+
+
+
+
+
+
+
+
+ <li class="md-nav__item">
+ <a href="../datasets-generators/" class="md-nav__link">
+
+
+
+ <span class="md-ellipsis">
+ Datasets and Generators
+
+ </span>
+
+
+ </a>
+ </li>
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class="md-nav__item md-nav__item--active">
+
+ <input class="md-nav__toggle md-toggle" type="checkbox" id="__toc">
+
+
+
+
+
+ <label class="md-nav__link md-nav__link--active" for="__toc">
+
+
+
+ <span class="md-ellipsis">
+ SpatialBench Queries
+
+ </span>
+
+
+ <span class="md-nav__icon md-icon"></span>
+ </label>
+
+ <a href="./" class="md-nav__link md-nav__link--active">
+
+
+
+ <span class="md-ellipsis">
+ SpatialBench Queries
+
+ </span>
+
+
+ </a>
+
+
+
+<nav class="md-nav md-nav--secondary" aria-label="Table of contents">
+
+
+
+
+
+
+ <label class="md-nav__title" for="__toc">
+ <span class="md-nav__icon md-icon"></span>
+ Table of contents
+ </label>
+ <ul class="md-nav__list" data-md-component="toc" data-md-scrollfix>
+
+ <li class="md-nav__item">
+ <a
href="#q1-find-trips-starting-within-50km-of-sedona-city-center-ordered-by-distance"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q1: Find trips starting within 50km of Sedona city center, ordered by
distance
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a href="#q2-count-trips-starting-within-coconino-county-arizona-zone"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q2: Count trips starting within Coconino County (Arizona) zone
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a
href="#q3-monthly-trip-statistics-within-a-15km-radius-of-the-sedona-city-center"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q3: Monthly trip statistics within a 15km radius of the Sedona city
center
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a href="#q4-zone-distribution-of-top-1000-trips-by-tip-amount"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q4: Zone distribution of top 1000 trips by tip amount
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a
href="#q5-monthly-travel-patterns-for-repeat-customers-convex-hull-of-dropoff-locations"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q5: Monthly travel patterns for repeat customers (convex hull of dropoff
locations)
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a
href="#q6-zone-statistics-for-trips-within-a-50km-radius-of-the-sedona-city-center"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q6: Zone statistics for trips within a 50km radius of the Sedona city
center
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a
href="#q7-detect-potential-route-detours-by-comparing-reported-vs-geometric-distances"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q7: Detect potential route detours by comparing reported vs. geometric
distances
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a href="#q8-count-nearby-pickups-for-each-building-within-a-500m-radius"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q8: Count nearby pickups for each building within a 500m radius
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a href="#q9-building-conflation-duplicateoverlap-detection-via-iou"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q9: Building Conflation (duplicate/overlap detection via IoU)
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a href="#q10-zone-statistics-for-trips-starting-within-each-zone"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q10: Zone statistics for trips starting within each zone
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a href="#q11-count-trips-that-cross-between-different-zones"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q11: Count trips that cross between different zones
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a
href="#q12-find-five-nearest-buildings-to-each-trip-pickup-location-using-knn-join"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q12: Find five nearest buildings to each trip pickup location using KNN
join
+ </span>
+ </a>
+
+</li>
+
+ </ul>
+
+</nav>
+
+ </li>
+
+
+
+
+ </ul>
+ </nav>
+
+ </li>
+
+
+
+
+
+
+
+
+
+ <li class="md-nav__item">
+ <a href="https://sedona.apache.org/latest/blog/"; class="md-nav__link">
+
+
+
+ <span class="md-ellipsis">
+ Blog
+
+ </span>
+
+
+ </a>
+ </li>
+
+
+
+
+
+
+
+
+
+ <li class="md-nav__item">
+ <a href="https://sedona.apache.org/latest/community/contact/";
class="md-nav__link">
+
+
+
+ <span class="md-ellipsis">
+ Community
+
+ </span>
+
+
+ </a>
+ </li>
+
+
+
+
+
+
+
+
+
+ <li class="md-nav__item">
+ <a href="https://sedona.apache.org/latest/asf/asf/"; class="md-nav__link">
+
+
+
+ <span class="md-ellipsis">
+ Apache Software Foundation
+
+ </span>
+
+
+ </a>
+ </li>
+
+
+
+
+
+
+
+
+
+ <li class="md-nav__item">
+ <a href="https://sedona.apache.org/latest/"; class="md-nav__link">
+
+
+
+ <span class="md-ellipsis">
+ Sedona Homepage
+
+ </span>
+
+
+ </a>
+ </li>
+
+
+
+ </ul>
+</nav>
+ </div>
+ </div>
+ </div>
+
+
+
+ <div class="md-sidebar md-sidebar--secondary"
data-md-component="sidebar" data-md-type="toc">
+ <div class="md-sidebar__scrollwrap">
+ <div class="md-sidebar__inner">
+
+
+<nav class="md-nav md-nav--secondary" aria-label="Table of contents">
+
+
+
+
+
+
+ <label class="md-nav__title" for="__toc">
+ <span class="md-nav__icon md-icon"></span>
+ Table of contents
+ </label>
+ <ul class="md-nav__list" data-md-component="toc" data-md-scrollfix>
+
+ <li class="md-nav__item">
+ <a
href="#q1-find-trips-starting-within-50km-of-sedona-city-center-ordered-by-distance"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q1: Find trips starting within 50km of Sedona city center, ordered by
distance
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a href="#q2-count-trips-starting-within-coconino-county-arizona-zone"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q2: Count trips starting within Coconino County (Arizona) zone
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a
href="#q3-monthly-trip-statistics-within-a-15km-radius-of-the-sedona-city-center"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q3: Monthly trip statistics within a 15km radius of the Sedona city
center
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a href="#q4-zone-distribution-of-top-1000-trips-by-tip-amount"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q4: Zone distribution of top 1000 trips by tip amount
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a
href="#q5-monthly-travel-patterns-for-repeat-customers-convex-hull-of-dropoff-locations"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q5: Monthly travel patterns for repeat customers (convex hull of dropoff
locations)
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a
href="#q6-zone-statistics-for-trips-within-a-50km-radius-of-the-sedona-city-center"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q6: Zone statistics for trips within a 50km radius of the Sedona city
center
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a
href="#q7-detect-potential-route-detours-by-comparing-reported-vs-geometric-distances"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q7: Detect potential route detours by comparing reported vs. geometric
distances
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a href="#q8-count-nearby-pickups-for-each-building-within-a-500m-radius"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q8: Count nearby pickups for each building within a 500m radius
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a href="#q9-building-conflation-duplicateoverlap-detection-via-iou"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q9: Building Conflation (duplicate/overlap detection via IoU)
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a href="#q10-zone-statistics-for-trips-starting-within-each-zone"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q10: Zone statistics for trips starting within each zone
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a href="#q11-count-trips-that-cross-between-different-zones"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q11: Count trips that cross between different zones
+ </span>
+ </a>
+
+</li>
+
+ <li class="md-nav__item">
+ <a
href="#q12-find-five-nearest-buildings-to-each-trip-pickup-location-using-knn-join"
class="md-nav__link">
+ <span class="md-ellipsis">
+ Q12: Find five nearest buildings to each trip pickup location using KNN
join
+ </span>
+ </a>
+
+</li>
+
+ </ul>
+
+</nav>
+ </div>
+ </div>
+ </div>
+
+
+
+ <div class="md-content" data-md-component="content">
+ <article class="md-content__inner md-typeset">
+
+
+
+
+
+ <a
href="https://github.com/apache/sedona-spatialbench/blob/main/docs/queries.ipynb";
title="Edit this page" class="md-content__button md-icon" rel="edit">
+
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 24 24"><path d="M10
20H6V4h7v5h5v3.1l2-2V8l-6-6H6c-1.1 0-2 .9-2 2v16c0 1.1.9 2 2 2h4zm10.2-7c.1 0
.3.1.4.2l1.3 1.3c.2.2.2.6 0 .8l-1 1-2.1-2.1 1-1c.1-.1.2-.2.4-.2m0 3.9L14.1
23H12v-2.1l6.1-6.1z"></path></svg>
+ </a>
+
+
+
+
+<script>
+(function (global, factory) {
+ typeof exports === 'object' && typeof module !== 'undefined' ?
module.exports = factory() :
+ typeof define === 'function' && define.amd ? define(factory) :
+ (global = global || self, global.ClipboardCopyElement = factory());
+ }(this, function () { 'use strict';
+
+ function createNode(text) {
+ const node = document.createElement('pre');
+ node.style.width = '1px';
+ node.style.height = '1px';
+ node.style.position = 'fixed';
+ node.style.top = '5px';
+ node.textContent = text;
+ return node;
+ }
+
+ function copyNode(node) {
+ if ('clipboard' in navigator) {
+ // eslint-disable-next-line flowtype/no-flow-fix-me-comments
+ // $FlowFixMe Clipboard is not defined in Flow yet.
+ return navigator.clipboard.writeText(node.textContent);
+ }
+
+ const selection = getSelection();
+
+ if (selection == null) {
+ return Promise.reject(new Error());
+ }
+
+ selection.removeAllRanges();
+ const range = document.createRange();
+ range.selectNodeContents(node);
+ selection.addRange(range);
+ document.execCommand('copy');
+ selection.removeAllRanges();
+ return Promise.resolve();
+ }
+ function copyText(text) {
+ if ('clipboard' in navigator) {
+ // eslint-disable-next-line flowtype/no-flow-fix-me-comments
+ // $FlowFixMe Clipboard is not defined in Flow yet.
+ return navigator.clipboard.writeText(text);
+ }
+
+ const body = document.body;
+
+ if (!body) {
+ return Promise.reject(new Error());
+ }
+
+ const node = createNode(text);
+ body.appendChild(node);
+ copyNode(node);
+ body.removeChild(node);
+ return Promise.resolve();
+ }
+
+ function copy(button) {
+ const id = button.getAttribute('for');
+ const text = button.getAttribute('value');
+
+ function trigger() {
+ button.dispatchEvent(new CustomEvent('clipboard-copy', {
+ bubbles: true
+ }));
+ }
+
+ if (text) {
+ copyText(text).then(trigger);
+ } else if (id) {
+ const root = 'getRootNode' in Element.prototype ? button.getRootNode()
: button.ownerDocument;
+ if (!(root instanceof Document || 'ShadowRoot' in window && root
instanceof ShadowRoot)) return;
+ const node = root.getElementById(id);
+ if (node) copyTarget(node).then(trigger);
+ }
+ }
+
+ function copyTarget(content) {
+ if (content instanceof HTMLInputElement || content instanceof
HTMLTextAreaElement) {
+ return copyText(content.value);
+ } else if (content instanceof HTMLAnchorElement &&
content.hasAttribute('href')) {
+ return copyText(content.href);
+ } else {
+ return copyNode(content);
+ }
+ }
+
+ function clicked(event) {
+ const button = event.currentTarget;
+
+ if (button instanceof HTMLElement) {
+ copy(button);
+ }
+ }
+
+ function keydown(event) {
+ if (event.key === ' ' || event.key === 'Enter') {
+ const button = event.currentTarget;
+
+ if (button instanceof HTMLElement) {
+ event.preventDefault();
+ copy(button);
+ }
+ }
+ }
+
+ function focused(event) {
+ event.currentTarget.addEventListener('keydown', keydown);
+ }
+
+ function blurred(event) {
+ event.currentTarget.removeEventListener('keydown', keydown);
+ }
+
+ class ClipboardCopyElement extends HTMLElement {
+ constructor() {
+ super();
+ this.addEventListener('click', clicked);
+ this.addEventListener('focus', focused);
+ this.addEventListener('blur', blurred);
+ }
+
+ connectedCallback() {
+ if (!this.hasAttribute('tabindex')) {
+ this.setAttribute('tabindex', '0');
+ }
+
+ if (!this.hasAttribute('role')) {
+ this.setAttribute('role', 'button');
+ }
+ }
+
+ get value() {
+ return this.getAttribute('value') || '';
+ }
+
+ set value(text) {
+ this.setAttribute('value', text);
+ }
+
+ }
+
+ if (!window.customElements.get('clipboard-copy')) {
+ window.ClipboardCopyElement = ClipboardCopyElement;
+ window.customElements.define('clipboard-copy', ClipboardCopyElement);
+ }
+
+ return ClipboardCopyElement;
+
+ }));
+</script>
+<script>
+ document.addEventListener('clipboard-copy', function(event) {
+ const notice = event.target.querySelector('.notice')
+ notice.hidden = false
+ setTimeout(function() {
+ notice.hidden = true
+ }, 1000)
+ })
+</script>
+<style type="text/css">
+ pre { line-height: 125%; }
+td.linenos .normal { color: inherit; background-color: transparent;
padding-left: 5px; padding-right: 5px; }
+span.linenos { color: inherit; background-color: transparent; padding-left:
5px; padding-right: 5px; }
+td.linenos .special { color: #000000; background-color: #ffffc0; padding-left:
5px; padding-right: 5px; }
+span.linenos.special { color: #000000; background-color: #ffffc0;
padding-left: 5px; padding-right: 5px; }
+.highlight-ipynb .hll { background-color:
var(--jp-cell-editor-active-background) }
+.highlight-ipynb { background: var(--jp-cell-editor-background); color:
var(--jp-mirror-editor-variable-color) }
+.highlight-ipynb .c { color: var(--jp-mirror-editor-comment-color);
font-style: italic } /* Comment */
+.highlight-ipynb .err { color: var(--jp-mirror-editor-error-color) } /* Error
*/
+.highlight-ipynb .k { color: var(--jp-mirror-editor-keyword-color);
font-weight: bold } /* Keyword */
+.highlight-ipynb .o { color: var(--jp-mirror-editor-operator-color);
font-weight: bold } /* Operator */
+.highlight-ipynb .p { color: var(--jp-mirror-editor-punctuation-color) } /*
Punctuation */
+.highlight-ipynb .ch { color: var(--jp-mirror-editor-comment-color);
font-style: italic } /* Comment.Hashbang */
+.highlight-ipynb .cm { color: var(--jp-mirror-editor-comment-color);
font-style: italic } /* Comment.Multiline */
+.highlight-ipynb .cp { color: var(--jp-mirror-editor-comment-color);
font-style: italic } /* Comment.Preproc */
+.highlight-ipynb .cpf { color: var(--jp-mirror-editor-comment-color);
font-style: italic } /* Comment.PreprocFile */
+.highlight-ipynb .c1 { color: var(--jp-mirror-editor-comment-color);
font-style: italic } /* Comment.Single */
+.highlight-ipynb .cs { color: var(--jp-mirror-editor-comment-color);
font-style: italic } /* Comment.Special */
+.highlight-ipynb .kc { color: var(--jp-mirror-editor-keyword-color);
font-weight: bold } /* Keyword.Constant */
+.highlight-ipynb .kd { color: var(--jp-mirror-editor-keyword-color);
font-weight: bold } /* Keyword.Declaration */
+.highlight-ipynb .kn { color: var(--jp-mirror-editor-keyword-color);
font-weight: bold } /* Keyword.Namespace */
+.highlight-ipynb .kp { color: var(--jp-mirror-editor-keyword-color);
font-weight: bold } /* Keyword.Pseudo */
+.highlight-ipynb .kr { color: var(--jp-mirror-editor-keyword-color);
font-weight: bold } /* Keyword.Reserved */
+.highlight-ipynb .kt { color: var(--jp-mirror-editor-keyword-color);
font-weight: bold } /* Keyword.Type */
+.highlight-ipynb .m { color: var(--jp-mirror-editor-number-color) } /*
Literal.Number */
+.highlight-ipynb .s { color: var(--jp-mirror-editor-string-color) } /*
Literal.String */
+.highlight-ipynb .ow { color: var(--jp-mirror-editor-operator-color);
font-weight: bold } /* Operator.Word */
+.highlight-ipynb .pm { color: var(--jp-mirror-editor-punctuation-color) } /*
Punctuation.Marker */
+.highlight-ipynb .w { color: var(--jp-mirror-editor-variable-color) } /*
Text.Whitespace */
+.highlight-ipynb .mb { color: var(--jp-mirror-editor-number-color) } /*
Literal.Number.Bin */
+.highlight-ipynb .mf { color: var(--jp-mirror-editor-number-color) } /*
Literal.Number.Float */
+.highlight-ipynb .mh { color: var(--jp-mirror-editor-number-color) } /*
Literal.Number.Hex */
+.highlight-ipynb .mi { color: var(--jp-mirror-editor-number-color) } /*
Literal.Number.Integer */
+.highlight-ipynb .mo { color: var(--jp-mirror-editor-number-color) } /*
Literal.Number.Oct */
+.highlight-ipynb .sa { color: var(--jp-mirror-editor-string-color) } /*
Literal.String.Affix */
+.highlight-ipynb .sb { color: var(--jp-mirror-editor-string-color) } /*
Literal.String.Backtick */
+.highlight-ipynb .sc { color: var(--jp-mirror-editor-string-color) } /*
Literal.String.Char */
+.highlight-ipynb .dl { color: var(--jp-mirror-editor-string-color) } /*
Literal.String.Delimiter */
+.highlight-ipynb .sd { color: var(--jp-mirror-editor-string-color) } /*
Literal.String.Doc */
+.highlight-ipynb .s2 { color: var(--jp-mirror-editor-string-color) } /*
Literal.String.Double */
+.highlight-ipynb .se { color: var(--jp-mirror-editor-string-color) } /*
Literal.String.Escape */
+.highlight-ipynb .sh { color: var(--jp-mirror-editor-string-color) } /*
Literal.String.Heredoc */
+.highlight-ipynb .si { color: var(--jp-mirror-editor-string-color) } /*
Literal.String.Interpol */
+.highlight-ipynb .sx { color: var(--jp-mirror-editor-string-color) } /*
Literal.String.Other */
+.highlight-ipynb .sr { color: var(--jp-mirror-editor-string-color) } /*
Literal.String.Regex */
+.highlight-ipynb .s1 { color: var(--jp-mirror-editor-string-color) } /*
Literal.String.Single */
+.highlight-ipynb .ss { color: var(--jp-mirror-editor-string-color) } /*
Literal.String.Symbol */
+.highlight-ipynb .il { color: var(--jp-mirror-editor-number-color) } /*
Literal.Number.Integer.Long */
+ </style>
+<style type="text/css">
+@charset "UTF-8";.jupyter-wrapper{--md-red-50: #ffebee;--md-red-100:
#ffcdd2;--md-red-200: #ef9a9a;--md-red-300: #e57373;--md-red-400:
#ef5350;--md-red-500: #f44336;--md-red-600: #e53935;--md-red-700:
#d32f2f;--md-red-800: #c62828;--md-red-900: #b71c1c;--md-red-A100:
#ff8a80;--md-red-A200: #ff5252;--md-red-A400: #ff1744;--md-red-A700:
#d50000;--md-pink-50: #fce4ec;--md-pink-100: #f8bbd0;--md-pink-200:
#f48fb1;--md-pink-300: #f06292;--md-pink-400: #ec407a;--md-pink-500:
#e91e63;--md-pink- [...]
+</style>
+<style type="text/css">
+.jupyter-wrapper{--jp-shadow-base-lightness: 0;--jp-shadow-umbra-color: rgba(
var(--jp-shadow-base-lightness), var(--jp-shadow-base-lightness),
var(--jp-shadow-base-lightness), .2 );--jp-shadow-penumbra-color: rgba(
var(--jp-shadow-base-lightness), var(--jp-shadow-base-lightness),
var(--jp-shadow-base-lightness), .14 );--jp-shadow-ambient-color: rgba(
var(--jp-shadow-base-lightness), var(--jp-shadow-base-lightness),
var(--jp-shadow-base-lightness), .12 );--jp-elevation-z0: none;--jp-elev [...]
+</style>
+<!-- Load mathjax -->
+<script src=""> </script>
+<!-- MathJax configuration -->
+<script type="text/x-mathjax-config">
+ init_mathjax = function() {
+ if (window.MathJax) {
+ // MathJax loaded
+ MathJax.Hub.Config({
+ TeX: {
+ equationNumbers: {
+ autoNumber: "AMS",
+ useLabelIds: true
+ }
+ },
+ tex2jax: {
+ inlineMath: [ ['$','$'], ["\\(","\\)"] ],
+ displayMath: [ ['$$','$$'], ["\\[","\\]"] ],
+ processEscapes: true,
+ processEnvironments: true
+ },
+ displayAlign: 'center',
+ messageStyle: 'none',
+ CommonHTML: {
+ linebreaks: {
+ automatic: true
+ }
+ }
+ });
+
+ MathJax.Hub.Queue(["Typeset", MathJax.Hub]);
+ }
+ }
+ init_mathjax();
+ </script>
+<!-- End of mathjax configuration --><script type="module">
+ document.addEventListener("DOMContentLoaded", async () => {
+ const diagrams = document.querySelectorAll(".jp-Mermaid > pre.mermaid");
+ // do not load mermaidjs if not needed
+ if (!diagrams.length) {
+ return;
+ }
+ const mermaid = (await
import("https://cdnjs.cloudflare.com/ajax/libs/mermaid/10.7.0/mermaid.esm.min.mjs";)).default;
+ const parser = new DOMParser();
+
+ mermaid.initialize({
+ maxTextSize: 100000,
+ maxEdges: 100000,
+ startOnLoad: false,
+ fontFamily: window
+ .getComputedStyle(document.body)
+ .getPropertyValue("--jp-ui-font-family"),
+ theme: document.querySelector("body[data-jp-theme-light='true']")
+ ? "default"
+ : "dark",
+ });
+
+ let _nextMermaidId = 0;
+
+ function makeMermaidImage(svg) {
+ const img = document.createElement("img");
+ const doc = parser.parseFromString(svg, "image/svg+xml");
+ const svgEl = doc.querySelector("svg");
+ const { maxWidth } = svgEl?.style || {};
+ const firstTitle = doc.querySelector("title");
+ const firstDesc = doc.querySelector("desc");
+
+ img.setAttribute("src", `data:image/svg+xml,${encodeURIComponent(svg)}`);
+ if (maxWidth) {
+ img.width = parseInt(maxWidth);
+ }
+ if (firstTitle) {
+ img.setAttribute("alt", firstTitle.textContent);
+ }
+ if (firstDesc) {
+ const caption = document.createElement("figcaption");
+ caption.className = "sr-only";
+ caption.textContent = firstDesc.textContent;
+ return [img, caption];
+ }
+ return [img];
+ }
+
+ async function makeMermaidError(text) {
+ let errorMessage = "";
+ try {
+ await mermaid.parse(text);
+ } catch (err) {
+ errorMessage = `${err}`;
+ }
+
+ const result = document.createElement("details");
+ result.className = 'jp-RenderedMermaid-Details';
+ const summary = document.createElement("summary");
+ summary.className = 'jp-RenderedMermaid-Summary';
+ const pre = document.createElement("pre");
+ const code = document.createElement("code");
+ code.innerText = text;
+ pre.appendChild(code);
+ summary.appendChild(pre);
+ result.appendChild(summary);
+
+ const warning = document.createElement("pre");
+ warning.innerText = errorMessage;
+ result.appendChild(warning);
+ return [result];
+ }
+
+ async function renderOneMarmaid(src) {
+ const id = `jp-mermaid-${_nextMermaidId++}`;
+ const parent = src.parentNode;
+ let raw = src.textContent.trim();
+ const el = document.createElement("div");
+ el.style.visibility = "hidden";
+ document.body.appendChild(el);
+ let results = null;
+ let output = null;
+ try {
+ let { svg } = await mermaid.render(id, raw, el);
+ svg = cleanMermaidSvg(svg);
+ results = makeMermaidImage(svg);
+ output = document.createElement("figure");
+ results.map(output.appendChild, output);
+ } catch (err) {
+ parent.classList.add("jp-mod-warning");
+ results = await makeMermaidError(raw);
+ output = results[0];
+ } finally {
+ el.remove();
+ }
+ parent.classList.add("jp-RenderedMermaid");
+ parent.appendChild(output);
+ }
+
+
+ /**
+ * Post-process to ensure mermaid diagrams contain only valid SVG and
XHTML.
+ */
+ function cleanMermaidSvg(svg) {
+ return svg.replace(RE_VOID_ELEMENT, replaceVoidElement);
+ }
+
+
+ /**
+ * A regular expression for all void elements, which may include
attributes and
+ * a slash.
+ *
+ * @see https://developer.mozilla.org/en-US/docs/Glossary/Void_element
+ *
+ * Of these, only `<br>` is generated by Mermaid in place of `\n`,
+ * but _any_ "malformed" tag will break the SVG rendering entirely.
+ */
+ const RE_VOID_ELEMENT =
+
/<\s*(area|base|br|col|embed|hr|img|input|link|meta|param|source|track|wbr)\s*([^>]*?)\s*>/gi;
+
+ /**
+ * Ensure a void element is closed with a slash, preserving any attributes.
+ */
+ function replaceVoidElement(match, tag, rest) {
+ rest = rest.trim();
+ if (!rest.endsWith('/')) {
+ rest = `${rest} /`;
+ }
+ return `<${tag} ${rest}>`;
+ }
+
+ void Promise.all([...diagrams].map(renderOneMarmaid));
+ });
+</script>
+<style>
+ .jp-Mermaid:not(.jp-RenderedMermaid) {
+ display: none;
+ }
+
+ .jp-RenderedMermaid {
+ overflow: auto;
+ display: flex;
+ }
+
+ .jp-RenderedMermaid.jp-mod-warning {
+ width: auto;
+ padding: 0.5em;
+ margin-top: 0.5em;
+ border: var(--jp-border-width) solid var(--jp-warn-color2);
+ border-radius: var(--jp-border-radius);
+ color: var(--jp-ui-font-color1);
+ font-size: var(--jp-ui-font-size1);
+ white-space: pre-wrap;
+ word-wrap: break-word;
+ }
+
+ .jp-RenderedMermaid figure {
+ margin: 0;
+ overflow: auto;
+ max-width: 100%;
+ }
+
+ .jp-RenderedMermaid img {
+ max-width: 100%;
+ }
+
+ .jp-RenderedMermaid-Details > pre {
+ margin-top: 1em;
+ }
+
+ .jp-RenderedMermaid-Summary {
+ color: var(--jp-warn-color2);
+ }
+
+ .jp-RenderedMermaid:not(.jp-mod-warning) pre {
+ display: none;
+ }
+
+ .jp-RenderedMermaid-Summary > pre {
+ display: inline-block;
+ white-space: normal;
+ }
+</style>
+<!-- End of mermaid configuration --><div class="jupyter-wrapper">
+<div class="jp-Notebook" data-jp-theme-light="true"
data-jp-theme-name="JupyterLab Light">
+<div class="jp-Cell jp-MarkdownCell jp-Notebook-cell"
id="cell-id=cf23cfd2-c45c-4ca3-9dcb-70541f3cc47d">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea"><div class="jp-InputPrompt
jp-InputArea-prompt">
+</div><div class="jp-RenderedHTMLCommon jp-RenderedMarkdown jp-MarkdownOutput"
data-mime-type="text/markdown">
+<h1 id="spatialbench-queries">SpatialBench Queries<a class="anchor-link"
href="#spatialbench-queries">¶</a></h1>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell jp-mod-noOutputs">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell jp-mod-noOutputs"
id="cell-id=9c1fe889-d035-4ed9-85a6-f7a8ecb891ad">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [22]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-1">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="kn">import</span><span class="w"> </span><span
class="nn">sedona.db</span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-1">import sedona.db</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell jp-mod-noOutputs">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell jp-mod-noOutputs"
id="cell-id=96cf6ad5-e106-48f7-9097-9f3f740e9e7f">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [23]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-2">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="n">sd</span> <span class="o">=</span> <span class="n">sedona</span><span
class="o">.</span><span class="n">db</span><span class="o">.</span><span
class="n">connect</span><span class="p">()</span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-2">sd = sedona.db.connect()</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell jp-mod-noOutputs">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell jp-mod-noOutputs"
id="cell-id=16a147f4-4a12-4050-a1bb-934834b4a6bc">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [24]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-3">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="n">sd</span><span class="o">.</span><span
class="n">read_parquet</span><span class="p">(</span><span
class="sa">f</span><span
class="s2">"/Users/matthewpowers/data/sf1-parquet/building.parquet"</span><span
class="p">)</span><span class="o">.</span><span class="n">to_view</span><span
class="p">(</span><span class="s2">"building"</span><span class="p">)</span>
+<span class="n">sd</span><span class="o">.</span><span
class="n">read_parquet</span><span class="p">(</span><span
class="sa">f</span><span
class="s2">"/Users/matthewpowers/data/sf1-parquet/customer.parquet"</span><span
class="p">)</span><span class="o">.</span><span class="n">to_view</span><span
class="p">(</span><span class="s2">"customer"</span><span class="p">)</span>
+<span class="n">sd</span><span class="o">.</span><span
class="n">read_parquet</span><span class="p">(</span><span
class="sa">f</span><span
class="s2">"/Users/matthewpowers/data/sf1-parquet/driver.parquet"</span><span
class="p">)</span><span class="o">.</span><span class="n">to_view</span><span
class="p">(</span><span class="s2">"driver"</span><span class="p">)</span>
+<span class="n">sd</span><span class="o">.</span><span
class="n">read_parquet</span><span class="p">(</span><span
class="sa">f</span><span
class="s2">"/Users/matthewpowers/data/sf1-parquet/trip.parquet"</span><span
class="p">)</span><span class="o">.</span><span class="n">to_view</span><span
class="p">(</span><span class="s2">"trip"</span><span class="p">)</span>
+<span class="n">sd</span><span class="o">.</span><span
class="n">read_parquet</span><span class="p">(</span><span
class="sa">f</span><span
class="s2">"/Users/matthewpowers/data/sf1-parquet/vehicle.parquet"</span><span
class="p">)</span><span class="o">.</span><span class="n">to_view</span><span
class="p">(</span><span class="s2">"vehicle"</span><span class="p">)</span>
+<span class="n">sd</span><span class="o">.</span><span
class="n">read_parquet</span><span class="p">(</span><span
class="sa">f</span><span
class="s2">"/Users/matthewpowers/data/sf1-parquet/zone.parquet"</span><span
class="p">)</span><span class="o">.</span><span class="n">to_view</span><span
class="p">(</span><span class="s2">"zone"</span><span class="p">)</span>
+</pre></div>
+<div class="clipboard-copy-txt"
id="cell-3">sd.read_parquet(f"/Users/matthewpowers/data/sf1-parquet/building.parquet").to_view("building")
+sd.read_parquet(f"/Users/matthewpowers/data/sf1-parquet/customer.parquet").to_view("customer")
+sd.read_parquet(f"/Users/matthewpowers/data/sf1-parquet/driver.parquet").to_view("driver")
+sd.read_parquet(f"/Users/matthewpowers/data/sf1-parquet/trip.parquet").to_view("trip")
+sd.read_parquet(f"/Users/matthewpowers/data/sf1-parquet/vehicle.parquet").to_view("vehicle")
+sd.read_parquet(f"/Users/matthewpowers/data/sf1-parquet/zone.parquet").to_view("zone")</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell jp-MarkdownCell jp-Notebook-cell"
id="cell-id=5e243bca-ca97-4f72-bc72-46ace7495019">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea"><div class="jp-InputPrompt
jp-InputArea-prompt">
+</div><div class="jp-RenderedHTMLCommon jp-RenderedMarkdown jp-MarkdownOutput"
data-mime-type="text/markdown">
+<h2
id="q1-find-trips-starting-within-50km-of-sedona-city-center-ordered-by-distance">Q1:
Find trips starting within 50km of Sedona city center, ordered by distance<a
class="anchor-link"
href="#q1-find-trips-starting-within-50km-of-sedona-city-center-ordered-by-distance">¶</a></h2><p><strong>Real-life
scenario:</strong> Identify and rank trips by proximity to a city center for
urban planning and transportation analysis.</p>
+<p>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.</p>
+<p><strong>Spatial query characteristics tested:</strong></p>
+<ol>
+<li>Distance-based spatial filtering (ST_DWithin)</li>
+<li>Distance calculation to a fixed point</li>
+<li>Coordinate extraction (ST_X, ST_Y)</li>
+<li>Ordering by spatial distance</li>
+</ol>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell"
id="cell-id=f121f799-08f9-4679-a772-fb5f5470986f">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [25]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-4">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="n">sd</span><span class="o">.</span><span class="n">sql</span><span
class="p">(</span><span class="s2">"""</span>
+<span class="s2">SELECT</span>
+<span class="s2"> 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,</span>
+<span class="s2"> ST_Distance(ST_GeomFromWKB(t.t_pickuploc),
ST_GeomFromText('POINT (-111.7610 34.8697)')) AS distance_to_center</span>
+<span class="s2">FROM trip t</span>
+<span class="s2">WHERE ST_DWithin(ST_GeomFromWKB(t.t_pickuploc),
ST_GeomFromText('POINT (-111.7610 34.8697)'), 0.45) -- 50km radius around
Sedona center</span>
+<span class="s2">ORDER BY distance_to_center ASC, t.t_tripkey ASC</span>
+<span class="s2">"""</span><span class="p">)</span><span
class="o">.</span><span class="n">show</span><span class="p">(</span><span
class="mi">3</span><span class="p">)</span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-4">sd.sql("""
+SELECT
+ 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,
+ ST_Distance(ST_GeomFromWKB(t.t_pickuploc), ST_GeomFromText('POINT
(-111.7610 34.8697)')) AS distance_to_center
+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
+""").show(3)</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell-outputWrapper">
+<div class="jp-Collapser jp-OutputCollapser jp-Cell-outputCollapser">
+</div>
+<div class="jp-OutputArea jp-Cell-outputArea">
+<div class="jp-OutputArea-child">
+<div class="jp-OutputPrompt jp-OutputArea-prompt"></div>
+<div class="jp-RenderedText jp-OutputArea-output" data-mime-type="text/plain"
tabindex="0">
+<pre>┌───────────┬────────────────┬──────────────┬──────────────────────────────┬──────────────────────┐
+│ t_tripkey ┆ pickup_lon ┆ pickup_lat ┆ t_pickuptime ┆
distance_to_center │
+│ int64 ┆ float64 ┆ float64 ┆ timestamp(millisecond, none) ┆
float64 │
+╞═══════════╪════════════════╪══════════════╪══════════════════════════════╪══════════════════════╡
+│ 1451371 ┆ -111.791052127 ┆ 34.826733457 ┆ 1998-08-12T06:47:01 ┆
0.05243333056935386 │
+├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+│ 2047835 ┆ -111.706967009 ┆ 34.883889472 ┆ 1992-04-08T07:36:09 ┆
0.055865062714050374 │
+├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+│ 3936870 ┆ -111.827619221 ┆ 34.882950924 ┆ 1998-11-10T13:32:07 ┆
0.06792427838042854 │
+└───────────┴────────────────┴──────────────┴──────────────────────────────┴──────────────────────┘
+</pre>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell jp-MarkdownCell jp-Notebook-cell"
id="cell-id=d1e2a458-fc72-4a21-889e-9099f9ad6fb7">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea"><div class="jp-InputPrompt
jp-InputArea-prompt">
+</div><div class="jp-RenderedHTMLCommon jp-RenderedMarkdown jp-MarkdownOutput"
data-mime-type="text/markdown">
+<h2 id="q2-count-trips-starting-within-coconino-county-arizona-zone">Q2: Count
trips starting within Coconino County (Arizona) zone<a class="anchor-link"
href="#q2-count-trips-starting-within-coconino-county-arizona-zone">¶</a></h2><p><strong>Real-life
scenario:</strong> Count all trips originating within a specific
administrative boundary (county) for regional transportation statistics.</p>
+<p>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.</p>
+<p><strong>Spatial query characteristics tested:</strong></p>
+<ol>
+<li>Point-in-polygon spatial filtering (ST_Intersects)</li>
+<li>Subquery with spatial geometry selection</li>
+<li>Simple aggregation on spatially filtered data</li>
+</ol>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell"
id="cell-id=cc219172-0cfb-4f60-a728-e4ec5cc5c21e">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [26]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-5">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="n">sd</span><span class="o">.</span><span class="n">sql</span><span
class="p">(</span><span class="s2">"""</span>
+<span class="s2">SELECT COUNT(*) AS trip_count_in_coconino_county</span>
+<span class="s2">FROM trip t</span>
+<span class="s2">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))</span>
+<span class="s2">"""</span><span class="p">)</span><span
class="o">.</span><span class="n">show</span><span class="p">(</span><span
class="mi">3</span><span class="p">)</span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-5">sd.sql("""
+SELECT COUNT(*) AS trip_count_in_coconino_county
+FROM trip t
+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))
+""").show(3)</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell-outputWrapper">
+<div class="jp-Collapser jp-OutputCollapser jp-Cell-outputCollapser">
+</div>
+<div class="jp-OutputArea jp-Cell-outputArea">
+<div class="jp-OutputArea-child">
+<div class="jp-OutputPrompt jp-OutputArea-prompt"></div>
+<div class="jp-RenderedText jp-OutputArea-output" data-mime-type="text/plain"
tabindex="0">
+<pre>┌───────────────────────────────┐
+│ trip_count_in_coconino_county │
+│ int64 │
+╞═══════════════════════════════╡
+│ 541 │
+└───────────────────────────────┘
+</pre>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell jp-MarkdownCell jp-Notebook-cell"
id="cell-id=998daae7-31e4-4e93-823d-70d4cee00605">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea"><div class="jp-InputPrompt
jp-InputArea-prompt">
+</div><div class="jp-RenderedHTMLCommon jp-RenderedMarkdown jp-MarkdownOutput"
data-mime-type="text/markdown">
+<h2
id="q3-monthly-trip-statistics-within-a-15km-radius-of-the-sedona-city-center">Q3:
Monthly trip statistics within a 15km radius of the Sedona city center<a
class="anchor-link"
href="#q3-monthly-trip-statistics-within-a-15km-radius-of-the-sedona-city-center">¶</a></h2><p><strong>Real-life
scenario:</strong> Track monthly travel trends and performance metrics in a
metropolitan area with seasonal analysis.</p>
+<p>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 over [...]
+<p><strong>Spatial query characteristics tested:</strong></p>
+<ol>
+<li>Distance-based spatial filtering (ST_DWithin) with buffer</li>
+<li>Temporal grouping (monthly aggregation)</li>
+<li>Multiple statistical aggregations on spatially filtered data</li>
+</ol>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell"
id="cell-id=eb5d6347-47dd-48af-adb5-929838954ac4">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [27]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-6">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="n">sd</span><span class="o">.</span><span class="n">sql</span><span
class="p">(</span><span class="s2">"""</span>
+<span class="s2">SELECT</span>
+<span class="s2"> DATE_TRUNC('month', t.t_pickuptime) AS pickup_month,
COUNT(t.t_tripkey) AS total_trips,</span>
+<span class="s2"> AVG(t.t_distance) AS avg_distance, AVG(t.t_dropofftime -
t.t_pickuptime) AS avg_duration,</span>
+<span class="s2"> AVG(t.t_fare) AS avg_fare</span>
+<span class="s2">FROM trip t</span>
+<span class="s2">WHERE ST_DWithin(</span>
+<span class="s2"> ST_GeomFromWKB(t.t_pickuploc),</span>
+<span class="s2"> 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</span>
+<span class="s2"> 0.045 -- Additional 5km buffer</span>
+<span class="s2">)</span>
+<span class="s2">GROUP BY pickup_month</span>
+<span class="s2">ORDER BY pickup_month</span>
+<span class="s2">"""</span><span class="p">)</span><span
class="o">.</span><span class="n">show</span><span class="p">(</span><span
class="mi">3</span><span class="p">)</span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-6">sd.sql("""
+SELECT
+ DATE_TRUNC('month', t.t_pickuptime) AS pickup_month, COUNT(t.t_tripkey) AS
total_trips,
+ AVG(t.t_distance) AS avg_distance, AVG(t.t_dropofftime - t.t_pickuptime)
AS avg_duration,
+ AVG(t.t_fare) AS avg_fare
+FROM trip t
+WHERE ST_DWithin(
+ ST_GeomFromWKB(t.t_pickuploc),
+ 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
+ 0.045 -- Additional 5km buffer
+)
+GROUP BY pickup_month
+ORDER BY pickup_month
+""").show(3)</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell-outputWrapper">
+<div class="jp-Collapser jp-OutputCollapser jp-Cell-outputCollapser">
+</div>
+<div class="jp-OutputArea jp-Cell-outputArea">
+<div class="jp-OutputArea-child">
+<div class="jp-OutputPrompt jp-OutputArea-prompt"></div>
+<div class="jp-RenderedText jp-OutputArea-output" data-mime-type="text/plain"
tabindex="0">
+<pre>┌──────────────────────┬─────────────┬───────────────────┬─────────────────────┬───────────────────┐
+│ pickup_month ┆ total_trips ┆ avg_distance ┆ avg_duration
┆ avg_fare │
+│ timestamp(milliseco… ┆ int64 ┆ decimal128(19, 9) ┆ duration(milliseco…
┆ decimal128(19, 9) │
+╞══════════════════════╪═════════════╪═══════════════════╪═════════════════════╪═══════════════════╡
+│ 1992-04-01T00:00:00 ┆ 2 ┆ 0.000020000 ┆ 0 days 1 hours 23 …
┆ 0.000075000 │
+├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+│ 1992-07-01T00:00:00 ┆ 1 ┆ 0.000010000 ┆ 0 days 0 hours 58 …
┆ 0.000040000 │
+├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+│ 1994-02-01T00:00:00 ┆ 2 ┆ 0.000020000 ┆ 0 days 1 hours 23 …
┆ 0.000050000 │
+└──────────────────────┴─────────────┴───────────────────┴─────────────────────┴───────────────────┘
+</pre>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell jp-MarkdownCell jp-Notebook-cell"
id="cell-id=b516ed52-8cf2-4c17-bc33-00002e22b287">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea"><div class="jp-InputPrompt
jp-InputArea-prompt">
+</div><div class="jp-RenderedHTMLCommon jp-RenderedMarkdown jp-MarkdownOutput"
data-mime-type="text/markdown">
+<h2 id="q4-zone-distribution-of-top-1000-trips-by-tip-amount">Q4: Zone
distribution of top 1000 trips by tip amount<a class="anchor-link"
href="#q4-zone-distribution-of-top-1000-trips-by-tip-amount">¶</a></h2><p><strong>Real-life
scenario:</strong> Analyze the geographic distribution of high-value trips (by
tip amount) to understand premium service areas.</p>
+<p>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 drive [...]
+<p><strong>Spatial query characteristics tested:</strong></p>
+<ol>
+<li>Subquery with ordering and limiting</li>
+<li>Point-in-polygon spatial join (ST_Within)</li>
+<li>Aggregation on spatially joined results</li>
+<li>Multi-step query with spatial filtering and grouping</li>
+</ol>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell"
id="cell-id=f8dcf328-a7f2-49be-9383-afa11766f871">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [28]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-7">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="n">sd</span><span class="o">.</span><span class="n">sql</span><span
class="p">(</span><span class="s2">"""</span>
+<span class="s2">SELECT z.z_zonekey, z.z_name, COUNT(*) AS trip_count</span>
+<span class="s2">FROM</span>
+<span class="s2"> zone z</span>
+<span class="s2"> JOIN (</span>
+<span class="s2"> SELECT t.t_pickuploc</span>
+<span class="s2"> FROM trip t</span>
+<span class="s2"> ORDER BY t.t_tip DESC, t.t_tripkey ASC</span>
+<span class="s2"> LIMIT 1000 -- Replace 1000 with x (how many top tips
you want)</span>
+<span class="s2"> ) top_trips ON
ST_Within(ST_GeomFromWKB(top_trips.t_pickuploc),
ST_GeomFromWKB(z.z_boundary))</span>
+<span class="s2">GROUP BY z.z_zonekey, z.z_name</span>
+<span class="s2">ORDER BY trip_count DESC, z.z_zonekey ASC</span>
+<span class="s2">"""</span><span class="p">)</span><span
class="o">.</span><span class="n">show</span><span class="p">(</span><span
class="mi">3</span><span class="p">)</span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-7">sd.sql("""
+SELECT z.z_zonekey, z.z_name, COUNT(*) AS trip_count
+FROM
+ zone z
+ JOIN (
+ SELECT t.t_pickuploc
+ FROM trip t
+ ORDER BY t.t_tip DESC, t.t_tripkey ASC
+ LIMIT 1000 -- Replace 1000 with x (how many top tips you want)
+ ) top_trips ON ST_Within(ST_GeomFromWKB(top_trips.t_pickuploc),
ST_GeomFromWKB(z.z_boundary))
+GROUP BY z.z_zonekey, z.z_name
+ORDER BY trip_count DESC, z.z_zonekey ASC
+""").show(3)</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell-outputWrapper">
+<div class="jp-Collapser jp-OutputCollapser jp-Cell-outputCollapser">
+</div>
+<div class="jp-OutputArea jp-Cell-outputArea">
+<div class="jp-OutputArea-child">
+<div class="jp-OutputPrompt jp-OutputArea-prompt"></div>
+<div class="jp-RenderedText jp-OutputArea-output" data-mime-type="text/plain"
tabindex="0">
+<pre>┌───────────┬─────────────────────────────────┬────────────┐
+│ z_zonekey ┆ z_name ┆ trip_count │
+│ int64 ┆ utf8view ┆ int64 │
+╞═══════════╪═════════════════════════════════╪════════════╡
+│ 65008 ┆ Ndélé ┆ 35 │
+├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤
+│ 147530 ┆ 乐山市 ┆ 27 │
+├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤
+│ 150276 ┆ 锡林郭勒盟 ᠰᠢᠯᠢ ᠶᠢᠨ ᠭᠣᠣᠯ ᠠᠶᠢᠮᠠᠭ ┆ 19 │
+└───────────┴─────────────────────────────────┴────────────┘
+</pre>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell jp-MarkdownCell jp-Notebook-cell"
id="cell-id=ad294223-742c-4b6b-a9ec-036723e67a1f">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea"><div class="jp-InputPrompt
jp-InputArea-prompt">
+</div><div class="jp-RenderedHTMLCommon jp-RenderedMarkdown jp-MarkdownOutput"
data-mime-type="text/markdown">
+<h2
id="q5-monthly-travel-patterns-for-repeat-customers-convex-hull-of-dropoff-locations">Q5:
Monthly travel patterns for repeat customers (convex hull of dropoff
locations)<a class="anchor-link"
href="#q5-monthly-travel-patterns-for-repeat-customers-convex-hull-of-dropoff-locations">¶</a></h2><p><strong>Real-life
scenario:</strong> Analyze the geographic spread of travel patterns for
frequent customers to understand their mobility behavior.</p>
+<p>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 [...]
+<p><strong>Spatial query characteristics tested:</strong></p>
+<ol>
+<li>Spatial aggregation (ST_Collect/ARRAY_AGG)</li>
+<li>Convex hull computation (ST_ConvexHull)</li>
+<li>Area calculation on complex geometries</li>
+<li>Temporal and customer-based grouping with spatial operations</li>
+</ol>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell"
id="cell-id=8c446f9a-3734-4a8d-87a8-0d9d6d34fd5c">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [29]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-8">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="n">sd</span><span class="o">.</span><span class="n">sql</span><span
class="p">(</span><span class="s2">"""</span>
+<span class="s2">SELECT</span>
+<span class="s2"> c.c_custkey, c.c_name AS customer_name,</span>
+<span class="s2"> DATE_TRUNC('month', t.t_pickuptime) AS
pickup_month,</span>
+<span class="s2">
ST_Area(ST_ConvexHull(ST_Collect(ST_GeomFromWKB(t.t_dropoffloc)))) AS
monthly_travel_hull_area,</span>
+<span class="s2"> COUNT(*) as dropoff_count</span>
+<span class="s2">FROM trip t JOIN customer c ON t.t_custkey =
c.c_custkey</span>
+<span class="s2">GROUP BY c.c_custkey, c.c_name, pickup_month</span>
+<span class="s2">HAVING dropoff_count > 5 -- Only include repeat customers
for meaningful hulls</span>
+<span class="s2">ORDER BY dropoff_count DESC, c.c_custkey ASC</span>
+<span class="s2">"""</span><span class="p">)</span><span
class="o">.</span><span class="n">show</span><span class="p">(</span><span
class="mi">3</span><span class="p">)</span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-8">sd.sql("""
+SELECT
+ c.c_custkey, c.c_name AS customer_name,
+ DATE_TRUNC('month', t.t_pickuptime) AS pickup_month,
+ ST_Area(ST_ConvexHull(ST_Collect(ST_GeomFromWKB(t.t_dropoffloc)))) AS
monthly_travel_hull_area,
+ COUNT(*) as dropoff_count
+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
+""").show(3)</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell-outputWrapper">
+<div class="jp-Collapser jp-OutputCollapser jp-Cell-outputCollapser">
+</div>
+<div class="jp-OutputArea jp-Cell-outputArea">
+<div class="jp-OutputArea-child">
+<div class="jp-OutputPrompt jp-OutputArea-prompt"></div>
+<div class="jp-RenderedText jp-OutputArea-output" data-mime-type="text/plain"
tabindex="0">
+<pre>┌───────────┬────────────────────┬────────────────────────────┬────────────────────┬───────────────┐
+│ c_custkey ┆ customer_name ┆ pickup_month ┆
monthly_travel_hul ┆ dropoff_count │
+│ int64 ┆ utf8view ┆ timestamp(millisecond, no… ┆ l_area…
┆ int64 │
+╞═══════════╪════════════════════╪════════════════════════════╪════════════════════╪═══════════════╡
+│ 12049 ┆ Customer#000012049 ┆ 1997-10-01T00:00:00 ┆
22290.72518508209 ┆ 18 │
+├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+│ 12091 ┆ Customer#000012091 ┆ 1997-08-01T00:00:00 ┆
18901.827089935185 ┆ 18 │
+├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+│ 13231 ┆ Customer#000013231 ┆ 1994-09-01T00:00:00 ┆
24930.673726244015 ┆ 18 │
+└───────────┴────────────────────┴────────────────────────────┴────────────────────┴───────────────┘
+</pre>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell jp-MarkdownCell jp-Notebook-cell"
id="cell-id=8501daf5-5e4a-46d5-86c0-c3f6ea8673ee">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea"><div class="jp-InputPrompt
jp-InputArea-prompt">
+</div><div class="jp-RenderedHTMLCommon jp-RenderedMarkdown jp-MarkdownOutput"
data-mime-type="text/markdown">
+<h2
id="q6-zone-statistics-for-trips-within-a-50km-radius-of-the-sedona-city-center">Q6:
Zone statistics for trips within a 50km radius of the Sedona city center<a
class="anchor-link"
href="#q6-zone-statistics-for-trips-within-a-50km-radius-of-the-sedona-city-center">¶</a></h2><p><strong>Real-life
scenario:</strong> Analyze trip patterns in zones within a metropolitan area
around a specific city center.</p>
+<p>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 Se [...]
+<p><strong>Spatial query characteristics tested:</strong></p>
+<ol>
+<li>Polygon containment check (ST_Contains) with bounding box</li>
+<li>Point-in-polygon spatial join (ST_Within)</li>
+</ol>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell"
id="cell-id=f168c302-5187-45f9-8d87-427bbd68c942">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [30]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-9">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="n">sd</span><span class="o">.</span><span class="n">sql</span><span
class="p">(</span><span class="s2">"""</span>
+<span class="s2">SELECT</span>
+<span class="s2"> z.z_zonekey, z.z_name,</span>
+<span class="s2"> COUNT(t.t_tripkey) AS total_pickups, AVG(t.t_totalamount)
AS avg_distance,</span>
+<span class="s2"> AVG(t.t_dropofftime - t.t_pickuptime) AS
avg_duration</span>
+<span class="s2">FROM trip t, zone z</span>
+<span class="s2">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</span>
+<span class="s2"> AND ST_Within(ST_GeomFromWKB(t.t_pickuploc),
ST_GeomFromWKB(z.z_boundary))</span>
+<span class="s2">GROUP BY z.z_zonekey, z.z_name</span>
+<span class="s2">ORDER BY total_pickups DESC, z.z_zonekey ASC</span>
+<span class="s2">"""</span><span class="p">)</span><span
class="o">.</span><span class="n">show</span><span class="p">(</span><span
class="mi">3</span><span class="p">)</span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-9">sd.sql("""
+SELECT
+ z.z_zonekey, z.z_name,
+ COUNT(t.t_tripkey) AS total_pickups, AVG(t.t_totalamount) AS avg_distance,
+ AVG(t.t_dropofftime - t.t_pickuptime) AS avg_duration
+FROM trip t, zone z
+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
+ AND ST_Within(ST_GeomFromWKB(t.t_pickuploc), ST_GeomFromWKB(z.z_boundary))
+GROUP BY z.z_zonekey, z.z_name
+ORDER BY total_pickups DESC, z.z_zonekey ASC
+""").show(3)</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell-outputWrapper">
+<div class="jp-Collapser jp-OutputCollapser jp-Cell-outputCollapser">
+</div>
+<div class="jp-OutputArea jp-Cell-outputArea">
+<div class="jp-OutputArea-child">
+<div class="jp-OutputPrompt jp-OutputArea-prompt"></div>
+<div class="jp-RenderedText jp-OutputArea-output" data-mime-type="text/plain"
tabindex="0">
+<pre>┌───────────┬─────────────────┬───────────────┬───────────────────┬────────────────────────────────┐
+│ z_zonekey ┆ z_name ┆ total_pickups ┆ avg_distance ┆
avg_duration │
+│ int64 ┆ utf8view ┆ int64 ┆ decimal128(19, 9) ┆
duration(millisecond) │
+╞═══════════╪═════════════════╪═══════════════╪═══════════════════╪════════════════════════════════╡
+│ 30084 ┆ Coconino County ┆ 541 ┆ 0.000085323 ┆ 0 days 1
hours 45 mins 16.591… │
+├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+│ 30083 ┆ Yavapai County ┆ 292 ┆ 0.000076643 ┆ 0 days 1
hours 36 mins 43.647… │
+├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+│ 29488 ┆ Gila County ┆ 39 ┆ 0.000065641 ┆ 0 days 1
hours 16 mins 59.769… │
+└───────────┴─────────────────┴───────────────┴───────────────────┴────────────────────────────────┘
+</pre>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell jp-MarkdownCell jp-Notebook-cell"
id="cell-id=fe58f65b-1403-4f91-81a3-e81784bfd3b4">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea"><div class="jp-InputPrompt
jp-InputArea-prompt">
+</div><div class="jp-RenderedHTMLCommon jp-RenderedMarkdown jp-MarkdownOutput"
data-mime-type="text/markdown">
+<h2
id="q7-detect-potential-route-detours-by-comparing-reported-vs-geometric-distances">Q7:
Detect potential route detours by comparing reported vs. geometric distances<a
class="anchor-link"
href="#q7-detect-potential-route-detours-by-comparing-reported-vs-geometric-distances">¶</a></h2><p><strong>Real-life
scenario:</strong> Identify suspicious trips where the reported distance
significantly exceeds the straight-line distance, potentially indicating fare
manipulation.</p>
+<p>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, helping id [...]
+<p><strong>Spatial query characteristics tested:</strong></p>
+<ol>
+<li>Line geometry creation (ST_MakeLine)</li>
+<li>Length calculation (ST_Length)</li>
+<li>Coordinate system conversion and distance calculations</li>
+<li>Ratio-based filtering on geometric vs. reported measurements</li>
+</ol>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell"
id="cell-id=3a8cddde-c04b-49d4-ab69-3d75d9a535ff">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [31]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-10">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="n">sd</span><span class="o">.</span><span class="n">sql</span><span
class="p">(</span><span class="s2">"""</span>
+<span class="s2">WITH trip_lengths AS (</span>
+<span class="s2"> SELECT</span>
+<span class="s2"> t.t_tripkey,</span>
+<span class="s2"> t.t_distance AS reported_distance_m,</span>
+<span class="s2"> ST_Length(</span>
+<span class="s2"> ST_MakeLine(</span>
+<span class="s2"> ST_GeomFromWKB(t.t_pickuploc),</span>
+<span class="s2"> ST_GeomFromWKB(t.t_dropoffloc)</span>
+<span class="s2"> )</span>
+<span class="s2"> ) / 0.000009 AS line_distance_m -- 1 meter = 0.000009
degree</span>
+<span class="s2"> FROM trip t</span>
+<span class="s2">)</span>
+<span class="s2">SELECT</span>
+<span class="s2"> t.t_tripkey,</span>
+<span class="s2"> t.reported_distance_m,</span>
+<span class="s2"> t.line_distance_m,</span>
+<span class="s2"> t.reported_distance_m / NULLIF(t.line_distance_m, 0) AS
detour_ratio</span>
+<span class="s2">FROM trip_lengths t</span>
+<span class="s2">ORDER BY detour_ratio DESC NULLS LAST, reported_distance_m
DESC, t_tripkey ASC</span>
+<span class="s2">"""</span><span class="p">)</span><span
class="o">.</span><span class="n">show</span><span class="p">(</span><span
class="mi">3</span><span class="p">)</span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-10">sd.sql("""
+WITH trip_lengths AS (
+ SELECT
+ t.t_tripkey,
+ t.t_distance AS reported_distance_m,
+ ST_Length(
+ ST_MakeLine(
+ ST_GeomFromWKB(t.t_pickuploc),
+ ST_GeomFromWKB(t.t_dropoffloc)
+ )
+ ) / 0.000009 AS line_distance_m -- 1 meter = 0.000009 degree
+ FROM trip t
+)
+SELECT
+ t.t_tripkey,
+ t.reported_distance_m,
+ t.line_distance_m,
+ 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
+""").show(3)</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell-outputWrapper">
+<div class="jp-Collapser jp-OutputCollapser jp-Cell-outputCollapser">
+</div>
+<div class="jp-OutputArea jp-Cell-outputArea">
+<div class="jp-OutputArea-child">
+<div class="jp-OutputPrompt jp-OutputArea-prompt"></div>
+<div class="jp-RenderedText jp-OutputArea-output" data-mime-type="text/plain"
tabindex="0">
+<pre>┌───────────┬─────────────────────┬────────────────────┬──────────────────────┐
+│ t_tripkey ┆ reported_distance_m ┆ line_distance_m ┆ detour_ratio │
+│ int64 ┆ decimal128(15, 5) ┆ float64 ┆ float64 │
+╞═══════════╪═════════════════════╪════════════════════╪══════════════════════╡
+│ 4688563 ┆ 0.00010 ┆ 11111.126052681648 ┆ 8.99998789734414e-9 │
+├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+│ 2380123 ┆ 0.00010 ┆ 11111.126095065882 ┆ 8.999987863013003e-9 │
+├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+│ 3077131 ┆ 0.00010 ┆ 11111.126138581423 ┆ 8.99998782776551e-9 │
+└───────────┴─────────────────────┴────────────────────┴──────────────────────┘
+</pre>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell jp-MarkdownCell jp-Notebook-cell"
id="cell-id=3040b853-100b-4c9d-8447-5b9bf4c4a895">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea"><div class="jp-InputPrompt
jp-InputArea-prompt">
+</div><div class="jp-RenderedHTMLCommon jp-RenderedMarkdown jp-MarkdownOutput"
data-mime-type="text/markdown">
+<h2 id="q8-count-nearby-pickups-for-each-building-within-a-500m-radius">Q8:
Count nearby pickups for each building within a 500m radius<a
class="anchor-link"
href="#q8-count-nearby-pickups-for-each-building-within-a-500m-radius">¶</a></h2><p><strong>Real-life
scenario:</strong> Count how many trips start within 500 meters of each
building.</p>
+<p>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 dr [...]
+<p><strong>Spatial query characteristics tested:</strong></p>
+<ol>
+<li>Distance spatial join between points and polygons</li>
+<li>Aggregation on spatial join result</li>
+</ol>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell"
id="cell-id=58ac6e6a-2e3d-4dd7-bb03-1b16f55fc186">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [32]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-11">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="n">sd</span><span class="o">.</span><span class="n">sql</span><span
class="p">(</span><span class="s2">"""</span>
+<span class="s2">SELECT b.b_buildingkey, b.b_name, COUNT(*) AS
nearby_pickup_count</span>
+<span class="s2">FROM trip t</span>
+<span class="s2">JOIN building b</span>
+<span class="s2">ON ST_DWithin(ST_GeomFromWKB(t.t_pickuploc),
ST_GeomFromWKB(b.b_boundary), 0.0045) -- ~500m</span>
+<span class="s2">GROUP BY b.b_buildingkey, b.b_name</span>
+<span class="s2">ORDER BY nearby_pickup_count DESC, b.b_buildingkey ASC</span>
+<span class="s2">"""</span><span class="p">)</span><span
class="o">.</span><span class="n">show</span><span class="p">(</span><span
class="mi">3</span><span class="p">)</span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-11">sd.sql("""
+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
+""").show(3)</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell-outputWrapper">
+<div class="jp-Collapser jp-OutputCollapser jp-Cell-outputCollapser">
+</div>
+<div class="jp-OutputArea jp-Cell-outputArea">
+<div class="jp-OutputArea-child">
+<div class="jp-OutputPrompt jp-OutputArea-prompt"></div>
+<div class="jp-RenderedText jp-OutputArea-output" data-mime-type="text/plain"
tabindex="0">
+<pre>┌───────────────┬──────────┬─────────────────────┐
+│ b_buildingkey ┆ b_name ┆ nearby_pickup_count │
+│ int64 ┆ utf8view ┆ int64 │
+╞═══════════════╪══════════╪═════════════════════╡
+│ 3779 ┆ linen ┆ 42 │
+├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+│ 19135 ┆ misty ┆ 36 │
+├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+│ 4416 ┆ sienna ┆ 26 │
+└───────────────┴──────────┴─────────────────────┘
+</pre>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell jp-MarkdownCell jp-Notebook-cell"
id="cell-id=eee8c5bc-1af6-484c-af91-b6c266ecb7c1">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea"><div class="jp-InputPrompt
jp-InputArea-prompt">
+</div><div class="jp-RenderedHTMLCommon jp-RenderedMarkdown jp-MarkdownOutput"
data-mime-type="text/markdown">
+<h2 id="q9-building-conflation-duplicateoverlap-detection-via-iou">Q9:
Building Conflation (duplicate/overlap detection via IoU)<a class="anchor-link"
href="#q9-building-conflation-duplicateoverlap-detection-via-iou">¶</a></h2><p><strong>Real-life
scenario:</strong> Detect duplicate or overlapping building footprints in GIS
datasets to identify data quality issues.</p>
+<p>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 structures [...]
+<p><strong>Spatial query characteristics tested:</strong></p>
+<ol>
+<li>Self-join with spatial intersection (ST_Intersects)</li>
+<li>Area calculations (ST_Area)</li>
+<li>Geometric intersection operations (ST_Intersection)</li>
+<li>Complex geometric ratio calculations (IoU - Intersection over Union)</li>
+</ol>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell"
id="cell-id=baae3710-6008-47cf-a31f-db2813f9fca3">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [34]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-12">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="n">sd</span><span class="o">.</span><span class="n">sql</span><span
class="p">(</span><span class="s2">"""</span>
+<span class="s2">WITH b1 AS (</span>
+<span class="s2"> SELECT b_buildingkey AS id, ST_GeomFromWKB(b_boundary) AS
geom</span>
+<span class="s2"> FROM building</span>
+<span class="s2">),</span>
+<span class="s2"> b2 AS (</span>
+<span class="s2"> SELECT b_buildingkey AS id,
ST_GeomFromWKB(b_boundary) AS geom</span>
+<span class="s2"> FROM building</span>
+<span class="s2"> ),</span>
+<span class="s2"> pairs AS (</span>
+<span class="s2"> SELECT</span>
+<span class="s2"> b1.id AS building_1,</span>
+<span class="s2"> b2.id AS building_2,</span>
+<span class="s2"> ST_Area(b1.geom) AS area1,</span>
+<span class="s2"> ST_Area(b2.geom) AS area2,</span>
+<span class="s2"> ST_Area(ST_Intersection(b1.geom, b2.geom)) AS
overlap_area</span>
+<span class="s2"> FROM b1</span>
+<span class="s2"> JOIN b2</span>
+<span class="s2"> ON b1.id < b2.id</span>
+<span class="s2"> AND ST_Intersects(b1.geom,
b2.geom)</span>
+<span class="s2"> )</span>
+<span class="s2">SELECT</span>
+<span class="s2"> building_1,</span>
+<span class="s2"> building_2,</span>
+<span class="s2"> area1,</span>
+<span class="s2"> area2,</span>
+<span class="s2"> overlap_area,</span>
+<span class="s2"> CASE</span>
+<span class="s2"> WHEN overlap_area = 0 THEN 0.0</span>
+<span class="s2"> WHEN (area1 + area2 - overlap_area) = 0 THEN 1.0</span>
+<span class="s2"> ELSE overlap_area / (area1 + area2 -
overlap_area)</span>
+<span class="s2"> END AS iou</span>
+<span class="s2">FROM pairs</span>
+<span class="s2">ORDER BY iou DESC, building_1 ASC, building_2 ASC</span>
+<span class="s2">"""</span><span class="p">)</span><span
class="o">.</span><span class="n">show</span><span class="p">(</span><span
class="mi">3</span><span class="p">)</span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-12">sd.sql("""
+WITH b1 AS (
+ SELECT b_buildingkey AS id, ST_GeomFromWKB(b_boundary) AS geom
+ FROM building
+),
+ b2 AS (
+ SELECT b_buildingkey AS id, ST_GeomFromWKB(b_boundary) AS geom
+ FROM building
+ ),
+ pairs AS (
+ SELECT
+ b1.id AS building_1,
+ b2.id AS building_2,
+ ST_Area(b1.geom) AS area1,
+ ST_Area(b2.geom) AS area2,
+ ST_Area(ST_Intersection(b1.geom, b2.geom)) AS overlap_area
+ FROM b1
+ JOIN b2
+ ON b1.id < b2.id
+ AND ST_Intersects(b1.geom, b2.geom)
+ )
+SELECT
+ building_1,
+ building_2,
+ area1,
+ area2,
+ overlap_area,
+ CASE
+ WHEN overlap_area = 0 THEN 0.0
+ WHEN (area1 + area2 - overlap_area) = 0 THEN 1.0
+ ELSE overlap_area / (area1 + area2 - overlap_area)
+ END AS iou
+FROM pairs
+ORDER BY iou DESC, building_1 ASC, building_2 ASC
+""").show(3)</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell-outputWrapper">
+<div class="jp-Collapser jp-OutputCollapser jp-Cell-outputCollapser">
+</div>
+<div class="jp-OutputArea jp-Cell-outputArea">
+<div class="jp-OutputArea-child">
+<div class="jp-OutputPrompt jp-OutputArea-prompt"></div>
+<div class="jp-RenderedText jp-OutputArea-output" data-mime-type="text/plain"
tabindex="0">
+<pre>┌────────────┬────────────┬───┬───────────────────────┬────────────────────┐
+│ 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 │
+└────────────┴────────────┴───┴───────────────────────┴────────────────────┘
+</pre>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell jp-MarkdownCell jp-Notebook-cell"
id="cell-id=5ba7497e-cff3-47f6-9834-3beed648fd68">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea"><div class="jp-InputPrompt
jp-InputArea-prompt">
+</div><div class="jp-RenderedHTMLCommon jp-RenderedMarkdown jp-MarkdownOutput"
data-mime-type="text/markdown">
+<h2 id="q10-zone-statistics-for-trips-starting-within-each-zone">Q10: Zone
statistics for trips starting within each zone<a class="anchor-link"
href="#q10-zone-statistics-for-trips-starting-within-each-zone">¶</a></h2><p><strong>Real-life
scenario:</strong> Analyze trip patterns and performance metrics for each
administrative zone (like city districts or neighborhoods).</p>
+<p>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 area [...]
+<p><strong>Spatial query characteristics tested:</strong></p>
+<ol>
+<li>Point-in-polygon spatial join (ST_Within)</li>
+<li>Aggregation with multiple metrics (average duration, distance, count)</li>
+<li>LEFT JOIN to include zones with no trips</li>
+</ol>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell"
id="cell-id=bd264220-b2dd-49a1-a8f9-0267ffb1371c">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [35]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-13">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="n">sd</span><span class="o">.</span><span class="n">sql</span><span
class="p">(</span><span class="s2">"""</span>
+<span class="s2">SELECT</span>
+<span class="s2"> z.z_zonekey, z.z_name AS pickup_zone, AVG(t.t_dropofftime
- t.t_pickuptime) AS avg_duration,</span>
+<span class="s2"> AVG(t.t_distance) AS avg_distance, COUNT(t.t_tripkey) AS
num_trips</span>
+<span class="s2">FROM zone z LEFT JOIN trip t ON
ST_Within(ST_GeomFromWKB(t.t_pickuploc), ST_GeomFromWKB(z.z_boundary))</span>
+<span class="s2">GROUP BY z.z_zonekey, z.z_name</span>
+<span class="s2">ORDER BY avg_duration DESC NULLS LAST, z.z_zonekey ASC</span>
+<span class="s2">"""</span><span class="p">)</span><span
class="o">.</span><span class="n">show</span><span class="p">(</span><span
class="mi">3</span><span class="p">)</span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-13">sd.sql("""
+SELECT
+ z.z_zonekey, z.z_name AS pickup_zone, AVG(t.t_dropofftime -
t.t_pickuptime) AS avg_duration,
+ AVG(t.t_distance) AS avg_distance, COUNT(t.t_tripkey) AS num_trips
+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
+""").show(3)</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell-outputWrapper">
+<div class="jp-Collapser jp-OutputCollapser jp-Cell-outputCollapser">
+</div>
+<div class="jp-OutputArea jp-Cell-outputArea">
+<div class="jp-OutputArea-child">
+<div class="jp-OutputPrompt jp-OutputArea-prompt"></div>
+<div class="jp-RenderedText jp-OutputArea-output" data-mime-type="text/plain"
tabindex="0">
+<pre>┌───────────┬────────────────┬────────────────────────────────────┬───────────────────┬───────────┐
+│ z_zonekey ┆ pickup_zone ┆ avg_duration ┆
avg_distance ┆ num_trips │
+│ int64 ┆ utf8view ┆ duration(millisecond) ┆
decimal128(19, 9) ┆ int64 │
+╞═══════════╪════════════════╪════════════════════════════════════╪═══════════════════╪═══════════╡
+│ 31558 ┆ 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 │
+├╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌┤
+│ 59928 ┆ Ndhiwa ┆ 2 days 4 hours 19 mins 39.000 secs ┆
0.001040000 ┆ 1 │
+└───────────┴────────────────┴────────────────────────────────────┴───────────────────┴───────────┘
+</pre>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell jp-MarkdownCell jp-Notebook-cell"
id="cell-id=cf6050f4-3968-4fd9-a701-1c43d81fa4f5">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea"><div class="jp-InputPrompt
jp-InputArea-prompt">
+</div><div class="jp-RenderedHTMLCommon jp-RenderedMarkdown jp-MarkdownOutput"
data-mime-type="text/markdown">
+<h2 id="q11-count-trips-that-cross-between-different-zones">Q11: Count trips
that cross between different zones<a class="anchor-link"
href="#q11-count-trips-that-cross-between-different-zones">¶</a></h2><p><strong>Real-life
scenario:</strong> Identify inter-district or inter-city trips to understand
cross-boundary travel patterns.</p>
+<p>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.</p>
+<p><strong>Spatial query characteristics tested:</strong></p>
+<ol>
+<li>Multiple point-in-polygon spatial joins</li>
+<li>Filtering based on spatial relationship results</li>
+</ol>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell"
id="cell-id=64bf9632-b4ca-4862-9443-41e90b82bee4">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [36]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-14">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="n">sd</span><span class="o">.</span><span class="n">sql</span><span
class="p">(</span><span class="s2">"""</span>
+<span class="s2">SELECT COUNT(*) AS cross_zone_trip_count</span>
+<span class="s2">FROM</span>
+<span class="s2"> trip t</span>
+<span class="s2"> JOIN zone pickup_zone ON
ST_Within(ST_GeomFromWKB(t.t_pickuploc),
ST_GeomFromWKB(pickup_zone.z_boundary))</span>
+<span class="s2"> JOIN zone dropoff_zone ON
ST_Within(ST_GeomFromWKB(t.t_dropoffloc),
ST_GeomFromWKB(dropoff_zone.z_boundary))</span>
+<span class="s2">WHERE pickup_zone.z_zonekey != dropoff_zone.z_zonekey</span>
+<span class="s2">"""</span><span class="p">)</span><span
class="o">.</span><span class="n">show</span><span class="p">(</span><span
class="mi">3</span><span class="p">)</span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-14">sd.sql("""
+SELECT COUNT(*) AS cross_zone_trip_count
+FROM
+ trip t
+ JOIN zone pickup_zone ON ST_Within(ST_GeomFromWKB(t.t_pickuploc),
ST_GeomFromWKB(pickup_zone.z_boundary))
+ JOIN zone dropoff_zone ON ST_Within(ST_GeomFromWKB(t.t_dropoffloc),
ST_GeomFromWKB(dropoff_zone.z_boundary))
+WHERE pickup_zone.z_zonekey != dropoff_zone.z_zonekey
+""").show(3)</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell-outputWrapper">
+<div class="jp-Collapser jp-OutputCollapser jp-Cell-outputCollapser">
+</div>
+<div class="jp-OutputArea jp-Cell-outputArea">
+<div class="jp-OutputArea-child">
+<div class="jp-OutputPrompt jp-OutputArea-prompt"></div>
+<div class="jp-RenderedText jp-OutputArea-output" data-mime-type="text/plain"
tabindex="0">
+<pre>┌───────────────────────┐
+│ cross_zone_trip_count │
+│ int64 │
+╞═══════════════════════╡
+│ 176391 │
+└───────────────────────┘
+</pre>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+<div class="jp-Cell jp-MarkdownCell jp-Notebook-cell"
id="cell-id=386e6a72-c825-4341-ab4a-8ea59b7a9439">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea"><div class="jp-InputPrompt
jp-InputArea-prompt">
+</div><div class="jp-RenderedHTMLCommon jp-RenderedMarkdown jp-MarkdownOutput"
data-mime-type="text/markdown">
+<h2
id="q12-find-five-nearest-buildings-to-each-trip-pickup-location-using-knn-join">Q12:
Find five nearest buildings to each trip pickup location using KNN join<a
class="anchor-link"
href="#q12-find-five-nearest-buildings-to-each-trip-pickup-location-using-knn-join">¶</a></h2><p><strong>Real-life
scenario:</strong> Identify the closest landmarks or buildings to each trip
start point for location context and navigation.</p>
+<p>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 [...]
+<p><strong>Spatial query characteristics tested:</strong></p>
+<ol>
+<li>K-nearest neighbor (KNN) spatial join</li>
+<li>Distance calculations between points and polygons</li>
+<li>Ranking and limiting results based on spatial proximity</li>
+</ol>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell jp-mod-noOutputs">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell jp-mod-noOutputs"
id="cell-id=0878af06-c518-44f3-9b45-83651cc37d04">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [ ]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-15">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span><span
class="n">sd</span><span class="o">.</span><span class="n">sql</span><span
class="p">(</span><span class="s2">"""</span>
+<span class="s2">WITH trip_with_geom AS (</span>
+<span class="s2"> SELECT t_tripkey, t_pickuploc,
ST_GeomFromWKB(t_pickuploc) as pickup_geom</span>
+<span class="s2"> FROM trip</span>
+<span class="s2">),</span>
+<span class="s2">building_with_geom AS (</span>
+<span class="s2"> SELECT b_buildingkey, b_name, b_boundary,
ST_GeomFromWKB(b_boundary) as boundary_geom</span>
+<span class="s2"> FROM building</span>
+<span class="s2">)</span>
+<span class="s2">SELECT</span>
+<span class="s2"> t.t_tripkey,</span>
+<span class="s2"> t.t_pickuploc,</span>
+<span class="s2"> b.b_buildingkey,</span>
+<span class="s2"> b.b_name AS building_name,</span>
+<span class="s2"> ST_Distance(t.pickup_geom, b.boundary_geom) AS
distance_to_building</span>
+<span class="s2">FROM trip_with_geom t JOIN building_with_geom b</span>
+<span class="s2">ON ST_KNN(t.pickup_geom, b.boundary_geom, 5, FALSE)</span>
+<span class="s2">ORDER BY distance_to_building ASC, b.b_buildingkey ASC</span>
+<span class="s2">"""</span><span class="p">)</span><span
class="o">.</span><span class="n">show</span><span class="p">(</span><span
class="mi">3</span><span class="p">)</span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-15">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
+)
+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
+""").show(3)</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div><div class="jp-Cell jp-CodeCell jp-Notebook-cell jp-mod-noOutputs">
+<div class="jp-Cell jp-CodeCell jp-Notebook-cell jp-mod-noOutputs"
id="cell-id=6bdfeffb-5015-4f34-ae73-a8e59af7d4f4">
+<div class="jp-Cell-inputWrapper" tabindex="0">
+<div class="jp-Collapser jp-InputCollapser jp-Cell-inputCollapser">
+</div>
+<div class="jp-InputArea jp-Cell-inputArea">
+<div class="jp-InputPrompt jp-InputArea-prompt">In [ ]:</div><div
class="jp-CodeMirrorEditor jp-Editor jp-InputArea-editor" data-type="inline">
+<div class="CodeMirror cm-s-jupyter">
+<div class="zeroclipboard-container">
+<clipboard-copy ,="" aria-label="Copy to Clipboard" for="cell-16">
+<div>
+<span class="notice" hidden="">Copied!</span>
+<svg aria-hidden="true" class="clipboard-copy-icon" data-view-component="true"
height="20" version="1.1" viewBox="0 0 16 16" width="20">
+<path d="M0 6.75C0 5.784.784 5 1.75 5h1.5a.75.75 0 010 1.5h-1.5a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-1.5a.75.75 0 011.5
0v1.5A1.75 1.75 0 019.25 16h-7.5A1.75 1.75 0 010 14.25v-7.5z"
fill="currentColor" fill-rule="evenodd"></path>
+<path d="M5 1.75C5 .784 5.784 0 6.75 0h7.5C15.216 0 16 .784 16 1.75v7.5A1.75
1.75 0 0114.25 11h-7.5A1.75 1.75 0 015 9.25v-7.5zm1.75-.25a.25.25 0
00-.25.25v7.5c0 .138.112.25.25.25h7.5a.25.25 0 00.25-.25v-7.5a.25.25 0
00-.25-.25h-7.5z" fill="currentColor" fill-rule="evenodd"></path>
+</svg>
+</div>
+</clipboard-copy>
+</div>
+<div class="highlight-ipynb hl-python"><pre><span></span>
+</pre></div>
+<div class="clipboard-copy-txt" id="cell-16"></div>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div>
+</div> <!-- jp-Notebook -->
+</div> <!-- jupyter-wrapper -->
+
+ <style>
+ ['pre { line-height: 125%; }\ntd.linenos .normal { color: inherit;
background-color: transparent; padding-left: 5px; padding-right: 5px;
}\nspan.linenos { color: inherit; background-color: transparent; padding-left:
5px; padding-right: 5px; }\ntd.linenos .special { color: #000000;
background-color: #ffffc0; padding-left: 5px; padding-right: 5px;
}\nspan.linenos.special { color: #000000; background-color: #ffffc0;
padding-left: 5px; padding-right: 5px; }\n.highlight-ipynb .hll { b [...]
+ </style>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <aside class="md-source-file">
+
+
+ <span class="md-source-file__fact">
+ <span class="md-icon" title="Last update">
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 24 24"><path d="M21
13.1c-.1 0-.3.1-.4.2l-1 1 2.1 2.1 1-1c.2-.2.2-.6
0-.8l-1.3-1.3c-.1-.1-.2-.2-.4-.2m-1.9 1.8-6.1 6V23h2.1l6.1-6.1zM12.5 7v5.2l4
2.4-1 1L11 13V7zM11 21.9c-5.1-.5-9-4.8-9-9.9C2 6.5 6.5 2 12 2c5.3 0 9.6 4.1 10
9.3-.3-.1-.6-.2-1-.2s-.7.1-1 .2C19.6 7.2 16.2 4 12 4c-4.4 0-8 3.6-8 8 0 4.1 3.1
7.5 7.1 7.9l-.1.2z"></path></svg>
+ </span>
+ <span class="git-revision-date-localized-plugin
git-revision-date-localized-plugin-datetime" title="September 22, 2025 21:43:24
UTC">September 22, 2025 21:43:24</span>
+ </span>
+
+
+
+
+
+ </aside>
+
+
+
+
+
+
+ </article>
+ </div>
+
+
+<script>var
target=document.getElementById(location.hash.slice(1));target&&target.name&&(target.checked=target.name.startsWith("__tabbed_"))</script>
+ </div>
+
+ <button type="button" class="md-top md-icon" data-md-component="top"
hidden>
+
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 24 24"><path d="M13
20h-2V8l-5.5 5.5-1.42-1.42L12 4.16l7.92 7.92-1.42 1.42L13 8z"></path></svg>
+ Back to top
+</button>
+
+ </main>
+
+ <footer class="md-footer">
+
+
+
+ <nav class="md-footer__inner md-grid" aria-label="Footer">
+
+
+ <a href="../datasets-generators/" class="md-footer__link
md-footer__link--prev" aria-label="Previous: Datasets and Generators">
+ <div class="md-footer__button md-icon">
+
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 24
24"><path d="M20 11v2H8l5.5 5.5-1.42 1.42L4.16 12l7.92-7.92L13.5 5.5 8
11z"></path></svg>
+ </div>
+ <div class="md-footer__title">
+ <span class="md-footer__direction">
+ Previous
+ </span>
+ <div class="md-ellipsis">
+ Datasets and Generators
+ </div>
+ </div>
+ </a>
+
+
+ </nav>
+
+
+ <div class="md-footer-meta md-typeset">
+ <div class="md-footer-meta__inner md-grid">
+ <div class="md-copyright">
+
+ <div class="md-copyright__highlight">
+ Copyright © 2025 The Apache Software Foundation. Apache Sedona, Sedona,
Apache, the Apache feather logo, and the Apache Sedona project logo are either
registered trademarks or trademarks of The Apache Software Foundation in the
United States and other countries. All other marks mentioned may be trademarks
or registered trademarks of their respective owners. Please visit <a
href="https://www.apache.org/";>Apache Software Foundation</a> for more
details.<img referrerpolicy="no-referre [...]
+ </div>
+
+
+ Made with
+ <a href="https://squidfunk.github.io/mkdocs-material/"; target="_blank"
rel="noopener">
+ Material for MkDocs
+ </a>
+
+</div>
+
+
+<div class="md-social">
+
+
+
+
+
+
+
+
+ <a href="https://github.com/apache/spatialbench"; target="_blank"
rel="noopener" title="github.com" class="md-social__link">
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 512 512"><!--! Font
Awesome Free 7.0.0 by @fontawesome - https://fontawesome.com License -
https://fontawesome.com/license/free (Icons: CC BY 4.0, Fonts: SIL OFL 1.1,
Code: MIT License) Copyright 2025 Fonticons, Inc.--><path d="M173.9 397.4c0
2-2.3 3.6-5.2 3.6-3.3.3-5.6-1.3-5.6-3.6 0-2 2.3-3.6 5.2-3.6 3-.3 5.6 1.3 5.6
3.6m-31.1-4.5c-.7 2 1.3 4.3 4.3 4.9 2.6 1 5.6 0
6.2-2s-1.3-4.3-4.3-5.2c-2.6-.7-5.5.3-6.2 2.3m44.2-1.7c-2.9.7-4.9 [...]
+ </a>
+
+
+
+
+
+
+
+
+ <a href="https://twitter.com/ApacheSedona"; target="_blank" rel="noopener"
title="twitter.com" class="md-social__link">
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 512 512"><!--! Font
Awesome Free 7.0.0 by @fontawesome - https://fontawesome.com License -
https://fontawesome.com/license/free (Icons: CC BY 4.0, Fonts: SIL OFL 1.1,
Code: MIT License) Copyright 2025 Fonticons, Inc.--><path d="M459.4 151.7c.3
4.5.3 9.1.3 13.6 0 138.7-105.6 298.6-298.6 298.6-59.5 0-114.7-17.2-161.1-47.1
8.4 1 16.6 1.3 25.3 1.3 49.1 0 94.2-16.6 130.3-44.8-46.1-1-84.8-31.2-98.1-72.8
6.5 1 13 1.6 19.8 1.6 9.4 0 18.8 [...]
+ </a>
+
+
+
+
+
+
+
+
+ <a href="https://discord.gg/9A3k5dEBsY"; target="_blank" rel="noopener"
title="discord.gg" class="md-social__link">
+ <svg xmlns="http://www.w3.org/2000/svg"; viewBox="0 0 576 512"><!--! Font
Awesome Free 7.0.0 by @fontawesome - https://fontawesome.com License -
https://fontawesome.com/license/free (Icons: CC BY 4.0, Fonts: SIL OFL 1.1,
Code: MIT License) Copyright 2025 Fonticons, Inc.--><path d="M492.5
69.8c-.2-.3-.4-.6-.8-.7-38.1-17.5-78.4-30-119.7-37.1-.4-.1-.8
0-1.1.1s-.6.4-.8.8c-5.5 9.9-10.5 20.2-14.9 30.6-44.6-6.8-89.9-6.8-134.4
0-4.5-10.5-9.5-20.7-15.1-30.6-.2-.3-.5-.6-.8-.8s-.7-.2-1.1-.2C16 [...]
+ </a>
+
+</div>
+
+ </div>
+ </div>
+</footer>
+
+ </div>
+ <div class="md-dialog" data-md-component="dialog">
+ <div class="md-dialog__inner md-typeset"></div>
+ </div>
+
+
+
+
+ <script id="__config" type="application/json">{"base": "..", "features":
["content.code.copy", "content.action.edit", "search.highlight",
"search.share", "search.suggest", "navigation.footer", "navigation.instant",
"navigation.top", "navigation.sections", "navigation.tabs",
"navigation.tabs.sticky"], "search":
"../assets/javascripts/workers/search.973d3a69.min.js", "tags": null,
"translations": {"clipboard.copied": "Copied to clipboard", "clipboard.copy":
"Copy to clipboard", "sear [...]
+
+
+ <script src="../assets/javascripts/bundle.f55a23d4.min.js"></script>
+
+ <script src="../assets/javascripts/main.min.js"></script>
+
+
+
+<script id="init-glightbox">const lightbox = GLightbox({"touchNavigation":
true, "loop": false, "zoomable": true, "draggable": true, "openEffect": "zoom",
"closeEffect": "zoom", "slideEffect": "slide"});
+document$.subscribe(()=>{ lightbox.reload(); });
+</script></body></html>
\ No newline at end of file
diff --git a/requirements.txt b/requirements.txt
index 500f80c..13a8918 100644
--- a/requirements.txt
+++ b/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/search/search_index.json b/search/search_index.json
index cbd758a..a6810cd 100644
--- a/search/search_index.json
+++ b/search/search_index.json
@@ -1 +1 @@
-{"config":{"lang":["en"],"separator":"[\\s\\-]+","pipeline":["stopWordFilter"]},"docs":[{"location":"","title":"Sedona
SpatialBench","text":"<p>SpatialBench is a benchmark for assessing geospatial
SQL analytics query performance across database systems.</p> <p>SpatialBench
makes it easy to run spatial benchmarks on a realistic dataset with any query
engine.</p> <p>The methodology is unbiased and the benchmarks in any
environment to compare relative performance between runtimes.</p>"},{"l [...]
\ No newline at end of file
+{"config":{"lang":["en"],"separator":"[\\s\\-]+","pipeline":["stopWordFilter"]},"docs":[{"location":"","title":"Sedona
SpatialBench","text":"<p>SpatialBench is a benchmark for assessing geospatial
SQL analytics query performance across database systems.</p> <p>SpatialBench
makes it easy to run spatial benchmarks on a realistic dataset with any query
engine.</p> <p>The methodology is unbiased and the benchmarks in any
environment to compare relative performance between runtimes.</p>"},{"l [...]
\ No newline at end of file
