EQuIS AQS Schema
Copyright © 2020 EarthSoft, Inc • Modified: 14 Feb 2020
Apply the AQS Schema (schema.AQS.xme) to the EQuIS database according to the steps outlined in the Update EQuIS Database article.
Note: The EQuIS database must already be updated to EQuIS 7.
The AQS Schema changes account for the one-to-many relationships between EQuIS and AQS reference values and are necessary for data import and export in EDP.
The AQS Schema stores data in the following new tables:
•RT_AQS_METHOD – An AQS Parameter-Method combination corresponds to an EQuIS analytic method; rules for data import and export.
•RT_AQS_PARAMETER – Relates an AQS parameter code to an EQuIS analyte (CAS number).
•RT_AQS_QUALIFIER – Allows storage of AQS qualifier codes separate from EQuIS qualifier codes, as they are typically unrelated to one another.
•RT_AQS_UNIT – Relates an AQS unit code to an EQuIS unit code.
•RT_COUNTY – This table is also added by the WQX Schema and will already be present if the WQX Schema has been applied to the EQuIS database.
The following fields are also added with the AQS Schema:
•RT_COUNTY.FIPS_CODE (Federal Information Processing Standards)
•RT_STATE.AQS_STATE_CODE (Also added by the WQX Schema)
•RT_STATE.FIPS_CODE (Also added by the WQX Schema)
•AT_EQUIPMENT_LOCATION.AQS_POC – Parameter Occurrence Code: One or two digit number identifying a specific monitor for a parameter as specified by EPA's AQS system.
•AT_EQUIPMENT_LOCATION.AQS_PARAMETER_CODE – Five-digit number identifying a specific parameter (used in conjunction with the AQS_METHOD_CODE) specified by EPA's AQS system.
•AT_EQUIPMENT_LOCATION.AQS_METHOD_CODE – Three-digit number identifying a specific method (used in conjunction with the AQS_PARAMETER_CODE) as specified by EPA's AQS system.
•AT_EQUIPMENT_LOCATION.G_VALUE – Value between 0 and 1 used in High Volume Flow field calculations (can change over time for a single piece of equipment) implemented in the EQuIS AQS Collect Templates.
The AQS Schema also adds a new database trigger TRG_DT_RESULT_AQS_VALID to help maintain data integrity and accuracy regardless of which EDD Format is used to load AQS data into the EQuIS database. This trigger validates that any data loaded to EQuIS matches the values entered in the EQUIS_* columns (listed below) in the RT_AQS_METHOD table.
The trigger will protect against users making unintentional changes to existing datasets in any of the fields specified above and in the RT_AQS_METHOD table itself. If values do need to be updated, new record(s) with the new changes will need to be added to the RT_AQS_METHOD table first. After this is added, users will be able to edit existing datasets. After existing datasets are updated, if the old RT_AQS_METHOD record is no longer needed, it can be removed.
The following tables and fields exist in the standard EarthSoft Schema, but are important to implement the AQS Module with SPM and/or EDGE.
•AT_EQUIPMENT_LOCATION – Track movement of equipment between locations over time. Contains fields to indicate data that changes when equipment is moved to another location (e.g., POC, G Value, Parameter Code, Method Code).
•DT_EQUIPMENT – Stores the equipment-related information, including serial numbers, equipment type, deployed location, manufacturer, service dates, and more. Equipment includes monitors, calibrators, cylinders, and samplers.
•DT_EQUIPMENT_PARAMETER – The EQuIS AQS Collect Templates have all been setup to import data into this table, which has a specific PARAM_CODE for each field.
•RT_EQUIPMENT_PARAM_TYPE – Used by EDGE.
•RT_GROUP – Used by EDGE.
•RT_GROUP_MEMBER – Create activity group types that allow users to organize field activities in SPM (e.g., 1 Point QC Annual PE, Annual Maintenance, Monthly Maintenance, Flow Rate Verification).
•DT_SPM_PLANNED_ACTIVITY.EQUIPMENT_CODE – Added to allow users to assign activities to specific equipment.
•DT_SPM_SCHEDULED_ACTIVITY.EQUIPMENT_CODE – Added to allow users to assign activities to specific equipment.