DC Traffic Enforcement Analytics

ETL Pipeline · Relational Database · SQL Analysis

---

Background

DC traffic violation records and weather data exist in separate public sources with no shared infrastructure for cross-analysis. Enforcement patterns, weather impact on violations, and agency performance cannot be analyzed without first building a unified data layer.

This project builds that layer — from raw CSV ingestion to a normalized relational database — and runs operational SQL analysis to surface actionable enforcement insights.

---

What's Inside

dc-traffic-enforcement-analytics/
├── etl/
│   ├── moving_violations_etl.py   
│   ├── weather_etl.py             
│   └── README.md                  
├── sql/
│   ├── 01_create_schema.sql       
│   ├── 02_analysis_queries.sql    
│   └── README.md                  
├── docs/
│   └── screenshots/
│       ├── ERD_Diagram.png
│       ├── database_tables.png
│       └── record_counts.png
├── data/
│   └── README.md                  
├── requirements.txt
├── .gitignore
└── README.md

---

Database Design

Two normalized tables linked by date:

Table	What it holds
moving_violations	Every ticket issued — location, agency, violation type, fine, accident flag
weather_dc	Hourly DC weather — temperature, rain, wind, UV, conditions

---

Pipeline Architecture

DC Open Data Portal       Visual Crossing
(Moving Violations CSV)   (Weather CSV)
        │                       │
        ▼                       ▼
moving_violations_etl.py  weather_etl.py
        │                       │
        ├── Column standardization
        ├── Type conversion
        ├── Null handling
        ├── Deduplication
        └──────────┬────────────┘
                   ▼
          MySQL Database
           (dc_traffic)
                   │
                   ▼
       SQL Analysis Layer

---

ETL Pipeline Details

Moving Violations

Parameter	Detail
Source	DC Open Data Portal
Processing	Chunked — 50,000 rows per chunk · 1.8M+ records total
Fields retained	28 columns
Key transformations	Date parsing, HHMM time conversion, boolean mapping, numeric coercion, deduplication
Output table	moving_violations

Weather Data

Parameter	Detail
Source	Visual Crossing (monthly CSVs)
Key transformations	Column renaming (23 fields), null fills, datetime parsing, deduplication
Output table	weather_dc

---

---

Analysis

Eight SQL queries across four themes:

Agency Performance Which agencies issue the most tickets, and how does that change month to month?

Temporal Patterns When do violations peak — by day of week and hour of day?

Weather Impact Do violation rates drop when it rains? Do accidents cluster in wet or dry conditions?

Financial Trends Where does fine revenue concentrate, and which violation types drive high-severity enforcement?

---

Key Findings

• Automated cameras (RCON) account for 98.4% of all 1.8M violations — DC enforcement is almost entirely camera-driven
• Peak enforcement at 1pm, running 2.6x higher than overnight (4am low); weekends average 26% more violations than weekdays
• 92.5% of accident violations occur on dry days — wet weather does not drive unsafe driving behavior
• Speeding fine revenue swings 2.5x across the year — $9.3M (Feb 2025) to $23.5M (Sep 2024) — seasonal patterns have measurable budget impact

---

Screenshots

Database Tables Loaded

Record Counts

---

Tech Stack

Tool	Purpose
Python 3.8+	ETL scripting
Pandas	Transformation and cleaning
SQLAlchemy	ORM and database connection
MySQL 8.0	Relational database
python-dotenv	Secure credential management
SQL	Operational analysis

---

Setup

1. Install MySQL

Download MySQL Community Server: 🔗 https://dev.mysql.com/downloads/mysql/

Once installed, open Workbench, connect, open a new query tab, and run:

CREATE DATABASE dc_traffic;

Confirm dc_traffic appears in the left panel under Schemas.

---

2. Get the Data

Raw files are not included in this repo — both datasets are publicly available and free. See data/README.md for download links and exact folder structure required.

---

3. Create Your .env File

In the project root, create a file named .env (no extension) containing:

DB_PASSWORD=your_mysql_root_password

---

4. Install Dependencies

pip install -r requirements.txt

Or install manually:

pip install pandas sqlalchemy pymysql python-dotenv cryptography

Verify Python version first:

python --version

Should return 3.8 or higher.

---

5. Confirm Folder Structure

Before running, your project root must contain:

Violations Data/     ← moving violations CSV files
Weather Data/        ← monthly weather CSV files
.env                 ← your DB_PASSWORD

---

6. Run the Pipelines

# Weather first — violations join against it
python etl/weather_etl.py

# Then violations
python etl/moving_violations_etl.py

---

7. Run the SQL Analysis

Open MySQL Workbench:

1. File → Open SQL Script → sql/01_create_schema.sql → run with ⚡
2. Open sql/02_analysis_queries.sql
3. Run queries individually with Ctrl+Enter (Windows) or Cmd+Return (Mac)

---

Troubleshooting

Problem	Fix
No module named 'dotenv'	pip install python-dotenv
Access denied for user 'root'	Check DB_PASSWORD in .env
MySQL won't connect	Open Workbench and verify the connection first
FileNotFoundError: Violations Data	Folder name must match exactly, including the space
Table doesn't exist	Run weather ETL before violations ETL

---

Author

Adarsh Shukla MS Business Analytics · University of Dayton LinkedIn · GitHub