Geospatial Analysis in Snowflake

Overview

Prerequisites

What You Will Learn

What You Will Build

Creating a Worksheet and Copying in our SQL

Overview

Step 1 - Accessing Snowflake via URL

Step 2 - Logging into Snowflake

Step 3 - Navigating to Worksheets

Step 4 - Creating a Worksheet

Step 5 - Renaming a Worksheet

Step 6 - Accessing Quickstart SQL in GitHub

Step 7 - Copying Setup SQL from GitHub

Step 8 - Pasting Setup SQL from GitHub into your Snowflake Worksheet

Step 9 - Click Next -->

Acquiring SafeGraph POI Data from the Snowflake Marketplace

Overview

Step 1 - Acquiring SafeGraph POI Data from the Snowflake Marketplace

aside positive SafeGraph’s Places data provides detailed information about physical places. Our data is fresh, cleaned and provided in a format that empowers the user. With POI data for countries around the world, you can gain insights about any location that a person can visit aside from private residences.

Step 2 - Evaluating SafeGraph POI Data

USE ROLE sysadmin;
USE WAREHOUSE tb_de_wh;
USE DATABASE tb_101;

SELECT 
    cpg.placekey,
    cpg.location_name,
    cpg.longitude,
    cpg.latitude,
    cpg.street_address,
    cpg.city,
    cpg.country,
    cpg.polygon_wkt
FROM tb_safegraph.public.frostbyte_tb_safegraph_s cpg
WHERE 1=1
    AND cpg.top_category = 'Museums, Historical Sites, and Similar Institutions'
    AND cpg.sub_category = 'Museums'
    AND cpg.city = 'Paris'
    AND cpg.country = 'France';

Step 3 - Click Next -->

Harmonizing and Promoting First and Third Party Data

Overview

Step 1 - Enriching our Analytics View

CREATE OR REPLACE VIEW analytics.orders_v
COMMENT = 'Tasty Bytes Order Detail View'
    AS
SELECT 
    DATE(o.order_ts) AS date,
    o.* ,
    cpg.* EXCLUDE (location_id, region, phone_number, country)
FROM tb_101.harmonized.orders_v o
JOIN tb_safegraph.public.frostbyte_tb_safegraph_s cpg
    ON o.location_id = cpg.location_id;

aside negative Note: For demonstration purposes the SafeGraph listing already has the Tasty Bytes location_id's included within. In a real-world scenario the mapping would leverage public Placekeys which SafeGraph is a founding partner of.

Step 2 - Click Next -->

Creating Geograpy Points from Latitude and Longitude

Overview

Step 1 - Creating a Geography Point

aside positive ST_MAKEPOINT/ST_POINT: Constructs a GEOGRAPHY object that represents a point with the specified longitude and latitude.

USE ROLE tb_data_engineer;

SELECT TOP 10
    location_id,
    ST_MAKEPOINT(longitude, latitude) AS geo_point,
    SUM(price) AS total_sales_usd
FROM analytics.orders_v
WHERE primary_city = 'Paris'
GROUP BY location_id, latitude, longitude
ORDER BY total_sales_usd DESC;

Step 2 - Click Next -->

Calculating Straight Line Distance between Points

Overview

Step 1 - ST_DISTANCE

aside positive ST_DISTANCE: Returns the minimum geodesic distance between two GEOGRAPHY or the minimum Euclidean distance between two GEOMETRY objects.

WITH _top_10_locations AS
(
    SELECT TOP 10
        location_id,
        ST_MAKEPOINT(longitude, latitude) AS geo_point,
        SUM(price) AS total_sales_usd
    FROM analytics.orders_v
    WHERE primary_city = 'Paris'
    GROUP BY location_id, latitude, longitude
    ORDER BY total_sales_usd DESC
)
SELECT
    a.location_id,
    b.location_id,
    ROUND(ST_DISTANCE(a.geo_point, b.geo_point)/1609,2) AS geography_distance_miles,
    ROUND(ST_DISTANCE(a.geo_point, b.geo_point)/1000,2) AS geography_distance_kilometers
FROM _top_10_locations a
JOIN _top_10_locations b
    ON a.location_id <> b.location_id -- avoid calculating the distance between the point itself
QUALIFY a.location_id <> LAG(b.location_id) OVER (ORDER BY geography_distance_miles) -- avoid duplicate: a to b, b to a distances
ORDER BY geography_distance_miles;

Step 2 - Click Next -->

Collecting Coordinates, Creating a Bounding Polygon & Finding its Center Point

Overview

Step 1 - Create a Geographic Collection of Points and build our Minimum Bounding Polygon

aside positive ST_NPOINTS/ST_NUMPOINTS: Returns the number of points in a GEOGRAPHY or GEOGRAPHY object.

ST_COLLECT: This function combines all the GEOGRAPHY objects in a column into one GEOGRAPHY object.

ST_ENVELOPE: Returns the minimum bounding box (a rectangular “envelope”) that encloses a specified GEOMETRY object.

ST_AREA: Returns the area of the Polygon(s) in a GEOGRAPHY or GEOMETRY object.

WITH _top_10_locations AS
(
    SELECT TOP 10
        location_id,
        ST_MAKEPOINT(longitude, latitude) AS geo_point,
        SUM(price) AS total_sales_usd
    FROM analytics.orders_v
    WHERE primary_city = 'Paris'
    GROUP BY location_id, latitude, longitude
    ORDER BY total_sales_usd DESC
)
SELECT
    ST_NPOINTS(ST_COLLECT( geo_point)) AS count_points_in_collection,
    ST_COLLECT(geo_point) AS collection_of_points,
    ST_ENVELOPE(collection_of_points) AS minimum_bounding_polygon,
    ROUND(ST_AREA(minimum_bounding_polygon)/1000000,2) AS area_in_sq_kilometers
FROM _top_10_locations;

Step 2 - Finding our Top Selling Locations Center Point

aside positive ST_CENTROID: Returns the Point representing the geometric center of a GEOGRAPHY or GEOMETRY object.

WITH _top_10_locations AS
(
    SELECT TOP 10
        o.location_id,
        ST_MAKEPOINT(o.longitude, o.latitude) AS geo_point,
        SUM(o.price) AS total_sales_usd
    FROM analytics.orders_v o
    WHERE primary_city = 'Paris'
    GROUP BY o.location_id, o.latitude, o.longitude
    ORDER BY total_sales_usd DESC
)
SELECT
    ST_COLLECT(tl.geo_point) AS collect_points,
    ST_CENTROID(collect_points) AS geometric_center_point
FROM _top_10_locations tl;

Step 3 - Storing our Center Point in a Variable

Step 4 - Click Next -->

Finding Locations Furthest Away from our Top Selling Center Point

Overview

Step 1 - Top 50 Locations furthest away from our Top Selling Center Point in Paris

WITH _paris_locations AS
(
    SELECT DISTINCT
        location_id,
        location_name,
        ST_MAKEPOINT(longitude, latitude) AS geo_point
    FROM analytics.orders_v
    WHERE primary_city = 'Paris'
)
SELECT TOP 50
    location_id,
    location_name,
    ROUND(ST_DISTANCE(geo_point, TO_GEOGRAPHY($center_point))/1000,2) AS kilometer_from_top_selling_center
FROM _paris_locations
ORDER BY kilometer_from_top_selling_center DESC;

Step 2 - Click Next -->

Geospatial Analysis with H3 (Hexagonal Hierarchical Geospatial Indexing System)

Overview

Step 1 - Locate H3 Codes in Integer and String form

SELECT DISTINCT
    location_id,
    location_name,
    H3_LATLNG_TO_CELL(latitude, longitude, 4) AS h3_integer_resolution_4, 
    H3_LATLNG_TO_CELL_STRING(latitude, longitude, 4) AS h3_hex_resolution_4,
    H3_LATLNG_TO_CELL(latitude, longitude, 8) AS h3_integer_resolution_8, 
    H3_LATLNG_TO_CELL_STRING(latitude, longitude, 8) AS h3_hex_resolution_8,
    H3_LATLNG_TO_CELL(latitude, longitude, 12) AS h3_integer_resolution_12,
    H3_LATLNG_TO_CELL_STRING(latitude, longitude, 12) AS h3_hex_resolution_12
FROM analytics.orders_v
WHERE location_name = 'Musee du Louvre';

aside positive Resolution 4 = 288 thousand hexagons covering the globe Resolution 8 = 92 billion hexagons covering the globe Resolution 12 = 1.6 billion hexagons covering the globe

• For more detail on Resolutions please visit: H3 Geo: Tables of Cell Statistics Across Resolutions

Step 2 - Parent and Children Cell ID's

SELECT DISTINCT
    location_id,
    location_name,
    H3_LATLNG_TO_CELL(latitude, longitude, 8) AS h3_int_resolution_8,
    H3_INT_TO_STRING(h3_int_resolution_8) AS h3_hex_resolution_8, -- convert above to hexadecimal format
    H3_CELL_TO_PARENT(h3_int_resolution_8, 5) AS h3_int_parent_resolution_5, -- locate resolution 5 parent of our H3 cell
    H3_CELL_TO_CHILDREN(h3_int_resolution_8, 10) AS h3_int_children_resolution_10 -- locate all children at resolution 10
FROM analytics.orders_v
WHERE location_name = 'Musee du Louvre';

Step 3 - Locations within Top Selling Hexagons

WITH _top_50_locations AS
(
    SELECT TOP 50
        location_id,
        ARRAY_SIZE(ARRAY_UNIQUE_AGG(customer_id)) AS customer_loyalty_visitor_count,
        H3_LATLNG_TO_CELL(latitude, longitude, 7) AS h3_integer_resolution_6,
        H3_LATLNG_TO_CELL_STRING(latitude, longitude, 7) AS h3_hex_resolution_6,
        SUM(price) AS total_sales_usd
    FROM analytics.orders_v
    WHERE primary_city = 'Paris'
    GROUP BY ALL
    ORDER BY total_sales_usd DESC
)
SELECT
    h3_hex_resolution_6,
    COUNT(DISTINCT location_id) AS number_of_top_50_locations,
    SUM(customer_loyalty_visitor_count) AS customer_loyalty_visitor_count,
    SUM(total_sales_usd) AS total_sales_usd
FROM _top_50_locations
GROUP BY ALL
ORDER BY total_sales_usd DESC;

Step 4 - H3_GRID_DISTANCE

aside positive A neighbor cell is one step away and two cells with one hexagon between them are two steps apart.

SELECT H3_GRID_DISTANCE('871fb4671ffffff', '871fb4670ffffff') AS cell_distance;

Step 5 - Click Next -->

Conclusion and Next Steps

Conclusion

Next Steps