Query - Study (studyquery)

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
Date	The field that identifies the date of a collision
XCoordinate	X coordinate field of a collision
YCoordinate	Y coordinate field of a collision.
UserCID1	This is a unique field to identify a group of collisions or the UniqueNum1 field of the Crash Magic CID system table
UserCID2	This can be another user ID field or the UniqueNum2 field of the Crash Magic CID system table
cmUserGroupID	The UserGroupID field of the Crash Magic CID system table
cmUserID	The UserID field of the Crash Magic CID system table
cmProjectID	The ProjectID field of the Crash Magic CID system table
cmStudyID	The StudyID field of the Crash Magic CID system table
Address1	Block address field for the Primary Street. This label is used for collision data that is located by street and address.
Address2	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	Milepost field where a collision occurred. This label is used for collision data that is located by route and milepost.
Route	Roadway field that identifies where a collision occurred. This label is used for collision data that is located by route and milepost.
RouteUnique1	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.
RouteUnique2	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.
PrimaryStreet	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
PrimaryStreetUnique1	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.
PrimaryStreetUnique2	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.
CrossStreet	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.
CrossStreetUnique1	Identifies the CrossStreet field is unique to an area.
CrossStreetUnique2	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.