Study queries

Top  Previous  Next

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.

 

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.