Handling Messy Iowa Paved Road Big Data

The Problem: Messy Pavement Data Across Years

State Departments of Transportation collect pavement condition data annually to monitor road health and plan maintenance. The Iowa DOT maintains extensive databases tracking metrics like the International Roughness Index (IRI), surface type, pavement width, and distress indicators across thousands of miles of roadway. However, a critical challenge emerges when attempting to use this data for predictive analytics: pavement sections are inconsistent across different years.

Why This Matters

Consider a single route surveyed over multiple years:

The same physical roadway is measured with different start/end points each year. This inconsistency creates several problems:

1. No Direct Temporal Comparison - You can’t track how a specific road segment deteriorates over time if the segment boundaries keep changing
2. ML Model Failure - Machine learning models require consistent feature spaces; shifting segment definitions break the fundamental assumption that observations represent the same entity
3. Prediction Impossibility - How do you predict 2023 IRI values when you don’t know which 2022 segment corresponds to which 2023 segment?
4. Data Quality Issues - Null values, coordinate errors, and measurement range anomalies compound the problem

Real-World Impact

Without standardized segmentation, infrastructure agencies face:

• Inaccurate deterioration modeling - Can’t distinguish true pavement degradation from measurement artifacts
• Wasted maintenance budgets - Poor predictions lead to reactive rather than proactive maintenance
• Safety risks - Inability to forecast critical distress prevents timely intervention

The Solution: Dynamic Temporal Segmentation

I developed IPAT Segmentation Analysis, a full-stack web application that solves this problem through intelligent temporal alignment and dynamic segmentation. The system standardizes pavement sections across multiple years by:

1. Finding Temporal Overlap - Identifies the measurement range that exists consistently across all available years for each route
2. Creating Uniform Segments - Divides this overlap into configurable segments (10-100) with fixed boundaries
3. Assigning Distress Records - Maps each year’s raw measurements to the nearest standardized segment
4. Validating Completeness - Ensures every segment has data for all years (no missing gaps)

The Result

After processing, the same route example becomes:

Now each segment has:

• Fixed geographic boundaries that don’t change across years
• Complete temporal coverage - IRI values for every year
• ML-ready format - Consistent feature space for time-series forecasting
• Traceable deterioration patterns - Clear year-over-year progression

Data Processing Pipeline

The segmentation engine executes entirely within BigQuery using SQL-based transformations:

Stage 1: Upload & Ingestion

• Users upload Excel files via multipart form data
• Backend extracts year from filename (e.g., pavement_2021.xlsx → 2021)
• Pandas validates schema and corrects anomalies (swaps FROM/TO if inverted)
• Data streams directly to raw_distress_[YEAR] table via BytesIO

Stage 2: Temporal Alignment

-- Identify routes with consecutive year coverage
CREATE TABLE valid_routes AS
SELECT ROUTE_NAME,
       MIN(FROM_MEASURE) as overlap_start,
       MAX(TO_MEASURE) as overlap_end
FROM all_years
WHERE ROUTE_NAME IN (
  SELECT ROUTE_NAME
  FROM all_years
  GROUP BY ROUTE_NAME
  HAVING COUNT(DISTINCT YEAR) >= 2
)
GROUP BY ROUTE_NAME;

Stage 3: Dynamic Segmentation

-- Generate N uniform segments per route
CREATE TABLE distress_segmented AS
SELECT
  ROUTE_NAME,
  YEAR,
  FLOOR((FROM_MEASURE - overlap_start) / segment_width) + 1 AS segment_id,
  AVG(IRI) as segment_iri,
  AVG(BEGIN_GLAT) as segment_lat,
  AVG(BEGIN_GLON) as segment_lon
FROM raw_distress
JOIN valid_routes USING (ROUTE_NAME)
GROUP BY ROUTE_NAME, YEAR, segment_id;

Stage 4: Completeness Validation

-- Retain only segments with all years present
CREATE TABLE distress_segmented_complete AS
SELECT *
FROM distress_segmented
WHERE (ROUTE_NAME, segment_id) IN (
  SELECT ROUTE_NAME, segment_id
  FROM distress_segmented
  GROUP BY ROUTE_NAME, segment_id
  HAVING COUNT(DISTINCT YEAR) = (SELECT COUNT(DISTINCT YEAR) FROM all_years)
);

Stage 5: Pivot Transformation

-- Generate final output with IRI as year columns
CREATE TABLE distress_final AS
SELECT
  ROUTE_NAME,
  segment_id,
  FROM_MEASURE,
  TO_MEASURE,
  MAX(IF(YEAR = 2020, IRI, NULL)) as IRI_2020,
  MAX(IF(YEAR = 2021, IRI, NULL)) as IRI_2021,
  MAX(IF(YEAR = 2022, IRI, NULL)) as IRI_2022
FROM distress_segmented_complete
GROUP BY ROUTE_NAME, segment_id, FROM_MEASURE, TO_MEASURE;

Key Architectural Patterns

1. Multi-Tenant Isolation Each county’s data resides in separate BigQuery datasets (ipat_story, ipat_polk, etc.), enabling:

• Parallel processing without conflicts
• County-specific access control
• Independent data lifecycle management

2. Vectorized Data Processing Pandas operations execute column-wise transformations across entire DataFrames:

# Convert types vectorized (not row-by-row)
df['IRI'] = pd.to_numeric(df['IRI'], errors='coerce')

# Swap measures where inverted (vectorized boolean indexing)
mask = df['FROM_MEASURE'] > df['TO_MEASURE']
df.loc[mask, ['FROM_MEASURE', 'TO_MEASURE']] = \
    df.loc[mask, ['TO_MEASURE', 'FROM_MEASURE']].values

3. Streaming I/O Direct memory streaming avoids disk writes:

buffer = BytesIO()
df.to_parquet(buffer)
buffer.seek(0)
client.load_table_from_file(buffer, table_ref)

4. Consistency Validation Multi-year coordinate/measure validation ensures data quality:

std_lat = df.groupby('segment_id')['lat'].std()
std_lon = df.groupby('segment_id')['lon'].std()
std_from = df.groupby('segment_id')['FROM_MEASURE'].std()

# Filter segments with stable locations
valid_segments = (std_lat < 0.0001) & (std_lon < 0.0001) & (std_from < 0.01)

Technologies Used

API Endpoints

Impact & Applications

Research Applications

• Pavement Deterioration Modeling - Time-series forecasting with LSTM/ARIMA models
• Maintenance Optimization - Predict when routes will exceed IRI thresholds
• Climate Impact Studies - Correlate freeze-thaw cycles with accelerated degradation

Operational Benefits

1. Cost Savings - Proactive maintenance scheduling reduces emergency repairs
2. Budget Justification - Data-driven evidence for infrastructure funding requests
3. Performance Tracking - Quantify pavement lifespan improvements from new materials

Technical Contributions

• Temporal alignment algorithm applicable to any time-series infrastructure dataset
• County-based multi-tenant architecture pattern for state DOT systems
• BigQuery-native processing demonstrating serverless data engineering

Future Enhancements

1. Automated Anomaly Detection - Flag routes with suspicious year-over-year IRI jumps
2. Machine Learning Integration - Built-in deterioration forecasting models
3. GIS Visualization - Interactive map showing segmented routes with color-coded IRI trends
4. Real-Time Streaming - Process data as it arrives from survey vehicles
5. Multi-State Support - Extend beyond Iowa to other state DOT datasets

Source Code

Technical Skills Demonstrated

• Backend API development with FastAPI
• Big data processing with Google BigQuery
• Infrastructure as Code (Terraform)
• Data quality control and ETL pipelines
• Temporal data alignment algorithms
• Cloud-native architecture design
• Multi-tenant system isolation
• RESTful API design
• Vectorized data processing with Pandas