Set Up WherobotsDB

WherobotsDB is the spatial query engine in Wherobots Notebook Runtimes and Job Runs. It keeps the Apache Sedona API names, so code identifiers such as SedonaContext, the sedona session variable, and sedona.* configuration keys refer to WherobotsDB.

Create a session

Start each notebook or job script by creating a session:

Python
Scala
Java

from sedona.spark import *

config = SedonaContext.builder().getOrCreate()
sedona = SedonaContext.create(config)

import org.apache.sedona.spark.SedonaContext

val config = SedonaContext.builder().getOrCreate()
val sedona = SedonaContext.create(config)

import org.apache.spark.sql.SparkSession;
import org.apache.sedona.spark.SedonaContext;

SparkSession config = SedonaContext.builder().getOrCreate();
SparkSession sedona = SedonaContext.create(config);

sedona is a Spark session with the WherobotsDB functions registered. In Wherobots, it reads and writes your Organization’s catalogs, such as org_catalog, with no further configuration. Call the ST_ vector functions and the RS_ raster functions in SQL with sedona.sql, or in DataFrame code with selectExpr and expr. For the functions, see Vector SQL APIs and Raster SQL APIs.

Create, load, and query a table

In the following steps, you create a table in org_catalog, your Organization’s default managed catalog, load NYC taxi trips from a CSV file into it, and query the trips by location. To write to another catalog, replace org_catalog.

Create a table

Create a database and a table with a GEOMETRY(4326) column for the pickup locations. 4326 declares the column’s CRS as WGS 84 longitude and latitude; see Geometry and Geography Tables.

Python
Scala
Java

sedona.sql("CREATE DATABASE IF NOT EXISTS org_catalog.test_db")
sedona.sql("""
CREATE TABLE IF NOT EXISTS org_catalog.test_db.taxi_trips (
  pickup GEOMETRY(4326),
  pickup_time STRING,
  payment_type STRING,
  fare DOUBLE
)
""")

sedona.sql("CREATE DATABASE IF NOT EXISTS org_catalog.test_db")
sedona.sql("""
CREATE TABLE IF NOT EXISTS org_catalog.test_db.taxi_trips (
  pickup GEOMETRY(4326),
  pickup_time STRING,
  payment_type STRING,
  fare DOUBLE
)
""")

sedona.sql("CREATE DATABASE IF NOT EXISTS org_catalog.test_db");
sedona.sql(
  "CREATE TABLE IF NOT EXISTS org_catalog.test_db.taxi_trips (" +
  "pickup GEOMETRY(4326)," +
  "pickup_time STRING," +
  "payment_type STRING," +
  "fare DOUBLE" +
  ")");

Load a CSV file

Read the sample CSV file of NYC taxi trips from S3, and build the columns of the table from it:

Python
Scala
Java

taxi_df = sedona.read.format("csv").option("header", "true").load("s3://wherobots-examples/data/nyc-taxi-data.csv")
taxi_df = taxi_df.selectExpr(
    "ST_SetSRID(ST_Point(CAST(Start_Lon AS DOUBLE), CAST(Start_Lat AS DOUBLE)), 4326) AS pickup",
    "Trip_Pickup_DateTime AS pickup_time",
    "UPPER(Payment_Type) AS payment_type",
    "CAST(Fare_Amt AS DOUBLE) AS fare",
)

var taxi_df = sedona.read.format("csv").option("header", "true").load("s3://wherobots-examples/data/nyc-taxi-data.csv")
taxi_df = taxi_df.selectExpr(
  "ST_SetSRID(ST_Point(CAST(Start_Lon AS DOUBLE), CAST(Start_Lat AS DOUBLE)), 4326) AS pickup",
  "Trip_Pickup_DateTime AS pickup_time",
  "UPPER(Payment_Type) AS payment_type",
  "CAST(Fare_Amt AS DOUBLE) AS fare")

Dataset<Row> taxi_df = sedona.read().format("csv").option("header", "true").load("s3://wherobots-examples/data/nyc-taxi-data.csv");
taxi_df = taxi_df.selectExpr(
  "ST_SetSRID(ST_Point(CAST(Start_Lon AS DOUBLE), CAST(Start_Lat AS DOUBLE)), 4326) AS pickup",
  "Trip_Pickup_DateTime AS pickup_time",
  "UPPER(Payment_Type) AS payment_type",
  "CAST(Fare_Amt AS DOUBLE) AS fare");

The CSV reader returns every column as a string, so the coordinates and fares are cast to DOUBLE. ST_Point returns points with SRID 0, and WherobotsDB rejects SRID 0 points written to a GEOMETRY(4326) column, so ST_SetSRID labels them with SRID 4326 without changing their coordinates. See Matching the column CRS. Some payment types appear in the file in two cases, such as CASH and Cash, so UPPER merges them.

Write to the table

Append the DataFrame to the table. Running append again adds the rows a second time, so run it once:

Python
Scala
Java

taxi_df.writeTo("org_catalog.test_db.taxi_trips").append()

taxi_df.writeTo("org_catalog.test_db.taxi_trips").append()

taxi_df.writeTo("org_catalog.test_db.taxi_trips").append();

Query the table

Count the trips that started in a box around Manhattan, and average their fares by payment type:

Python
Scala
Java

result = sedona.sql("""
SELECT payment_type, COUNT(*) AS trips, ROUND(AVG(fare), 2) AS avg_fare
FROM org_catalog.test_db.taxi_trips
WHERE ST_Within(pickup, ST_PolygonFromEnvelope(-74.02, 40.70, -73.93, 40.88))
GROUP BY payment_type
ORDER BY trips DESC
""")
result.show()

val result = sedona.sql("""
SELECT payment_type, COUNT(*) AS trips, ROUND(AVG(fare), 2) AS avg_fare
FROM org_catalog.test_db.taxi_trips
WHERE ST_Within(pickup, ST_PolygonFromEnvelope(-74.02, 40.70, -73.93, 40.88))
GROUP BY payment_type
ORDER BY trips DESC
""")
result.show()

Dataset<Row> result = sedona.sql(
  "SELECT payment_type, COUNT(*) AS trips, ROUND(AVG(fare), 2) AS avg_fare " +
  "FROM org_catalog.test_db.taxi_trips " +
  "WHERE ST_Within(pickup, ST_PolygonFromEnvelope(-74.02, 40.70, -73.93, 40.88)) " +
  "GROUP BY payment_type " +
  "ORDER BY trips DESC");
result.show();

93,130 of the 99,998 trips in the file started inside the box:

+------------+-----+--------+
|payment_type|trips|avg_fare|
+------------+-----+--------+
|        CASH|72795|    8.43|
|      CREDIT|20108|    10.4|
|   NO CHARGE|  189|   11.27|
|     DISPUTE|   38|    13.9|
+------------+-----+--------+

To remove the example table, run sedona.sql("DROP TABLE org_catalog.test_db.taxi_trips").

Next steps

Load Vector Data

Read GeoParquet, shapefiles, GeoJSON, and other vector formats into DataFrames.

Load Raster Data

Load GeoTIFF files and STAC assets as rasters, and tune out-DB raster reads.

Geometry and Geography Tables

Declare a column’s CRS, write and query geometry data, and use geography columns.

Your Organization's Catalogs

Use org_catalog, or create a managed catalog backed by your own S3 storage.

Start here

Havasu data catalogs

Vector data

Raster data

Iceberg tables

Maps and visualization

Set Up WherobotsDB

Create a session

Create, load, and query a table

Create a table

Load a CSV file

Write to the table

Query the table

Next steps

Load Vector Data

Load Raster Data

Geometry and Geography Tables

Your Organization's Catalogs

​Create a session

​Create, load, and query a table

​Create a table

​Load a CSV file

​Write to the table

​Query the table

​Next steps

Load Vector Data

Load Raster Data

Geometry and Geography Tables

Your Organization's Catalogs

Create a session

Create, load, and query a table

Create a table

Load a CSV file

Write to the table

Query the table

Next steps