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:
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.
Ready to try out the CARTO Spatial Extension for Snowflake?Request access on Snowflake Marketplace
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.
There are a number of examples already available within the documentation, so this is just a highlight of a few of them.
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.
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)
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!
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...News
Please fill out the below form and we'll be in touch real soon.