and

Extending Spatial Analytics on Snowflake with CARTO

Exciting news for Snowflake users! As part of our commitment to enabling geospatial analysis natively within the cloud, today we are announcing the launch of our Spatial Extension for the leading cloud data platform, Snowflake. Check out the official announcement on the Snowflake Blog.

We have been working with Snowflake for a number of months now to add more spatial capabilities to the popular data warehouse. Based on years of experience with PostGIS development, this is a great milestone that brings the full power of Spatial SQL to the fast growing community of Snowflake users.

Most spatial analysis needs to be performed together with other 3rd party data, so we are also happy to announce the availability of our Data Observatory on the Snowflake Data Marketplace, giving users seamless access to location data to enhance their spatial analyses.

There are many CARTO users already leveraging Snowflake to power their data infrastructure, and now with full cloud native support for Snowflake they will benefit from:

1. Increased performance. By running spatial analysis inside Snowflake, users will be able to utilize its parallelization capabilities.
2. Convenience and interoperability. By adding spatial support directly as an extension inSQL, our users are able to integrate geospatial capabilities within their BI tools, ETLs, Low Code apps, etc.

Enhanced connectivity and geospatial capabilities

Back in September we announced the availability of our Snowflake connector letting CARTO users seamlessly ingest spatial datasets hosted in the Snowflake cloud data platform, as a first step to becoming Cloud Native. With our latest development we now provide a wealth of spatial capabilities built within Snowflake.

The Spatial Extension for Snowflake is distributed as a set of User Defined Functions (UDFs) that extends the capabilities of Snowflake for everything geo. It is available as a Snowflake data share so there is no need to install or manage anything special, they just complement your existing Snowflake data warehouse.

This is a very innovative way of distributing new analytical capabilities, in fact, this is the first Snowflake Data Share that is used for distributing User Defined Functions. By working closely with Snowflake on this release we have been able to clearly see that this is going to be a fantastic distribution method for the future. We start by providing some foundational components, such as support for spatial indexes like H3 or S2, or functions for spatial measurements and further build on this by adding more advanced capabilities.

We are releasing the CORE of the Spatial Extension as Open Source under a BSD license. For the advanced package you will need a subscription to the CARTO platform.

Check out our documentation and a full reference to see what functions are currently available, and stay tuned as we will continue to add new ones.

Some example queries that you can perform with the Spatial Extension

There are a number of examples already available within the documentation, so this is just a highlight of a few of them.

Computing US airports connections and routes interpolations

Compute all the paths that interconnect the main four US airports using the Spatial Extension ST_GREATCIRCLE

WITH data AS(
SELECT 'SEA' AS abbrev, TO_GEOGRAPHY('POINT(-122.302289722924 47.4435819127259)') as geog UNION
SELECT 'MIA', TO_GEOGRAPHY('POINT(-80.2789718277441 25.7949407212406)') UNION
SELECT 'LAX', TO_GEOGRAPHY('POINT(-118.402468548522 33.9441742543586)') UNION
SELECT 'JFK', TO_GEOGRAPHY('POINT(-73.7863268609295 40.6459595584081)')
)
SELECT sfcarto.transformations.ST_GREATCIRCLE(t1.geog, t2.geog, 25) AS geom
FROM data AS t1
CROSS JOIN data AS t2
WHERE t1.abbrev != t2.abbrev


This query first creates all possible combinations between airports and then generates the paths between them using the ST_GREATCIRCLE function. The resulting paths contain 25 points, but you can set the number of points in order to make the lines smoother if needed (you could also exclude this parameter).

The result is displayed in this visualization. Notice that we are not using straight lines to interconnect the different airports, but great circles instead.

New supplier offices based on store locations clusters

In this example we are going to use points clustering to analyze where to locate 10 new supplier offices in the US so they can best serve all Starbucks locations.

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), 10) 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)


Analyzing store locations coverage using a Voronoi diagram

Voronoi diagrams are a very useful tool to calculate influence regions from a set of points and the Spatial Extension provides a convenient function to build them. An example application of these diagrams is the calculation of the coverage areas of a series of Starbucks stores. In the following query we are going to calculate these influence areas in Atlanta.

The result can be seen in the visualization below, where the color of each polygon indicates its area, which gives an insight on the coverage provided by each store.

WITH starbucks AS
(
SELECT geog
FROM sfcarto.public.STARBUCKS_LOCATIONS_USA
WHERE CITY = 'Atlanta' AND geog IS NOT NULL
ORDER BY id
),
starbucks_array AS (
SELECT ARRAY_AGG(ST_ASGEOJSON(geog)::STRING) AS geog_array
FROM starbucks
),
voronoi_array AS (
SELECT sfcarto.accessors.ST_ENVELOPE(geog_array) AS envelope,
sfcarto.processing.ST_VORONOIPOLYGONS(geog_array, ARRAY_CONSTRUCT(ST_XMIN(envelope), ST_YMIN(envelope), ST_XMAX(envelope), ST_YMAX(envelope))) AS nested_voronoi
FROM starbucks_array
)
SELECT TO_GEOGRAPHY(VALUE) AS geom, ST_AREA(geom) AS area FROM voronoi_array, lateral FLATTEN(input => nested_voronoi)


These are just a few examples of how some of the 60 or so geospatial functions currently available as part of the CARTO Spatial Extension for Snowflake can be used to seamlessly enhance user data with 3rd party sources and surface valuable location-based insights right inside the Snowflake cloud data platform.

If you would like a personalized demo of the Spatial Extension for Snowflake, reach out to us on the link below. Our team is really excited to showcase its full potential!

Want to see this in action?

Javier de la Torre is founder and Chief Strategy Officer of CARTO. One of the pioneers of location intelligence, Javier founded the company with a vision to democratize data analysis and visualization. Under his leadership, CARTO has grown from a groundbreaking idea into one of the fastest growing geospatial companies in the world.
In 2007, he founded Vizzuality, a renowned geospatial company dedicated to bridging the gap between science and policy making by the better use of data.

Product Manager at CARTO

• What we learned: #SDSC22 New York

The 19th-20th October 2022 marked a huge day in the Location Intelligence calendar - the Spatial Data Science Conference NYC!

• What's New in CARTO - Q3 2022

This month we celebrate the first anniversary since the General Availability launch of the cloud native CARTO platform. During the past 12 months, we have continued to focu...

• Databricks support for H3 in collaboration with CARTO

Over the past few months we have been working with Databricks to add built-in support for H3, and this added functionality was released recently. Native support for H3 mean...