Analytics Toolbox for Snowflake

Analytics Toolbox for Snowflake

Running queries from Builder

Once you have gained access to the Analytics Toolbox through the Snowflake Data Marketplace (see Getting Access for details), you can create custom SQL layers in Builder that make use of any of the available spatial functions.

To get started, let’s run a simple example query to cluster a set of points using the ST_CLUSTERKMEANS function from the clustering module.

  1. Click on the Add source from button in Builder, that can be found at the bottom left of the screen.
Choosing a connection
  1. Select the second tab Custom Query (SQL) and pick the Snowflake connection that you will use to run the query. Please make sure this connection has access to the Analytics Toolbox database.
Picking a connection to run a custom query
  1. Click on Add source. A SQL console will be displayed.
Picking a connection to run a custom query
  1. Copy and paste the following query:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
WITH data AS(
  SELECT geog
  FROM sfcarto.public.starbucks_locations_usa
  WHERE geog IS NOT null
  ORDER BY id
),
clustered_points AS
(
    SELECT sfcarto.clustering.ST_CLUSTERKMEANS(ARRAY_AGG(ST_ASGEOJSON(geog)::STRING), 8) AS cluster_arr
    FROM data
)
SELECT GET(VALUE, 'cluster') AS cluster, TO_GEOGRAPHY(GET(VALUE, 'geom')) AS geom 
FROM clustered_points, lateral FLATTEN(input => cluster_arr)
  1. Run the query. This query computes eight clusters from the points of the starbucks_locations_usa table, provided as sample data within the Analytics Toolbox. As a result, each point is assigned a cluster ID.

  2. Style the layer by the cluster attribute.

Map add query