GeotrackerEDF Format

Format Name: GeotrackerEDF

File Name: GeotrackerEDF.zip

Source: Available for the EarthSoft Primary Point of Contact (POC) on the EarthSoft Community Center downloads dashboard > Products > Formats > v7 > 22.1+.

Export(s):

•GeoTrackerEDF EDD

•GeoTrackerEDF FLAT EDD

•GeoTrackerEDF FLAT EDD (by EDD)

•GeoTrackerGEO XYZ_SURVEY EDD

•GeoTrackerGEO WELL CONSTRUCTION EDD

•GeoTrackerGEO WELL EDD

Note: Exports will not work as expected if the data were imported using a different format, as each format has different mappings, checks, etc.

Description: GeoTracker is the State of California Water Boards' data management system (https://www.waterboards.ca.gov/) for sites that impact groundwater. EarthSoft designed the GeoTrackerEDF format for use by organizations receiving GeoTracker data in this format. This format allows GeoTracker data to be converted to EQuIS data and loaded to an EQuIS database through remaps and lookups, if needed.

Installation Instructions: Download the GeotrackerEDF format from the EarthSoft Community Center and unblock the *.zip file. See the Interfaces page on how to use the GeoTracker button in EQuIS Professional to export data.

Overview: The GeotrackerEDF format is broken into three separate sections: Analytical Data, Survey/Gauge Data, and Flat File. It is intended that only the Analytical Data section or Flat File section be used, not both.

•Analytical Data

•EDFSAMP: Contains collection, location, and administrative information about field samples.

•EDFTEST: Contains information about analytical tests performed on the samples, the EDFTEST file is related to the SAMPLE file by sample collection information and field sample number.

•EDFRES: Contains information on results generated by the laboratory.

•EDFQC: Contains data related to laboratory quality control (QC) samples.

•EDFCL: Contains data associated with analytical control limits (CL). Each CL file record contains control limit information for a parameter analyzed by a particular analytical method.

•Survey/Gauge Date

•GEO_XY: Contains sub-meter field location measurements data. For each groundwater monitoring well, report each field point sampling location (X,Y) only once.

•GEO_Z: Contains field elevation measurements data.

•GEO_WELL: Contains field measurements from groundwater wells.

•GEO_FIELD: Contains information pertaining to the sampling information from the field and additional fields that allow the blinding of that information to the analytical laboratory.

•WELL_CONSTRUCTION: Contains well construction information for wells.

•Flat File

•EDFFlat: Contains all the data fields from the SAMPLE, TEST, RESULTS, and QC files of the relational format in one large “flat” file.

•CLFlat: Links to the EDFFlat file through the same key fields with which the EDFRES file links to the EDFCL file (LABCODE, MATRIX, ANMCODE, EXMCODE, PARLABEL, CLREVDATE, AND CLCODE).

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

•Data can only be loaded to a single facility at a time.

•Test Alternate Key (TAK) for the format: SAMPLE_ID, ANALYTIC_METHOD, ANALYSIS_DATE , FRACTION, and TEST_TYPE.

•PARVQ is checked for surrogates. If PARVQ='SU', then PARVAL is loaded to DT_RESULT_QC.QC_SPIKE_RECOVERY. This overrides the other QCCODE mappings listed below.

•EDFQC and EDFCL do not create new records in EQuIS. Instead, those records are used to populate DT_RESULT_QC fields if matches are found based on ANMCODE, EXMCODE, LABCODE, CLREVDATE, and PARLABELs.

•PARVAL mapping depends on the QCCODE (SAMPLE_TYPE_CODE). In addition, as stated in the above bullet, if a match is found for a EDFQC record, EDFQC.EXPECTED is mapped to one of the following DT_RESULT_QC fields depending on the EDFRES.QCCODE (SAMPLE_TYPE_CODE):

•If QCCODE begins with "BD", "KD", "RM", "SD":
Then PARVAL is loaded to DT_RESULT_QC.QC_DUP_SPIKE_MEASURED.
And EXPECTED is loaded to QC_DUP_SPIKE_ADDED.

•If QCCODE begins with "BS", "MS", "SU":
Then PARVAL is loaded to DT_RESULT_QC.QC_SPIKE_MEASURED.
And EXPECTED is loaded to QC_SPIKE_ADDED.

•If QCCODE begins with "CS", "DU":
Then PARVAL is loaded to DT_RESULT.RESULT_TEXT and RESULT_NUMERIC.
And EXPECTED is not loaded to the database.

•If QCCODE begins with "LB", “LR”, “RS”:
Then PARVAL is loaded to DT_RESULT.RESULT_TEXT and RESULT_NUMERIC.
And EXPECTED is loaded to QC_ORIGINAL_CONC.

•If QCCODE is anything else:
Then PARVAL is loaded to DT_RESULT.RESULT_TEXT and RESULT_NUMERIC.
And EXPECTED is not loaded to the database.

•For EDFCL, if a match is found as stated above, LOWERCL and UPPERCL are mapped to the following fields depending on the last character in the EDFCL.CLCODE:

•If CLCODE ends with "A" (Accuracy):
Then LOWERCL is loaded to DT_RESULT_QC.QC_SPIKE_LCL and UPPERCL is loaded to DT_RESULT_QC.QC_SPIKE_UCL.

•If CLCODE ends with "P" (Precision):
Then UPPERCL is loaded to DT_RESULT.CUSTOM_FIELD_3.

Setup for Valid Values / Reference Values: Various GeoTracker data fields require valid values (VVLs). For an updated list of all GeoTracker VVLs, consult the GeoTracker website.

Prior to using the GeoTracker EDF Format, values in various EQuIS reference tables must be appropriately populated. The RT_LOOKUP table is the main reference table used in this format for the reference value list, but standard EQuIS RT_ tables are referenced in the EDFSAMP, EDFQC, EDFCL, GEO_FIELD, EDFFlat, CLFlat tables.

Columns in the GeotrackerEDF format use different tables for their reference value lookups. The EQuIS EDD (Electronic Data Deliverable) Description file includes which tables are used for the reference value source. Create the EQuIS EDD Description file in EDP or the Enterprise EDP EDD Upload widget. Some VVLs are built into the format for certain fields using enumerations available in the GeoTracker EDF-enum.xml enumeration file. While other VVLs may need to be added to the RT_LOOKUP table and/or other reference value tables (RT_ table).

The VVLs used by GeoTracker may differ from the reference values in the EQuIS database reference (RT_) tables. To accommodate this, VVLs used in the EDD can be remapped to EQuIS RT_ tables value via RT_LOOKUP. If the GeoTracker VVLs match the reference value tables in the database, remapping is not necessary. If remapping is needed, RT_REMAP and RT_REMAP_DETAIL tables must be populated with records with REMAP_CODE = ‘GeotrackerEDF’. Remapping will automatically occur during the Create step. See Remap for additional information.

Example RT_LOOKUP, RT_REMAP and RT_REMAP_DETAIL tables are available in the example Geotracker_RefVals.xlsx file. Review and modify these reference values based upon the company, organization and/or project requirements. After making modifications, upload the reference values using the Refvals format (see Reference Values - Importing for more information).