EQEDD_WQX Format

Format Name: EQEDD_WQX

File Name: EQEDD_WQX.zip

Source: Available in the EQuIS WQX package (EQEDD_WQX_FormatPackage.zip) on the EarthSoft Community Center downloads dashboard from the All Items > Formats folder for users who have Download access.

Export(s): Not Applicable

Enumeration File: EQEDD_WQX-enum.xml

License Level: PlusD

Description: The Water Quality eXchange (WQX) is a standardized data framework and submission mechanism for sharing water quality data that is administered by the United States Environmental Protection Agency (USEPA). The EQuIS EQEDD_WQX format is based on the standard EQuIS EQEDD_v2 format, but has been modified to work in parallel with the EQuIS WQX Schema to transition data imported into EQuIS to WQX. Data exported from EQuIS can then be manually uploaded to WQX via WQXWeb, an online tool used to upload and share data with the USEPA and the public. See the EQEDD_v2 Format article for additional information on the standard EQEDD_v2 format.

See the EQuIS WQX Package Overview for more information on WQX, related terminology, and anticipated workflow for using EQuIS to submit data to WQX.

Installation Instructions: The EQEDD_WQX format is available as part of a format package (EQEDD_WQX_FormatPackage.zip). See the Installation and Configuration article for more details. Requires the EQuIS WQX Schema (schema.WQX.xme) applied to the EQuIS database. WQX reference values and/or remapping may be required for the EQEDD_WQX format to work properly.

Overview: The EQEDD_WQX format consists of many of the standard fields found in the EQEDD_v2 format with the addition of fields that are more specific to the USEPA’s WQX schema. Important differences between the two formats can be found in the EQEDD_WQX VS. EQEDD_v2 section below. The EQEDD_WQX format is divided into three separate groups: Field, Biological, and Lab. The Files_v2 section can be used to load a file to an EQuIS database in EDP. See Uploading Files in EDP for additional information.

The EQEDD_WQX format contains the following sections within the Field, Biological, and Lab groups:

Field

•DataProvider_v2 – Creates record(s) in the RT_COMPANY table.

•Task_v2 – Creates record(s) in the DT_TASK table.

•Subfacilities_v2 – Creates record(s) in the the DT_SUBFACILITY table.

•Location_v2 – Creates record(s) in the DT_LOCATION, DT_COORDINATE, DT_WELL, DT_MEASURE_DATUM and DT_WELL_SEGMENT tables.

•LocationParameter_v2 – Creates record(s) in the DT_LOCATION_PARAMETER table.

•Coordinate_v2 – Creates record(s) in the DT_COORDINATE table.

•DrillActivity_v2 – Creates record(s) in the DT_DRILL_ACTIVITY table.

•Well_v2 – Creates record(s) in the DT_WELL and DT_MEASURE_DATUM tables.

•Lithology_v2 – Creates record(s) in the DT_LITHOLOGY table.

•WellConstruction_v2 – Creates record(s) in the DT_WELL_SEGMENT table.

•GeoSample_v2 – Creates record(s) in the DT_SAMPLE, DT_STATIC_PROPS, and DT_ATTERBERG tables.

•WaterLevel_v2 – Creates record(s) in the DT_WATER_LEVEL table.

•WaterTable_v2 – Creates record(s) in the DT_WATER_TABLE table.

•DownholePoint_v2 – Creates record(s) in the DT_DOWNHOLE_POINT_DATA table.

•ExtractionInjectionWells_v2 – Creates record(s) the in DT_PUMP_RATE table.

•Measure_Datum_v2 – Creates record(s) in the DT_MEASURE_DATUM table.

•Equipment_v2 – Creates record(s) in the DT_EQUIPMENT table.

•Equipment_Parameter_v2 – Creates record(s) in the DT_EQUIPMENT_PARAMETER table.

•Purge_v2 – Creates record(s) in the DT_PURGE table.

•Precipitation_v2 – Creates record(s) in the DT_PRECIPITATION table.

•Flow_v2 – Creates record(s) in the DT_FLOW table.

•FieldSample_v2 – Creates record(s) in the DT_SAMPLE, DT_STATIC_PROPS, DT_SDG, DT_CHAIN_OF_CUSTODY, DT_TASK and DT_FIELD_SAMPLE tables.

•FieldQC_Batch_v2 – Creates record(s) in the DT_FIELDQC_BATCH_PARTNER table.

•SampleParameter_v2 – Creates record(s) in the DT_SAMPLE_PARAMETER table.

•FieldResults_v2 – Creates record(s) in the DT_RESULT and DT_TEST tables for field samples.

•COC_v2 – Creates record(s) in the DT_CHAIN_OF_CUSTODY table.

Biological

•BiologicalSample – Creates record(s) in the DT_BIOLOGICAL_SAMPLE table.

•BiologicalResults – Creates record(s) in the DT_BIOLOGICAL_RESULT table.

Lab

•LabSample_v2 – Creates record(s) in the DT_SAMPLE, DT_SDG, DT_FIELD_SAMPLE and DT_CHAIN_OF_CUSTODY tables.

•TestResults_v2 – Creates record(s) in the DT_TEST, DT_SDG, and DT_RESULT tables for lab samples.

•TestResultsQC_v2 – Creates record(s) in the DT_TEST, DT_RESULT, DT_SDG, and DT_RESULT_QC tables for lab and lab QC samples.

•TestBatch_v2 – Creates record(s) in the DT_TEST_BATCH and AT_TEST_BATCH_ASSIGN tables.

•Files_v2 – Creates record(s) in the DT_FILE table.

Limitations/Assumptions: The checks and mappings in any EDD format involve assumptions. The following are assumptions and limitations for the EQEDD_WQX format.

•The format uses the SAMPLE_ID, ANALYTIC_METHOD, ANALYSIS_DATE, FRACTION, COLUMN_NUMBER, TEST_TYPE, BIOLOGICAL_INTENT and TAXON fields in its Test Alternate Key (TAK).

•If DT_RESULT.DETECT_FLAG is populated for existing records prior to applying the EQuIS WQX Schema, these values will automatically be added to the RT_DETECT_FLAG table once the Schema is applied. It is important to check these reference values and confirm that they align with the WQX domain values. Valid values for this field may be either 'Y' for detected analytes or 'N' for non-detects or 'TR' for trace. Use 'Y' for estimated values (above detection limit but below the quantitation limit).

•Known Issues:

•Records being imported through the COC_v2 section will trigger an error in EDP, which is currently being resolved as this section is being modified to include a mapping to DT_CHAIN_OF_CUSTODY.CHAIN_OF_CUSTODY_ID.

•The following values must exist in the database prior to import:

Note: Reference values can also be added in EDP by right clicking the foreign reference value and choosing to add the value individually, by column, or by format.

•DataProvider_v2 Section

•data_provider must exist in the RT_COMPANY table as COMPANY_CODE.

•data_contact_state must exist in the RT_STATE table as STATE_CODE.

•Task_v2 Section

•task_type must exist in the RT_TASK_TYPE table as TASK_TYPE.

•Subfacilities_v2 Section

•subfacility_type must exist in the RT_SUBFACILITY_TYPE table as SUBFACILITY_TYPE.

•state must exist in the RT_STATE table as STATE_CODE.

•Location_v2 Section

•depth_unit and datum_unit must exist in the RT_UNIT table as UNIT_CODE.

•loc_type must exist in the RT_LOCATION_TYPE table as LOCATION_TYPE_CODE.

•loc_state_code must exist in the RT_STATE table as STATE_CODE.

•loc_major_basin must exist in the RT_BASIN table as BASIN_CODE.

•data_provider must exist in the DataProvider_v2 section or in the RT_COMPANY table as COMPANY_CODE.

•loc_country must exist in the RT_COUNTRY as COUNTRY.

•loc_county_code must exist in the RT_COUNTY table as COUNTY_CODE (with correlating state code).

•datum_collection_method_code must exist in the RT_COORD_ELEV_METHOD table as ELEV_COLLECT_METHOD_CODE.

•‘SCREEN’ must exist in the RT_WELL_SEGMENT_TYPE table as SEGMENT_TYPE (with a correlating MATERIAL_TYPE_CODE of ‘UNKNOWN’).

•LocationParameter_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•parameter_code must exist in the RT_LOCATION_PARAM_TYPE table as PARAM_CODE.

•parameter_unit must exist in the RT_UNIT table as UNIT_CODE.

•Coordinate_v2 Section

•sys_loc_code must exist in the Locaiton_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•elev_unit, horz_accuracy_unit, and elev_accuracy_unit must exist in the RT_UNIT table as UNIT_CODE.

•coord_type_code must exist in the RT_COORD_TYPE table as COORD_TYPE_CODE.

•horz_collect_method_code must exist in the RT_COORD_HORZ_METHOD table as HORZ_COLLECT_METHOD_CODE.

•horz_datum_code must exist in the RT_COORD_HORZ_DATUM table as HORZ_DATUM_CODE.

•elev_collect_method_code must exist in the RT_COORD_ELEV_METHOD table as ELEV_COLLECT_METHOD_CODE.

•elev_datum_code must exist in the RT_COORD_ELEV_DATUM table as ELEV_DATUM_CODE.

•subcontractor_name_code and elev_company_code must exist in the RT_COMPANY table as COMPANY_CODE.

•geometric_type_code must exist in the RT_COORD_GEOMETRIC_TYPE table as GEOMETRIC_TYPE_CODE.

•DrillActivity_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•diameter_unit must exist in the RT_UNIT table as UNIT_CODE.

•drilling_subcontractor must exist in the RT_COMPANY table as COMPANY_CODE.

•Well_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•datum_unit, depth_unit, stickup_unit, sump_unit, and pump_unit must exist in the RT_UNIT table as UNIT_CODE.

•datum_collection_method_code must exist in the RT_COORD_ELEV_METHOD table as ELEV_COLLECT_METHOD_CODE.

•construct_contractor must exist in the RT_COMPANY table as COMPANY_CODE.

•measure_datum_type must exist in the RT_MEASURE_DATUM table as MEASURE_DATUM_TYPE.

•Lithology_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•material_type must exist in the RT_MATERIAL table as MATERIAL_NAME.

•geo_unit_code_1 and geo_unit_code_2 must exist in the RT_GEOLOGIC_UNIT table as GEOLOGIC_UNIT_CODE.

•color and odor must exist in the RT_LOOKUP table as LOOKUP_CODE (with LITH_COLOR and LITH_ODOR as LOOKUP_TYPE).

•WellConstruction_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•depth_unit, diameter_unit, thickness_unit, and slot_size_unit must exist in the RT_UNIT table as UNIT_CODE.

•GeoSample_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•K_unit, density_unit and organic_carbon_unit must exist in the RT_UNIT table as UNIT_CODE.

•WaterLevel_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•equipment_code must exist in the DT_EQUIPMENT table as EQUIPMENT_CODE.

•depth_unit must exist in the RT_UNIT table as UNIT_CODE.

•lnapl_cas_rn and dnapl_cas_rn must exist in the RT_ANALYTE_DETAIL table as CAS_RN.

•approval_code must exist in the RT_VALID_CODE table as ACCEPT_CODE.

•WaterTable_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•capped_pressure_unit and temperature_unit must exist in the RT_UNIT table as UNIT_CODE.

•DownholePoint_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•param_unit must exist in the RT_UNIT table as UNIT_CODE.

•ExtractionInjectionWells_v2 Section

•sys_loc_code must exist in the Well_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•pump_rate_unit and design_rate_unit must exist in the RT_UNIT table as UNIT_CODE.

•Measure_Datum_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•datum_unit must exist in the RT_UNIT table as UNIT_CODE.

•datum_company_code must exist in the RT_COMPANY table as COMPANY_CODE.

•measure_datum_type must exist in the RT_MEASURE_DATUM table as MEASURE_DATUM_TYPE.

•Equipment_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•equipment_type must exist in the RT_EQUIPMENT_TYPE table as EQUIPMENT_TYPE.

•size_unit must exist in the RT_UNIT table as UNIT_CODE.

•Equipment_Parameter_v2 Section

•equipment_code must exist in the Equipment_v2 section or the DT_EQUIPMENT table as EQUIPMENT_CODE.

•param_code must exist in the RT_EQUIPMENT_PARAM_TYPE table as PARAM_CODE.

•param_unit must exist in the RT_UNIT table as UNIT_CODE.

•company_code must exist in the RT_COMPANY table as COMPANY_CODE.

•Purge_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•depth_unit, purge_vol_unit, and purge_rate_unit must exist in the RT_UNIT table as UNIT_CODE.

•Precipitation_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•unit must exist the RT_UNIT table as UNIT_CODE.

•task_code must exist in the Task_v2 section or the DT_TASK table as TASK_CODE.

•Flow_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•equipment_code must exist in the Equipment_v2 section or the DT_EQUIPMENT table as EQUIPMENT_CODE.

•sys_sample_code must exist in the DT_SAMPLE table as SYS_SAMPLE_CODE.

•depth_unit, gauge_unit, width_unit, velocity_unit, pressure_unit, instant_flow_unit, average_flow_unit and cumulative_flow_unit must exist in the RT_UNIT table as UNIT_CODE.

•FieldSample_v2 Section

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•sample_matrix_code must exist in the RT_MATRIX table as MATRIX_CODE.

•sample_type_code must exist in the RT_SAMPLE_TYPE table as SAMPLE_TYPE_CODE.

•depth_unit and duration_unit must exist in the RT_UNIT table as UNIT_CODE.

•sampling_company_code must exist in the RT_COMPANY table as COMPANY_CODE.

•sampling_method must exist in the RT_SAMPLE_METHOD table as METHOD_CODE.

•geologic_unit_code must exist in the RT_GEOLOGIC_UNIT table as GEOLOGIC_UNIT_CODE.

•equipment_code must exist in the DT_EQUIPMENT table as EQUIPMENT_CODE.

•equipment_type must exist in the RT_EQUIPMENT_TYPE table as EQUIPMENT_TYPE.

•activity_media must exist in the RT_MEDIUM table as MEDIUM_CODE.

•activity_start_time_zone must exist in the RT_TIMEZONE table as TIMEZONE_CODE.

•activity_group_type must exist in the RT_TASK_TYPE table as TASK_TYPE.

•FieldQC_Batch_v2 Section

•sys_sample_code must exist in the FieldSample_v2 section or the DT_FIELD_SAMPLE table (SAMPLE_ID from the DT_SAMPLE table).

•qc_sys_sample_code must exist in the FieldSample_v2 section or the DT_FIELD_SAMPLE table (SAMPLE_ID from the DT_SAMPLE table).

•SampleParameter_v2 Section

•param_code must exist in the RT_SAMPLE_PARAM_TYPE table as PARAM_CODE.

•param_unit must exist in the RT_UNIT table as UNIT_CODE.

•sys_sample_code must exist in the DT_SAMPLE table as SYS_SAMPLE_CODE.

•FieldResults_v2 Section

•data_provider must exist in the RT_COMPANY table as COMPANY_CODE.

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•sys_sample_code must exist in the FieldSample_v2 section or in the DT_SAMPLE table as SYS_SAMPLE_CODE.

•cas_rn must exist in the RT_ANALYTE table as CAS_RN.

•result_unit must exist in the RT_UNIT table as UNIT_CODE.

•sample_matrix_code must exist in the RT_MATRIX table as MATRIX_CODE.

•qualifier must exist in the RT_QUALIFIER table as QUALIFIER.

•sampling_company_code must exist in the RT_COMPANY table as COMPANY_CODE.

•sampling_method must exist in the RT_SAMPLE_METHOD table as METHOD_CODE.

•method_speciation must exist in the RT_METHOD_SPECIATION table as METHOD_SPECIATION.

•fraction_WQX must exist in the RT_FRACTION table as FRACTION.

•approval_code must exist in the RT_VALID_CODE table as ACCEPT_CODE.

•stat_type must exist in the RT_STAT_TYPE table as STAT_TYPE.

•basis must exist in the RT_WEIGHT_BASIS table as WEIGHT_BASIS.

•BiologicalSample Section

•assemblage must exist in the RT_ASSEMBLAGE table as ASSEMBLAGE.

•depth_unit, duration_unit, reach_length_unit, reach_width_unit, net_surface_area_unit, net_mesh_size_unit, boat_speed_unit, and current_speed_unit must exist in the RT_UNIT table as UNIT_CODE.

•net_type must exist in the RT_NET_TYPE table as NET_TYPE.

•toxicity_type must exist in the RT_TOXICITY_TYPE table as TOXICITY_TYPE.

•equipment_type must exist in the RT_EQUIPMENT_TYPE table as EQUIPMENT_TYPE.

•equipment_code must exist in the DT_EQIUPMENT table as EQUIPMENT_CODE.

•parent_sample_code must exist in the BiologicalSample section or in the DT_SAMPLE table as SYS_SAMPLE_CODE.

•sys_loc_code must exist in the Location_v2 section or in the RT_COMPANY table as COMPANY_CODE or in the DT_LOCATION table as SYS_LOC_CODE.

•sample_matrix_code must exist in the RT_MATRIX table as MATRIX_CODE.

•sample_type_code must exist in the RT_SAMPLE_TYPE table as SAMPLE_TYPE_CODE.

•sampling_method must exist in the RT_SAMPLE_METHOD table as METHOD_CODE.

•activity_media must exist in the RT_MEDIUM table as MEDIUM_CODE.

•activity_start_time_zone must exist in the RT_TIMEZONE table as TIMEZONE_CODE.

•activity_group_type must exist in the DT_TASK table as TASK_TYPE.

•BiologicalResults Section

•data_provider must exist in the RT_COMPANY table as COMPANY_CODE.

•sys_loc_code must exist in the Location_v2 section or in the DT_LOCATION table as SYS_LOC_CODE.

•cas_rn must exist in the RT_ANALYTE table as CAS_RN.

•depth_unit, result_unit, group_summary_weight_unit, frequency_class_unit, and result_depth_height_unit must exist in the RT_UNIT table as UNIT_CODE.

•biological_intent must exist in the RT_BIOLOGICAL_INTENT table as BIOLOGICAL_INTENT.

•taxon must exist in the RT_TAXON table as TAXON.

•sample_tissue_anatomy must exist in the RT_ANATOMY table as ANATOMY.

•cell_form must exist in the RT_CELL_FORM table as CELL_FORM.

•cell_shape must exist in the RT_CELL_SHAPE table as CELL_SHAPE.

•habit must exist in the RT_HABIT table as HABIT.

•voltinism must exist in the RT_VOLTINISM table as VOLTINISM.

•frequency_class_descriptor must exist in the RT_FREQUENCY_CLASS table as FREQUENCY_CLASS.

•sys_sample_code must exist in the BiologicalSample section or in the DT_SAMPLE table as SYS_SAMPLE_CODE.

•basis must exist in the RT_WEIGHT_BASIS table as WEIGHT_BASIS.

•method_speciation must exist in the RT_METHOD_SPECIATION table as METHOD_SPECIATION.

•fraction_WQX must exist in the RT_FRACTION table as FRACTION.

•approval_code must exist in the RT_VALID_CODE table as ACCEPT_CODE.

•stat_type must exist in the RT_STAT_TYPE table as STAT_TYPE.

•qualifier must exist in the RT_QUALIFIER table as QUALIFIER.

•sample_matrix_code must exist in the RT_MATRIX table as MATRIX_CODE.

•LabSample_v2 Section

•sample_matrix_code must exist in the RT_MATRIX table as MATRIX_CODE.

•medium_code must exist in the RT_MEDIUM table as MEDIUM_CODE.

•sample_type_code must exist in the RT_SAMPLE_TYPE table as SAMPLE_TYPE_CODE.

•sampling_company_code must exist in the RT_COMPANY table as COMPANY_CODE.

•sampling_method must exist in the RT_SAMPLE_METHOD table as METHOD_CODE.

•sample_date_timezone must exist in the RT_TIMEZONE table as TIMEZONE_CODE.

•relative_depth must exist in the RT_RELATIVE_DEPTH table as RELATIVE_DEPTH.

•equipment_code must exist in the DT_EQUIPMENT table as EQUIPMENT_CODE.

•equipment_type must exist in the RT_EQUIPMENT_TYPE table as EQUIPMENT_TYPE.

•parent_sample_code must exist in the LabSample_v2 section or in the DT_SAMPLE table as SYS_SAMPLE_CODE.

•depth_unit must exist in the RT_UNIT table as UNIT_CODE.

•TestResults_v2 Section

•sys_sample_code must exist in the LabSample_v2 section or in the DT_SAMPLE table as SYS_LOC_CODE.

•lab_anl_method_name must exist in the RT_ANALYTIC_METHOD table as ANALYTIC_METHOD.

•total_or_dissolved must exist in the RT_FRACTION table as FRACTION.

•test_type must exist in the RT_TEST_TYPE table as TEST_TYPE.

•lab_matrix_code must exist in the RT_MATRIX table as MATRIX_CODE.

•prep_method must exist in the RT_PREP_METHOD table as PREP_METHOD.

•lab_name_code must exist in the RT_COMPANY table as COMPANY_CODE.

•subsample_amount_unit, final_volume_unit, result_unit, and detection_limit_unit must exist in the RT_UNIT table as UNIT_CODE.

•preservative must exist in the RT_PRESERVATIVE table as PRESERVATIVE.

•cas_rn must exist in the RT_ANALYTE table as CAS_RN.

•result_type_code must exist in the RT_RESULT_TYPE table as RESULT_TYPE_CODE.

•interpreted_qualifiers must exist in the RT_QUALIFIER table as QUALIFIER.

•duration_basis must exist in the RT_TIME_BASIS table as TIME_BASIS.

•method_speciation must exist in the RT_METHOD_SPECIATION table as METHOD_SPECIATION.

•temp_basis must exist in the RT_TEMP_BASIS table as TEMP_BASIS.

•analysis_date_timezone must exist in the RT_TIMEZONE table as TIMEZONE_CODE.

•approval_code must exist in the RT_VALID_CODE table as ACCEPT_CODE.

•TestResultsQC_v2 Section

•sys_sample_code must exist in the LabSample_v2 section or in the DT_SAMPLE table as SYS_SAMPLE_CODE.