Prepare data for use in Crash Magic

Table of Contents
Issue

Crash Magic hosted on GovernmentTools.com provides a number of options for importing new crash data. This article describes those options.

Explanation

Crash Magic has been designed to support a wide variety of data formats for import. This data may originate on a mainframe, mini, or micro computer. In some areas data may be obtained directly from the state, California, Iowa, Oregon, Arizona and Idaho to name a few. In other areas, data is stored in a police department RMS. In still others, data is maintained in PC database programs. Crash Magic supports all of these cases. This article describes the various options and requirements for preparing data to import into Crash Magic.

While Crash Magic prefers XML files, it can import any file(s) supported by a Microsoft ADO driver including:

  • CSV
  • Fixed field
  • DBF
  • MDB
  • etc.

Internally, Crash Magic uses an SQL Server for data storage and processing.

Along with the data, a list of the fields and their decoded values is required.

Solution

Crash Magic imports XML data.  The structure is very flexible, but some things make it much easier to import.

Ideally, each XML file represents one crash.  Typically the structure will contain the “Environment” data such as date, time, case number, type of collision, road information, location information and so on.  Then within that element will be a list of vehicles involved in the crash.  This section will contain Vehicle number, vehicle movement, direction, type, etc.  Inside of the vehicle section would be a driver and perhaps a list of passengers.  There is also likely a pedestrian section which may be at the crash level, or associated with a vehicle.  Of course many other possibilities exist.  Here’s what a typical structure might look like:

<root>
  <Crash>
    <Vehicles> 
      <Vehicle>
        <Driver></Driver>
        <Passengers>
          <Passenger></Passenger>
          <Passenger></Passenger>
        </Passengers>    
      </Vehicle> 
      <Vehicle>
        <Driver></Driver>
        <Passengers>
          <Passenger></Passenger>
          <Passenger></Passenger>
        </Passengers> 
      </Vehicle> 
    </Vehicles>
    <Pedestrians>
      <Pedestrian></Pedestrian>
    </Pedestrians>
  </Crash>
</root>

Crash Magic uses an “import definition” to specify the structure of each client’s file format.

Understanding that not all customers have the ability to produce XML, Crash Magic also incorporates a “convert” routine that can convert text and database files to XML during the import process.  The most typical of these formats is CSV.  Often there may be several CSV files representing the different tables stored by the system. Another common file type is “fixed field”. Any format understood by MS ADO is supported.

When assembling data it is very important that records provided separately can be “joined” by case id numbers, vehicle numbers and person numbers.  Without those references, it can be very challenging to assemble an accurate crash record.

As with the structure, Crash Magic is very flexible with the content of the data.  We have a number of required and recommended fields (described here) and then the analyst may select any other field they would like to be able to query/report on.

Was this article helpful?
0 out of 5 stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
How can we improve this article?