CID Query

Top  Previous  Next

CID queries allow users to query against the internal CID table. The CID table is one of the Crash Magic System tables.  CID queries require that the Crash Magic connection have access to the CID system table. Meaning the table must reside on the same database as the collision data that is being used. The .shared connection in used by the CID query must have Select and Delete privileges to the table.

 

This table is populated through the Map Magic send selections tool. Collisions are selected in ArcMap, and then a file is created to post the information to Crash Magic. The file automatically posts the information to Crash Magic. Crash Magic then populates the CID table with the UserGroupID, UserID, ProjectID, StudyID, and any collision ID fields. The UserGroupID, UserID, ProjectID, and StudyID fields are populated with the user that is currently logged into Crash Magic.

 

The Send Selections has the following field options:

UniqueStr - A unique string parameter to populate the UniqueStr field of the CID table.

 

UniqueDate - A date to populate the UniqueDate field of the CID table.

 

UniqueNum1 - A unique number to populate the UniqueNum1 field of the CID table.

 

UniqueNum2 - An additional number to populate the UniqueNum2 field of the CID table.

 

UniqueNum3 - An additional number to populate the UniqueNum3 field of the CID table.

 

The following is an example post file that has been created.

<!-- saved from url=(0014)about:internet -->

<html>

<head>

  <title>Crash Magic Online</title>

</head>

 

<body onload="document.autosubmit.submit();">

 

<form name="autosubmit" method="post" 

action=" http://www.GovernmentTools.com/cm/CrashMagicOnline_ISAPI.dll/MagicAuto/?ACTION=diagram&

StudyDefinition=Case ID list&

Name=Untitled&

Template=Four way intersection&

CategoriesTemplate=None&

FilterTemplate=Clear">

<table width="100%" bgcolor="silver"><tr><td align="center">

 

<h1>Map Magic</h1>

Is preparing your new diagram.<br /> 

<img src="http://www.GovernmentTools.com/cm/cmfiles/icons/pd/main/png/24x24/diagram_24.png" />

<br /><br />

 

<input name="LoadCID" type="hidden" 

value="<pdroot><CIDList>

<CID><UniqueNum1>1521036</UniqueNum1></CID>

<CID><UniqueNum1>1515828</UniqueNum1></CID>

<CID><UniqueNum1>1535033</UniqueNum1></CID>

<CID><UniqueNum1>1540522</UniqueNum1></CID>

</CIDList></pdroot>" 

>

</form>

 

<img src="http://www.GovernmentTools.com/cm/cmfiles/icons/processing.gif" title="Processing image" />

 

</td></tr></table>

 

<script language="Javascript">window.focus();</script>

 

</body>

</html>

 

In this example the value attribute of the input tag is xml that will be passed on to Crash Magic. Unique1 is a unique number passed from the GIS map to identify a collision. Each CID tag will populate a single row in the CID system table. The UniqueNum1 tag will populate the UniqueNum1 field of the CID system table. The input tag is required to be named "LoadCID".

 

The query then uses the CID table to join against the main collision table:

FROM

( Cm1SysProd.CrashMagic.Cm1CID CID

LEFT JOIN Traffic.Collisions Crash ON (Crash.CollisionNbr = CID.UniqNum1))

WHERE

(CID.UserGroupID =:cmUserGroupID)
AND(CID.UserID = :cmUserID)
AND(CID.ProjectID =:cmProjectID)
AND(CID.StudyID = :cmStudyID)

In this example CID is an alias for the <schema.prfix>CID system table. Crash Magic will pass all parameters into the where clause based on the current user that is logged in.

 

:cmUserGroupID  - This is the user Group ID of the user that is currently running the query.

 

:cmUserID - This is the user ID of the user that is currently running the query.

 

:cmProjectID - This is the project ID of the study that is running the query.

 

:cmStudyID - This is the study id of the study that is running the query.

 

When a user deletes a CID study the associated records in the CID table are also deleted.