Fleet Intelligence Car

Generate Driver Locations & Deploy Fleet Intelligence Dashboard

Generates realistic vehicle/driver location data using Overture Maps Places/Addresses, OpenRouteService Native App routing, route interpolation, and configurable location/fleet size. Also provides Data Studio projection views that read from SYNTHETIC_DATASETS.UNIFIED filtered by CONFIG table (vehicle type + region), making it compatible with any synthetic dataset.

IMPORTANT: Location Must Match OpenRouteService Configuration

Before selecting a location, verify your OpenRouteService Native App is configured for that region. Read and follow .cortex/skills/routing-customization/SKILL.md if a map change is needed.

Configuration Parameters

Supported Locations (Pre-configured)

Recommended Warehouse Sizes

Prerequisites

1. Snowflake Account with appropriate privileges
2. OpenRouteService Native App installed from Snowflake Marketplace (configured for target region)
3. Overture Maps Data -- auto-installed in Step 3b if missing. Requires IMPORT SHARE privilege.

Required Privileges

| USAGE ON DATABASE OPENROUTESERVICE_APP | DATABASE | Calls DIRECTIONS function for routing | | IMPORTED PRIVILEGES ON OVERTURE_MAPS__PLACES | Database | Reads POI locations | | IMPORTED PRIVILEGES ON OVERTURE_MAPS__ADDRESSES | Database | Reads address data |

Note: ACCOUNTADMIN is NOT required. Create a custom role with the above privileges.

Error Logging

Follow the Error Logging convention in AGENTS.md. Log file prefix: fleet-intelligence-car.

Quick Start

The fastest path to a working demo. Creates projection views over SYNTHETIC_DATASETS.UNIFIED tables (loaded by install-fleet-apps Step 8). No ORS calls needed.

These projection views are now dataset-scoped (V_FACT_TRIPS_CURRENT, V_FACT_VEHICLE_TELEMETRY_CURRENT, V_DIM_FLEET_CURRENT, V_DIM_POIS_CURRENT). Run Data Studio for the target preset before expecting rows.

Quick check

SELECT COUNT(*) FROM FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.TRIP_SUMMARY;

Note: Seed data uses vehicle_type=ebike (San Francisco e-bike fleet). The CONFIG table is set to ebike/SanFrancisco accordingly. To see car-fleet data, generate a new dataset via Data Studio with a car/driving-car profile.

Create views

Execute references/seed-data.sql. This creates CONFIG, VW_DRIVER_LOCATIONS, VW_TRIP_SUMMARY, and 5 wrapper views (TRIP_SUMMARY, DRIVER_LOCATIONS_V, ROUTE_NAMES, TRIP_ROUTE_PLAN, TRIPS_ASSIGNED_TO_DRIVERS) over UNIFIED data.

Generate data for other regions (optional)

To generate data for a region other than San Francisco, use the full pipeline starting at Step 2.

Or use the centralized provisioner:

CALL FLEET_INTELLIGENCE.CORE.PROVISION_REGION('<RegionName>', ARRAY_CONSTRUCT('fleet-intelligence-car'));

Workflow

Execute each step in order using snowflake_sql_execute. Substitute {PLACEHOLDER} values based on the user's chosen configuration before executing.

Read references/sql-pipeline.md for complete SQL for every step below.

CRITICAL: Execution Rules

1. One statement per snowflake_sql_execute tool call. Multi-statement blocks can silently fail. This rule applies to the snowflake_sql_execute tool only; snow sql -f and other CLI execution is fine.
2. Always use fully qualified object names (FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.<object>). Session context from USE statements does not persist across calls.
3. Never use SET session variables. Substitute literal values directly into SQL.
4. Verify row counts after each CTAS.
5. All CREATE statements must include a COMMENT tracking tag per AGENTS.md convention: COMMENT = '{"origin":"sf_sit-is-fleet","name":"oss-fleet-intelligence-car",...}'. See references/sql-pipeline.md for tagged SQL.

Step 1: Set Query Tag

Execute:

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

Step 2: Detect ORS Configuration, Choose Location, Verify Services

1. Describe ORS service to extract configured <REGION_NAME> from volume source path.
2. Download ORS config and parse enabled routing profiles. Follow .cortex/skills/routing-customization/read-ors-configuration/SKILL.md.
3. Ask user which location to use - recommend the currently configured region first.
4. Check region match - if mismatch, user must reconfigure ORS via .cortex/skills/routing-customization/SKILL.md.
5. Check/resume services - run SHOW SERVICES IN OPENROUTESERVICE_APP.CORE and resume any suspended services.
6. Test ORS routing with a DIRECTIONS call using center coordinates of the target city.

Step 3: Configure Database, Warehouse, and Schema

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

SELECT COUNT(*) AS cnt FROM FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.VEHICLE_LOCATIONS_INTERPOLATED;

If cnt > 0, the data pipeline has already run. Skip to Step 9 (analytics views) or Step 10 (Streamlit deployment) as needed.

Create FLEET_INTELLIGENCE database, ROUTING_ANALYTICS warehouse, and FLEET_INTELLIGENCE_CAR schema.

Step 3b: Check & Install Overture Maps Datasets

Check if Overture Maps datasets are accessible:

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

If either fails, install from Marketplace:

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;

STOP if install fails -- requires IMPORT SHARE privilege.

Step 4: Create Base Locations (VEHICLE_LOCATIONS)

CTAS combining POIs from OVERTURE_MAPS__PLACES.CARTO.PLACE and addresses from OVERTURE_MAPS__ADDRESSES.CARTO.ADDRESS filtered by the bounding box. Verify counts by SOURCE_TYPE.

Step 5: Create Drivers with Shift Patterns (VEHICLE_DRIVERS)

CTAS with 5 shift patterns (Graveyard, Early, Morning, Day, Evening). Default 80 drivers: 8/18/22/18/14. Verify driver counts per shift.

Step 6: Generate Trips

1. Materialize VEHICLE_LOCATIONS_NUMBERED with stable row numbers.
2. Create DRIVER_TRIPS - trip assignments with hour/pickup/dropoff location IDs. Trip counts vary by shift.
3. Create DRIVER_TRIPS_WITH_COORDS - join trips with coordinate geometry.
4. Verify trip distribution per shift.

Step 7: Generate ORS Routes

WARNING: This step makes many ORS API calls. ~1,000 trips: 3-5 min, ~5,000: 15-20 min.

1. Create DRIVER_ROUTES - call OPENROUTESERVICE_APP.CORE.DIRECTIONS for each trip.
2. Create DRIVER_ROUTES_PARSED - extract geometry, distance, duration from JSON response.
3. Create DRIVER_ROUTE_GEOMETRIES - add cumulative timing with {START_DATE}.
4. Verify route statistics.

Step 8: Create Driver Locations (DRIVER_LOCATIONS)

Interpolate 15 points per trip along route geometry with driver states (waiting, pickup, driving, dropoff, idle) and realistic speeds varying by time of day (rush hour, overnight, normal). Verify point counts and speed distributions.

Step 9: Create Analytics Views

Create 5 analytics views:

• DRIVER_LOCATIONS_V - locations with LAT/LON
• TRIPS_ASSIGNED_TO_DRIVERS - trip assignments with geometry
• ROUTE_NAMES - origin→destination labels
• TRIP_ROUTE_PLAN - full trip details with ROUTE JSON
• TRIP_SUMMARY - route geometries with avg/max speed

Verify all view row counts.

Dashboard Schema Contract

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

TRIP_SUMMARY (view)

DRIVER_LOCATIONS_V (view)

Examples

Example 1: Quick deploy with seed data

User says: "Set up the car fleet dashboard" Actions:

1. Run references/seed-data.sql to create projection views over UNIFIED tables
2. Verify TRIP_SUMMARY view returns rows Result: Fleet dashboard shows San Francisco e-bike fleet data via projection views (~2 min)

Example 2: Full generation for New York

User says: "Generate car-fleet data for New York with 80 drivers" Actions:

1. Verify ORS is configured for New York (Step 2)
2. Create database/schema (Step 3), install Overture Maps (Step 3b)
3. Create base locations from Overture POIs (Step 4), drivers (Step 5), trips (Step 6)
4. Generate ORS routes (Step 7), interpolate locations (Step 8)
5. Create analytics views (Step 9) Result: 80 drivers with 18,000 realistic GPS points across New York (8 min)

Troubleshooting

Cleanup

To remove all objects created by this skill:

-- Reverse dependency order: views, tables, schema
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.TRIP_SUMMARY;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.TRIP_ROUTE_PLAN;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.ROUTE_NAMES;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.TRIPS_ASSIGNED_TO_DRIVERS;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.DRIVER_LOCATIONS_V;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.DRIVER_LOCATIONS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.DRIVER_ROUTE_GEOMETRIES;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.DRIVER_ROUTES_PARSED;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.DRIVER_ROUTES;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.DRIVER_TRIPS_WITH_COORDS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.DRIVER_TRIPS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.VEHICLE_LOCATIONS_NUMBERED;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.VEHICLE_DRIVERS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR.VEHICLE_LOCATIONS;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR;

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