Required and recommended fields for Crash Magic

Article created: Sep 29 2008, updated: Apr 24 2014

Crash Magic has no requirements for specific data types from collision data. It is possible to configure the program using text or numeric fields. However, any text fields must be consistent in order to produce reliable results. Non-validated, free-form data entry is not conducive to producing quality diagrams and reports. For example it can be impossible to determine a vehicle direction from an officer notes field. While large format fields are supported by Crash Magic(BLOBS and CLOBS), they should not be used for the required and recommended fields. The ideal structure is numeric or short text fields drawn from a predefined set of values like a lookup table.

• Unique crash field name(s): A field or list of fields that can uniquely identify a crash across the entire database. This is used to be able to reference crashes within the diagram, as well as to provide feedback when "clicking on" a diagram graphic to obtain more information.
• Date (a valid date field)
• Location of crash (at least one of the following - all can be used simultaneously):
  • Primary and nearest cross street names: This is the most common means of identifying locations in urban and rural city settings. Jurisdiction information may be required for analysis on multiple locations with the same name. (For example jurisdiction information would be required for a database that contains information from the cities of Denver and Boulder, and both cities have an intersection location named Broadway and Arapahoe). 
  • Intersection node: a unique identifier for a location
  • Route and Mile marker: Usually used in county or state DOT agencies
  • Street and hundred block / address: Usually used in urban settings as a supplement to "Primary and nearest cross street names"
  • XY coordinates for a collision
• Vehicle sequence: Crash Magic must be able to identify the first and second vehicles involved in a collision.
• Direction of travel: Each of the vehicles involved in the crash must have a direction of travel prior to the collision. Direction of travel is used to place the crashes on the diagram schematic. It is also used to align the arrows within the collision graphic.

• Type of collision field that indicates that a collision was a sideswipe or rear-end. This is used when the first two vehicles are traveling in the same direction. Without it, all such crashes are rendered as rear-end collisions.
• Vehicle movements for each of the first two vehicles involved in the collision. Values such as "left turn", "slowing", "backing", "straight", etc. are used to create the appropriate arrows for each graphic. Without this information, all vehicles will have to be represented with straight arrows.

• Time of collision. Used for charts within Crash Magic. Without a valid time field charts will display all collisions as unhandled data. 
• Crash severity. Used to render an injury or fatality symbol associated with a collision graphic. This field should indicate at least "property damage"; "injury"; "fatality" across the entire crash. (i.e. all vehicles combined) A less convenient means of determining this is by examining each vehicle individually.
• Indication of pedestrian, bicycle or fixed object. Used to render an appropriate annotation to the graphic. If these values are represented as vehicle types, and given direction and movement, a more accurate diagram can be created with bicycle and pedestrian collisions. Otherwise a pedestrian or bike symbol is simply located in front of the first vehicle with no indication of direction or movement.
• Lighting condition. Used to render a "nighttime" symbol associated with the collision graphic.
• Driver or DUI condition(s). Used to a render DUI annotation on the crash, or for each appropriate vehicle.

• Distance and direction from the nearest cross street. These fields are used to place crashes in the appropriate positions in the diagram.  Without it, many assumptions need to be made about vehicles in or near an intersection.
• Lookup tables. These tables will be used to convert raw data values to user readable values.
• Collision images. Scanned images of police reports, collision photos, and collision location photos can be included within Crash Magic
• Roadway section order. The creation of corridor diagrams schematics requires information to place roadway sections in their correct order. Street milepost studies order roadway sections by tenth of a mile sections based on the milepost number. Street address corridors order roadway sections by 100th block based on the block number.
• List of engineer-desired fields to be shown for labels, filters, etc.

User should be able to provide Pd' Programming access to their collision data. This can be done by uploading a copy of the database to our web site, providing access through a VPN connection or in the case of hosted solutions the files to be imported(.txt, .csv and .del files must have header rows that label each field). The data at a minimum should contain a years' worth of collision data. A sample of hard copy collision reports are also required to confirm that collision diagrams are being displayed correctly.

While most databases are self documented through table names and field names the following information must also be provided for a Crash Magic configuration:

• What are the tables used for the collision database?
• How do the collision tables relate to each other (primary and foreign key relationships)?
• What is the Unique crash field that identifies a crash in the database?
• What is the date field for a crash?
• How are collisions located in the collision database, and what fields are used?
• How is vehicle order determined?
• What table and field is used for vehicle direction?
• What table and field is used for type of collision?
• What table and field is used for vehicle movement?
• What fields contain unique values and what fields contain a list of possible values(Fields that have lookups)?
• What are the common fields that traffic engineers prefer to see with each collision?(This will be used as the default list of fields displayed for reports. Users are able to add or remove fields from any reports, but this will be the default list fields that a user will start with.)

The following information would enhance collision diagrams in Crash Magic:

• What table/file and field is used to determine the time of the collision?
• What table/file and field is used for the severity of the entire collision?
• What table/file and fields is used to determine pedestrian, bicycle and fixed object collisions?
• What table/file and field is used to determine lighting condition?
• What table/file and field is used to determine DUI?
• What table/file and fields contain lookup information?
• What table/file and fields are used to store images?
• What table/file and fields are used to order roadway information?

In addition to the database of files we also request reports from the data for the configuration:

1. At least one injury or fatality crash
2. At least one single-vehicle crash
3. At least one multi-vehicle crash
4. At least one crash at an intersection
5. At least one mid-block / milepost crash - This is used to determine how distance and direction from intersection (or addresses) is coded
6. At least one left-turn crash
7. At least one rear-end crash
8. At least one pedestrian crash
9. At least one bicycle crash
10. Example reports of any data elements that you feel are unique or need extra attention when describing your data

These reports will be compared to the Crash Magic output to ensure they are being correctly rendered.