Tuesday, 21 February 2017

Other Activties undertaken – James Kings' (Wellington Region) recollections

Road Safety Action Plans

Another area we became involved in was Road Safety Action Plans (RSAP).
I’m not sure when they were initiated or by whom (possibly 2004 and possibly under Demetra Kennedy’s  -Regional Manager)- watch in Wellington Region).

The idea was to get all parties within a local authority with an interest in road safety to come together to discuss and plan road safety actions that were within their milieu and that they could cooperate and have a positive impact on road safety in that authority’s area.

Generally, the group consisted of the road safety coordinator and the roading engineers from the authority, the Police, Accident Compensation Authority, Automobile Association, an LTSA/LTNZ/NZTA officer and a state highways representative.

In Wellington, these meetings were initially chaired by the Regional Director (which suggests the RSAPs were initiated by LTSA/LTNZ/NZTA possibly to better understand and support the work of the road safety coordinator whom we were largely funding), before they devolved to the local authority.

In the first meeting, there was a general discussion of what each organisation thought were the road safety issues that needed to be addressed and what if anything could be bought to the table to address the problems.

Subsequent meetings generally came together on a quarterly basis to discuss progress and liaise with each other on their programmes.

These meetings required some analysis of their projects or trends in the road safety data they obtained associated with their projects and hence whether they believed they had made some progress.

We bought crash trend statistics for each of the problem categories that had been defined for the group to tackle.

I had, now, “discovered” that Excel could get data directly from CAS, so promptly set about producing a spreadsheet that could extract the appropriate data for individual authorities (see ‘RSAP Printout’) that tied in to the problem categories.

It was well received in Wellington and Christchurch Regions and though I wanted them to be run locally there were blockages in the network that did not allow the program to run from Christchurch or Auckland.

The program thus was never fully developed but I consider it to have been successful.

(Note from Chris Hewitt - In Auckland the origins were much older most certainly in the 1990's or before and these groups and the term RSAP was not used until much later. 
I remember them being really functional in Auckland.  I was looking after "south" at that stage which was Franklin District, Papakura District and Manukau City. Each one had its own "road safety liaison" group reflective of  geography and population. 

For myself they were a chance to deliver data, stay connected, keep pushing road safety and develop quality working relationships with local bodies. 
I thought as a single point of contact for the relatively diverse professions that made up these liaison groups they were excellent as an information exchange. 

I met many highly skilled and motivated people in that time from the local bodies, Opus (who helped Franklin and Manukau out), ACC, the Fire service, NZ Police , St Johns, Police Intel and the Road Safety co-ordinators. In Franklin the Road Safety Co-ordinator Mark Ball later became the Mayor - nothing like support from the top. 

As a personal view I felt they fell over once HQ started demanding a more formal one size fits all RSAP type approach possibly because that sense of local ownership and operating style was being taken away. 

I do remember working hard with Inspector Sandy Newsome of the Manukau Police to develop some data driven road safety objectives, but I did have a sense of frustration that many of the "targets" suggested were far from measurable. 

I can't remember if anything in the way of a full plan was ever completed as by that stage all the LTSA/LTNZ/NZTA mergers had muddied the water. Certainly after Doug Miller stopped being our manager the idea of rolling up ones sleeves and getting stuck in with the local people all but ceased. Under the CAS Manager we were even pulled from CAS training outside the Agency, which we'd seen as an essential activity to get local bodies up-skilled and doing their own fishing in CAS which the NZTA supplied free).

Crash Rates

Crash rates on individual roads was the target with this analysis but was never fully realised though I believed it had promise.

It took data from CAS and from the database on local authority’s traffic volume and length of individual road links and matched them to determine a crash rate on that road.

This was feasible because each crash was supposedly tied to an individual piece of a road on which that crash event had occurred.

One problem that was discovered in this process was that crashes at intersections were not being correctly assigned to the roads on which the crash occurred and that they were just being assigned to one of the intersecting roads. (Bugs in CAS turn up in the most unexpected moments)

I was also looking to aggregate the individual roads but had not developed a robust way of doing so given the different traffic volumes on individual bits of a road and the likely difference in section profiles.

(Note from Chris Hewitt - a reliable crash rate system built into CAS had been a goal from the outset but one of the issues is the way the traffic volumes are gathered. Much of the network is not actually counted regularly, if at all and this alone can lead to wild variations in the resultant rates. As this was an area that most end users wanted Stuart Badger and RTI worked to refine the process over many years and got close at the time the Investment Group at NZTA took over CAS , with Stuart just needing to do further testing. However a lack of enthusiasm from the new NZTA CAS Steering group and the dropping of the maintenance contract with RTI saw the end of development.)

Risk Profile

Late in the piece (2009/2010) we looked at risk profiles and again I used data extraction from CAS to Excel to create a table of the risk profile. This was then further advanced by Colin Morrison.

I am sure there were other minor projects undertaken for other parties and even some assistance given to others with Excel problems but I’ll need reminding!

Wednesday, 15 February 2017

Road Safety Briefing Notes - the teams most popular, most recognised safety publication

The Road Safety Briefing Notes or Road Safety Issues Reports as they were initially called were the LTSA’s most popular road safety product – running in their “booklet” form from 2000 through to 2011. That these are still mentioned today in conversations with road safety professionals is testament to the success of the format.

The Briefing Notes were born from two needs:
  1.  A desire to get more easily digested (and brief) statistical material from the 100 page data report available at a media and chief executive level (Dennis Robertson – LTSA Regional Manger Christchurch) and
  2. A need to provide something in more detail (than Dennis’s idea) highlighting key safety issues in a relatively consistent format for each local body in NZ (Glenn Bunting Regional Manger in the Hamilton Office) again in a more easily digested form. 

Glenn says he can clearly remember selling the idea to the other RM’s so I think we can safely give credit for the general idea to the then group of Regional Managers at the LTSA.

The look of the original 2000 Road Safety Issues Reports :

As with the Road Safety Data Reports the process was managed by one of LTSA’s Expert groups, the same one as managed the Data Reports. In 2002 this group was David Croft, Robyn Denton, Colin Goble, David Curson, Jeremy Byfield and Yvonne Warnaar (convenor) with help from Stuart Badger and Alan Dixon.
Chris Hewitt, Geoff Holland and Colin Morrison took over in the Performance information time.  In the Investment team era Colin Morrison carried on as best he could with a much reduced resource. 

Thus the overall concept of the Briefing notes was to provide road safety professionals – and non-professionals with a document that gave between three and five key road safety issues for a particular local body or local government region. Each report was specifically written for the local body in the issues contained but presented in a consistent format nationally.

The general time frame: 
  1. December – February production of MS Publisher "issues" subject templates to be used (giving a consistent national look and feel) - around 15 variants for example speed, bends, alcohol, pedestrians, cyclists, motorcyclists, intersections, weather, driver age etc etc, 
  2. End of March - finalisation of all the previous years CAS data, that is all editing (grid positions added to crashes, all error checks done, all fatal crashes accounted for)
  3. Early April – development of the issues. Initially from the data reports and later using CAS directly and even later confined to NZTA's funding agenda.  
  4. April – June - production with drafts sometimes shared with local body for whom report was being written. Initially this was wholly via hard copy, later by printed and .pdf and towards the end in .pdf with selected recipients receiving hard copy. Even later when produced solely from HQ only in downloadable topic by topic format.
There were some regional variations depending on need – for example in Auckland the Motorway system was treated as a separate entity to prevent the huge number of rear-end crashes unduly colouring what was found in the adjoining local authorities.

Following the initial year 2000 note the format matured and it may have been the involvement or the LTSA’s comms team that got them there, however this marriage proved a frustrating one as I recall with delays, meanings being changed and pedantic arguments over grammar rather than content.

In the end the engineering team divorced Comms and took full ownership with most of us coming to grips with MS Publisher which proved a far more flexible means of production than MS Word - and wasn’t so hard to learn thus destroying the comms teams mystique.

LTSA variant - corporate colours of the time .....

Geoff Holland, Colin Morrison and Chris Hewitt (the LTNZ days) further matured the look of the product and streamlined production. 
There is a lot to be said for taking full responsibility for a product and managing the data, the issues and finally publication. 

In any case the engineering team definitely “owned” its product and took great pride in its popularity with clients.

LTNZ - NZTA Performance Info version and corporate  colours.....

How the issues were derived (prior to their being confined to what the Agency would fund):
  1. Crashes in the area (local, regional or State Highway Region) being examined were compared to the rates found in national data and in peer authorities. This being made possible utilising the two page summary report in CAS. Issues were those crash types or crash causes that appeared to be “over-represented” when compared to peer or national values. Or sometimes just by share number – for example in Auckland intersection crashes would be an issue most years because there are a lot of intersections and by default a lot of crashes.
  2. The Data Reports was also a tool - being a tool used more before the CAS summary report matured
I know when I was looking after the process in the Auckland and Waikato areas in the LTNZ Performance Information days it would take me about a week using CAS to develop the issues to be discussed.

The demise of Doug Millers three Performance Information teams spelt the end of the Briefing notes as they were originally intended.

Colin Morrison struggled on man-alone making them a page-by-page download from the Transport Agency’s web site however even this was terminated when Access and Use took over what remained of the Engineering / CAS team. 
However a "fetch" approach does not always work as well as a "push" approach and I very much doubt that Colins reports reached the same audience - which is a shame considering the massive effort needed.

I have little doubt that these products would be very well received should they ever be revived.

Monday, 13 February 2017

Road Safety Data Reports Part 2 - James Kings' technical notes

From James King – former Senior Engineer Wellington Region .

"I cannot remember exactly when I took over the programme for producing the crash data reports but it was definitely after they were in Excel.

I recall we often had problems with the sizing of the charts usually because we were individually extracting the charts for use in the data reports and trying to associate the charts with specific text.
Each of us was also running the Excel to get the files for the authorities we were writing the report for after the Master Data File (MDF) had been created and distributed to each Regional office.

I am not absolutely certain but Yvonne (Warnaar) may have been having problems with the VBA aspects of the Excel and I must have offered to help even though my VBA skills were not great.
In any regard, I seem to have taken responsibility for the maintenance of the program which was distributed annually to the Regional offices when it had been modified/updated to the current analysis period.

The process for creating the charts started after we were satisfied the crash data for the last year had been entered into CAS and that the data was as clean as could be expected. This was usually expected to be about April in the current year.

The first step was to run a SAS program that extracted the required data from CAS to a file that could be read by an Excel spreadsheet.

The Excel program then created the MDF which also required population data obtained from Statistics NZ which was contained in a separate spreadsheet and usually had the need for some extrapolation of data and some estimations as there was some boundary clashes and some data was incomplete.

The Ministry of Transport (MOT) also supplied the social cost of crashes for the year and they also supplied Health statistics that enables a comparison between CAS injury numbers and reported hospital admissions.

There was also a program that used CAS data that gave an estimate of the crash rates.
All these data sources were then input into the MDF and the spreadsheet that created the individual authorities’ spreadsheets.

Copies of the MDF and the spreadsheet for creating the charts were then distributed to the Region’s coordinator.

Running the spreadsheet to create the individual spreadsheets for the local authorities, the Regional authorities and Transit NZ Regions was generally straight forward provided the individual (person doing the report) remembered to rename the resultant spreadsheet with the correct name rather than just saving and ‘contaminating’ the base spreadsheet that everyone was using.

There were problems with the spreadsheet and it was advisable to review the individual sheets to check the charts for size and axis problems and sometimes title problem before printing out.

There were also page size problems which generated much hair pulling but overall the process was usually fairly quick and would take about half an hour for each authority.

It was also possible to create sets of charts for specific aspects of crash investigation but this required making changes in the MDF by the coordinator/administrator at the Regional office using their copy of the MDF.

It occurs to me that Yvonne may have allowed me to take over the administration of the process when several local body mergers happened as it required changes in all the programmes and the Groups we were using.

The first changes required were in the SAS program and I had to relearn what I was doing there which did not prove too onerous.

The main problems were in the spreadsheets where lines disappeared and tables had to be resized.
There were also changes that had to be made in the population, health and rates programs which overall was not exactly a nightmare but created a few strange errors that had to be found and fixed.
It was often a case of changing pointers in the VBA but it also allowed me the opportunity to get all the charts slightly more standardised.
Some charts remained stubbornly difficult to get their axis correct and they remained a problem that had to be corrected individually – the problem appears to be related to small numbers and/or division by zero (I did try to overcome the problem but was not successful).

We were also individually creating blackspot lists from CAS that were put into the reports.

That problem was countered to some extent by local bodies being able to get the data themselves though there was the comment that our reports were independent and what they could produce was not in the same format.

Consequently, I began to automate the process to create all the individual authority reports in one run which reduced production time from about 10 minutes per authority to 5 minute for all 80+ authorities with the files posted to the server for anyone to access.

I also looked at creating the blackspot tables on a more automated basis, were more uniform and in similar format to the charts/tables in the data reports.
This was done by creating files of the blackspots for each authority from CAS which were then imported into an Excel spreadsheet which could then define how much of the blackspot information could be displayed by number of crashes at sites or the social cost of the crashes.

There was also the ability to select sites on the types of crash (injury/non-injury).

Somehow, I acquired the responsibility for all the data reports and was thus able to create all authority report incorporating the blackspot tables in relatively short order.

I also tried my hand at creating the specialist report and succeeded in doing individual data reports for pedestrians, motorcyclists and trucks which were well received if not widely used.

We were posting the reports to NZTA’s web site and I believe some of the reports are still available if you know where to look.

It was proposed that there be an interactive posting on the web site that would enable anyone to obtain crash data information and download a spreadsheet of the information they wanted.
The spreadsheet was duly produced by an outside contractor and it accessed the MDF but it seems no one maintained this as no MDF was produced after 2010 and the program only used data to 2009 and the version on the web was a beta version.

There was also another web based program that could get data down to the suburb level but the data was somewhat limit as I recall and I do not know if that has been maintained."

Friday, 3 February 2017

Road Safety data reports - Part 1

The road safety data reports were multi page collections of charts and tables which were first attempted around 1986 (James Kings recollection)

“If my memory serves me right I believe the earliest reports were generated some time around 1986 as a result of the early crash reduction studies.
They were written in “Word” and may have been printed on dot matrix printers! There were also attempts to automatically insert the charts directly into the Word documents in appropriate places but the technology was not helpful and the files became very large.” 

The data reports were always considered a starting place for further investigation and were not intended for a general audience. 

As far as I can gather for the reports that were published the writer of the script which interrogated the crash data base was Zarko Andjic. He was in the Christchurch office from 1995 to 1999.

At that time the LTSA was using Quattro Pro and Word Perfect and Zarko’s process of dropping data into Quattro Pro (which in itself was good package as far as I can remember) and then linking to Word Perfect was especially unwieldy requiring multiple data extracts and much gnashing of teeth.

The whole process was managed by the Road Safety Report Expert Group which in September of 1999 included Wayne Osmers , David Croft, Yvonne Warnaar, Alan Dixon, Stuart Badger and Paul Phipps. 

After Zarko left the LTSA Yvonne Warnaar in Christchurch had the task of making the whole process more efficient and “press of a button” – as she describes it.
There was also the matter of converting the whole thing to Excel and MS Word as the LTSA had ditched Borland as its software provider. When Yvonne had finished all the data was held in a single extraction in what was known as the “master data” within one Excel file.

The data reports continued to be used as the source of road safety trend data along side the Road Safety Briefing Notes for more than a decade with James King producing the last full national set in 2010 (as in the example below) under the Performance Information banner through the Wellington Office handing the process by himself.
I recently read the 36 page “Standard Operating Procedure”  desk file outlining the production process and was overwhelmed with that sense of foreboding I had every time the task was carried out – while getting the data might have been called “push of a button” production was far from that. In the Auckland Office David Croft shouldered most of that burden up until the merger of LTSA with Transfund.

The final report (running to around 100 pages) had the following content which was typical of those that preceded. 

Introduction and general information

Crash rates and costs 


Crash counts 

Crash type statistics

Crash factor statistics

Environmental statistics 

Date and time statistics 

Council road statistics 

Crash location statistics 

Grouping of crash types

Groupings of contributing factors