Overview

Prerequisites

What You’ll Learn

What You’ll Need

What You’ll Build

Acquire Marketplace Data and Analytics Toolbox

Access Snowflake's Web UI

If this is not the first time you are logging into the Snowflake UI, you should see a "Select an account to sign into" prompt and a button for your account name listed below it. Click the account you wish to access and you will be prompted for your user name and password (or another authentication mechanism).

Increase Your Account Permission

If you don't have the ACCOUNTADMIN role, switch to a role with IMPORT SHARE privileges instead.

Create a Virtual Warehouse (if needed)

Acquire Data from the Snowflake Marketplace

On the Get screen, you may be prompted to complete your user profile if you have not done so before. Click the link as shown in the screenshot below. Enter your name and email address into the profile screen and click the blue Save button. You will be returned to the Get screen.

Install CARTO Analytics Toolbox from the Snowflake Marketplace

Connect Snowflake and CARTO

Connection to Snowflake

Use MY_WH or the name of the data warehouse you created in the previous step otherwise some queries will fail because CARTO won't know which warehouse to run them against.

Understand Geospatial Formats

Open a New Worksheet and Choose Your Warehouse

The GEOGRAPHY data type

// Set the working database schema
use schema osm_newyork.new_york;

// Describe the v_osm_ny_shop_electronics view 
desc view v_osm_ny_shop_electronics;

View GEOGRAPHY Output Formats

// Set the output format to GeoJSON
alter session set geography_output_format = 'GEOJSON';

// Query the v_osm_ny_shop_electronics view for rows of type 'node' (long/lat points)
select coordinates, name from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  where type = 'node' limit 25;

{   "coordinates": [     -7.390351649999999e+01,     4.074499730000000e+01   ],   "type": "Point" }

// Query the v_osm_ny_shop_electronics view for rows of type 'way' (a collection of many points)
select coordinates, name from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  where type = 'way' limit 25;

// Set the output format to WKT
alter session set geography_output_format = 'WKT';

select coordinates, name from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  where type = 'node' limit 25;
select coordinates, name from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  where type = 'way' limit 25;

// An example of a POINT
POINT(-74.0266511 40.6346599)
// An example of a POLYGON
POLYGON((-74.339971 43.0631175,-74.3397734 43.0631363,-74.3397902 43.0632306,-74.3399878 43.0632117,-74.339971 43.0631175))

You will use several different geospatial object types in this guide, and the guide will explain them more in later sections as you use them.

// Set the output format to WKB
alter session set geography_output_format = 'WKB';

select coordinates, name from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  where type = 'node' limit 25;
select coordinates, name from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  where type = 'way' limit 25;

Unload/Load Data

Create New WKB Files From Queries

The WKB format is being chosen here for its simplicity within a file. Since WKB is a single alpha-numeric string with no delimiters, spaces, or other difficult characters, it is excellent for storing geospatial data in a file. That doesn't mean other formats are to be avoided in real world use cases, but WKB will make your work easier in this guide.

alter session set geography_output_format = 'WKB';

// Define the write location (@~/ = my user stage) and file name for the file 
copy into @~/osm_ny_shop_electronics_all.csv 
// Define the query that represents the data output
from (select id,coordinates,name,type from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics ) 
// Indicate the comma-delimited file format and tell it to double-quote strings
file_format=(type=csv field_optionally_enclosed_by='"') 
// Tell Snowflake to write one file and overwrite it if it already exists
single=true overwrite=true;

copy into @~/osm_ny_shop_electronics_points.csv 
from (
  select id,coordinates,name,type,st_x(coordinates),st_y(coordinates) 
  from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  where type='node'
) file_format=(type=csv field_optionally_enclosed_by='"') 
single=true overwrite=true;

LIST and Query User Staged Files

list @~/osm;

select $1,$2,$3,$4 from @~/osm_ny_shop_electronics_all.csv;

// Create a new local database
create or replace database geocodelab;
// Change your working schema to the public schema in that database
use schema geocodelab.public;
// Create a new file format in that schema
create or replace file format geocsv type = 'csv' field_optionally_enclosed_by='"';
// Set the output format back to WKT
alter session set geography_output_format = 'WKT';

select $1,TO_GEOGRAPHY($2),$3,$4 
from @~/osm_ny_shop_electronics_all.csv 
(file_format => 'geocsv');

// Create a new 'all' table in the current schema
create or replace table electronics_all 
(id number, coordinates geography, name string, type string);
// Load the 'all' file into the table
copy into electronics_all from @~/osm_ny_shop_electronics_all.csv 
file_format=(format_name='geocsv');

select $1,ST_MAKEPOINT($5,$6),$3,$4,$5,$6 
from @~/osm_ny_shop_electronics_points.csv 
(file_format => 'geocsv');

Notice in ST_MAKEPOINT that the longitude column is listed first. Despite the common verbal phrase "lat long," you always put longitude before latitude to represent a geospatial POINT object in Snowflake.

// Create a new 'points' table in the current schema
create or replace table electronics_points 
(id number, coordinates geography, name string, type string, 
long number(38,7), lat number(38,7));
// Load the 'points' file into the table
copy into electronics_points from (
  select $1,ST_MAKEPOINT($5,$6),$3,$4,$5,$6 
  from @~/osm_ny_shop_electronics_points.csv
) file_format=(format_name='geocsv');

In the ‘all' file load statement, you didn't have to specify a query to load the file because when you have a column in a file that is already in a Snowflake supported geospatial format, and load that value into a GEOGRAPHY typed column, Snowflake automatically does the geospatial construction for you. In the ‘points' file, however, you must use a transform query to construct two discrete columns into a single GEOGRAPHY column using a geospatial constructor function.

select * from electronics_all;
select * from electronics_points;

Calculations and More Constructors

It's worth noting here that the scenario in the next three sections is more akin to what a person would do with a map application on their mobile phone, rather than how geospatial data would be used in fictional business setting. This was chosen intentionally to make this guide and these queries more relatable to the person doing the guide, rather than trying to create a realistic business scenario that is relatable to all industries, since geospatial data is used very differently across industries.

use schema osm_newyork.new_york;
// Run just one of the below queries based on your preference
alter session set geography_output_format = 'GEOJSON';
alter session set geography_output_format = 'WKT';

The Scenario

select to_geography('POINT(-73.986226 40.755702)');

POINT(-73.986226 40.755702) is already a geography object in WKT format, so there was no real need to convert it again, but it was important to show the most basic way to use TO_GEOGRAPHY to construct a simple geography object.

select to_geography('POINT(-73.986226 40.755702)') as geom;

CARTO requires that the column containing geospatial data be named geom so that is why we add as geom in the query. If you don't do this it will fail.

Feel free to use this as your SQL Editor for the next steps, you can delete and re-run the queries from the workshop here.

Find the Closest Locations

// Find the closest Best Buy
select id, coordinates as geom, name, addr_housenumber, addr_street, 
// Use st_distance to calculate the distance between your location and Best Buy
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  
// Filter just for Best Buys
where name = 'Best Buy' and 
// Filter for Best Buys that are within about a US mile (1600 meters)
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
// Order the results by the calculated distance and only return the lowest
order by 6 limit 1;

// Find the closest liquor store
select id, coordinates as geom, name, addr_housenumber, addr_street, 
st_distance(geom,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages  
where shop = 'alcohol' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 6 limit 1;

// Find the closest coffee shop
select id, coordinates as geom, name, addr_housenumber, addr_street, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages  
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 6 limit 1;

Collect Points Into a Line

// Create the CTE 'locations'
with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates as geom, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics 
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select coordinates as geom, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages 
where shop = 'alcohol' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select coordinates as geom, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages 
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
// Query the CTE result set, aggregating the coordinates into one object
Select st_makeline(st_collect(coordinates),to_geography('POINT(-73.986226 40.755702)')) as geom from locations;

You may be wondering why your current position point was added as an additional point in the line when you already included it as the first point in the MULTIPOINT collection above? Stay tuned for why you need this later, but logically it makes sense that you plan to go back to your New York City apartment at the end of your shopping trip.

// Create the CTE 'locations'
with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates as geom, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics 
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select coordinates as geom, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages 
where shop = 'alcohol' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select coordinates as geom, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages 
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
// Query the CTE result set, aggregating the coordinates into one object
Select st_makeline(st_collect(coordinates),to_geography('POINT(-73.986226 40.755702)')) as geom ,
st_length(geom)  as distance
from locations;

It is correct to note that this distance represents a path based on how a bird would fly, rather than how a human would navigate the streets. The point of this exercise is not to generate walking directions, but rather to give you a feel of the various things you can parse, construct, and calculate with geospatial data and functions in Snowflake. CARTO actually lets you to calculate drive/walk routes within Snowflake with its Location Data Services module if you're interested in a more accurate calculation.

Joins

Is There Anything Closer to Best Buy?

In order to create a new layer ( and keep the query done in the previous step) click on the blue (+) Add source from button, copy the query and click run. .

// Join to electronics to find a liquor store closer to Best Buy
select fb.id,fb.coordinates as geom,fb.name,fb.addr_housenumber,fb.addr_street,
// The st_distance calculation uses coordinates from both views
st_distance(e.coordinates,fb.coordinates) as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  e 
// The join is based on being within a certain distance
join OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
// Hard-coding the known Best Buy id below
where e.id = 1428036403 and fb.shop = 'alcohol' 
// Ordering by distance and only showing the lowest
order by 6 limit 1;

// Join to electronics to find a coffee shop closer to Best Buy
select fb.id,fb.coordinates as geom,fb.name,fb.addr_housenumber,fb.addr_street,
st_distance(e.coordinates,fb.coordinates) as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  e 
join OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
where e.id = 1428036403 and fb.shop = 'coffee' 
order by 6 limit 1;

Make sure you style the result to make the point / lines bigger or more colorful so that you can see them.

The id of the selected Best Buy was hard coded into the above queries to keep them easier to read and to keep you focused on the join clause of these queries, rather than introducing sub queries to dynamically calculate the nearest Best Buy. Those sub queries would have created longer queries that were harder to read.

If you're feeling adventurous, go read about other possible relationship functions that could be used in the join for this scenario here.

Calculate a New Linestring

with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages  
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select fb.coordinates, st_distance(e.coordinates,fb.coordinates) as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  e 
join OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
where e.id = 1428036403 and fb.shop = 'alcohol' 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
select st_makeline(st_collect(coordinates),
to_geography('POINT(-73.986226 40.755702)')) as geom from locations;

with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages  
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select fb.coordinates, st_distance(e.coordinates,fb.coordinates) as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  e 
join OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
where e.id = 1428036403 and fb.shop = 'alcohol' 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
select st_makeline(st_collect(coordinates),
to_geography('POINT(-73.986226 40.755702)')) as geom,
st_length(geom) as distance
from locations;

Additional Calculations and Constructors

Remember in a previous section when you added your Times Square Apartment location to both the beginning and the end of the LINESTRING? In addition to the logical explanation of returning home after your shopping trip, that point was duplicated at the beginning and end so you can construct a POLYGON in this section!

Construct a Polygon

select st_makepolygon(st_makeline(st_collect(coordinates),
to_geography('POINT(-73.986226 40.755702)')))

with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages  
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select fb.coordinates, st_distance(e.coordinates,fb.coordinates) as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  e 
join OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
where e.id = 1428036403 and fb.shop = 'alcohol' 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
select st_makepolygon(st_makeline(st_collect(coordinates),
to_geography('POINT(-73.986226 40.755702)'))) as geom
from locations

with locations as (
(select to_geography('POINT(-73.986226 40.755702)') as coordinates, 
0 as distance_meters)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages  
where shop = 'coffee' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1)
union all
(select fb.coordinates, st_distance(e.coordinates,fb.coordinates) as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  e 
join OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_food_beverages fb on st_dwithin(e.coordinates,fb.coordinates,1600) 
where e.id = 1428036403 and fb.shop = 'alcohol' 
order by 2 limit 1)
union all
(select coordinates, 
st_distance(coordinates,to_geography('POINT(-73.986226 40.755702)'))::number(6,2) 
as distance_meters 
from OSM_NEWYORK.NEW_YORK.v_osm_ny_shop_electronics  
where name = 'Best Buy' and 
st_dwithin(coordinates,st_makepoint(-73.986226, 40.755702),1600) = true 
order by 2 limit 1))
select st_makepolygon(st_makeline(st_collect(coordinates),
to_geography('POINT(-73.986226 40.755702)'))) as geom,
st_perimeter(geom) as perimeter_meters
from locations

Using Spatial Indexes

The Scenario, Part 2

SELECT GEOG AS GEOM, store_name
FROM CARTO.public.starbucks_locations_usa
WHERE CITY = 'New York'

We are using a sample dataset included in the CARTO Analytics Toolbox named starbucks_locations_usa. You can find it under PUBLIC. So the full qualified name should be something like CARTO.public.starbucks_locations_usa.

WITH data AS (
  SELECT carto.carto.QUADINT_FROMGEOGPOINT(geog, 15) AS qk,
  COUNT(*) as agg_total
  FROM carto.public.starbucks_locations_usa
  WHERE geog IS NOT null
  AND CITY = 'New York'
  GROUP BY qk
)
SELECT
  qk,
  agg_total,
  carto.carto.QUADINT_BOUNDARY(qk) AS geom
FROM
  data

Conclusion

What we've covered