Documentation of Strategic Goals Indexes

This story was created by BIO-WEST and is intended to document all source tables, calculations, scripts, methodology, and other information used to calculate index values used in the Strategic Direction pages for SAFETY, INFRASTRUCTURE, and MOBILITY. 

Measures

SAFETY

  • Traffic Fatalities
  • Traffic Serious Crashes
  • Traffic Crashes
  • UDOT Internal Fatalities
  • UDOT Internal Injuries
  • UDOT Equipment Damage

MOBILITY

  • Reliability
  • Delay
  • Snow
  • Mode Split

INFRASTRUCTURE

  • Pavement
  • Bridges
  • ATMS
  • Signals

Definitive Table Strategic Goals Indexes

Table of current indexes. Contains the three composite indexes as well as the component indexes that are weighted and summed to create the component indexes.
Display or use of indexes should link to this table and not try to calculate them. See examples below.

HTML to Display Indexes & Status Graphs

The HTML code from the following examples can be copied and used in Socrata stories as "HTML Embed".
These examples use HTML code to get the index and build status graphs from the "Definitive" table and display it using the UDOT desired blue style.

Zero Fatalities Index

Optimize Mobility Index

Preserve Infrastructure Index

Archive of Strategic Goals Indexes

This is a live table owned by BIO-WEST  and maintained via R-script which tracks index values over time (daily).  Annual values are calculated from the average daily values for each year.
Phil Nacamuli has a similar table (weekly) which is not currently being updated (as of Aug. 08, 2018. We are not using this table.

Targets, Weights, and Index Values

The Strategic Direction planning process established targets for each measure. A weighting system was also used to assign a relative weight based on each measures importance. Within each measure, the combined weights should equal 100.  Finally, Index Values are calculated base on how close UDOT is to meeting measure targets as follows. If all targets are met for a measure, the total index value would be 100.
Index Value = ((Measure/Target ) * Weight ) * 100

or

Index Value = 100-((Measure-Target)/(Max Value-Target)) * 100

Weights and Targets Table

Statewide and Regional
Edit this table to change the assigned weights and targets.  Scripts running in the background will update all calculations based on values in this table.
Comment field indicates which weights and targets are used in the calculations. Safety only uses weights, as targets are dynamically calculated based on data. 

SAFETY

Traffic Fatalities, Traffic Serious Injuries, and Traffic Crashes

Statewide and Regional
This is a live table updated nightly. All three measures use this table.
Script: uses latest year, queries table using Category field ('fatalities', 'injuries', or 'crashes') and calculates index value from the Actual,  Target and 3 Year Average plus 25% (maximum).


Internal Fatalities

Statewide and Regional
This is a STATIC table maintained by BIO-WEST. There is no update schedule but updated as necessary.
Script: uses latest year,
If count = 0 Index = 100
If count > 0 Index = 0

Internal Injury Rate and Equipment Damage

Statewide and Regional
This is a live table updated nightly. Both measures use this table. 
Script: uses CHART_DISPLAY field to determine months to use, calculates index value from Injury_Rate and Injury Target or Damage_Rate and Damage_Target

Mobility

Delay (I-15 Delay Hour) Statewide

Statewide table owned by Andrea Moser is updated monthly via script from Grant Thorton. 
Script: uses score field.

Delay Regions

Live table for regional Delay values with monthly updates owned by Phil Nacamuli. As of August 02, 2018 table did not contain goals or scores.
Using Statewide data for Region indexes.

Reliability Score Statewide

Table originally from an Excel file via email. Table Link
Script: uses normalized_score or regional score field.

Reliability Score Regions

Live table with monthly updates owned by Phil Nacamuli. As of August 02, 2018 table did not contain statewide values or goals.
Using Statewide data for Region indexes.

Mode Split

Statewide and Regional
This table maintained by BIO-WEST and updated as data becomes available. Metadata page here.
Script: uses auto_trips and transit_trips fields.
Statewide data (I-15) updated 25 Sep 2018.
Regional data is not yet available. Using statewide data for Regions for now.

Snow

Statewide and Regional
Live data maintained by Paul Legler. Updated on a monthly basis during snow season. 
Script: where performance_2 field is equal to "Exceptional" or "Acceptable" script uses performance_1 or regional performance field.
Regional data not yet available. Using statewide data for Regions for now.

Infrastructure

Pavement (Statewide)

Statewide pavement condition is a static table owned by HDR. It is updated periodically. Do not display future conditions in graphs.
Script: use fair and good fields

Pavement (Regions)

Regional pavement condition is a static table owned by HDR. It is updated periodically.
Script: use fair and good fields
NOTE: Graphs use filter on year set at 2017.

Bridges

Statewide
The dynamic table is maintained by phil.nacamuliBridge Index is calculated for each category, NHS, State and Local.
Script: uses combined_avg field

Bridges

Regional
A live statewide and regional strucutre table with conditions. 
Script: uses Region_Name and BHI fields
A discussion should be had to determine if bridge information can come from only one table. The Statewide table has been used by the script for a long time, but it only has statewide data. But in September 2018 Glen found the table with regional data, so it is now used for the region calculations.

Signals 

Statewide and Regional
This is a STATIC table maintained by BIO-WEST from a Google Drive table provided by Jamie Mackey. Update schedule is variable but usually indicated by an email to Glen.
Script: uses Percent Good and Average

ATMS

Statewide and Regional
This is a STATIC table maintained by BIO-WEST. Original data is from two sources: 1) statewide values prior to July 2018 from Excel file ATMS Device ATMS Device Summary.xlsx from Rob Clayton via email, 2) regional and statewide values from January 2018 and afterward are from Google Drive sheet "ATMS Devices by Region"
Script: uses % Operational

Reference Table

Measures, Targets, and Weights of Individual Components

This table contains the measures, targets, and weights used in the latest calculation of the Strategic Goals composite indexes.

Values used in latest index calculations


Script Versions

DefinitiveStrategicGoalsIndexes
(These notes were not started until version 3. Some of the earlier changes may not be listed here.)
v1. Original Script
v1.1 etc. Adjustments to script. But then decided to drop the point increments of versions.
v2. Changed Internal Injuries and Equipment Damage to use all months with CHART_DISPLAY = TRUE. (This turned out to be an error and fixed in v3.)  Also, the Reference Table of intermediate values was dropped because of this change.
v3. 25 Sep 2018. Changes 1. Changed Internal Injuries and Equipment Damage to use the latest month with CHART_DISPLAY = True. 2. Recreated the Reference Table of intermediate values. 3. New Bridge index table of values for regions. 4. Field name changed in Strategic Direction_Zero Fatalities_External Crash Data_Data Set table, so script changed to work with new name. 5. Re worked Mode Split, Transit Goal became the Target, Renamed some fields. 6. Added code to not write to Socrata when testing. 
v4. 18 Dec 2018. 1. Changed the script to account for the unique way Equipment Damage and Internal Injury data is posted. The data is delayed and posts on the 15th. So before the 15th use data from two months ago, after the 15th use data from one month ago.  2. Also changed the "now" format to match the way Socrata's Date Time field likes it. For some reason the version 3 script could no longer save data to the Reference table unless we changed the format, although it did still work in the Definitive table. 
v5. 17 Jan 2019. Added logic to handle the situation when some but not all categories having data for the new calendar year in the input "Strategic Direction_Zero Fatalities_External Crash Data_Data Set" data set. The script needed to distinguish the correct year to use based upon the individual category.