MadTraffic AI
Urban Intelligence
Database Schema & Definitions
| Column Name | Definition / Meaning (English) |
|---|---|
| fecha | Timestamp of the traffic reading (Date and Time). |
| ocupacion | Occupancy (0 - 100): Percentage of time the sensor area is occupied. 0 = Completely empty | 100 = Total gridlock. |
| carga | Load (0 - 100): A calculated index representing road congestion severity. 0 = Free-flowing | 100 = Completely blocked. |
| intensidad | Intensity: Total number of vehicles passing the sensor per hour. 0 = No vehicles passing. |
| vmed | Average Speed (km/h): Average vehicle speed. 0 = Standstill / stopped traffic. |
| zona_id | Identifier for the urban area or district (e.g., m30, lavapies). |
| asignacion / veredicto | Data quality flags (e.g., 'VÁLIDO' for valid data). |
| tipo_elem | Type of road or infrastructure element (e.g., M30, URB). |
| sensor_nombre | Unique alphanumeric code identifying the specific traffic sensor. |
| p25, p50, p75, p90 | Percentiles: Statistical distribution of occupancy over a period. |
🚦 Understanding Occupancy — A Beginner's Guide
Occupancy is the primary metric used to measure traffic congestion. It represents the percentage of time a small inductive loop sensor embedded in the road surface detects a vehicle passing over it, measured in 15-minute intervals. The scale is technically 0% to 100%.
💡 Why does it never reach 100%?
The sensor loop is only ~2 meters long. It registers "occupied" only while a vehicle's metal chassis is physically above it. Even in bumper-to-bumper gridlock, there are tiny gaps between cars, time between axles, and moments when the queue briefly moves. In practice, values above 30–40% already indicate extreme congestion, and readings above 50% are exceptionally rare. A road at 100% occupancy would mean a vehicle is literally parked on the sensor non-stop for the entire 15-minute window.
The sensor loop is only ~2 meters long. It registers "occupied" only while a vehicle's metal chassis is physically above it. Even in bumper-to-bumper gridlock, there are tiny gaps between cars, time between axles, and moments when the queue briefly moves. In practice, values above 30–40% already indicate extreme congestion, and readings above 50% are exceptionally rare. A road at 100% occupancy would mean a vehicle is literally parked on the sensor non-stop for the entire 15-minute window.
How the System Judges Congestion — Dynamic Thresholds
This system does NOT use fixed percentages to decide if traffic is heavy or light. Instead, it calculates dynamic thresholds per zone from real historical data. Each zone and road type has its own definition of "normal" and "congested", because what's heavy for a quiet residential street is light for the M-30 highway.
Step 1 — Rush Hour Percentiles
Thresholds are computed using only peak-hour data (Mon–Fri, 7:30–9:30 and 17:30–20:30) from the last 6 months. This avoids diluting the numbers with quiet off-peak hours where every zone looks empty.
Step 2 — Absolute Floors
To prevent zones with sparse data from getting absurdly low thresholds, minimum floors are enforced by road type. This guarantees sensible baselines even for zones with few sensors.
🛣️ M-30 Highway
Floor thresholds: P25 ≥ 8%, P50 ≥ 18%, P75 ≥ 35%, P90 ≥ 55%.
Highways carry more sustained flow, so higher occupancy is "normal".
Highways carry more sustained flow, so higher occupancy is "normal".
🏙️ Urban Roads
Floor thresholds: P25 ≥ 5%, P50 ≥ 12%, P75 ≥ 25%, P90 ≥ 40%.
City streets have traffic lights and intersections, so lower values can already mean delays.
City streets have traffic lights and intersections, so lower values can already mean delays.
How to read the percentiles:
P25 = 25% of rush-hour readings are below this → the "quiet side of busy"
P50 (median) = the typical rush-hour level → what you'd expect on a normal weekday
P75 = 75% of readings are below this → a bad day
P90 = only 10% of readings exceed this → a really bad day, near the worst it gets
When the AI says traffic is "heavy", it means the occupancy is above the P75 threshold for that specific zone. When it says "congested", it's above P90. This is why the same 15% occupancy can be "moderate" on the M-30 but "heavy" on an urban street — each zone is judged against its own history.
P25 = 25% of rush-hour readings are below this → the "quiet side of busy"
P50 (median) = the typical rush-hour level → what you'd expect on a normal weekday
P75 = 75% of readings are below this → a bad day
P90 = only 10% of readings exceed this → a really bad day, near the worst it gets
When the AI says traffic is "heavy", it means the occupancy is above the P75 threshold for that specific zone. When it says "congested", it's above P90. This is why the same 15% occupancy can be "moderate" on the M-30 but "heavy" on an urban street — each zone is judged against its own history.