Use case of bridge data analysis: Part I Interactive Data Visualisation

1 Data overview

Data are in a nested format: 120 recorded items per bridge per state, repeated annually in 2010 - 2018.

  • 665063 structures
  • 52 US states
  • 5492796 structure-year records processed

1.1 Bridge map

1.2 Number of structures

  • Bars = Total counts across 9 years.
    • Across 9 years, how many unique structures have been recorded?
  • Dots = Averaged yearly count across 9 years.
    • In each year, X number of bridges were recorded, what is the average of X in 9 years?
  • Recording error in any state? Which ones?
    • Bars should be fairly close to Dots so that recording error is minor (e.g. entry error of bridge code).
    • A clear gap between bars and dots indicate a clear recording error, either at primary data collection stage or at data integration stage.
    • Highlighted states: Illinois, Pennsylvania, Michigan, South Carolina, Arizona.

1.3 The top 10 states with the most structures

## # A tibble: 10 x 3
##    STATE_CODE_001   cnt pct  
##    <fct>          <int> <chr>
##  1 Texas          57177 8%   
##  2 Illinois       54877 8%   
##  3 Pennsylvania   47185 6%   
##  4 Ohio           31324 4%   
##  5 California     26973 4%   
##  6 Missouri       26887 4%   
##  7 Kansas         26821 4%   
##  8 Iowa           26657 4%   
##  9 Oklahoma       26008 4%   
## 10 Michigan       22198 3%

1.4 Number of structures over time

  • Number of bridges with data in both 2010 and 2018
## [1] "Count=476879 (72%)"
  • More details

2 Outcomes

2.1 Cost

  • Dropdown menu to select Bridge/Roadway/Total cost
  • Sum across all bridges within each state for each year
  • One data point = one state-year record

All states

Select states: highly variable annual cost

  • List of such states is selected by the year-by-year cost variation.
  • Variation = inter-quartile-range (IQR) of natural cost scale (summed over all bridges) across years.
  • Cost trajectory is plotted using a log10-scale of the total cost for the select states.
  • Solid triangle = median across these 10 selected states.

All states: by general condition

  • General condition computed as the minimum of deck, superstructure, substructure, culverts condition (items 58, 59, 60, 62).
  • Grouped into Poor (<=4), Fair (5-6), Good (>=7)
  • Cost trajectory is plotted using a log10-scale of the median cost of all structures across all states in each condition bucket.
  • Solid triangle = median across all conditions.

All states: by scour condition

  • Grouped code: 0-3 (Closed/critical), 4-7,U (Stable), 8-9,T (Good).
  • NA = not recorded
  • Cost trajectory is plotted using a log10-scale of the median cost of all structures across all states in each condition bucket.
  • Solid triangle = median across all scour conditions.

2.2 Condition: General

  • Original codes = 0 (bad) - 9 (excellent)
  • Grouped codes: Poor (0-4), Fair (5-6), Good (7-9)
  • General condition is computed as the minimum of deck, superstructure, substructure, culverts condition (items 58, 59, 60, 62). Grouped into Poor (<=4), Fair (5-6), Good (>=7)
  • Y-axis = percentage of structures across all states in each year (stratified by respective condition)
  • Unless otherwise stated, NA = not recorded/not applicable
  • Dropdown menu to select states

General

DECK

Superstructure

Substructure

Channel

Culvert

2.3 Condition: Scour

  • Original code: 0 (structure closed) - 9 (excellent condition), N, T, U
  • Grouped code: 0-3 (Closed/critical), 4-7,U (Stable), 8-9,T (Good). NA = not recorded/not applicable
  • Code N: no waterway (will be excluded in the following plots)
  • Code T: tidal water cannot be inspected (manual states low risk)
  • Dropdown menu to select states

2.4 Evaluation: Detour

  • Detour is evaluated in kilometers
  • Solid triangle = median across all conditions
  • Dropdown menu to select states

All states

Select states: highly variable detour by year

All states: by general condition

All states: by scour condition

2.5 Evaluation: Traffic

  • Average daily traffic (ADT), recoded in millions (M)
  • All plots by general or scour condition
  • Dropdown menu to select states

2.5.1 By general condition

ADT (current)

  • Current = at the year of inspection
  • Plotted total ADT across all bridges

ADT-truck (current, in %)

  • Current = current percentage of the average daily traffic that is truck traffic
  • Measured at the year of inspection
  • Plotted median across all bridges

ADT (future)

  • Estimated future average daily traffic (a basis for 20-year forecast)
  • Plotted total across all bridges

2.5.2 By scour condition

ADT (current)

  • Current = at the year of inspection
  • Plotted total ADT across all bridges

ADT-truck (current, in %)

  • Current = current percentage of the average daily traffic that is truck traffic
  • Measured at the year of inspection
  • Plotted median across all bridges

ADT (future)

  • Estimated future average daily traffic (a basis for 20-year forecast)
  • Plotted total across all bridges

3 Relationship between outcomes

Outcomes of interest are:

  1. Cost (continuous)
* Bridge, Roadway, Total improvement cost estimates
  1. Condition (categorical)
* General
* Scour

  1. Evaluation_Detour (continuous)

  2. Evaluation_Traffic (continuous)

* ADT_current, ADT_truck, ADT_future

Correlation plots:

  • Dropdown menu to select states
  • Correlations are plotted for bridges with median annual values in 2010-2018
  • States with co-linear outcomes are not shown (potential data recording error)
  • Edge = correlation, Green = positive, Red = negative
  • Non-signficant edges, with p-value (Bonferroni-corrected) > 0.05. are removed

4 Relationship between predictors

  • Distributions are summarised in a downloadable spreadsheet.
  • Summaries for year 2018 are reported. 20 bridges are randomly selected in each state.
  • Summaries are separated by general condition, with p-values<0.05 indicating statistically significant relationship between predictor and condition.