Study queries are used by studies and extract the collision records from the database for analysis. Study queries require a single record be returned for each collision. The Crash Magic query editor allows fields from the query to be identified as normalized fields. Normalized fields are query fields that are mapped to the Crash Magic program. These fields tell the program when and where a collision occurred. Crash Magic uses the normalized fields to add where clauses information to the study query. Depending on the collision data in use some or all normalized fields will be used. Study Definitions are used to define which fields will be used when a new study is created.
Normalized fields are selected in the query editor using the Normalized field drop down menu.
Nomalized field |
Description |
The field that identifies the date of a collision |
|
X coordinate field of a collision |
|
Y coordinate field of a collision. |
|
This is a unique field to identify a group of collisions or the UniqueNum1 field of the Crash Magic CID system table |
|
This can be another user ID field or the UniqueNum2 field of the Crash Magic CID system table |
|
The UserGroupID field of the Crash Magic CID system table |
|
The UserID field of the Crash Magic CID system table |
|
The ProjectID field of the Crash Magic CID system table |
|
The StudyID field of the Crash Magic CID system table |
|
Block address field for the Primary Street. This label is used for collision data that is located by street and address. |
|
Block address field for the Cross Street. This label is used for collision data that is located by street and address along with cross street and address. |
|
Milepost field where a collision occurred. This label is used for collision data that is located by route and milepost. |
|
Roadway field that identifies where a collision occurred. This label is used for collision data that is located by route and milepost. |
|
Identifies the Route field is unique to an area. For example this could be used to identify the specific field that contains the county where the collision occurred. |
|
Identifies the Route field is unique to an area within the area identified in RouteUnique1. For example this could be used to identify the specific field that contains the city with in a county where the collision occurred. |
|
The field that contain the street the collision occurred on. This label is used for collision data that is located by street and address and/or street and cross street |
|
Identifies the PrimaryStreet field is unique to an area. For example this could be used to identify the specific field that contains the county where the collision occurred. |
|
Identifies the PrimaryStreet field is unique to an area within the area identified in PrimaryStreetUnique1. For example this could be used to identify the specific field that contains the city with in a county where the collision occurred. |
|
This field identifies the nearest intersecting street of a collision. This field is used for collision data that is located using an intersection of a primary street and cross street. |
|
Identifies the CrossStreet field is unique to an area. |
|
Identifies the CrossStreet field is unique to an area within the area identified in CrossStreetUnique1. |
Clients that use the Map Magic selection tool will need to join the CID Crash Magic system table with the collision data. This generally requires that both the crash data and system tables reside on the same SQL server.
Study queries must have an order by clause. An order by clause will assure the database returns the records in the same order each time the query is performed. Without an order by clause the clients using collision diagrams may experience problems when moving collision graphics in a diagram. The id field or fields for the collision record should be selected for the order by clause. This field or fields are also used as the _CaseID field with in the calculated fields.
Where clauses should be avoided with study queries as they will be dynamically added to the query by the study definition.
If multiple study queries are required every effort should be used to use the same fields in the select statement of each study query and the ClickOn query.
