Stop 3 of 8 · Weekly
Count and crash data
Count exports and a stripped crash file become peak hour tables, trend lines, a crash pattern list and the figures the report will need.
The exports on Leila's desk
A turning movement count job comes back as a workbook with one sheet per intersection, fifteen minute bins by movement, two days of data and a sheet of pedestrian counts nobody looks at. The crash file comes from the agency as a flat export, five years, one row per crash, forty columns. Synchro gives her delay and queue reports as text or a spreadsheet dump.
None of it is hard. All of it is hours: transposing bins, finding the peak, factoring, building the same six tables she built on the last four studies, then reading five years of crash rows looking for the pattern that is obviously there once someone names it.
Strip first, upload second
The crash export is the one file in this job with people in it. Open it in Excel, delete the columns for name, address, date of birth, licence number, phone, officer name and the narrative field, and save it as a new file with a name that says it is the cleaned one. Keep case number, date, time, location or milepost, crash type, severity code, direction, light and surface conditions. Then upload.
Do it in that order every time. A file that never went up cannot come back in a draft.
[upload the count workbook] For each intersection sheet, find the weekday AM and PM peak hour as four consecutive fifteen minute bins, and give me one table per intersection: movement, volume by approach, total entering volume, and the peak hour factor. Name the sheet and the four bin start times behind every peak hour. If a sheet has missing bins or a partial day, say so and leave that intersection out rather than estimating it.
[upload the cleaned crash export] Five years of crashes on this corridor. Give me four things. One: crashes by year, by intersection and by severity code, as tables. Two: crash type by intersection, so I can see where rear end and left turn crashes concentrate. Three: any pattern in time of day, day of week, lighting or surface condition worth a sentence in the report. Four: the three locations with the pattern most likely to have an engineering cause. State every count as a row count from the file. Do not calculate a crash rate, and do not tell me a location is unsafe or a countermeasure is warranted.
[upload the historical counts and this year's counts] Compare total entering volume by intersection across the years in these files. Give me a table of year, intersection, total entering volume and percentage change from the previous count, and name the source file for every figure. Flag any change over 15 percent as something I need to explain, and list the possible explanations you can see in the data rather than guessing at causes outside it.
From the tables above, list the figures this study needs: what each one shows, which table it comes from, and what a reviewer would look for on it. Include the collision diagram, the volume figures and the level of service exhibits. Number them in the order they will appear in the report.
The stamp rule holds here as well. These tables are evidence. Whether a pattern means a signal, a turn lane or nothing at all is Leila's call, made against the current manual, and it goes in the report under her seal.