Traffic Sign Data Cleaning

update

Check out the updated shiny dashboard of ZAT-level indicators!

In this post, the goal is to clean the traffic sign raw data and count individual types of signs at the street level.

library(dplyr)
library(sf)
library(stringr)
library(leaflet)
library(glue)
library(reactable)
library(crosstalk)

Data Preparation

Import

Raw data is UNI_ANDES 2/Punto 4/Inventario.gdb, a geodatabase with multiple layers, and from the layer “SEN_VERTICAL” we can find each type of traffic signs with their status and coordinates (with geometry being points). For example, the first ten rows of the raw data looks like this (last column SHAPE is for geometries):

raw <- st_read("../data/traffic_sign/Inventario.gdb/", layer = "SEN_VERTICAL")

In the column names:

TIPO_SENAL is the type of signs. FASE refers to its current state. ACCION refers to what needs to happen to the sign. FECHA_FASE is the date of that state. ESTADO is the condition of the sign. 

FASE categories:

• Implementación = they need to be installed

• Programación = they need to schedule the action 

• Inventario = part of the existing inventory, no action

ACCION categories:

• Instalación - installation (not sure if it means to be I stalled or was installed on the date)

• Retirar - remove

• Inventario - no action

• Reemplazar - replace 

• Mantenimiento- maintenance 

ESTADO:

• Bueno - good

• Regular - fair

• Malo - poor

Cleaning and grouping

Since we need to group the traffic sign data to the street level, we’ll perform a spatial join to identify which street (polygon) each sign (point) is in (no point unassigned). Next, we’re removing the rows with missing TIPO_SENAL to clean the data, and grouping the data by type of traffic signs and their status (FASE and ACCION) to count a street-level sum.

calle <- st_read("../data/Calles/Calles_datos/Calles_datos.shp")

sign_st <- calle %>% 
  select(CodigoCL, geometry) %>% 
  st_transform(crs = st_crs(raw)) %>% 
  st_join(raw, left = TRUE, join = st_contains) %>% 
  as.data.frame()

clean_sign <- sign_st %>% 
  select(-geometry) %>% 
  drop_na(TIPO_SENAL) %>% 
  group_by(CodigoCL, TIPO_SENAL, FASE, ACCION) %>% 
  summarise(n = n(), .groups = "drop") 

For pedestrian crossing sign, we’re including SP-46, SI-24 and SR-19. For parking sign, we’re including SI-07. The resulting tables with each type of traffic signs and their status are stored in pedx_calle.csv and parking_calle.csv.

# ped crossing: SP-46, SI-24, SR-19 ---------------------------------
pedx <- clean_sign %>% 
  filter(str_detect(TIPO_SENAL, pattern = "SP-46|SI-24|SR-19")) 
  #ungroup()

write_csv(pedx, file = "../../../../clean_data/pedx_calle.csv")

# parking: SI-07, SI-07A -----------------------------------------------
parking <- clean_sign %>% 
  filter(str_detect(TIPO_SENAL, pattern = "SI-07"))

write_csv(parking, file = "../../../../clean_data/parking_calle.csv")

Spatial Visualization

For exploratory purposes, we’ll plot the pedestrian crossing signs and parking signs on the map, without taking into consideration the status of the signs.

Pedestrian Crossing

pedx_pa_geo_sm <- readRDS("../../../../clean_data/traffic_sign/pedx_pa_geo_sm.rds")

pedx <- pedx_pa_geo_sm %>% 
  filter(str_detect(sign, "SP46|SI24|SR19"))

pal <- colorNumeric(
  palette = "plasma",
  domain = pedx$n
)

zat_label <- glue("CodigoCL{pedx$CodigoCL} Traffic sign {pedx$sign} count: {pedx$n}")

pedx %>% 
  st_zm() %>%
  st_transform(crs = st_crs("+proj=longlat +datum=WGS84")) %>%
  leaflet() %>%
  addProviderTiles(providers$CartoDB.Voyager)  %>%
  addPolygons(color = "white", 
              weight = 0.5,
              smoothFactor = 0.5,
              opacity = 1,
              fillColor = ~pal(n),
              fillOpacity = 0.8,
              highlightOptions = highlightOptions(
                weight = 5,
                color = "#666",
                fillOpacity = 0.8,
                bringToFront = TRUE),
              label = zat_label,
              labelOptions = labelOptions(
                style = list(
                  "font-family" = "Fira Sans, sans-serif",
                  "font-size" = "1.2em"
                ))
              )%>% 
  addLegend("bottomleft",
            pal = pal,
            values = ~n,
            title = "Pedestrian Crossing Sign <br/> (Street-level)",
            opacity = 1)

In total there are 4536 streets that have pedestrian crossing signs in Bogotá. Pedestrian crossing signs are present throughout many neighborhoods, despite the often small number of crossing signs per street.

Parking

parking <- pedx_pa_geo_sm %>% 
  filter(str_detect(sign, pattern = "SI07"))

pal2 <- colorNumeric(
  palette = "plasma",
  domain = parking$n
)

zat_label2 <- glue("CodigoCL{parking$CodigoCL} Traffic sign {parking$sign} count: {parking$n}")

parking %>% 
  st_zm() %>%
  st_transform(crs = st_crs("+proj=longlat +datum=WGS84")) %>%
  leaflet() %>%
  addProviderTiles(providers$CartoDB.Voyager)  %>%
  addPolygons(color = "white", 
              weight = 0.5,
              smoothFactor = 0.5,
              opacity = 1,
              fillColor = ~pal2(n),
              fillOpacity = 0.8,
              highlightOptions = highlightOptions(
                weight = 5,
                color = "#666",
                fillOpacity = 0.8,
                bringToFront = TRUE),
              label = zat_label2,
              labelOptions = labelOptions(
                style = list(
                  "font-family" = "Fira Sans, sans-serif",
                  "font-size" = "1.2em"
                ))
              )%>% 
  addLegend("bottomleft",
            pal = pal2,
            values = ~n,
            title = "Parking Sign </br> (Street-level)",
            opacity = 1)

In total there are 524 streets that have parking signs in Bogotá, and they seem to have the highest density near the area “Localidad Chapinero”.