Querying Datasets

Havasu tables in Wherobots Cloud are automatically managed by the Wherobots Catalog, allowing you to interact with your data with Spatial SQL queries, and browse and explore your datasets and tables from within the Data Hub section of Wherobots Cloud. The Wherobots Catalog system allows you to manage multiple catalogs. Wherobots Cloud provides a default catalog called wherobots for your Organization in which you can create and store your Havasu tables and spatial datasets. Tables can be created by SQL queries directly, or using the WherobotsDB/Sedona Python SDK from a notebook.

Catalog structure

Each catalog is composed of databases (namespaces), and each database can contain tables, which are referenced by <catalog>.<database>.<table>; for example:

SELECT id,geometry FROM wherobots_open_data.overture_maps_foundation.places_place

Browsing datasets

The Data Hub lets you inspect the contents of your Managed and foreign catalogs as well as their respective schemas.

Create a Managed Catalog from an S3 Bucket

For more information on creating a Managed Catalog from an Amazon S3 private bucket, see Managed Catalog in the Wherobots Amazon S3 Integration Documentation.

In alignment with security best practices, Wherobots strongly discourages using a public bucket to create a Managed Catalog, since doing so requires giving write access to a public bucket. Granting write access to a public bucket is generally discouraged due to the potential for unauthorized modification and data breaches.

Working with catalogs in SQL

You can interact with your catalogs using either standard SQL or the Wherobots Python SDK.

List Schemas

Lists the available schemas (databases) within a specified catalog.

SQL
Python

SHOW SCHEMAS IN wherobots_open_data;

+---------+
|namespace|
+---------+
| overture|
+---------+

sedona.sql("SHOW SCHEMAS IN wherobots_pro_data").show()

+----------------+
|       namespace|
+----------------+
|google_microsoft|
|         landsat|
...
+----------------+

List Tables

Lists the available tables within a specified schema.

SQL
Python

SHOW TABLES IN wherobots_open_data.overture LIKE 'places_*';

+---------+------------+-----------+
|namespace|   tableName|isTemporary|
+---------+------------+-----------+
| overture|places_place|      false|
+---------+------------+-----------+

sedona.sql("SHOW TABLES IN wherobots_pro_data.weather").show()

+---------+--------------+-----------+
|namespace|     tableName|isTemporary|
+---------+--------------+-----------+
|  weather|weather_events|      false|
|  weather|    wild_fires|      false|
+---------+--------------+-----------+

Inspect Table Schema

Displays the column names and data types for a specific table.

SQL
Python

DESCRIBE TABLE wherobots_pro_data.weather.weather_events;

sedona.table("wherobots_pro_data.weather.weather_events").printSchema()

root
|-- EventId: string (nullable = true)
|-- Type: string (nullable = true)
...
|-- geometry: geometry (nullable = true)

View Table Content

Retrieves and displays the rows from a table.

SQL
Python

SELECT * FROM wherobots_pro_data.weather.weather_events LIMIT 10;`

sedona.table("wherobots_pro_data.weather.weather_events").show()

Managing Catalogs

The following management commands are available in SQL and Python.

List Catalogs

Lists the available catalogs in your environment.

SQL
Python

SHOW CATALOGS LIKE 'wherobots*';

+-------------------+
|            catalog|
+-------------------+
|          wherobots|
|wherobots_open_data|
| wherobots_pro_data|
+-------------------+

sedona.sql("SHOW CATALOGS LIKE 'wherobots*'").show()

Create a Schema

Creates a new schema (database) within a catalog to organize your tables.

SQL
Python

CREATE DATABASE IF NOT EXISTS org_catalog.test_db;

sedona.sql("CREATE DATABASE IF NOT EXISTS org_catalog.test_db")

Create a Table

Creates a new table from the results of a query.

SQL
Python

CREATE TABLE org_catalog.test_db.top_100_hot_buildings_daily AS
SELECT
  buildings.id,
  first(buildings.names),
  count(places.geometry),
  '2023-07-24' as ts
FROM
  wherobots_open_data.overture_maps_foundation.places_place places
JOIN
  wherobots_open_data.overture_maps_foundation.buildings_building buildings ON ST_CONTAINS(buildings.geometry, places.geometry)
WHERE
  places.updatetime >= '2023-07-24' AND places.updatetime < '2023-07-25'
GROUP BY 1
ORDER BY 3 DESC
LIMIT 100;

create_table_sql = """
CREATE TABLE org_catalog.test_db.top_100_hot_buildings_daily AS
SELECT
  buildings.id,
  first(buildings.names),
  count(places.geometry),
  '2023-07-24' as ts
FROM
  wherobots_open_data.overture_maps_foundation.places_place places
JOIN
  wherobots_open_data.overture_maps_foundation.buildings_building buildings ON ST_CONTAINS(buildings.geometry, places.geometry)
WHERE
  places.updatetime >= '2023-07-24' AND places.updatetime < '2023-07-25'
GROUP BY 1
ORDER BY 3 DESC
LIMIT 100
"""
sedona.sql(create_table_sql)

Accessing Historical Overture Maps Foundation data snapshots

The Overture Maps Foundation regularly releases updated datasets. To ensure data consistency for your analysis and applications, Wherobots maintains historical snapshots of Overture Maps data. By default, when you query an Overture Data table without specifying a version, you will always access the latest stable release of the data. You can, however, query specific historical snapshots of Overture Maps data using the VERSION AS OF clause in your SQL queries. This allows you to reproduce results or analyze changes over time.

Maintained Overture Maps Foundation versions

We maintain all non-alpha and non-beta versions of Overture Maps Foundation datasets. New stable versions are released regularly. Query the .refs column to get the most up-to-date list of available versions for a specific table.

Discovering available versions

To view all available named tags (versions) for an Overture Maps table, query its .refs column. The following query lists all available version tags and their metadata for the places_place table:

SQL
Python

SELECT
  *
FROM
  wherobots_open_data.overture_maps_foundation.places_place.refs
WHERE
  type = 'TAG'
ORDER BY
  name

available_versions_sql = """
SELECT
  *
FROM
  wherobots_open_data.overture_maps_foundation.places_place.refs
WHERE
  type = 'TAG'
ORDER BY
  name
"""
sedona.sql(available_versions_sql).show()

Querying a specific data snapshot

Overture’s data release tags are in the following format: YYYY-MM-DD.X

YYYY: Publication Year of the dataset.
MM: Publication Month of the dataset.
DD: Publication Date of the dataset.
X: Dataset patch number.

Replace YYYY-MM-DD.X with the specific version tag you wish to query. To access a particular version of an Overture Maps table, use VERSION AS OF:

SQL
Python

SELECT
  *
FROM
  wherobots_open_data.overture_maps_foundation.places_place
  VERSION AS OF 'YYYY-MM-DD.X'

versioned_sql = """
SELECT
  *
FROM
  wherobots_open_data.overture_maps_foundation.places_place
  VERSION AS OF 'YYYY-MM-DD.X'
"""
sedona.sql(versioned_sql).show()

Historical snapshot example

For a list of Overture Maps releases and their version numbers, see the Overture Maps Release Calendar. For example, to access the places_place table as it was on May 21, 2025:

SQL
Python

SELECT
  *
FROM
  wherobots_open_data.overture_maps_foundation.places_place
VERSION AS OF '2025-05-21.0'

historical_sql = """
SELECT
  *
FROM
  wherobots_open_data.overture_maps_foundation.places_place
VERSION AS OF '2025-05-21.0'
"""
sedona.sql(historical_sql).show()

Spatial Catalog

WherobotsDB

WherobotsAI

Catalog structure

Browsing datasets

Create a Managed Catalog from an S3 Bucket

Working with catalogs in SQL

List Schemas

List Tables

Inspect Table Schema

View Table Content

Managing Catalogs

List Catalogs

Create a Schema

Create a Table

Accessing Historical Overture Maps Foundation data snapshots

Maintained Overture Maps Foundation versions

Discovering available versions

Querying a specific data snapshot

Historical snapshot example

Spatial Catalog

WherobotsDB

WherobotsAI

​Catalog structure

​Browsing datasets

​Create a Managed Catalog from an S3 Bucket

​Working with catalogs in SQL

​List Schemas

​List Tables

​Inspect Table Schema

​View Table Content

​Managing Catalogs

​List Catalogs

​Create a Schema

​Create a Table

​Accessing Historical Overture Maps Foundation data snapshots

​Maintained Overture Maps Foundation versions

​Discovering available versions

​Querying a specific data snapshot

​Historical snapshot example

Catalog structure

Browsing datasets

Create a Managed Catalog from an S3 Bucket

Working with catalogs in SQL

List Schemas

List Tables

Inspect Table Schema

View Table Content

Managing Catalogs

List Catalogs

Create a Schema

Create a Table

Accessing Historical Overture Maps Foundation data snapshots

Maintained Overture Maps Foundation versions

Discovering available versions

Querying a specific data snapshot

Historical snapshot example