{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Cover","FillForm","Safety","Efficiency","Recommendations","Budget_Confidence"],"not_rendered":{"charts":2,"images":1,"macros":1,"external_links":0,"defined_names":2,"comments":2,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Cover","index":0,"n_rows":41,"n_cols":1,"rows_kept":41,"cut":false,"hidden":false,"merged":[]}
[]
[]
["Safety and efficiency at intersections "]
["October 2016"]
[]
[]
[]
[]
[]
[]
["Balancing safety and efficiency at intersections: a tool-based approach (appendix F in the main report)"]
[]
[]
[]
[]
[]
[]
[]
["Tim Brown and Steve Griffith"]
["Resolve Group, Auckland"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["NZ Transport Agency research report 600"]
["Contracted research organisation – Resolve Group Limited"]

{"sheet":"FillForm","index":1,"n_rows":65,"n_cols":17,"rows_kept":65,"cut":false,"hidden":false,"merged":[[34,1,34,7],[36,1,36,7],[39,1,39,7],[12,1,12,2],[21,1,21,11],[25,1,25,6],[27,1,27,5],[29,1,29,7]]}
[]
[]
[]
[null,null,null,null,null,null,null,"Balancing Safety and Efficiency at Intersections: A tool-based approach"]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Key"]
[null,"user input required"]
[null,"Driven from backend code",null,null,null,null,null,null,null,null,null,null,null,null,null,"accidents","\"To properly compare safety performance with respect to DSIs, the \nfrequency of injury crashes also has to be considered.\""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"F&S","number of fatal and serious accidents"]
[null,"Workflow description",null,null,null,null,null,null,null,null,null,null,null,null,null,"DSI","number of deaths or seriously injured road users"]
[1,"Read treatments",null,null,null,null,null,null,null,null,null,null,null,null,null,"DSI equivalents","injury crashes djusted for the typical proportion of DSIs of each crash type (estimated risk)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"LoSS"]
[2,"Fill fields: with/without \"signalization\"",null,null,null,null,null,null,null,null,null,null,null,null,null,"severity index","expected ratio of  DSI casualties to all injury crashes"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"injury crashes"]
[3,"Select 'Other features' excludes existing features at the intersection in the shortlisting",null,null,null,null,null,null,null,null,null,null,null,null,null,"collective risk","F&S crashes or DSI equivalents per intersection in a period"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"personal risk","risk of death or serious injuries to each vehicle entering the intersection"]
[4,"Select 'urban' /'rural'"]
[]
[5,"Enter AADT for each approach"]
[]
[6,"Select 'Priority Control' features"]
[]
[7,"Enter total number of Fatal and Serious Crashes"]
[]
[8,"DSI's calculated based on Rural/Urban Selection, and total F/S crashes provided "]
[]
[null,"Advanced Options' Tab"]
[9,"Select Minor/major change (required for intersection)",null,null,null,null,null,null,null,null,null,null,null,null,null,"Methods for calculating DSI equivalents"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"using F&S crashes","no calculation necessary\nproblem: overestimation (ca. 100%)\nif using longer time spans for evaluation, intersection has often changed"]
[10,"Select share allocation of road users . Either 1. Evenly distribute, 2. Set priority (4 based), or 3. Set number of users by mode (if known)",null,null,null,null,null,null,null,null,null,null,null,null,null,"using all injury crashes","more data available -> higher reliability\ncalculation based on (known) severity for each crash type\nseverity indices available based on\n  - crash movement type\n  - intersection form and control \n  - speed limit\nbetter estimation than using the number of F&S crashes\ncalculation: multiply each injury crash at an intersection by the DSI per injury crash for that crash type (\"DSI casualty severity index\")"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"using CAS","database access  and external tools required"]
[null,"One Network Road Classification' Tab",null,null,null,null,null,null,null,null,null,null,null,null,null,"using crash models","wide variety of models at different complexity levels"]
[11,"Enter key target level of service parameters"]
[]
[12,null,null,null,null,"normalize road user priorities "]
[]
[null,null,null,null,null,"individual treatment rating for safety = "]
[null,null,null,null,null,"normalised car rating  x safety improvement for cars  x 4"]
[null,null,null,null,null,"+ normalised ped rating  x safety improvement for peds x 4"]
[null,null,null,null,null,"+ normalised cyclist rating  x safety improvement for cyclists x 4"]
[null,null,null,null,null,"+ normalised public transport rating  x safety improvement for public transport  x 4"]
[]
[null,null,null,null,null,"individual treatment rating for efficiency = "]
[null,null,null,null,null,"normalised car rating  x efficiency improvement for cars  x 4"]
[null,null,null,null,null,"+ normalised ped rating  x efficiency improvement for peds x 4"]
[null,null,null,null,null,"+ normalised cyclist rating  x efficiency improvement for cyclists x 4"]
[null,null,null,null,null,"+ normalised public transport rating  x efficiency improvement for public transport  x 4"]
[]
[null,null,null,null,null,"merge treatments, safety evaluation, efficiency evaluation on new sheet"]
[]
[null,null,null,null,null,"rank measures by safety"]
[]
[null,null,null,null,null,"safety treatments do not sum up -> reduce geometrically:  1/1 + 1/2 + 1/3 + 1/4 + 1/5 …"]
[]
[null,null,null,null,null,"final valuation per treatment (\"SEFT value\"): "]
[null,null,null,null,null,"Average of Safety score  + Efficiency score"]
[null,null,null,null,null,"↓"]
[null,null,null,null,null,"sort by final evaluation"]

{"sheet":"Safety","index":2,"n_rows":34,"n_cols":10,"rows_kept":34,"cut":false,"hidden":false,"merged":[]}
["measure","location","improvement for cars","improvement for pedestrians","improvement for cyclists","improvement for public transport","improvement for heavy vehicles","total improvement [%]","transformation/transition","comments"]
["roundabout","urban",{"$f":"$H2/5","v":15},15,15,15,15,75,"transformation","conversion from T or X intersection\n10-40% reduction in injury crashes\n90% reduction in serious and fatal crashes \n80% reduction in all crashes from uncontrolled intersection\n25-50% reduction in all crashes from traffic signals"]
["red light camera enforcement","both",13.8,13.8,13.8,13.8,13.8,69,"transition","69% reduction in red light running crashes at red light camera sites"]
["T-intersection","both",12,12,12,12,12,60,"transformation","from Y intersection\n15-50% reduction in all crashes\n87% reduction in injury crashes"]
["roundabouts: optimal geometry","both",10.8,10.8,10.8,10.8,10.8,54,"transformation","54% reduction in total crashes"]
["grade separation","urban",10,10,10,10,10,50,"transformation","50% reduction in injury crashes when replacing crossroads"]
["cyclist facilities","both",10,10,10,10,10,50,"transition","50% reduction in cyclist injuries with green lane surfaces at signals"]
["staggered T intersections","rural",9,9,9,9,9,45,"transformation","crosssroads to staggered intersection\n25-35% where minor road flows are +15% of main road\n35% where minor traffic flows is <15% of main road\n40-95% reduction in injury crashes"]
["coloured high friction surfacing","both",8.6,8.6,8.6,8.6,8.6,43,"transition","18-74% reduction in injury crashes due to improved skidding resistance\n40% reduction in rear-end crashes\nLimited data on the effectiveness of colour alone as it is usually used in conjunction with other measures, although generally accepted by industry as an effective measure to raise awareness."]
["central lighting","both",8,8,8,8,8,40,"transition","40% reduction in injury crashes for improving lighting (all intersection forms)\n50% reduction in night time crashes\nrural intersection 40% of night time crashes\nurban intersection 20% of night time crashes"]
["transverse markings and rumble strips","rural",7.6,7.6,7.6,7.6,7.6,38,"transition","33% reduction in injury crashes\n17-50% reduction in total crashes\n24-54% reduction in crashes"]
["intelligent active warning signs","rural",{"$f":"$H12/5","v":7},7,7,7,7,35,"transition","35% reduction in injury crashes"]
["signalization:  advanced cycle stop box","both",7,7,7,7,7,35,"transition","35% reduction in crashes for advanced cycle stop box"]
["enhanced signing","urban",6.8,6.8,6.8,6.8,6.8,34,"transition","24-54% reduction in crashes"]
["turning bays","both",6.8,6.8,6.8,6.8,6.8,34,"transformation","33% reduction in injury crashes \n35% reduction in injury crashes\nHowever these are low severity rear end crashes that are saved. Fatal and serious crash risk may increase."]
["signalization: separate right turn phase","both",6.2,6.2,6.2,6.2,6.2,31,"transition","35% reduction in injury crashes\n27% reduction in injury crashes"]
["signalization: install basic traffic signals","urban",6,6,6,6,6,30,"transformation","from uncontrolled / give-way\n15-30% reduction in all crashes from uncontrolled intersection"]
["sight distance improvements","both",6,6,6,6,6,30,"transformation","30% reduction where sight distance is improved"]
["urban pedestrian facilities","urban",6,6,6,6,6,30,"transition","30% reduction in crashes"]
["clear or safe zones: frangible sign posts","both",6,6,6,6,6,30,"transition","Widely acknowledged to reduce crash severity although overall number of crashes unlikely to reduce. \n30% reduction in injury crashes where frangible sign posts used"]
["minor road central islands","both",5.4,5.4,5.4,5.4,5.4,27,"transformation","17-35% reduction in injury crashes at crossroads\n39% reduction in total crashes\nminor road: 40% at X intersections\nminor road: 45% at T intersections\nmajor road: 15% if mountable\nmajor road: 25% if not mountable"]
["signalization: improved signal conspicuity","both",{"$f":"$H22/5","v":5},5,5,5,5,25,"transition","based on one US study on the benefits of installing mast arms"]
["speed camera","urban",4.5999999999999996,4.5999999999999996,4.5999999999999996,4.5999999999999996,4.5999999999999996,23,"transition","23% reduction in fatal and serious crashes at urban speed camera sites"]
["signalization: pedestrian-only phase","both",4,4,4,4,4,20,"transition","No reduction at intersections with phases permitting conflicting pedestrian/vehicle movements (eg left turn or right turn filtering)"]
["roundabouts: pedestrian fencing","both",4,4,4,4,4,20,"transition","20% if pedestrian fencing used"]
["urban and rural pedestrian facilities","both",3,3,3,3,3,15,"transition","15% for pedestrian refuge islands"]
["roundabouts: raised pedestrian refugee islands","both",3,3,3,3,3,15,"transition","15% if raised pedestrian refuge island"]
["signalization: marked crossing for cyclists at signals","both",2.4,2.4,2.4,2.4,2.4,12,"transition","10-15% reduction in crashes for marked crossing at signals"]
["speed camera","rural",2.2000000000000002,2.2000000000000002,2.2000000000000002,2.2000000000000002,2.2000000000000002,11,"transition","11% reduction in fatal and serious crashes at rural speed camera sites"]
["speed discrimination equipment","both",0,0,0,0,0,0,"transition","no current research data"]
["align opposing right turns","both",0,0,0,0,0,0,"transformation","no current research data"]
["roundabouts: reverse curves to reduce speeds","both",0,0,0,0,0,0,"transformation","no current research data"]
["roundabouts: cyclist facilities","both",0,0,0,0,0,0,"transition","no current research data"]
["roundabous: adverse camber rectification","both",0,0,0,0,0,0,"transformation","no current research data"]

{"sheet":"Efficiency","index":3,"n_rows":34,"n_cols":9,"rows_kept":34,"cut":false,"hidden":false,"merged":[]}
["measure","location","improvement for cars","improvement for pedestrians","improvement for cyclists","improvement for public transport","improvement for heavy vehicles","efficiency evaluation","comments"]
["roundabout","urban",{"$f":"$H2/5","v":6},6,6,6,{"$f":"$H2/5","v":6},30]
["staggered T intersections","rural",2,2,2,2,2,10]
["T-intersection","both",1,1,1,1,1,5]
["signalization: install basic traffic signals","urban",7,7,7,7,7,35]
["grade separation","urban",2,2,2,2,2,10]
["intelligent active warning signs","rural",0,0,0,0,0,0]
["transverse markings and rumble strips","rural",0,0,0,0,0,0]
["coloured high friction surfacing","both",0,0,0,0,0,0]
["enhanced signing","urban",1,1,1,1,1,5]
["minor road central islands","both",0,0,0,0,0,0]
["turning bays","both",3,3,3,3,3,15]
["sight distance improvements","both",1,1,1,1,1,5]
["urban and rural pedestrian facilities","both",0,0,0,0,0,0,"pedestrian refuge islands"]
["signalization: pedestrian-only phase","both",-2,-2,-2,-2,-2,-10]
["cyclist facilities","both",-0.4,-0.4,-0.4,-0.4,-0.4,-2,"green lane surfaces at signals"]
["speed discrimination equipment","both",0,0,0,0,0,0]
["signalization: separate right turn phase","both",{"$f":"$H18/5","v":-2},-2,-2,-2,-2,-10]
["signalization: improved signal conspicuity","both",1,1,1,1,1,5]
["align opposing right turns","both",0,0,0,0,0,0]
["urban pedestrian facilities","urban",0,0,0,0,0,0]
["signalization: marked crossing for cyclists at signals","both",-2,-2,-2,-2,-2,-10]
["signalization:  advanced cycle stop box","both",-0.4,-0.4,-0.4,-0.4,-0.4,-2]
["roundabouts: reverse curves to reduce speeds","both",0.6,0.6,0.6,0.6,0.6,3]
["roundabouts: optimal geometry","both",5,5,5,5,5,25]
["central lighting","both",0,0,0,0,0,0]
["roundabouts: raised pedestrian refugee islands","both",0,0,0,0,0,0]
["roundabouts: pedestrian fencing","both",0,0,0,0,0,0]
["roundabouts: cyclist facilities","both",-0.6,-0.6,-0.6,-0.6,-0.6,-3]
["roundabous: adverse camber rectification","both",2,2,2,2,2,10]
["clear or safe zones: frangible sign posts","both",0,0,0,0,0,0]
["red light camera enforcement","both",-0.6,-0.6,-0.6,-0.6,-0.6,-3]
["speed camera","urban",0,0,0,0,0,0]
["speed camera","rural",0,0,0,0,0,0]

{"sheet":"Recommendations","index":4,"n_rows":45,"n_cols":8,"rows_kept":45,"cut":false,"hidden":false,"merged":[[1,0,1,3],[3,0,3,4]]}
[]
["Safety and Efficiency at Intersections Recommendation"]
[]
["PROOF OF CONCEPT - EVALUATION VERSION"]
[]
["measure","Installation Cost","weighted CRF","efficiency score","SEFT Evaluation"]
["roundabouts: optimal geometry",4000000,54,25,39.5]
["signalization: install basic traffic signals",500000,30,35,32.5]
["grade separation",10000000,50,10,30]
["turning bays",100000,34,15,24.5]
["sight distance improvements",500000,30,5,17.5]
["minor road central islands",40000,27,0,13.5]
["roundabous: adverse camber rectification",500000,0,10,5]
["roundabouts: reverse curves to reduce speeds",1000000,0,3,1.5]
["align opposing right turns",500000,0,0,0]
[]
[]
[null,null,null,null,null,null,null,"Traffic Flow and Intersection Properties"]
[null,null,null,null,null,null,null,"Number of Approaches: 4"]
[null,null,null,null,null,null,null,"Total Traffic Flow: 41500 (AADT)"]
[null,null,null,null,null,null,null,"Peak Hour Flow: 4565 "]
[null,null,null,null,null,null,null,"Product Of Conflicting Flow: 2601 "]
[null,null,null,null,null,null,null,"Peak Hour Product Of Conflicting Flow: 286 "]
[]
[null,null,null,null,null,null,null,"Efficiency"]
[null,null,null,null,null,null,null,"Peak Hour Utilization: 1.52 (normalised: 0%)"]
[null,null,null,null,null,null,null,"Peak Hour Efficiency LoS: F"]
[null,null,null,null,null,null,null,"OneNetwork Road Classification (target): National (high volume)"]
[null,null,null,null,null,null,null,"Travel Time Reliability: \nvaries (affected by other road users, physical road conditions, weather events)"]
[null,null,null,null,null,null,null,"Network Operating Gap: 100"]
[]
[null,null,null,null,null,null,null,"Safety"]
[null,null,null,null,null,null,null,"Collective Risk: 1.5"]
[null,null,null,null,null,null,null,"Collective Risk Level: Medium-High"]
[null,null,null,null,null,null,null,"Personal Risk: 185.883"]
["measure","location","Investment","annual costs","confidence",null,null,"Personal Risk Level: High"]
["roundabouts: optimal geometry","both",4000000,10000,0]
["signalization: install basic traffic signals","urban",500000,0,0.5]
["grade separation","urban",10000000,0,0.14999999999999991]
["turning bays","both",100000,0,0.29999999999999982]
["sight distance improvements","urban",500000,0,0]
["minor road central islands","both",40000,0,0]
["roundabous: adverse camber rectification","both",500000,0,9.9999999999999645E-2]
["roundabouts: reverse curves to reduce speeds","both",1000000,20000,9.9999999999999645E-2]
["align opposing right turns","both",500000,0,0.14999999999999991]

{"sheet":"Budget_Confidence","index":5,"n_rows":34,"n_cols":6,"rows_kept":34,"cut":false,"hidden":false,"merged":[]}
["measure","installation costs (NZD)","operational costs (NZD p.a.)","confidence in cost","confidence in safety","confidence in efficiency"]
["roundabout",3000000,0,0.4,0.5,0.85]
["red light camera enforcement",100000,0,0.8,0.8,0.4]
["T-intersection",1000000,20000,0.6,0.7,0.7]
["roundabouts: optimal geometry",4000000,10000,0.3,0.55000000000000004,0.9]
["grade separation",10000000,0,0.3,0.9,0.95]
["cyclist facilities",50000,0,0.7,0.7,0.7]
["staggered T intersections",3000000,0,0.6,0.8,0.7]
["coloured high friction surfacing",50000,0,0.9,0.8,0.5]
["central lighting",250000,0,0.8,0.9,0.7]
["transverse markings and rumble strips",50000,0,0.9,0.8,0.7]
["intelligent active warning signs",150000,0,0.8,0.7,0.7]
["signalization:  advanced cycle stop box",20000,0,0.8,0.7,0.6]
["enhanced signing",20000,0,0.9,0.7,0.6]
["turning bays",100000,0,0.7,0.8,0.8]
["signalization: separate right turn phase",25000,0,0.7,0.9,0.7]
["sight distance improvements",500000,0,0.5,0.8,0.7]
["urban pedestrian facilities",30000,20000,0.7,0.8,0.7]
["clear or safe zones: frangible sign posts",10000,10000,0.9,0.8,0.6]
["minor road central islands",40000,0,0.7,0.6,0.6]
["signalization: improved signal conspicuity",50000,0,0.7,0.7,0.8]
["speed camera",100000,0,0.8,0.8,0.6]
["signalization: install basic traffic signals",500000,0,0.8,0.8,0.9]
["signalization: pedestrian-only phase",50000,0,0.8,0.9,0.7]
["roundabouts: pedestrian fencing",25000,0,0.9,0.75,0.7]
["urban and rural pedestrian facilities",50000,0,0.8,0.8,0.7]
["roundabouts: raised pedestrian refuge islands",100000,0,0.7,0.7,0.7]
["signalization: marked crossing for cyclists at signals",20000,0,0.8,0.8,0.7]
["speed camera",100000,0,0.8,0.8,0.7]
["speed discrimination equipment",100000,0,0.8,0.8,0.7]
["align opposing right turns",500000,0,0.6,0.8,0.75]
["roundabouts: reverse curves to reduce speeds",1000000,20000,0.6,0.8,0.7]
["roundabouts: cyclist facilities",100000,10000,0.7,0.8,0.7]
["roundabous: adverse camber rectification",500000,0,0.6,0.8,0.7]