Denver Urban Pulse

Overview

Automated analytics platform using data from 4 public APIs, a PostgreSQL raw → staging → marts architecture, Python ETL, interactive maps, heatmaps, KPI trends, and centralized filtering. Data is refreshed on a scheduled batch pipeline, with source freshness shown in the dashboard. It covers crime, traffic crashes, 311 service requests, and air quality, with neighborhood-level analysis.

Architecture

┌─────────────────────────────────────────────────────┐
│                    Data Sources                      │
│  ArcGIS (Crime, Crashes, 311)  ·  AirNow API (AQI)  │
└──────────────────────┬──────────────────────────────┘
                       │  daily cron (06:00 UTC)
                       ▼
┌─────────────────────────────────────────────────────┐
│              Python Pipeline (Railway)               │
│  Migrations → Ingestion → Staging → Marts            │
└──────────────────────┬──────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────┐
│              PostgreSQL (Railway)                     │
│  raw (5 tables) → staging (5) → marts (9)            │
└──────────────────────┬──────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────┐
│          Next.js API Routes (Vercel)                 │
│  /city-pulse/kpis  · /categories · /heatmap          │
│  /category-trends  · /neighborhoods                  │
│  /environment/aqi  · /environment/comparison          │
└──────────────────────┬──────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────┐
│            React Dashboard (Vercel)                   │
│  KPIs · Map · Charts · Heatmap · Ranking · Filters   │
└─────────────────────────────────────────────────────┘

Data Sources

Key Features

• KPI cards with sparklines, deltas, and per-domain data freshness
• Interactive choropleth map — click a neighborhood to filter the entire dashboard
• Category breakdown — incident types grouped by domain with trend sparklines
• Day & hour heatmap — incident density matrix (day-of-week × hour)
• AQI trend chart — multi-pollutant line chart (Ozone, PM2.5, PM10)
• Neighborhood ranking — composite score with domain toggle
• Change leaders — top 5 improved / worsened neighborhoods by period
• Global filters — time window (7d / 30d / 90d) and neighborhood selector
• Responsive layout — mobile, tablet, and desktop breakpoints

Tech Stack