Key Fields play a vital part in the structure of AGS 3.x data. This article shows some of the problems commonly encountered when transferring data in the AGS
SAMP and test tables and gives guidence on how to minimise and eliminate these problems.
Keep checking back for the next in this series on sample referencing as we explain how DIGGSML solves these problems soon.
AGS fields are marked in the AGS publication as either "Key" or "Common". Common fields are used to transfer descriptive data and do not have any integrity implications. Key fields ensure that each record in an AGS file can be uniquely defined and that each line of data can be referenced to its parent information.
Incorrect key fields in the laboratory test tables are one of the most common reasons for AGS data failing integrity checks and often results in orphan records or phantom parents. This fact sheet will assist data suppliers and receivers in effectively checking AGS files and reducing the problems caused by incorrect key fields.
HOLEtables from the AGS data returned from the laboratory and check the remaining file together with the original AGS file supplied to the laboratory. This will ensure that the test information returned is correctly registered to the original samples.
HOLEtable from your SI system. This will ensure that the test information returned will be compatible with your current data and is ready for import.
Key fields enable each line of data in an AGS group to be uniquely referenced. For example,
HOLE_ID is the only key field in the
HOLE group and so each
HOLE_ID must be unique within the
Very few groups in an AGS file have only one key field. For example if the Depth Related Remarks group (
HOLE_ID as its only key field it wouldn’t be possible to have more than one remark per hole. Include the depth of the remark (
DREM_DPTH) in the list of key fields and it is possible to have as many remarks for each hole as long as they are at different depths. When key fields are combined to make a unique key it is often referred to as a combined key.
|DREM||HOLE_ID + DREM_DPTH|
|SAMP||HOLE_ID + SAMP_TOP + SAMP_TYPE + SAMP_REF|
SAMP table has 4 Key Fields
SAMP_REF and when combined these must be unique for every sample in the AGS file
The key fields in a group also define the data's relationship to its parent group. This may sound like database gobbledegook if you are not familiar with database design but it can be easily explained with the aid of an example.
DREM group (as discussed above) contains a
HOLE_ID field. This enables the user (or receiving computer) to look up the
HOLE_ID in the
HOLE table to understand more information about the hole – for example the Easting and Northing information. In this instance the
HOLE table is the parent of the
DREM group. The relationship between these two tables is called a Parent - Child relationship, a Parent can have many Children but a Child can only have one Parent (in database speak this is called a 1-to-many relationship).
If a remark exists for
HOLE_ID "BH3" in the
DREM table but there is no "BH3" in the
HOLE table then this
DREM data would be classified as an orphan record - an item of data that has no parent record.
The AGS publication contains information of the parent child relationships in the hierarchy table in section 10.3.
DREM is one of the simplest relationships; it gets more complicated when you look at a Lab testing data level such as the
CLSS table. The
CLSS table is a child of the
SAMP table. The
SAMP table has 4 key fields (
SAMP_REF) and so the
CLSS table must include these 4 fields correctly to match to a sample.
CLSS table then requires 2 further key fields to ensure that tests carried out on different specimens within the same sample are unique referenced - this results in the Key fields in the
CLSS table being (
The AGS states that all parent tables must be present in an AGS file for it to be valid. This means that a laboratory must report the
HOLE table records for each classification test. If the laboratory were to issue a file with only the
CLSS table in it then any AGS checking software will state that every
CLSS result is an orphan as there is no sample data in the file. Most software used to create Laboratory AGS will create
SAMP groups that contain just the Key Fields for the Laboratory data and if an AGS file is produced using this method then it will validate using the common AGS checkers. However if the data has changed at source then merging the laboratory data with the current data can result in Phantom Parents being produced.
Problems with Orphan Sample records and Phantom Parents can occur due to one of the 3 following reasons. Being aware of these reasons will help you to avoid them in your project.
If the laboratory’s IT system does not allow for the import of AGS data or the client has not given them AGS data, the only option available to them is to retype the data. Retyping data is never 100% perfect and so some errors will be introduced to the system.
If the laboratory’s IT system does not allow 4 client reference fields, the laboratory may therefore decide to only use
SAMP_REF. They will still produce AGS data but as the
CLSS table only contains 3 out of the 4 required key fields the data will not link to the
SAMP group and all the records will be seen as orphans
Key Fields are descriptive and may change. For example the 4 fields that uniquely define a sample also describe it (it's the first D sample in BH1 at 4.56 metres depth) and so if the engineer decides that this descriptive data is wrong and changes it they effectively create a new sample and delete the old one.
If AGS data has already been passed to the laboratory for testing then they will return data with the old referencing system and thus create an orphan record when checked against the latest version of the data. This problem is caused by the engineer and the data should be fixed by them on receipt. It is for this reason that the best practice notes at the front of this fact sheet recommend you check the data with the sample information that you supplied to the laboratory before you check it against the most current version of the sample data.
This is a vital check to do as if the laboratory passes back your original SAMP table with their data (standard practice) and you import it into your database system after you have changed some of the sample references manually then you will create Phantom samples or holes. Getting out of this problem can be extremely difficult so you must do everything possible to avoid it.
Key fields within the AGS format can be a cause of some confusion, this article has hopefully explained that this is not necessarily justified, there are a lot of situations where problems may arise with multiple Key fields in tables but with careful handling these can be eliminated.