This R script will retrieve all 2022 National Bridge Inventory data for Tennessee, filter for bridges in Rutherford County, Tennessee, pare the dataset to 14 key variables, save the pared dataset locally as a .csv file, then interactively map all bridges in the filtered, pared dataset that have a rating of “Poor.” Other states, counties, and bridge conditions can be specified.
if (!require("dplyr")) install.packages("dplyr")
if (!require("tidyverse")) install.packages("tidyverse")
library(dplyr)
library(tidyverse)
library(readr)
#Reading the latest Tennessee data file
#Data files for other states at:
#https://www.fhwa.dot.gov/bridge/nbi/ascii.cfm
data <- read_csv("https://www.fhwa.dot.gov/bridge/nbi/2022/delimited/TN22.txt")
#Converting Lat/Long to decimal format
data$LatH <- as.numeric(substr(data$LAT_016,1,2))
data$LatM <- as.numeric(substr(data$LAT_016,3,4))
data$LatS <- as.numeric(substr(data$LAT_016,5,8))/100
data$LatD <- data$LatH+(data$LatM/60)+(data$LatS/3600)
data$LonH <- as.numeric(substr(data$LONG_017,1,3))
data$LonM <- as.numeric(substr(data$LONG_017,4,5))
data$LonS <- as.numeric(substr(data$LONG_017,6,9))/100
data$LonD <- (data$LonH+(data$LonM/60)+(data$LonS/3600))*-1
#Filtering for bridges in Rutherford (County FIPS = 149)
#Other county FIPS codes at
#https://transition.fcc.gov/oet/info/maps/census/fips/fips.txt
#For example: Davidson = 037, Williamson = 187, Shelby = 157
data <- data[data$COUNTY_CODE_003 %in% c("149"),]
#Simplifying variable names and
#labeling categorical variables
#See dataset codebook at:
#https://www.fhwa.dot.gov/bridge/mtguide.pdf
#Bridge rating
data$Rating <- data$BRIDGE_CONDITION
data$Rating <- recode(data$Rating,
"G" = "Good",
"F" = "Fair",
"P" = "Poor")
#Bridge ID:
data$ID <- data$STRUCTURE_NUMBER_008
#Feature spanned:
data$Feature <- data$FEATURES_DESC_006A
#Latitude
data$Latitude <- data$LatD
#Longitude
data$Longitude <- data$LonD
#Maintained by:
data$Maintenance <- data$MAINTENANCE_021
data$Maintenance <- recode(data$Maintenance,
"01" = "State Highway Agency",
"02" = "County Highway Agency",
"03" = "Town or Township Highway Agency",
"04" = "City or Municipal Highway Agency",
"11" = "State Park, Forest, or Reservation Agency",
"12" = "Local Park, Forest, or Reservation Agency",
"21" = "Other State Agencies",
"25" = "Other Local Agencies",
"26" = "Private (other than railroad)",
"27" = "Railroad",
"31" = "State Toll Authority",
"32" = "Local Toll Authority",
"60" = "Other Federal Agencies (not listed below)",
"61" = "Indian Tribal Government",
"62" = "Bureau of Indian Affairs",
"63" = "Bureau of Fish and Wildlife",
"64" = "U.S. Forest Service",
"66" = "National Park Service",
"67" = "Tennessee Valley Authority",
"68" = "Bureau of Land Management",
"69" = "Bureau of Reclamation",
"70" = "Corps of Engineers (Civil)",
"71" = "Corps of Engineers (Military)",
"72" = "Air Force",
"73" = "Navy/Marines",
"74" = "Army",
"75" = "NASA",
"76" = "Metropolitan Washington Airports Service",
"80" = "Unknown")
#Year built:
data$Built <- data$YEAR_BUILT_027
#Year reconstructed:
data$Reconstructed <- data$YEAR_RECONSTRUCTED_106
#Average daily traffic:
data$Traffic <- data$ADT_029
#Deck:
data$Deck <- data$DECK_COND_058
#Superstructure:
data$Superstructure <- data$SUPERSTRUCTURE_COND_059
#Substructure:
data$Substructure <- data$SUBSTRUCTURE_COND_060
#Culvert:
data$Culvert <- data$CULVERT_COND_062
#Type:
data$Type <- data$STRUCTURE_TYPE_043B
data$Type <- recode(data$STRUCTURE_TYPE_043B,
"01" = "Slab",
"02" = "Stringer/Multi-beam or Girder",
"03" = "Girder and Floorbeam System",
"04" = "Tee Beam",
"05" = "Box Beam or Girders - Multiple",
"06" = "Box Beam or Girders - Single or Spread",
"07" = "Frame (except frame culverts)",
"08" = "Orthotropic",
"09" = "Truss - Deck",
"10" = "Truss - Thru",
"11" = "Arch - Deck",
"12" = "Arch - Thru",
"13" = "Suspension",
"14" = "Stayed Girder",
"15" = "Movable - Lift",
"16" = "Movable - Bascule",
"17" = "Movable - Swing",
"18" = "Tunnel",
"19" = "Culvert (includes frame culverts)",
"20" = "* Mixed types",
"21" = "Segmental Box Girder",
"22" = "Channel Beam",
"00" = "Other")
#Deleting unneeded columns
keeps <- c("Rating",
"ID",
"Feature",
"Latitude",
"Longitude",
"Maintenance",
"Built",
"Reconstructed",
"Traffic",
"Deck",
"Superstructure",
"Substructure",
"Culvert",
"Type")
data <- data[keeps]
data <- data[order(data$Traffic, decreasing = TRUE),]
rm(keeps)
#Writing data to a local .csv file
write_csv(data, "BridgeData.csv")
#Making a leaflet map
#Installing and activating leaflet package
if (!require("leaflet")) install.packages("leaflet")
library(leaflet)
#Filtering for bridges rated "Poor"
data <- data[data$Rating %in% c("Poor"),]
data$long <- data$Longitude
data$lat <- data$Latitude
data$popup <- paste("<b>Rating: #: </b>", data$Rating,
"<br>", "<b>ID: </b>", data$ID,
"<br>", "<b>Feature: </b>", data$Feature,
"<br>", "<b>Maintenance: </b>", data$Maintenance,
"<br>", "<b>Built: </b>", data$Built,
"<br>", "<b>Reconstructed: </b>", data$Reconstructed,
"<br>", "<b>Traffic: </b>", data$Traffic,
"<br>", "<b>Deck: </b>", data$Deck,
"<br>", "<b>Superstructure: </b>", data$Superstructure,
"<br>", "<b>Substructure: </b>", data$Substructure,
"<br>", "<b>Culvert: </b>", data$Culvert,
"<br>", "<b>Type: </b>", data$Type)
leaflet(data = data) %>%
addTiles() %>%
addCircleMarkers(~long,
~lat,
popup = ~data$popup,
label = ~Traffic,
fillColor = "royalblue",
fillOpacity = .75,
stroke = F)