Spatial Catalog¶

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 Spatial Catalog 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.

You can create and access other catalogs, including catalogs shared with your organization, like the Wherobots Spatial Data Catalogs.

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.places_place

Browsing the Wherobots Catalog¶

The "Spatial Catalog" tab takes you to Wherobots' Catalog browser interface, where you can inspect the contents of your managed catalogs and the schema of each of one of your tables.

Create a Spatial Catalog from an S3 Bucket¶

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

Warning

In alignment with security best practices, Wherobots strongly discourages using a public bucket to create a Spatial 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 also of course explore, and manipulate your Wherobots catalogs and their contents using SQL queries.

Listing catalog contents¶

> SHOW CATALOGS LIKE 'wherobots*';
+-------------------+
|            catalog|
+-------------------+
|          wherobots|
|wherobots_open_data|
| wherobots_pro_data|
+-------------------+

> SHOW SCHEMAS IN wherobots_open_data;
+---------+
|namespace|
+---------+
| overture|
+---------+

> SHOW TABLES IN wherobots_open_data.overture LIKE 'places_*';
+---------+------------+-----------+
|namespace|   tableName|isTemporary|
+---------+------------+-----------+
| overture|places_place|      false|
+---------+------------+-----------+

Creating a database¶

Create databases in your Wherobots catalog to organize your tables with the CREATE DATABASE statement:

CREATE DATABASE IF NOT EXISTS wherobots.test_db;

Creating a table¶

CREATE TABLE wherobots.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.places_place places
         JOIN wherobots_open_data.overture.buildings_building buildings
         ON ST_CONTAINS(buildings.geometry, places.geometry)
    WHERE places.updatetime >= '2023-07-24'
          AND places.updatetime < '2023-07-25'
          AND ST_CONTAINS(ST_PolygonFromEnvelope(-79.762152, 40.496103, -71.856214, 45.01585), places.geometry)
          AND ST_CONTAINS(ST_PolygonFromEnvelope(-79.762152, 40.496103, -71.856214, 45.01585), buildings.geometry)
    GROUP BY 1
    ORDER BY 3 desc
    LIMIT 100

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:

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

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:

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

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:

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