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 Open 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