Top TxDOT Roadway Segments by All

library(sf)
library(dplyr)
library(readr)
library(leaflet)
library(htmlwidgets)
library(tigris)
library(scales)

options(tigris_use_cache = TRUE)

setwd("C:/Users/mvx13/OneDrive - Texas State University/Projects/2026/RTI_2026/0_7263_mb_TXST/TX_Pedestrian_DataConflation/shapefiles")


##library(foreign)
##datsp= read.dbf("txdot_roadway_data_fc_gt_2.dbf")
##dim(datsp)
##names(datsp)

library(sf)
library(dplyr)
library(readr)

road_sf <- st_read("txdot_roadway_data_fc_gt_2.shp")

## Reading layer `txdot_roadway_data_fc_gt_2' from data source 
##   `C:\Users\mvx13\OneDrive - Texas State University\Projects\2026\RTI_2026\0_7263_mb_TXST\TX_Pedestrian_DataConflation\shapefiles\txdot_roadway_data_fc_gt_2.shp' 
##   using driver `ESRI Shapefile'
## Simple feature collection with 965972 features and 134 fields
## Geometry type: MULTILINESTRING
## Dimension:     XY
## Bounding box:  xmin: 339025 ymin: 8461859 xmax: 4428810 ymax: 12341480
## Projected CRS: NAD83 / Texas Central (ftUS)

setwd("C:/Users/mvx13/OneDrive - Texas State University/Projects/2026/RTI_2026/0_7263_mb_TXST/TX_Pedestrian_DataConflation/excel analysis")
top_csv= read.csv("only_lines_with_crashes_by_year_KABCO.csv")



# 4. Select LINE_ID and last three columns from CSV
last3_cols <- names(top_csv)[(ncol(top_csv) - 2):ncol(top_csv)]

top50_line <- top_csv %>%
  select(LINE_ID, all_of(last3_cols)) %>%
  slice_head(n = 50)


# 5. Make sure LINE_ID type matches in both files
road_sf <- road_sf %>%
  mutate(LINE_ID = as.character(LINE_ID))

top50_line <- top50_line %>%
  mutate(LINE_ID = as.character(LINE_ID))

# 6. Subset shapefile using top 50 LINE_ID and attach last three CSV columns
road_top50_sf <- road_sf %>%
  inner_join(top50_line, by = "LINE_ID")

library(sf)
library(dplyr)
library(readr)
library(leaflet)
library(htmlwidgets)
library(tigris)
library(scales)

# Texas boundary
tx_boundary <- states(cb = TRUE, year = 2023) %>%
  filter(STUSPS == "TX") %>%
  st_transform(st_crs(road_top50_sf))

# Transform for leaflet
road_top50_leaflet <- st_transform(road_top50_sf, 4326)
tx_boundary_leaflet <- st_transform(tx_boundary, 4326)

# Create top 10, top 20, top 50 layers
road_top10 <- road_top50_leaflet %>% filter(Rank <= 10)
road_top20 <- road_top50_leaflet %>% filter(Rank <= 20)
road_top50 <- road_top50_leaflet


# Better color palette based on All_KABCO
pal <- colorNumeric(
  palette = "inferno",
  domain = road_top50$All_KABCO,
  na.color = "gray70"
)

# Popup function
make_popup <- function(df) {
  paste0(
    "<div style='font-size:13px; line-height:1.35;'>",
    "<b>Rank:</b> ", df$Rank, "<br>",
    "<b>LINE_ID:</b> ", df$LINE_ID, "<br>",
    "<b>GID:</b> ", df$GID, "<br>",
    "<b>All_KABCO:</b> ", df$All_KABCO, "<br>",
    "<b>C_SEC:</b> ", df$C_SEC, "<br>",
    "<b>FRM_DFO:</b> ", df$FRM_DFO, "<br>",
    "<b>TO_DFO:</b> ", df$TO_DFO, "<br>",
    "<b>DI:</b> ", df$DI, "<br>",
    "<b>CO:</b> ", df$CO, "<br>",
    "<b>CITY:</b> ", df$CITY, "<br>",
    "<b>ADT_CUR:</b> ", df$ADT_CUR, "<br>",
    "<b>SPD_MAX:</b> ", df$SPD_MAX,
    "</div>"
  )
}

road_top10$popup_text <- make_popup(road_top10)
road_top20$popup_text <- make_popup(road_top20)
road_top50$popup_text <- make_popup(road_top50)

road_top10$label_text <- paste0(
  "Rank ", road_top10$Rank,
  " | LINE_ID: ", road_top10$LINE_ID,
  " | All_KABCO: ", road_top10$All_KABCO
)

road_top20$label_text <- paste0(
  "Rank ", road_top20$Rank,
  " | LINE_ID: ", road_top20$LINE_ID,
  " | All_KABCO: ", road_top20$All_KABCO
)

road_top50$label_text <- paste0(
  "Rank ", road_top50$Rank,
  " | LINE_ID: ", road_top50$LINE_ID,
  " | All_KABCO: ", road_top50$All_KABCO
)


# Function to add midpoint coordinates and Google Street View link
add_streetview <- function(x) {
  
  # Convert to WGS84
  x_wgs <- st_transform(x, 4326)
  
  # Project first so point creation is stable
  x_proj <- st_transform(x_wgs, 5070)
  
  # Create one representative point per roadway feature
  pts_proj <- st_point_on_surface(st_geometry(x_proj))
  
  # Convert point back to lat/lon
  pts_wgs <- st_transform(st_as_sf(data.frame(id = seq_along(pts_proj)),
                                   geometry = pts_proj,
                                   crs = 5070), 4326)
  
  coords <- st_coordinates(pts_wgs)
  
  x_wgs$sv_lon <- coords[, 1]
  x_wgs$sv_lat <- coords[, 2]
  
  x_wgs$streetview_link <- paste0(
    "https://www.google.com/maps/@?api=1&map_action=pano&viewpoint=",
    x_wgs$sv_lat, ",", x_wgs$sv_lon
  )
  
  x_wgs
}

road_top10 <- road_top10 %>% add_streetview()
road_top20 <- road_top20 %>% add_streetview()
road_top50 <- road_top50 %>% add_streetview()



make_popup <- function(df) {
  paste0(
    "<div style='font-size:13px; line-height:1.35; width:360px;'>",
    
    "<b>Rank:</b> ", df$Rank, "<br>",
    "<b>LINE_ID:</b> ", df$LINE_ID, "<br>",
    "<b>GID:</b> ", df$GID, "<br>",
    "<b>All_KABCO:</b> ", df$All_KABCO, "<br>",
    "<b>C_SEC:</b> ", df$C_SEC, "<br>",
    "<b>FRM_DFO:</b> ", df$FRM_DFO, "<br>",
    "<b>TO_DFO:</b> ", df$TO_DFO, "<br>",
    "<b>DI:</b> ", df$DI, "<br>",
    "<b>CO:</b> ", df$CO, "<br>",
    "<b>CITY:</b> ", df$CITY, "<br>",
    "<b>ADT_CUR:</b> ", df$ADT_CUR, "<br>",
    "<b>SPD_MAX:</b> ", df$SPD_MAX, "<br><br>",
    
    "<b>Google Street View:</b><br>",
    "<iframe ",
    "width='340' ",
    "height='230' ",
    "style='border:0; margin-top:6px;' ",
    "loading='lazy' ",
    "allowfullscreen ",
    "src='https://www.google.com/maps?layer=c&cbll=",
    df$sv_lat, ",", df$sv_lon,
    "&cbp=11,0,0,0,0&output=svembed'>",
    "</iframe>",
    
    "</div>"
  )
}


road_top10$popup_text <- make_popup(road_top10)
road_top20$popup_text <- make_popup(road_top20)
road_top50$popup_text <- make_popup(road_top50)





m <- leaflet() %>%
  addProviderTiles(providers$CartoDB.Positron) %>%
  
  # Texas boundary
  addPolygons(
    data = tx_boundary_leaflet,
    fill = FALSE,
    color = "black",
    weight = 3,
    opacity = 1,
    group = "Texas Boundary"
  ) %>%
  
  # Top 50
  addPolylines(
    data = road_top50,
    color = ~pal(All_KABCO),
    weight = ~rescale(All_KABCO, to = c(3, 8)),
    opacity = 0.85,
    popup = ~popup_text,
    popupOptions = popupOptions(maxWidth = 430),
    label = ~label_text,
    highlightOptions = highlightOptions(
      weight = 11,
      color = "cyan",
      opacity = 1,
      bringToFront = TRUE
    ),
    group = "Top 50"
  ) %>%
  
  # Top 20
  addPolylines(
    data = road_top20,
    color = ~pal(All_KABCO),
    weight = ~rescale(All_KABCO, to = c(5, 11)),
    opacity = 0.95,
    popup = ~popup_text,
    popupOptions = popupOptions(maxWidth = 430),
    label = ~label_text,
    highlightOptions = highlightOptions(
      weight = 13,
      color = "cyan",
      opacity = 1,
      bringToFront = TRUE
    ),
    group = "Top 20"
  ) %>%
  
  # Top 10
  addPolylines(
    data = road_top10,
    color = ~pal(All_KABCO),
    weight = ~rescale(All_KABCO, to = c(7, 14)),
    opacity = 1,
    popup = ~popup_text,
    popupOptions = popupOptions(maxWidth = 430),
    label = ~label_text,
    highlightOptions = highlightOptions(
      weight = 16,
      color = "cyan",
      opacity = 1,
      bringToFront = TRUE
    ),
    group = "Top 10"
  ) %>%
  
  addLegend(
    position = "bottomright",
    pal = pal,
    values = road_top50$All_KABCO,
    title = "All_KABCO",
    opacity = 1
  ) %>%
  
  addLayersControl(
    overlayGroups = c(
      "Texas Boundary",
      "Top 10",
      "Top 20",
      "Top 50"
    ),
    options = layersControlOptions(collapsed = FALSE)
  ) %>%
  
  hideGroup("Top 20") %>%
  hideGroup("Top 50")

m

Top TxDOT Roadway Segments by All_KABCO

Subasish Das