Wherobots Spatial SQL API and SDKs

The Wherobots Spatial SQL API allows programmatic integration with Wherobots Cloud, enabling direct SDK access to execute Spatial SQL queries.

Powered by WherobotsDB with Apache Sedona at its core, this distributed engine scales for large dataset computation and analytics with Wherobots Cloud managing the underlying infrastructure.

Connecting to the API starts a SQL Session on your selected WherobotsDB runtime, where Wherobots securely executes and fully manages the compute for your queries.

We provide client SDKs in Java and in Python to easily connect and interact with WherobotsDB through the Spatial SQL API, as well as an Airflow Provider to build your spatial ETL DAGs; all of them are open-source and available on package registries, as well as on Wherobots’ GitHub page.

API row limit

Currently the API has a hard limit of 1000 rows on returned results. This doesn't affect computations on the clusters, just the amount of data the API or the drivers send back on the wire.

API keys

To authenticate and use the Spatial SQL APIs and SDKs, we often require an API key. For more information, see API Keys.

Python DB-API driver

To access Wherobots DB, we provide a Python DB-API implementation. The driver is a PEP-0249 compatible driver to programmatically connect to a Wherobots DB runtime and execute Spatial SQL queries.

Installation

If you use Poetry in your project, add the dependency with poetry add:

poetry add wherobots-python-dbapi

Otherwise, just pip install it:

pip install wherobots-python-dbapi

Usage

Usage follows the typical pattern of establishing the connection, acquiring a cursor, and executing SQL queries through it:

import logging

from wherobots.db import connect
from wherobots.db.region import Region
from wherobots.db.runtime import Runtime

# Optionally, setup logging to get information about the driver's
# activity.
logging.basicConfig(
    stream=sys.stdout,
    level=logging.INFO,
    format="%(asctime)s.%(msecs)03d %(levelname)s %(name)20s: %(message)s",
    datefmt="%Y-%m-%d %H:%M:%S",
)

# Get your API key, or securely read it from a local file.
api_key = '...'

with connect(
    host="api.cloud.wherobots.com",
    api_key=api_key,
    # runtime parameter specifies the compute resources allocated for the runtime environment.
    # Replace 'Runtime.TINY' with the desired runtime size, for example:
    # - For a small runtime: runtime=Runtime.SMALL
    # - For a medium runtime: runtime=Runtime.MEDIUM
    runtime=Runtime.TINY,
    # region parameter establishes a connection to a specified AWS cloud provider region.
    # Replace 'Region.AWS_US_WEST_2' with the desired AWS region, for example:
    # - For AWS US East (N. Virginia): region=Region.AWS_US_EAST_1
    region=Region.AWS_US_WEST_2) as conn:
        curr = conn.cursor()
        sql = """
            SELECT
                id,
                names['primary'],
                geometry,
                population
            FROM
                wherobots_open_data.overture_2024_02_15.admins_locality
            WHERE localityType = 'country'
            SORT BY population DESC
            LIMIT 10
        """
        curr.execute(sql)
        results = curr.fetchall()
        results.show()

The Cursor supports the context manager protocol, so you can use it within a with statement when needed:

with connect(...) as conn:
    with conn.cursor() as curr:
        curr.execute(...)
        results = curr.fetchall()
        results.show()

It also implements the close() method, as suggested by the PEP-2049 specification, to support situations where the cursor is wrapped in a contextmanager.closing().

Runtime and region selection

You can choose the Wherobots runtime you want to use with the runtime parameter, passing in one of the Runtime enum values.

For guidance on runtime sizing and selection, see Runtimes.

The following AWS regions are currently supported in the Wherobots Spatial SQL API:

Wherobots Parameter	AWS Region Name	AWS Region Code	Access
Region.AWS_US_WEST_2	Oregon	us-west-2	All Organization Editions
Region.AWS_EU_WEST_1	Ireland	eu-west-1	Paid Organizations Only
Region.AWS_US_EAST_1	Northern Virginia	us-east-1	Paid Organizations Only

Advanced parameters

The connect() method takes some additional parameters that can be useful for optimizing performance, controlling data formats, and managing connections:

Parameter	Type	Description	Default
results_format	ResultsFormat enum	Format for receiving query results.	arrow (Arrow encoding)
data_compression	DataCompression enum	Compression algorithm for receiving query results.	brotli (Brotli compression)
geometry_representation	GeometryRepresentation enum	Encoding of geometry columns returned to the client application.	ewkt (EWKT string)
session_type	"single" or "multi"	Establishes connection type to a Wherobots runtime.	"single" (Exclusive connection)

Consider the "multi" session type for potential cost savings, but be mindful of performance impacts from shared resources. You might need to adjust cluster size if slowdowns occur, which could affect overall cost.

Harlequin-wherobots SQL IDE for your terminal

As an example usage for our Python DB-API driver, we also integrated with Harlequin.

Quick start guide

Detailed usage guide is available as part of Harlequin's Wherobots Adapter documentation.

# Install Harlequin with Wherobots adapter
pip install harlequin-wherobots

# Start harlequin
harlequin -a wherobots --api-key <api-key> --runtime SEDONA --region AWS_US_WEST_2

JDBC (type 4) driver

We provide an open-source Java library that implements a JDBC (Type 4) driver for connecting to WherobotsDB.

Installation (JDBC type 4)

To start building Java applications around the Wherobots JDBC driver, add the following line to your build.gradle file’s dependency section:

implementation "com.wherobots:wherobots-jdbc-driver"

Usage (JDBC type 4)

In your application, you only need to work with Java’s JDBC APIs from the java.sql package:

import com.wherobots.db.Region;
import com.wherobots.db.Runtime;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

// Get your API key, or securely read it from a local file.
String apiKey = "...";

Properties props = new Properties();
props.setProperty("apiKey", apiKey);
props.setProperty("runtime", Runtime.TINY);
props.setProperty("region", Region.AWS_US_WEST_2);

try (Connection conn = DriverManager.getConnection("jdbc:wherobots://api.cloud.wherobots.com", props)) {
    String sql = """
        SELECT
            id,
            names['primary'] AS name,
            geometry,
            population
        FROM
            wherobots_open_data.overture_2024_02_15.admins_locality
        WHERE localityType = 'country'
        SORT BY population DESC
        LIMIT 10
    """;
  Statement stmt = conn.createStatement();
  try (ResultSet rs = stmt.executeQuery(sql)) {
    while (rs.next()) {
      System.out.printf("%s: %s %f %s\n",
        rs.getString("id"),
        rs.getString("name"),
        rs.getDouble("population"),
        rs.getString("geometry"));
    }
  }
}

For more information and future releases, see our driver on GitHub.

Wherobots SQL Driver TypeScript SDK

This is the TypeScript SDK for interacting with WherobotsDB. This package implements a Node.js client that programmatically connects to a WherobotsDB runtime and executes Spatial SQL queries.

Prerequisites (TypeScript SDK)

The following resources are needed to run the Wherobots SQL Driver's TypeScript SDK:

1. Node.js version 18 or higher
2. TypeScript version 5.x (if using TypeScript)

Installation (TypeScript SDK)

To complete the installation, run the following command:

npm install wherobots-sql-driver

Usage (TypeScript SDK)

Example: Executing SQL statement and printing results

This example:

• Establishes the connection to WherobotsDB with an async function
• Calls async methods to execute SQL queries through this connection.

import { Connection, Runtime } from "wherobots-sql-driver";

(async () => {
  const conn = await Connection.connect({
    // replace "YOUR-WHEROBOTS-API-KEY" with the key created above
    // or alternatively the key can be set with the `WHEROBOTS_API_KEY` environment variable
    apiKey: "YOUR-WHEROBOTS-API-KEY",
    runtime: Runtime.TINY,
  });
  const results = await conn.execute("SHOW SCHEMAS IN wherobots_open_data");
  console.log(JSON.stringify(results.toArray(), null, 2));
  conn.close();
})();

Running this example returns the results of the query as JSON:

[
  {
    "namespace": "overture"
  },
  {
    "namespace": "overture_2024_02_15"
  },
  {
    "namespace": "overture_2024_05_16"
  },
  {
    "namespace": "overture_2024_07_22"
  },
  {
    "namespace": "test_db"
  }
]

Code example explanation

1. Calling Connection.connect() asynchronously establishes a SQL Session connection in Wherobots Cloud and returns a Connection instance.
2. Calling the connection's execute() methods runs the given SQL statement and asynchronously returns the result as an Apache Arrow Table instance.
3. The Arrow Table instance is converted to a primitive by calling toArray(), and then printed to the console as formatted JSON with JSON.stringify().
4. Calling the connection's close() method tears down the SQL Session connection.

Running the example (TypeScript SDK)

JavascriptTypeScript

1. Paste the contents of the above code example into a file called wherobots-example.js
2. Run the example with: node wherobots-example.js

1. Paste the contents of the above code example into a file called wherobots-example.ts
2. Run the example with: npx tsx wherobots-example.ts

Runtime and region selection (TypeScript SDK)

Select your desired Wherobots runtime using the runtime parameter and specifying a runtime enum value. For guidance on runtime sizing and selection, see Runtimes.

Additional parameters to connect()

The Connection.connect() function can take the following additional options:

• sessionType: "single" or "multi"; if set to "single", then each call to Connection.connect() establishes an exclusive connection to a Wherobots runtime; if set to "multi", then multiple Connection.connect() calls with the same arguments and credentials will connect to the same shared Wherobots runtime; "single" is the default.

Consider multi-session for potential cost savings, but be mindful of performance impacts from shared resources. You might need to adjust cluster size if slowdowns occur, which could affect overall cost.

• resultsFormat: one of the ResultsFormat enum values; Arrow encoding is the default and most efficient format for receiving query results.

  Note

  Currently, only Arrow encoding is supported

• dataCompression: one of the DataCompression enum values; Brotli compression is the default and the most efficient compression algorithm for receiving query results.

  Note

  Currently, only Brotli compression is supported

• geometryRepresentation: one of the GeometryRepresentation enum values; selects the encoding of geometry columns returned to the client application. The default is EWKT (string) and the most convenient for human inspection while still being usable by geospatial data manipulation libraries.

• region: The region where the SQL session compute region. For more information, see Runtime and Region Selection.

Additional parameters to execute()

The Connection#execute method can take an optional second argument, options:

• options.signal: an AbortSignal which can be used to cancel the execution (optional)