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."

No comments:

Post a Comment