This is an automated email from the ASF dual-hosted git repository.
jiayu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/sedona-db.git
The following commit(s) were added to refs/heads/main by this push:
new 9bc6ffe docs: crs tracking example (#81)
9bc6ffe is described below
commit 9bc6ffed88a39b71855330850989a19225f2cff4
Author: Matthew Powers <[email protected]>
AuthorDate: Mon Sep 22 00:01:07 2025 -0400
docs: crs tracking example (#81)
---
docs/crs-examples.ipynb | 481 ++++++++++++++++++++++++++++++++++++++++++++++++
docs/crs-examples.md | 334 +++++++++++++++++++++++++++++++++
mkdocs.yml | 2 +-
3 files changed, 816 insertions(+), 1 deletion(-)
diff --git a/docs/crs-examples.ipynb b/docs/crs-examples.ipynb
new file mode 100644
index 0000000..74192a3
--- /dev/null
+++ b/docs/crs-examples.ipynb
@@ -0,0 +1,481 @@
+{
+ "cells": [
+ {
+ "cell_type": "markdown",
+ "id": "91910e50-a5ae-4d5a-a431-62ac5fbc11ca",
+ "metadata": {},
+ "source": [
+ "# Coordinate Reference System (CRS) Examples\n",
+ "\n",
+ "This example demonstrates how one table with an EPSG 4326 CRS cannot be
joined with another table that uses EPSG 3857."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 2,
+ "id": "be8ffe47-dc89-4842-bb1e-1e8640afffc3",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "import sedonadb\n",
+ "\n",
+ "sd = sedonadb.connect()"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "54b48173-6be0-4827-ac42-1439eb31e9f7",
+ "metadata": {},
+ "source": [
+ "Read a table with a geometry column that uses EPSG 4326.\n",
+ "\n",
+ "Note how SedonaDB reads the CRS specified in the Parquet file."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 2,
+ "id": "143f00d5-6878-4dab-a82c-c9fb4dbfaf00",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "countries = sd.read_parquet(\n",
+ "
\"https://raw.githubusercontent.com/geoarrow/geoarrow-data/v0.2.0/natural-earth/files/natural-earth_countries_geo.parquet\"\n";,
+ ")"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 3,
+ "id": "8ef94b7b-b65d-4da5-9443-3253e84e2e7f",
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "SedonaSchema with 3 fields:\n",
+ " name: Utf8View\n",
+ " continent: Utf8View\n",
+ " geometry: wkb_view <epsg:4326>"
+ ]
+ },
+ "execution_count": 3,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "countries.schema"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 4,
+ "id": "12d94c4f-5e7f-47c6-b5cc-3a3363bbc290",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "cities = sd.sql(\"\"\"\n",
+ "SELECT city, ST_SetSRID(ST_GeomFromText(wkt), 3857) AS geometry FROM
(VALUES\n",
+ " ('New York', 'POINT(-8238310.24 4969803.34)'),\n",
+ " ('Los Angeles', 'POINT(-13153204.78 4037636.04)'),\n",
+ " ('Chicago', 'POINT(-9757148.04 5138517.44)'))\n",
+ "AS t(city, wkt)\"\"\")"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 5,
+ "id": "36e53438-c2d2-444e-9f34-d391f0f3f588",
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "SedonaSchema with 2 fields:\n",
+ " city: Utf8\n",
+ " geometry: wkb <epsg:3857>"
+ ]
+ },
+ "execution_count": 5,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "cities.schema"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 6,
+ "id": "62c87571-50aa-4f57-a7dd-4afa3210320a",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "cities.to_view(\"cities\", overwrite=True)\n",
+ "countries.to_view(\"countries\", overwrite=True)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "561b3c8c-4952-4fa7-9fe1-3fa0522b0d9f",
+ "metadata": {},
+ "source": [
+ "## Join with mismatched CRSs\n",
+ "\n",
+ "The cities and countries tables have different CRSs.\n",
+ "\n",
+ "The cities table uses EPSG:3857 and the countries table uses
EPSG:4326.\n",
+ "\n",
+ "Let's confirm that the code errors out if we try to join the mismatched
tables."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 7,
+ "id": "906bad37-4f3f-4028-82b4-487fabe5957f",
+ "metadata": {},
+ "outputs": [
+ {
+ "ename": "SedonaError",
+ "evalue": "type_coercion\ncaused by\nError during planning: Mismatched
CRS arguments: epsg:3857 vs epsg:4326\nUse ST_Transform() or ST_SetSRID() to
ensure arguments are compatible.",
+ "output_type": "error",
+ "traceback": [
+
"\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
+ "\u001b[0;31mSedonaError\u001b[0m
Traceback (most recent call last)",
+ "Cell \u001b[0;32mIn[7], line 6\u001b[0m\n\u001b[1;32m 1\u001b[0m
\u001b[38;5;66;03m# join doesn't work when CRSs don't
match\u001b[39;00m\n\u001b[1;32m 2\u001b[0m
\u001b[43msd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43msql\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\"\"\u001b[39;49m\n\u001b[1;32m
3\u001b[0m \u001b[38;5;124;43mselect * from
cities\u001b[39;49m\n\u001b[1;32m 4\u001b[0m \u001b[38;5;124;43mjoin
countries\u001b[39;49m\n\u001b[1;32m [...]
+ "File
\u001b[0;32m/opt/miniconda3/lib/python3.12/site-packages/sedonadb/dataframe.py:297\u001b[0m,
in \u001b[0;36mDataFrame.show\u001b[0;34m(self, limit, width,
ascii)\u001b[0m\n\u001b[1;32m 272\u001b[0m
\u001b[38;5;250m\u001b[39m\u001b[38;5;124;03m\"\"\"Print the first limit rows
to the console\u001b[39;00m\n\u001b[1;32m 273\u001b[0m \n\u001b[1;32m
274\u001b[0m \u001b[38;5;124;03mArgs:\u001b[39;00m\n\u001b[0;32m
(...)\u001b[0m\n\u001b[1;32m 294\u001b[0m \n\u001b[1;32 [...]
+ "\u001b[0;31mSedonaError\u001b[0m: type_coercion\ncaused by\nError
during planning: Mismatched CRS arguments: epsg:3857 vs epsg:4326\nUse
ST_Transform() or ST_SetSRID() to ensure arguments are compatible."
+ ]
+ }
+ ],
+ "source": [
+ "# join doesn't work when CRSs don't match\n",
+ "sd.sql(\"\"\"\n",
+ "select * from cities\n",
+ "join countries\n",
+ "where ST_Intersects(cities.geometry, countries.geometry)\n",
+ "\"\"\").show()"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "41e6f59f-5217-40b2-b05a-9c95eae29df8",
+ "metadata": {},
+ "source": [
+ "## Convert CRS and then join\n",
+ "\n",
+ "Let's convert the cities table to use EPSG:4326 and then perform the join
with the two tables once they have matching CRSs."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 13,
+ "id": "122857c1-f68d-4037-9787-54c20706e60f",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "# update cities to use 4326\n",
+ "cities = sd.sql(\"\"\"\n",
+ "SELECT city, ST_Transform(geometry, 'EPSG:4326') as geometry\n",
+ "FROM cities\n",
+ "\"\"\")"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 14,
+ "id": "947e085c-62a4-4315-b155-007a95156964",
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "SedonaSchema with 2 fields:\n",
+ " city: Utf8\n",
+ " geometry: wkb <ogc:crs84>"
+ ]
+ },
+ "execution_count": 14,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "cities.schema"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 15,
+ "id": "3bcbaf7a-ec40-4b7e-85c2-db5ef1e3232e",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "cities.to_view(\"cities\", overwrite=True)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 17,
+ "id": "819c8d04-fa03-4ef8-aecb-e79d48f0b820",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+
"┌─────────────┬──────────────────────┬──────────────────────┬───────────────┬──────────────────────┐\n",
+ "│ city ┆ geometry ┆ name ┆
continent ┆ geometry │\n",
+ "│ utf8 ┆ geometry ┆ utf8view ┆
utf8view ┆ geometry │\n",
+
"╞═════════════╪══════════════════════╪══════════════════════╪═══════════════╪══════════════════════╡\n",
+ "│ New York ┆ POINT(-74.006000039… ┆ United States of Am… ┆ North
America ┆ MULTIPOLYGON(((-122… │\n",
+
"├╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ Los Angeles ┆ POINT(-118.15724889… ┆ United States of Am… ┆ North
America ┆ MULTIPOLYGON(((-122… │\n",
+
"├╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ Chicago ┆ POINT(-87.649952137… ┆ United States of Am… ┆ North
America ┆ MULTIPOLYGON(((-122… │\n",
+
"└─────────────┴──────────────────────┴──────────────────────┴───────────────┴──────────────────────┘\n"
+ ]
+ }
+ ],
+ "source": [
+ "# join works when CRSs match\n",
+ "sd.sql(\"\"\"\n",
+ "select * from cities\n",
+ "join countries\n",
+ "where ST_Intersects(cities.geometry, countries.geometry)\n",
+ "\"\"\").show()"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "5279bebd-1d8d-4f33-bcd9-2c1e93ff7221",
+ "metadata": {},
+ "source": [
+ "## Example #2: Joining two tables with different CRSs\n",
+ "\n",
+ "This example shows how to join a `vermont` table with an EPSG 32618 CRS
with a `buildings` table that uses an EPSG 4326 CRS.\n",
+ "\n",
+ "The example highlights the following features:\n",
+ "\n",
+ "1. SedonaDB reads the CRS stored in the files\n",
+ "2. SedonaDB protects you from accidentally joining files with mismatched
CRSs\n",
+ "3. It's easy to convert a GeoPandas DataFrame => a SedonaDB DataFrame and
maintain the CRS"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 3,
+ "id": "d9ea1469-8e6d-4ef1-a440-5573c1345f0d",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "import geopandas as gpd\n",
+ "\n",
+ "path =
\"https://raw.githubusercontent.com/geoarrow/geoarrow-data/v0.2.0/example-crs/files/example-crs_vermont-utm.fgb\"\n";,
+ "gdf = gpd.read_file(path)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 4,
+ "id": "46660482-3fed-4e6c-b37a-2947326e884a",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "vermont = sd.create_data_frame(gdf)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 5,
+ "id": "1571aa5e-638c-493a-90a1-0cffbeea0bd9",
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "SedonaSchema with 1 field:\n",
+ " geometry: wkb <epsg:32618>"
+ ]
+ },
+ "execution_count": 5,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "vermont.schema"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 7,
+ "id": "eb4b33f1-972e-4c18-bdbd-fd4fe268b339",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "buildings = sd.read_parquet(\n",
+ "
\"https://github.com/geoarrow/geoarrow-data/releases/download/v0.2.0/microsoft-buildings_point_geo.parquet\"\n";,
+ ")"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 8,
+ "id": "22baf082-736e-4881-9704-d57eea07068c",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "┌─────────────────────────────────┐\n",
+ "│ geometry │\n",
+ "│ geometry │\n",
+ "╞═════════════════════════════════╡\n",
+ "│ POINT(-77.10109681 42.53495524) │\n",
+ "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ POINT(-77.10048552 42.53695011) │\n",
+ "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
+ "│ POINT(-77.10096508 42.53681338) │\n",
+ "└─────────────────────────────────┘\n"
+ ]
+ }
+ ],
+ "source": [
+ "buildings.show(3)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 9,
+ "id": "e1d2d89d-8227-43ea-8507-fd6524fe2ac5",
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "SedonaSchema with 1 field:\n",
+ " geometry: wkb_view <ogc:crs84>"
+ ]
+ },
+ "execution_count": 9,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "buildings.schema"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 10,
+ "id": "e959081e-3a8d-4041-b00f-a19bca10be39",
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "129735970"
+ ]
+ },
+ "execution_count": 10,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "buildings.count()"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 11,
+ "id": "d9ef702f-e424-4e53-9629-da6ed256ee7f",
+ "metadata": {},
+ "outputs": [],
+ "source": [
+ "buildings.to_view(\"buildings\", overwrite=True)\n",
+ "vermont.to_view(\"vermont\", overwrite=True)"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 12,
+ "id": "0c300dad-aa60-4291-a10b-d2a332b37593",
+ "metadata": {},
+ "outputs": [
+ {
+ "ename": "SedonaError",
+ "evalue": "type_coercion\ncaused by\nError during planning: Mismatched
CRS arguments: ogc:crs84 vs epsg:32618\nUse ST_Transform() or ST_SetSRID() to
ensure arguments are compatible.",
+ "output_type": "error",
+ "traceback": [
+
"\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
+ "\u001b[0;31mSedonaError\u001b[0m
Traceback (most recent call last)",
+ "Cell \u001b[0;32mIn[12], line 5\u001b[0m\n\u001b[1;32m 1\u001b[0m
\u001b[43msd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43msql\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\"\"\u001b[39;49m\n\u001b[1;32m
2\u001b[0m \u001b[38;5;124;43mselect count(*) from
buildings\u001b[39;49m\n\u001b[1;32m 3\u001b[0m \u001b[38;5;124;43mjoin
vermont\u001b[39;49m\n\u001b[1;32m 4\u001b[0m \u001b[38;5;124;43mwhere
ST_Intersects(buildings.geometry, vermont.geometry)\u [...]
+ "File
\u001b[0;32m/opt/miniconda3/lib/python3.12/site-packages/sedonadb/dataframe.py:297\u001b[0m,
in \u001b[0;36mDataFrame.show\u001b[0;34m(self, limit, width,
ascii)\u001b[0m\n\u001b[1;32m 272\u001b[0m
\u001b[38;5;250m\u001b[39m\u001b[38;5;124;03m\"\"\"Print the first limit rows
to the console\u001b[39;00m\n\u001b[1;32m 273\u001b[0m \n\u001b[1;32m
274\u001b[0m \u001b[38;5;124;03mArgs:\u001b[39;00m\n\u001b[0;32m
(...)\u001b[0m\n\u001b[1;32m 294\u001b[0m \n\u001b[1;32 [...]
+ "\u001b[0;31mSedonaError\u001b[0m: type_coercion\ncaused by\nError
during planning: Mismatched CRS arguments: ogc:crs84 vs epsg:32618\nUse
ST_Transform() or ST_SetSRID() to ensure arguments are compatible."
+ ]
+ }
+ ],
+ "source": [
+ "sd.sql(\"\"\"\n",
+ "select count(*) from buildings\n",
+ "join vermont\n",
+ "where ST_Intersects(buildings.geometry, vermont.geometry)\n",
+ "\"\"\").show()"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 14,
+ "id": "648984ce-ac7a-4f76-ac9f-17bd5c628bd0",
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "┌──────────┐\n",
+ "│ count(*) │\n",
+ "│ int64 │\n",
+ "╞══════════╡\n",
+ "│ 361856 │\n",
+ "└──────────┘\n"
+ ]
+ }
+ ],
+ "source": [
+ "sd.sql(\"\"\"\n",
+ "select count(*) from buildings\n",
+ "join vermont\n",
+ "where ST_Intersects(buildings.geometry, ST_Transform(vermont.geometry,
'EPSG:4326'))\n",
+ "\"\"\").show()"
+ ]
+ }
+ ],
+ "metadata": {
+ "kernelspec": {
+ "display_name": "Python 3 (ipykernel)",
+ "language": "python",
+ "name": "python3"
+ },
+ "language_info": {
+ "codemirror_mode": {
+ "name": "ipython",
+ "version": 3
+ },
+ "file_extension": ".py",
+ "mimetype": "text/x-python",
+ "name": "python",
+ "nbconvert_exporter": "python",
+ "pygments_lexer": "ipython3",
+ "version": "3.12.4"
+ }
+ },
+ "nbformat": 4,
+ "nbformat_minor": 5
+}
diff --git a/docs/crs-examples.md b/docs/crs-examples.md
new file mode 100644
index 0000000..cfa7b51
--- /dev/null
+++ b/docs/crs-examples.md
@@ -0,0 +1,334 @@
+<!---
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+# Coordinate Reference System (CRS) Examples
+
+This example demonstrates how one table with an EPSG 4326 CRS cannot be joined
with another table that uses EPSG 3857.
+
+
+```python
+import sedonadb
+
+sd = sedonadb.connect()
+```
+
+Read a table with a geometry column that uses EPSG 4326.
+
+Note how SedonaDB reads the CRS specified in the Parquet file.
+
+
+```python
+countries = sd.read_parquet(
+
"https://raw.githubusercontent.com/geoarrow/geoarrow-data/v0.2.0/natural-earth/files/natural-earth_countries_geo.parquet";
+)
+```
+
+
+```python
+countries.schema
+```
+
+
+
+
+ SedonaSchema with 3 fields:
+ name: Utf8View
+ continent: Utf8View
+ geometry: wkb_view <epsg:4326>
+
+
+
+
+```python
+cities = sd.sql("""
+SELECT city, ST_SetSRID(ST_GeomFromText(wkt), 3857) AS geometry FROM (VALUES
+ ('New York', 'POINT(-8238310.24 4969803.34)'),
+ ('Los Angeles', 'POINT(-13153204.78 4037636.04)'),
+ ('Chicago', 'POINT(-9757148.04 5138517.44)'))
+AS t(city, wkt)""")
+```
+
+
+```python
+cities.schema
+```
+
+
+
+
+ SedonaSchema with 2 fields:
+ city: Utf8
+ geometry: wkb <epsg:3857>
+
+
+
+
+```python
+cities.to_view("cities", overwrite=True)
+countries.to_view("countries", overwrite=True)
+```
+
+## Join with mismatched CRSs
+
+The cities and countries tables have different CRSs.
+
+The cities table uses EPSG:3857 and the countries table uses EPSG:4326.
+
+Let's confirm that the code errors out if we try to join the mismatched tables.
+
+
+```python
+# join doesn't work when CRSs don't match
+sd.sql("""
+select * from cities
+join countries
+where ST_Intersects(cities.geometry, countries.geometry)
+""").show()
+```
+
+
+ ---------------------------------------------------------------------------
+
+ SedonaError Traceback (most recent call last)
+
+ Cell In[7], line 6
+ 1 # join doesn't work when CRSs don't match
+ 2 sd.sql("""
+ 3 select * from cities
+ 4 join countries
+ 5 where ST_Intersects(cities.geometry, countries.geometry)
+ ----> 6 """).show()
+
+
+ File
/opt/miniconda3/lib/python3.12/site-packages/sedonadb/dataframe.py:297, in
DataFrame.show(self, limit, width, ascii)
+ 272 """Print the first limit rows to the console
+ 273
+ 274 Args:
+ (...)
+ 294
+ 295 """
+ 296 width = _out_width(width)
+ --> 297 print(self._impl.show(self._ctx, limit, width, ascii), end="")
+
+
+ SedonaError: type_coercion
+ caused by
+ Error during planning: Mismatched CRS arguments: epsg:3857 vs epsg:4326
+ Use ST_Transform() or ST_SetSRID() to ensure arguments are compatible.
+
+
+## Convert CRS and then join
+
+Let's convert the cities table to use EPSG:4326 and then perform the join with
the two tables once they have matching CRSs.
+
+
+```python
+# update cities to use 4326
+cities = sd.sql("""
+SELECT city, ST_Transform(geometry, 'EPSG:4326') as geometry
+FROM cities
+""")
+```
+
+
+```python
+cities.schema
+```
+
+
+
+
+ SedonaSchema with 2 fields:
+ city: Utf8
+ geometry: wkb <ogc:crs84>
+
+
+
+
+```python
+cities.to_view("cities", overwrite=True)
+```
+
+
+```python
+# join works when CRSs match
+sd.sql("""
+select * from cities
+join countries
+where ST_Intersects(cities.geometry, countries.geometry)
+""").show()
+```
+
+
┌─────────────┬──────────────────────┬──────────────────────┬───────────────┬──────────────────────┐
+ │ city ┆ geometry ┆ name ┆ continent
┆ geometry │
+ │ utf8 ┆ geometry ┆ utf8view ┆ utf8view
┆ geometry │
+
╞═════════════╪══════════════════════╪══════════════════════╪═══════════════╪══════════════════════╡
+ │ New York ┆ POINT(-74.006000039… ┆ United States of Am… ┆ North
America ┆ MULTIPOLYGON(((-122… │
+
├╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+ │ Los Angeles ┆ POINT(-118.15724889… ┆ United States of Am… ┆ North
America ┆ MULTIPOLYGON(((-122… │
+
├╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+ │ Chicago ┆ POINT(-87.649952137… ┆ United States of Am… ┆ North
America ┆ MULTIPOLYGON(((-122… │
+
└─────────────┴──────────────────────┴──────────────────────┴───────────────┴──────────────────────┘
+
+
+## Example #2: Joining two tables with different CRSs
+
+This example shows how to join a `vermont` table with an EPSG 32618 CRS with a
`buildings` table that uses an EPSG 4326 CRS.
+
+The example highlights the following features:
+
+1. SedonaDB reads the CRS stored in the files
+2. SedonaDB protects you from accidentally joining files with mismatched CRSs
+3. It's easy to convert a GeoPandas DataFrame => a SedonaDB DataFrame and
maintain the CRS
+
+
+```python
+import geopandas as gpd
+
+path =
"https://raw.githubusercontent.com/geoarrow/geoarrow-data/v0.2.0/example-crs/files/example-crs_vermont-utm.fgb";
+gdf = gpd.read_file(path)
+```
+
+
+```python
+vermont = sd.create_data_frame(gdf)
+```
+
+
+```python
+vermont.schema
+```
+
+
+
+
+ SedonaSchema with 1 field:
+ geometry: wkb <epsg:32618>
+
+
+
+
+```python
+buildings = sd.read_parquet(
+
"https://github.com/geoarrow/geoarrow-data/releases/download/v0.2.0/microsoft-buildings_point_geo.parquet";
+)
+```
+
+
+```python
+buildings.show(3)
+```
+
+ ┌─────────────────────────────────┐
+ │ geometry │
+ │ geometry │
+ ╞═════════════════════════════════╡
+ │ POINT(-77.10109681 42.53495524) │
+ ├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+ │ POINT(-77.10048552 42.53695011) │
+ ├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤
+ │ POINT(-77.10096508 42.53681338) │
+ └─────────────────────────────────┘
+
+
+
+```python
+buildings.schema
+```
+
+
+
+
+ SedonaSchema with 1 field:
+ geometry: wkb_view <ogc:crs84>
+
+
+
+
+```python
+buildings.count()
+```
+
+
+
+
+ 129735970
+
+
+
+
+```python
+buildings.to_view("buildings", overwrite=True)
+vermont.to_view("vermont", overwrite=True)
+```
+
+
+```python
+sd.sql("""
+select count(*) from buildings
+join vermont
+where ST_Intersects(buildings.geometry, vermont.geometry)
+""").show()
+```
+
+
+ ---------------------------------------------------------------------------
+
+ SedonaError Traceback (most recent call last)
+
+ Cell In[12], line 5
+ 1 sd.sql("""
+ 2 select count(*) from buildings
+ 3 join vermont
+ 4 where ST_Intersects(buildings.geometry, vermont.geometry)
+ ----> 5 """).show()
+
+
+ File
/opt/miniconda3/lib/python3.12/site-packages/sedonadb/dataframe.py:297, in
DataFrame.show(self, limit, width, ascii)
+ 272 """Print the first limit rows to the console
+ 273
+ 274 Args:
+ (...)
+ 294
+ 295 """
+ 296 width = _out_width(width)
+ --> 297 print(self._impl.show(self._ctx, limit, width, ascii), end="")
+
+
+ SedonaError: type_coercion
+ caused by
+ Error during planning: Mismatched CRS arguments: ogc:crs84 vs epsg:32618
+ Use ST_Transform() or ST_SetSRID() to ensure arguments are compatible.
+
+
+
+```python
+sd.sql("""
+select count(*) from buildings
+join vermont
+where ST_Intersects(buildings.geometry, ST_Transform(vermont.geometry,
'EPSG:4326'))
+""").show()
+```
+
+ ┌──────────┐
+ │ count(*) │
+ │ int64 │
+ ╞══════════╡
+ │ 361856 │
+ └──────────┘
diff --git a/mkdocs.yml b/mkdocs.yml
index 6a9515a..99fbfe6 100644
--- a/mkdocs.yml
+++ b/mkdocs.yml
@@ -27,7 +27,7 @@ nav:
- Working with Overture: overture-examples.md
- Working with Parquet Files: working-with-parquet-files.md
- Contributors Guide: contributors-guide.md
-
+ - CRS Examples: crs-examples.md
- SedonaDB Reference:
- Python:
- Python Functions: reference/python.md
