Retail Catchment

Deploy Retail Catchment Demo

Deploy the Retail Catchment Analysis demo that visualizes trade areas, competitors, and address density using OpenRouteService isochrones and Overture Maps data. Dashboard pages are served via the shared React Demo Dashboard app.

Configuration

Prerequisites

• OpenRouteService App installed (e.g., OPENROUTESERVICE_APP)
• A role with privileges listed in the Required Privileges section below
• snow CLI installed and configured

Required Privileges

Note: ACCOUNTADMIN is NOT required. Create a custom role with the above privileges, or use any role that has them.

Error Logging

Follow the Error Logging convention in AGENTS.md. Log file prefix: retail-catchment.

Execution Rules

1. One statement per snowflake_sql_execute tool call.
2. Always use fully qualified object names.
3. Never use SET session variables.
4. Verify row counts after each CTAS.
5. All CREATE statements must include a COMMENT tracking tag.

Workflow

Quick deploy: Run references/seed-data.sql via snow sql -f to execute Steps 4-5 in one shot. Edit the SET variables at the top to customize for your region. Expected duration: 3-5 minutes (queries large Overture Maps datasets).

Full SQL with step-by-step explanations: references/sql-pipeline.md All CREATE statements in the referenced SQL include COMMENT tracking tags per AGENTS.md convention ("origin":"sf_sit-is-fleet","name":"oss-retail-catchment").

Step 1: Set Query Tag for Tracking

Pre-check: If data already exists, skip to Step 6. Run:

SELECT COUNT(*) AS cnt FROM FLEET_INTELLIGENCE.CATCHMENT.POIS;

If cnt > 0, the data pipeline has already run. Skip to Step 6 (Verify) as needed.

Goal: Set session query tag for attribution tracking.

Execute:

ALTER SESSION SET query_tag = '{"origin":"sf_sit-is-fleet","name":"oss-retail-catchment","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}';

Step 2: Verify OpenRouteService Installation

Goal: Confirm OpenRouteService Native App is installed and services are running.

Execute:

SHOW SERVICES IN DATABASE OPENROUTESERVICE_APP;

If any services are SUSPENDED, resume them:

CALL OPENROUTESERVICE_APP.CORE.RESUME_ALL_SERVICES();

STOP if ORS is not installed. Direct user to install-fleet-apps skill.

Step 3: Get Carto Overture Datasets from Marketplace

Goal: Acquire Overture Maps Places and Addresses datasets for POI and density data.

Execute:

CALL SYSTEM$ACCEPT_LEGAL_TERMS('DATA_EXCHANGE_LISTING', 'GZT0Z4CM1E9KR');
CREATE DATABASE IF NOT EXISTS OVERTURE_MAPS__PLACES FROM LISTING GZT0Z4CM1E9KR;

CALL SYSTEM$ACCEPT_LEGAL_TERMS('DATA_EXCHANGE_LISTING', 'GZT0Z4CM1E9NQ');
CREATE DATABASE IF NOT EXISTS OVERTURE_MAPS__ADDRESSES FROM LISTING GZT0Z4CM1E9NQ;

Verify:

SELECT COUNT(*) FROM OVERTURE_MAPS__PLACES.CARTO.PLACE LIMIT 1;
SELECT COUNT(*) FROM OVERTURE_MAPS__ADDRESSES.CARTO.ADDRESS LIMIT 1;

Step 4: Create Database, Schema, Warehouse, and CONFIG

Goal: Set up the demo database, schema, warehouse, and CONFIG table.

See references/sql-pipeline.md Step 4.

Output: Database FLEET_INTELLIGENCE, schema CATCHMENT created with CONFIG table.

Step 5: Create Optimized Data Tables

Goal: Create pre-filtered, performance-optimized tables from Overture Maps marketplace data.

Important: Step 5 uses SQL session variables (SET REGION_KEY, SET BBOX_*). Execute all Step 5 sub-steps in a single session (e.g., via snow sql -f) or prepend the SET statements to each sub-step's SQL block when using snowflake_sql_execute.

1. Set region key and bounding box configuration (customize for target region)
2. Create and populate filtered POI table (POIS)
3. Create and populate pre-aggregated cities table (CITIES_BY_STATE)
4. Create and populate addresses table (REGIONAL_ADDRESSES)
5. Store region configuration (REGION_CONFIG)
6. Add search optimization and clustering
7. Verify tables have data

STOP if any table has 0 rows. Check bounding box config and Marketplace access.

See references/sql-pipeline.md Step 5.

Step 6: Verify

Goal: Confirm data tables exist and have rows.

See references/sql-pipeline.md Step 8.

Dashboard Schema Contract

The React Demo Dashboard page queries these exact tables and columns. If the pipeline changes column names, the React page must be updated to match.

CONFIG

POIS

CITIES_BY_STATE

REGIONAL_ADDRESSES

Features

The deployed app provides:

• Isochrone Analysis: Travel-time based catchment zones (1-60 min)
• Competitor Mapping: Find competitors within catchment areas
• H3 Address Density: Visualize residential density using hexagonal grid
• Smart Location Recommendation: AI-powered optimal new location suggestions
• Market Analysis: Synthetic footfall, population density, income data

Stopping Points

• ✋ Step 2: Verify ORS is installed before proceeding
• ✋ Step 3: Confirm marketplace data is accessible
• ✋ Step 5: Verify optimized tables have data before registering

Troubleshooting

Output

Deployed resources:

• Database: FLEET_INTELLIGENCE
• Schema: FLEET_INTELLIGENCE.CATCHMENT
• Warehouse: ROUTING_ANALYTICS
• Tables: CONFIG, POIS, CITIES_BY_STATE, REGIONAL_ADDRESSES, REGION_CONFIG

Cleanup

To remove all objects created by this skill:

DROP TABLE IF EXISTS FLEET_INTELLIGENCE.CATCHMENT.CONFIG;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.CATCHMENT.REGION_CONFIG;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.CATCHMENT.REGIONAL_ADDRESSES;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.CATCHMENT.CITIES_BY_STATE;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.CATCHMENT.POIS;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.CATCHMENT;

Tip: Use the cleanup skill to auto-discover all tagged objects via COMMENT tracking.