Skip to content

Snowflake

You will need to perform the following operations using a Snowflake user with the accountadmin role.

Optional: Create a new Snowflake Database

You can either use an existing database or create a new database for Wherobots to use. Wherobots will install Sedona to your chosen Database.

-- Create a new database
CREATE DATABASE {DB_NAME};

Setup Permissions for Wherobots through Snowflake

To enable Wherobots to deploy to your environment, we need the user to prepare the following (please replace DB_NAME and WAREHOUSE_NAME):

Setup Warehouse/Database permissions

-- First, create a new role for Wherobots to use
CREATE ROLE WHEROBOTS_ROLE;

-- Allow the role to use and run queries on a warehouse
GRANT USAGE,OPERATE ON warehouse {WAREHOUSE_NAME} TO WHEROBOTS_ROLE;

-- Allow the role to use the target DB
GRANT USAGE ON DATABASE {DB_NAME} TO WHEROBOTS_ROLE;

Setup Schema Permissions

Wherobots registers functions in a hardcoded schema named sedona. You need to allow Wherobots to create the schema for you

GRANT CREATE SCHEMA ON DATABASE {DB_NAME} TO WHEROBOTS_ROLE;
GRANT ALL ON FUTURE SCHEMAS IN DATABASE {DB_NAME} TO WHEROBOTS_ROLE;
GRANT ALL ON FUTURE stages in DATABASE {DB_NAME} to WHEROBOTS_ROLE;

Optional: assign the Role to a User

The last step is to assign the role to an existing user or a new user. You can create a new user for Wherobots as follows:

-- Create user
CREATE USER wherosnow PASSWORD='XXXXXX';
-- Grant role to user
GRANT ROLE wherobots_role TO USER wherosnow;

Get your Snowflake Credentials

The credentials required to use Snowflake from Wherobots are: * Account Identifier * Username * Password * Role (created above)

Steps to get your Account Identifier

  1. Login to your Snowflake account
  2. Click the icon that says "Copy account identifier" Copy Account Identifier

The account identifier should be in this format: XHNABCDE-WLB12345. If the copied identifier is in a different format such as XHNABCDE.WLB12345, please replace . with -.

Deploy Sedona to Snowflake

  1. Navigate to the Wherobots Cloud Providers Page and select Snowflake Select Snowflake
  2. Enter the credentials you obtained above. For the Account Identifier, paste value from above and change the period to a hyphen. (ex. XXXXXXX.WLB00000 => XXXXXXX-WLB00000) Snowflake Credentials
  3. Select the database you want to use and click "Set Up Sedona" Setup Sedona
  4. Hit install on the dialog and wait for Wherobots to complete installation to your database. Install Sedona
  5. Done!

Run Spatial Queries on Snowflake

After the installation done, you can start using Sedona functions. Please log in to Snowflake again using the user that has the privilege to access the database. In our example, this user is wherosnow.

A good practice is to always keep at least the schema name "SEDONA" to avoid conflicting with Snowflake's built-in functions

SELECT SEDONA.ST_S2CellIDs(SEDONA.ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'), 9);

Sedona uses EWKB type as the input/output type for geometry. If you have datasets of built-in Snowflake GEOMETRY/GEOGRAPHY type, you can easily transform them into EWKB through this function.

SELECT SEDONA.ST_X(st_asewkb(geom)) FROM {{ geometry_table }};

If you wish to pass the result of Sedona functions to built-in Snowflake spatial functions, you just need to call the built-in to_geometry function. As the below example shows, you can to_geometry(sedona_result) and then call the Snowflake functions from then on.

SELECT st_area(to_geometry(SEDONA.ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')));

Last update: August 30, 2023 18:56:20