Geospatial AI for Vision Zero — turning crash data into life-saving decisions
Quick Start • Architecture • Notebooks • Deploy • Production Use
Every year, 42,000+ people die on U.S. roads. State DOTs and MPOs run Vision Zero and Toward Zero Deaths (TZD) programs — national strategies committed to eliminating all traffic fatalities and serious injuries. But they face a critical bottleneck:
| Challenge | Impact |
|---|---|
| Siloed crash data | Crash records, telematics, weather, and infrastructure live in separate systems |
| GIS analyst bottleneck | Decision-makers queue requests with specialists instead of self-serving insights |
| Reactive, not predictive | Agencies respond after fatalities instead of preventing them |
| No natural-language access | Planners write SQL or wait for dashboards — neither scales |
"Public responsibility moves faster than public data." — The gap this platform closes.
Spatial Safety Analytics is a production-ready geospatial AI platform that brings crash records, mobility data, telematics, and infrastructure into a single spatial decision system — powered by BigQuery, CARTO, H3 hexagonal indexing, and a Gemini generative-AI layer for natural-language interaction.
┌─────────────────────────────────────────────────────────────────────────────┐
│ SPATIAL SAFETY ANALYTICS │
│ │
│ "Where do fatal run-off-road crashes cluster on I-94 in winter?" │
│ │
│ → Gemini interprets the question │
│ → BigQuery + CARTO execute the spatial query │
│ → H3 hexagonal hotspot map renders in seconds │
│ → AI drafts a safety summary with countermeasure recommendations │
└─────────────────────────────────────────────────────────────────────────────┘
graph TB
subgraph DATA["🗂️ Data Sources"]
CRASH[("Crash Records<br/>FARS · State DOT")]
TELEM[("Telematics<br/>Google RMI · IoT")]
WEATHER[("Weather<br/>NOAA · NWS")]
INFRA[("Infrastructure<br/>Road Geometry · Signals")]
CENSUS[("Demographics<br/>Census · ACS")]
end
subgraph INGEST["⚡ Ingestion Layer"]
PUBSUB["Cloud Pub/Sub<br/>Real-time streams"]
GCS["Cloud Storage<br/>Batch uploads"]
DTS["BigQuery DTS<br/>Scheduled transfers"]
end
subgraph WAREHOUSE["🏗️ Analytics Engine — BigQuery"]
BQ_RAW["Raw Tables<br/>Partitioned by date"]
BQ_H3["H3 Indexed Tables<br/>Hexagonal spatial index"]
CARTO_UDF["CARTO Analytics Toolbox<br/>100+ spatial UDFs"]
BQ_ML["BigQuery ML<br/>Crash prediction models"]
end
subgraph SPATIAL["🗺️ Spatial Analytics — CARTO"]
BUILDER["CARTO Builder<br/>Interactive dashboards"]
HOTSPOT["Space-Time Hotspots<br/>H3 clustering"]
CORRIDOR["Corridor Analysis<br/>Route-segment scoring"]
OD["Origin-Destination<br/>Mobility flow analysis"]
end
subgraph AI["🤖 Gemini AI Layer"]
NLQ["Natural Language Query<br/>Plain-English → SQL"]
SUMMARY["AI Safety Summaries<br/>Countermeasure drafts"]
AGENT["Agentic GIS<br/>Reusable spatial skills"]
end
subgraph OUTPUT["📊 Decision Layer"]
DASH["Live Dashboards<br/>CARTO + Looker"]
REPORT["Safety Reports<br/>AI-generated briefs"]
API_OUT["REST API<br/>Agency integrations"]
PUBLIC["Open Data Portal<br/>Citizen transparency"]
end
CRASH & TELEM & WEATHER & INFRA & CENSUS --> PUBSUB & GCS & DTS
PUBSUB & GCS & DTS --> BQ_RAW
BQ_RAW --> BQ_H3
BQ_H3 --> CARTO_UDF
CARTO_UDF --> BQ_ML
BQ_H3 --> BUILDER
BUILDER --> HOTSPOT & CORRIDOR & OD
BQ_H3 --> NLQ
NLQ --> SUMMARY
SUMMARY --> AGENT
HOTSPOT & CORRIDOR & OD --> DASH
AGENT --> REPORT
DASH & REPORT --> API_OUT & PUBLIC
style DATA fill:#E8F0FE,stroke:#4285F4
style INGEST fill:#FEF7E0,stroke:#F9AB00
style WAREHOUSE fill:#E6F4EA,stroke:#34A853
style SPATIAL fill:#FCE8E6,stroke:#EA4335
style AI fill:#F3E8FD,stroke:#8E75B2
style OUTPUT fill:#E8F0FE,stroke:#4285F4
Crash records, road geometry, weather, and telematics are unified in BigQuery with H3 hexagonal spatial indexing for ultra-fast geospatial queries at any resolution.
graph LR
subgraph H3["H3 Hexagonal Grid System"]
R4["Resolution 4<br/>~1,770 km²<br/>State overview"]
R7["Resolution 7<br/>~5.16 km²<br/>County analysis"]
R9["Resolution 9<br/>~0.105 km²<br/>Intersection-level"]
R4 --> R7 --> R9
end
style H3 fill:#FFF3E0,stroke:#FF6F00
-- Example: H3 crash hotspot aggregation using CARTO Analytics Toolbox
SELECT
`carto-un`.carto.H3_FROMGEOGPOINT(crash_location, 7) AS h3_index,
COUNT(*) AS crash_count,
COUNTIF(severity = 'FATAL') AS fatal_count,
AVG(vehicles_involved) AS avg_vehicles,
ARRAY_AGG(DISTINCT contributing_factor) AS factors
FROM `project.safety.crash_records`
WHERE crash_date BETWEEN '2023-01-01' AND '2024-12-31'
GROUP BY h3_index
HAVING crash_count >= 5
ORDER BY fatal_count DESCCARTO runs natively on BigQuery — no ETL, no data movement. The Analytics Toolbox provides 100+ spatial UDFs for advanced analysis:
| Capability | What It Does | TZD Application |
|---|---|---|
| Space-Time Hotspots | Identifies statistically significant crash clusters across space AND time | Pinpoints where and when crashes concentrate |
| H3 Spatial Indexing | Hexagonal grid aggregation at multiple resolutions | Consistent analysis from state-level down to intersections |
| Corridor Analysis | Scores route segments by composite risk metrics | Prioritizes infrastructure investment per corridor |
| Origin-Destination | Analyzes commuter flow patterns against crash locations | Reveals exposure-adjusted risk |
| Isochrone Analysis | Maps reachable areas by travel time from crash sites | Optimizes EMS station placement |
The differentiator: a generative-AI layer that lets any stakeholder — not just GIS specialists — interact with spatial safety data in plain English.
sequenceDiagram
participant User as 👤 Safety Engineer
participant Gemini as 🤖 Gemini 2.5
participant BQ as 🏗️ BigQuery
participant CARTO as 🗺️ CARTO
User->>Gemini: "Show me fatal run-off-road crashes<br/>near schools in Hennepin County<br/>during winter months"
Gemini->>BQ: Generated SQL with H3 + spatial filters
BQ->>CARTO: Spatial query results with H3 indexes
CARTO->>Gemini: Hotspot clusters + corridor scores
Gemini->>User: Interactive map + AI safety summary:<br/>"12 fatal clusters identified near 8 schools.<br/>Top countermeasure: cable median barriers<br/>on segments 47-52 of County Road 81."
Sample AI-Generated Safety Summary:
Corridor Risk Assessment — I-94 Westbound, MP 210-225
📍 23 fatal crashes in the analysis period, concentrated in 3 H3-R9 hexagons near the interchange at MP 217.
🌧️ 78% occurred during wet/icy conditions (Nov–Mar), with run-off-road as the dominant crash type (65%).
🛡️ Recommended countermeasures: High-tension cable barrier (CMF: 0.56), enhanced curve signage, and friction surface treatment.
💰 Estimated B/C ratio: 3.2:1 based on FHWA CMF Clearinghouse values.
This architecture has been implemented in production for state-level safety programs:
|
|
Value delivered: Agencies shifted from reactive incident response to proactive, data-driven prevention — surfacing risk before conditions turn fatal.
spatial-safety-analytics/
│
├── README.md # You are here
├── LICENSE
│
├── src/
│ ├── ingestion/
│ │ ├── fars_crash_loader.py # NHTSA FARS data ingestion to BigQuery
│ │ ├── weather_enrichment.py # NOAA weather overlay pipeline
│ │ └── rmi_stream_ingest.py # Google Roads Management Insights streaming
│ │
│ ├── analytics/
│ │ ├── h3_hotspot_analysis.sql # H3 hexagonal crash clustering queries
│ │ ├── corridor_risk_scoring.sql # Route-segment composite risk scoring
│ │ ├── space_time_clusters.sql # CARTO space-time hotspot detection
│ │ └── od_exposure_analysis.sql # Origin-destination crash exposure
│ │
│ └── gemini_layer/
│ ├── nl_query_engine.py # Natural-language → SQL translation
│ ├── safety_summary_agent.py # AI safety report generator
│ └── prompts/
│ ├── crash_query.txt # Prompt template for crash queries
│ └── countermeasure.txt # Prompt template for recommendations
│
├── notebooks/
│ ├── 01_crash_data_eda.ipynb # Exploratory analysis of FARS data
│ ├── 02_h3_spatial_indexing.ipynb # H3 indexing and resolution analysis
│ ├── 03_hotspot_detection.ipynb # Space-time hotspot walkthrough
│ ├── 04_corridor_scoring.ipynb # Corridor risk scoring methodology
│ └── 05_gemini_nl_queries.ipynb # Natural-language query demo
│
├── infrastructure/
│ ├── main.tf # Terraform — BigQuery, Pub/Sub, IAM
│ ├── variables.tf # Configurable project/region variables
│ ├── carto_connection.tf # CARTO ↔ BigQuery connection setup
│ └── outputs.tf # Resource endpoints
│
├── config/
│ ├── h3_resolutions.yaml # H3 resolution mapping per use case
│ ├── crash_severity_weights.yaml # KABCO severity weighting scheme
│ └── data_sources.yaml # Data source registry
│
├── data/
│ └── sample/
│ ├── sample_crashes.csv # 1000-row sample for local testing
│ └── h3_hexagons.geojson # Sample H3 grid for visualization
│
└── docs/
├── images/
│ └── banner.svg # Repository banner
├── ARCHITECTURE.md # Detailed architecture deep-dive
└── CARTO_SETUP.md # CARTO + BigQuery connection guide
| Tool | Version | Purpose |
|---|---|---|
| Python | >= 3.10 | Core runtime |
| Google Cloud SDK | latest | gcloud CLI authentication |
| Terraform | >= 1.5 | Infrastructure provisioning |
| CARTO Account | — | Free trial with BigQuery connection |
git clone https://github.com/genaigeek1/spatial-safety-analytics.git
cd spatial-safety-analytics
python -m venv .venv && source .venv/bin/activate
pip install -r requirements.txtcd infrastructure
cp terraform.tfvars.example terraform.tfvars
# Edit terraform.tfvars with your GCP project ID
terraform init && terraform apply# Load NHTSA FARS crash data into BigQuery
python src/ingestion/fars_crash_loader.py \
--project your-project-id \
--dataset safety_analytics \
--years 2019-2024
# Enrich with weather overlays
python src/ingestion/weather_enrichment.py \
--project your-project-id \
--dataset safety_analytics# Open Jupyter notebooks for interactive walkthrough
jupyter lab notebooks/from src.gemini_layer.nl_query_engine import SafetyQueryEngine
engine = SafetyQueryEngine(project="your-project-id")
response = engine.query(
"Where do pedestrian fatalities cluster near transit stops in Honolulu?"
)
print(response.summary)
response.show_map() # Renders CARTO H3 hexagonal hotspot mapThis project uses CARTO's cloud-native spatial analytics running directly on BigQuery:
graph LR
BQ["BigQuery<br/>Crash data warehouse"] -->|"Native connection<br/>No ETL"| CARTO["CARTO Platform"]
CARTO --> AT["Analytics Toolbox<br/>100+ spatial UDFs"]
CARTO --> VIZ["Builder<br/>Interactive maps"]
AT --> H3["H3 Indexing"]
AT --> ST["Space-Time Analysis"]
AT --> ISO["Isochrones"]
style BQ fill:#E6F4EA,stroke:#34A853
style CARTO fill:#FCE8E6,stroke:#EA4335
style AT fill:#FEF7E0,stroke:#F9AB00
Key CARTO functions used:
-- H3 hexagonal indexing
SELECT `carto-un`.carto.H3_FROMGEOGPOINT(geom, 7) AS h3_id FROM crashes;
-- Space-time hotspot detection (Getis-Ord Gi*)
CALL `carto-un`.carto.GETIS_ORD_H3(
'project.dataset.h3_crash_counts', 'h3_id', 'crash_count',
3, 'project.dataset.hotspot_results'
);
-- Isochrone analysis for EMS optimization
SELECT `carto-un`.carto.CREATE_ISOLINES(
origin_point, 'walk', [300, 600, 900], 'time'
) AS service_areas;| Layer | Technology | Role |
|---|---|---|
| Data Warehouse | BigQuery | Crash data lake, spatial queries, ML |
| Spatial Platform | CARTO + Analytics Toolbox | H3 indexing, hotspots, visualization |
| Spatial Index | Uber H3 | Hexagonal hierarchical grid system |
| AI/ML | Gemini 2.5 + BigQuery ML | NL queries, crash prediction, summaries |
| Streaming | Cloud Pub/Sub | Real-time telematics ingestion |
| Infrastructure | Terraform | Reproducible GCP resource provisioning |
| Visualization | CARTO Builder + Looker | Dashboards + open data portals |
| Data Sources | FARS, NOAA, Google RMI | Crash records, weather, traffic flow |
Vision Zero / Toward Zero Deaths (TZD)
Vision Zero is a global road safety strategy originating in Sweden (1997) built on the ethical principle that no loss of life in traffic is acceptable. Toward Zero Deaths (TZD) is the U.S. national strategy — adopted by AASHTO, FHWA, and state DOTs — aligned with Vision Zero's goal of eliminating all traffic fatalities and serious injuries. Both reject the notion that crashes are inevitable "accidents." This platform operationalizes Vision Zero / TZD by making crash pattern analysis accessible to every stakeholder — not just GIS specialists — so agencies can act before conditions turn fatal.
H3 Hexagonal Spatial Index
Uber's H3 is a hierarchical hexagonal grid system. Unlike square grids, hexagons have consistent adjacency (6 neighbors, equidistant centers), making them ideal for spatial statistics. This project uses resolutions 4 (state overview) through 9 (intersection-level) for multi-scale analysis.
Space-Time Hotspot Detection
Using the Getis-Ord Gi* statistic on H3-indexed crash counts, the platform identifies clusters that are statistically significant in both space AND time — distinguishing chronic risk corridors from random variation.
Agentic GIS
CARTO's paradigm where AI agents encode validated spatial workflows as reusable skills. A safety engineer's corridor analysis becomes a callable tool that other agents and stakeholders can invoke — building institutional knowledge into the platform.
- CARTO: From Reactive to Predictive — Geospatial AI for Public Sector
- CARTO Analytics Toolbox — 100+ spatial UDFs for BigQuery
- CARTO + BigQuery Spatial Analytics
- Uber H3 — Hexagonal Hierarchical Spatial Index
- NHTSA FARS — Fatality Analysis Reporting System
- FHWA CMF Clearinghouse — Crash Modification Factors
- Google Roads Management Insights
- ARUP + CARTO: London Road Safety Analysis
- Vision Zero Network — National campaign for safe mobility
- Toward Zero Deaths (TZD) National Strategy — U.S. DOT-aligned zero-fatality strategy
Apache 2.0 — see LICENSE for details.
Built for state DOTs and MPOs pursuing Vision Zero / Toward Zero Deaths.
Spatial analytics on CARTO · AI by Gemini · Data in BigQuery · Indexed by H3