Skip to content

Dataset 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 the Data page 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 "Data" 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.

Catalog Browser

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

Last update: May 17, 2024 13:46:47