LTPP Ratings Report

This page contains a national summary of the Visual Ratings 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 Rating type for all sites.

Data Importing

The code below imports the data from the Ratings in the database

#Sys.Date()
Sys.time()

## [1] "2021-11-29 10:10:58 NZDT"

#setup plot theme for plots
#library(ggplot2)
#mytheme <- theme_grey() + theme(text = element_text(colour = "black", size=18))


#load database connection library
library(RODBC)
 
#connect to database 1 - CAPTIF PR3 0404.mdb
db <- "C:/local_databases/NZTA_SH&LA_LTPPData_IntCalib_1Jul2001-30Jun2021.mdb"

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

Importing tables

#Check column names of a table 
#sqlColumns(con2, "Profilometer Profiles")$COLUMN_NAME


Rating50m <- sqlFetch(con2, "50mRating")
CalibrationSections <- sqlFetch(con2, "CalibrationSections")

#Data tidy

# 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"))

View(CalibrationSections)
#print(CalibrationSections)

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

All Rating 50m - plots!

#View(Rutting10m)

library(ggplot2)

ALoss%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`ALoss%`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m ALoss% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

## Warning: Removed 12 rows containing non-finite values (stat_boxplot).

SLoss%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`SLoss%`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m SLoss% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

## Warning: Removed 12 rows containing non-finite values (stat_boxplot).

ChipLoss%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`ChipLoss%`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m ChipLoss% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

## Warning: Removed 18732 rows containing non-finite values (stat_boxplot).

Delam%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`Delam%`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m Delam% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

MechDamage%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`MechDamage%`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m MechDamage% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

Flush-Low%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`Flush-Low%`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m Flush-Low% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

## Warning: Removed 13 rows containing non-finite values (stat_boxplot).

Flush-Mid%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`Flush-Mid%`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m Flush-Mid% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

Flush-High%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`Flush-High%`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m Flush-High% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

LongE-N%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`LongE-N`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m LongE-N% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

LongE-W%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`LongE-W`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m LongE-W% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

LongE-S%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`LongE-S`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m LongE-S% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

LongWT-N%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`LongWT-N`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m LongWT-N% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

LongWT-W%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`LongWT-W`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m LongWT-W% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

LongWT-S%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`LongWT-S`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m LongWT-S% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

LongIR-N%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`LongIR-N`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m LongIR-N% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

LongIR-W%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`LongIR-W`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m LongIR-W% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

LongIR-S%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`LongIR-S`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m LongIR-S% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

Trans-N%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`Trans-N`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m Trans-N% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

Trans-W%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`Trans-W`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m Trans-W% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

Trans-S%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`Trans-S`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m Trans-S% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

AlG-N

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`AlG-N`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m AlG-N Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

AlG-W

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`AlG-W`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m AlG-W Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

AlG-S

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`AlG-S`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m AlG-S Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

AlWT-N

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`AlWT-N`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m AlWT-N Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

AlWT-W

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`AlWT-W`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m AlWT-W Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

AlWT-S

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`AlWT-S`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m AlWT-S Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

PAR-N

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`PAR-N`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m PAR-N Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

PAR-W

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`PAR-W`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m PAR-W Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

PAR-S

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`PAR-S`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m PAR-S Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

PatchSurf

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`PatchSurf`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m PatchSurf Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

PatchStructure

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`PatchStructure`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m PatchStructure Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

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

PotholeDiameter

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`PotholeDiameter`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m PotholeDiameter Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

PotholeDepth

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`PotholeDepth`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m PotholeDepth Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

PotholeNumber

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`PotholeNumber`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m PotholeNumber Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

EdgeA

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`EdgeA`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m EdgeA Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

EdgeD

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`EdgeD`, col = as.factor(LANE_DIRECTION))) +  geom_boxplot() + ggtitle("Rating50m EdgeD Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

Shoving%

ggobj2 <- ggplot(data=Rating50m, aes(x=FinancialYear, y=`Shoving%`, col = as.factor(LANE_DIRECTION))) + geom_boxplot() + ggtitle("Rating50m Shoving% Boxplot") + facet_wrap(~SECTION_ID, ncol=5) + theme(axis.text.x=element_text(angle=90,hjust=1,vjust=0.5))
print(ggobj2)

## Warning: Removed 7 rows containing non-finite values (stat_boxplot).

close(con2)