This page contains a national summary of the roughness from the LTPP database.

The code to generate this is displayed first - then the graphics.

Use the menu to left to go straight to the roughness for all sites, State Highway sites, Local Authority sites or Individual sites.

Data Importing

The code below imports the data from the 10m databases

#Sys.Date()
Sys.time()
## [1] "2021-11-29 10:45:43 NZDT"
#load database connection library
library(RODBC)

db <- "C:/local_databases/NZTA_SH&LA_LTPPData_IntCalib_1Jul2001-30Jun2021.mdb"

con2 <- odbcDriverConnect(paste0("Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=", db))

Roughness10m <- sqlFetch(con2, "10mRoughness")
CalibrationSections <- sqlFetch(con2, "CalibrationSections")

library(tidyverse)
## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --
## v tibble  3.1.6     v purrr   0.3.4
## v tidyr   1.1.4     v forcats 0.5.1
## v readr   2.1.0
## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter()          masks stats::filter()
## x kableExtra::group_rows() masks dplyr::group_rows()
## x dplyr::lag()             masks stats::lag()
Councils <- CalibrationSections %>% filter(is.na(Region)) %>% group_by(NMA) %>% summarise()

# Create SH/LA column
library(dplyr)
library(stringr)

CalibrationSections <- CalibrationSections %>% mutate(OwnerType = if_else((str_detect(CAL_SECTION_ID, "CAL")|str_detect(CAL_SECTION_ID, "CS")), "SH", "LA"))

Roughness10m <- Roughness10m %>% left_join(CalibrationSections, by = c("SECTION_ID"="CAL_SECTION_ID"))

All Roughness Plots

# View(Roughness10m)

library(dplyr)
Roughness10mL <- Roughness10m %>% filter(LwpIRI < 25)
Roughness10mR <- Roughness10m %>% filter(RwpIRI < 25)
Roughness10mLN <- Roughness10m %>% filter(LaneIRI < 25)

library(ggplot2)
ggobj2 <- ggplot(data=Roughness10mL, aes(x=FinancialYear, y=LwpIRI, col = as.factor(LANE_DIRECTION))) +
  geom_boxplot() + ggtitle("All Roughness10m LWP Boxplot") + facet_wrap(~SECTION_ID, ncol =5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5)) + labs(col = "I/D") + ylab("LWP Roughness (IRI)") + xlab("Financial Year") 
print(ggobj2)

ggobj2 <- ggplot(data=Roughness10mR, aes(x=FinancialYear, y=RwpIRI, col = as.factor(LANE_DIRECTION))) +
  geom_boxplot() + ggtitle("All Roughness10m RWP Boxplot") + facet_wrap(~SECTION_ID, ncol =5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5)) + labs(col = "I/D") + ylab("RWP Roughness (IRI)") + xlab("Financial Year") 
print(ggobj2)

ggobj2 <- ggplot(data=Roughness10mLN, aes(x=FinancialYear, y=LaneIRI, col = as.factor(LANE_DIRECTION))) +
  geom_boxplot() + ggtitle("All Roughness10m Lane Boxplot") + facet_wrap(~SECTION_ID, ncol =5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5)) + labs(col = "I/D") + ylab("Lane Roughness (IRI)") + xlab("Financial Year") 
print(ggobj2)

SH Site Roughness

#SH Sites

Roughness10mLNSH <- Roughness10mLN %>% filter(OwnerType == "SH")

library(ggplot2)
ggobj2 <- ggplot(data=Roughness10mLNSH, aes(x=FinancialYear, y=LaneIRI, col = as.factor(LANE_DIRECTION))) +
  geom_boxplot() + ggtitle("SH Roughness10m Lane Boxplot") + facet_wrap(~SECTION_ID, ncol =5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5)) + labs(col = "I/D") + ylab("Lane Roughness (IRI)") + xlab("Financial Year") 
print(ggobj2)

SH CAL Sites

#SH CAL Sites

Roughness10mLNSHCAL <- Roughness10mLNSH %>% filter(stringr::str_detect(SECTION_ID, "CAL"))

library(ggplot2)
ggobj2 <- ggplot(data=Roughness10mLNSHCAL, aes(x=FinancialYear, y=LaneIRI, col = as.factor(LANE_DIRECTION))) +
  geom_boxplot() + ggtitle("SH CAL Roughness10m Lane Boxplot") + facet_wrap(~SECTION_ID, ncol =5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5)) + labs(col = "I/D") + ylab("Lane Roughness (IRI)") + xlab("Financial Year") 
print(ggobj2)

SH CS Sites

#SH Cs Sites

Roughness10mLNSHCS <- Roughness10mLNSH %>% filter(stringr::str_detect(SECTION_ID, "CS"))

library(ggplot2)
ggobj2 <- ggplot(data=Roughness10mLNSHCS, aes(x=FinancialYear, y=LaneIRI, col = as.factor(LANE_DIRECTION))) +
  geom_boxplot() + ggtitle("SH CS Roughness10m Lane Boxplot") + facet_wrap(~SECTION_ID, ncol =5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5)) + labs(col = "I/D") + ylab("Lane Roughness (IRI)") + xlab("Financial Year") 
print(ggobj2)

LA Site Roughness

#LA Sites

Roughness10mLNSH <- Roughness10mLN %>% filter(OwnerType == "LA")

library(ggplot2)
ggobj2 <- ggplot(data=Roughness10mLNSH, aes(x=FinancialYear, y=LaneIRI, col = as.factor(LANE_DIRECTION))) +
  geom_boxplot() + ggtitle("LA Roughness10m Lane Boxplot") + facet_wrap(~SECTION_ID, ncol =5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5)) + labs(col = "I/D") + ylab("Lane Roughness (IRI)") + xlab("Financial Year") 
print(ggobj2)

Big individual Roughness plots

library(dplyr)
ggobj2Roughness10m <- Roughness10m %>% group_by(SECTION_ID) %>% do(plots=ggplot(data=.) +
         aes(x=FinancialYear, y=LaneIRI, col = as.factor(LANE_DIRECTION)) +  geom_boxplot() + labs(col = "I/D") + ggtitle("Roughness - Lane IRI", subtitle = .$SECTION_ID) + ylab("Lane Roughness (IRI)") + xlab("Financial Year"))

print(ggobj2Roughness10m$plots)
## [[1]]

## 
## [[2]]

## 
## [[3]]

## 
## [[4]]

## 
## [[5]]

## 
## [[6]]
## Warning: Removed 90 rows containing non-finite values (stat_boxplot).

## 
## [[7]]

## 
## [[8]]

## 
## [[9]]

## 
## [[10]]

## 
## [[11]]

## 
## [[12]]

## 
## [[13]]

## 
## [[14]]

## 
## [[15]]

## 
## [[16]]

## 
## [[17]]

## 
## [[18]]

## 
## [[19]]

## 
## [[20]]

## 
## [[21]]

## 
## [[22]]

## 
## [[23]]

## 
## [[24]]

## 
## [[25]]

## 
## [[26]]

## 
## [[27]]
## Warning: Removed 16 rows containing non-finite values (stat_boxplot).

## 
## [[28]]

## 
## [[29]]

## 
## [[30]]

## 
## [[31]]

## 
## [[32]]

## 
## [[33]]

## 
## [[34]]

## 
## [[35]]

## 
## [[36]]

## 
## [[37]]

## 
## [[38]]

## 
## [[39]]

## 
## [[40]]

## 
## [[41]]

## 
## [[42]]

## 
## [[43]]

## 
## [[44]]

## 
## [[45]]

## 
## [[46]]

## 
## [[47]]

## 
## [[48]]

## 
## [[49]]

## 
## [[50]]

## 
## [[51]]

## 
## [[52]]

## 
## [[53]]

## 
## [[54]]

## 
## [[55]]

## 
## [[56]]

## 
## [[57]]

## 
## [[58]]

## 
## [[59]]

## 
## [[60]]

## 
## [[61]]

## 
## [[62]]

## 
## [[63]]

## 
## [[64]]

## 
## [[65]]

## 
## [[66]]

## 
## [[67]]

## 
## [[68]]
## Warning: Removed 20 rows containing non-finite values (stat_boxplot).

## 
## [[69]]

## 
## [[70]]

## 
## [[71]]

## 
## [[72]]

## 
## [[73]]

## 
## [[74]]

## 
## [[75]]

## 
## [[76]]

## 
## [[77]]

## 
## [[78]]

## 
## [[79]]

## 
## [[80]]

## 
## [[81]]

## 
## [[82]]

## 
## [[83]]

## 
## [[84]]

## 
## [[85]]

## 
## [[86]]

## 
## [[87]]

## 
## [[88]]

## 
## [[89]]

## 
## [[90]]

## 
## [[91]]

## 
## [[92]]

## 
## [[93]]
## Warning: Removed 120 rows containing non-finite values (stat_boxplot).

## 
## [[94]]

## 
## [[95]]

## 
## [[96]]

## 
## [[97]]

## 
## [[98]]

## 
## [[99]]

## 
## [[100]]

## 
## [[101]]

## 
## [[102]]

## 
## [[103]]

## 
## [[104]]

## 
## [[105]]

## 
## [[106]]

## 
## [[107]]

## 
## [[108]]

## 
## [[109]]

## 
## [[110]]

## 
## [[111]]

## 
## [[112]]

## 
## [[113]]

## 
## [[114]]

## 
## [[115]]

## 
## [[116]]

## 
## [[117]]

## 
## [[118]]

## 
## [[119]]

## 
## [[120]]

## 
## [[121]]

## 
## [[122]]

## 
## [[123]]

## 
## [[124]]

## 
## [[125]]

## 
## [[126]]

## 
## [[127]]

## 
## [[128]]

## 
## [[129]]

## 
## [[130]]

## 
## [[131]]
## Warning: Removed 90 rows containing non-finite values (stat_boxplot).

## 
## [[132]]

## 
## [[133]]

## 
## [[134]]

## 
## [[135]]

## 
## [[136]]

## 
## [[137]]

## 
## [[138]]

## 
## [[139]]

## 
## [[140]]

## 
## [[141]]

## 
## [[142]]

## 
## [[143]]

## 
## [[144]]

## 
## [[145]]

## 
## [[146]]

## 
## [[147]]

## 
## [[148]]

## 
## [[149]]

## 
## [[150]]

## 
## [[151]]

## 
## [[152]]

## 
## [[153]]

## 
## [[154]]

## 
## [[155]]

## 
## [[156]]

## 
## [[157]]

## 
## [[158]]

## 
## [[159]]
## Warning: Removed 120 rows containing non-finite values (stat_boxplot).

## 
## [[160]]
## Warning: Removed 120 rows containing non-finite values (stat_boxplot).

close(con2)