<< Click to Display Table of Contents >> Navigation:  EQuIS Library > Formats > EarthSoft Interface Formats > 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+.

 

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. By design, the EQuIS GeotrackerEDF format uses remap tables and the DT_LOOKUP table to allow users to remap incoming GeoTracker reference values to their non-GeoTracker EQuIS reference values, and to check their exported data against the GeoTracker reference values.

 

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.

 

 

Format Details

 

The GeotrackerEDF format contains 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.

•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.

•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.

 

 

The Primary Key (PK) fields in the EQuIS GeotrackerEDF format are consistent with EQuIS requirements. GeoTracker PK fields that are not consistent with the EQuIS Schema will be required but may not be part of the GeotrackerEDF format's primary key. Adding GeoTracker PK fields that are not consistent with the EQuIS Schema may cause errors on commit.

 

Sample IDs

Field samples / client samples map directly to DT_SAMPLE.SYS_SAMPLE_CODE and will not concatenate LOGDATE and LOGTIME to the Sample IDs (SAMPIDs).

 

Sample IDs for Lab QC Rows

GeoTracker does not require sample level data for lab QC data, however, EQuIS requires sample level data for all samples in EQuIS, including QC. SAMPIDs for laboratory QC samples are created by concatenating fields when submitting EDDs. The logic for populating SAMPID is different for the Flat File section versus the Analytical Data section of the GeoTrackerEDF format, as described below.

 

Logic for Flat File Section (EDFFlat and EDFCL)

•If the SAMPID is null and QCCODE is NOT “CS” when loading an EDD, the format will populate the SAMPID with a concatenation of:

<EXTDATE><ANMCODE><LABSAMPID>

And populate:

•LOGDATE = EXTDATE

•LOGTIME = '0000' (default)

•LOGCODE = 'LAB' (default)

•PROJNAME = 'LAB' (default)

 

•The EDFFlat section will also check for orphan rows.

 

Logic for Anaytical Data Section (EDFSAMP, EDFTEST, EDFRES, EDFQC, and EDFCL)

•The SAMPID is part of the PK in the EDFSAMP section and cannot be null.

•The SAMPID is not required in the EDFTEST section. EDFTEST includes QC samples from the laboratory and GeoTracker does not require a SAMPID for laboratory samples. If QCCODE is null upon loading the file, it will default to “CS”.

•The creation of the SAMPID for laboratory QC samples is done on the Create step when loading an EDD. If the SAMPID is null and QCCODE is NOT “CS”, the format will populate the SAMPID with a concatenation of:

<EXTDATE><ANMCODE><LABSAMPID>

And populate:

•LOGDATE = EXTDATE

•LOGTIME = '0000' (default)

•LOGCODE = 'LAB' (default)

•PROJNAME = 'LAB' (default)

 

•The EDFTEST section will also check for orphan rows. The format requires parent EDFSAMP records for all samples including QC samples.

•Sample level rows will not be created on EDD load into EDP for QC samples as they are in the EDFFlat section.

•EDFSAMP does not contain a SAMPLE_TYPE_CODE field. QCCODE is equivalent to the SAMPLE_TYPE_CODE field in EQuIS. QCCODE exits in the EDFTEST section. In EQuIS, the SAMPLE_TYPE_CODE exists at the sample level in the DT_SAMPLE table. This means that GeoTracker client samples (CS) will not have an EQuIS SAMPLE_TYPE_CODE assigned when a GeoTracker EDD is loaded at the EQuIS sample level. The EQuIS GeoTrackerEDF format runs a method on the Create step that populates the EQuIS SAMPLE_TYPE_CODE from the GeoTracker QCCODE in the EDFTEST section.

•The SAMPID cannot be the FIELD_PT_NAME. If the SAMPID is populated and the FIELD_PT_NAME is null, the format will not produce an error for the SAMPID (known issue). EarthSoft recommends populating the FIELD_PT_NAME in EDP prior to the Create step.

 

 

 

Survey/Gauge Data

This section has not had the attention/testing as the Analytical Data and Flat File sections. EarthSoft understands that clients may use other standard EQuIS formats to enter this information. This section may be used for export rather than import, depending on the user's workflows.

 

 

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 the following standard EQuIS RT_ tables are referenced in the EDFSAMP, EDFQC, EDFCL, GEO_FIELD, EDFFlat, and EDFCL sections:

•RT_ANALYTE

•RT_ANALYTIC_METHOD

•RT_COMPANY

•RT_COMPANY_TYPE

•RT_DETECT_FLAG (may not be in Geotracker Schema)

•RT_FRACTION

•RT_LOOKUP

•RT_MATRIX

•RT_PREP_METHOD

•RT_RESULT

•RT_SAMPLE_TYPE

•RT_TEST_BATCH_TYPE

•RT_TEST_TYPE

•RT_WEIGHT_BASIS (may not be in the standard Schema)

 

The GeotrackerEDF format expects certain reference values to run properly.

•The RT_COMPANY table should contain a COMPANY_CODE = “LAB” and COMPANY_TYPE = “OTHER”.

•The RT_COMPANY_TYPE table should contain the value “OTHER”.

•EarthSoft has removed the lookup to the RT_LOOKUP table for the GeoTracker ANMCODE field, but has retained the remap option. AMNCODE is now a direct mapping to the RT_ANALYTICAL_METHOD table.

 

 

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).

 

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).  

 

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.  

 

The GeotrackerEDF format contains remaps. If the remap tables are not being used/populated the value in the EDD will not be affected.

 

 

 

TEST_BATCH_TYPE Options

 

Within the GeotrackerEDF format, the TEST_BATCH_TYPE will default to “UNKNOWN”. This value needs to be added to the RT_TEST_BATCH_TYPE table.

 

If a user prefers that the TEST_BATCH_TYPE defaults to “ANALYSIS”, then “ANALYSIS” needs to be added to the RT_TEST_BATCH_TYPE table and the following row must be added to the ST_CONFIG table:

 

 

 

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 should include: COLUMN_NUMBER, ANALYSIS_DATE/TIME, FRACTION, and TEST_TYPE.

•GeoTracker field BASIS maps to EQuIS tables as follows:

•The original value is written to DT_TEST.CUSTOM_FIELD_4. This allows for an accurate export.

•Two methods (GetTotalOrDissolved and GetBasis) are applied in EDP to remap BASIS to DT_TEST.BASIS and DT_TEST.FRACTION using the following logic:

•If BASIS = "N", then DT_TEST.FRACTION is populated with "T" (Total) and DT_TEST.BASIS field is populated with ”NA”.

•If BASIS = "F" or "L", then DT_TEST.FRACTION is populated with "D" (Dissolved) and DT_TEST.BASIS field is populated with ”NA”.

•If BASIS = "D", then DT_TEST.BASIS is populated with "Dry" and DT_TEST.FRACTION is populated with "N" (Not applicable).

•If BASIS = "W", then DT_TEST.BASIS is populated with "Wet" and DT_TEST.FRACTION is populated with "N" (Not applicable).

•For all other values on the BASIS list, DT_TEST.BASIS defaults to "NA" and DT_TEST.FRACTION defaults to "N".

•GeoTracker field PARVQ maps to EQuIS tables as follows:

•The original value is written to DT_RESULT.CUSTOM_FIELD_1. This allows for an accurate export.

•Two methods (GetDetectFlag and GetResultType) are applied in EDP to remap PARVQ to DT_RESULT.DETECT_FLAG and DT_RESULT.RESULT_TYPE_CODE using the following logic:

•If PARVQ = "ND", then PARVAL is evaluated. If PARVAL > 0, then DT_RESULT.DETECT_FLAG = “Y”. If PARVAL <= 0, then DT_RESULT.DETECT_FLAG = “N” and DT_RESULT.RESULT_TYPE_CODE = "TRG".

•If PARVQ = “<”, then DT_RESULT.DETECT_FLAG = “N” and DT_RESULT.RESULT_TYPE_CODE = "TRG".

•If PARVQ is “=”, “>”, “IN”, or “TI”, then DT_RESULT.DETECT_FLAG = “Y” and DT_RESULT.RESULT_TYPE_CODE = "TRG".

•PARVQ is also checked for surrogates. If PARVQ = "SU", then PARVAL is loaded to DT_RESULT_QC.QC_SPIKE_RECOVERY, DT_RESULT.RESULT_TYPE_CODE = “SUR” and DT_RESULT.DETECT_FLAG = “Y”.

•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.  

•GeoTracker field 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.

 

 

Field QC Parent sample Codes Are Non-existent in GeoTracker

 

GeoTracker does not appear to have a field that addresses field QC parent sample codes (e.g., field duplicate, field blank, etc.). GeoTracker does contain the LABREFID field for lab QC parent sample codes (see definition below). Datasets from the labs reviewed by EarthSoft do not populate the LABREFID field, which causes an issue when loading data to EQuIS. There is a trigger in the EQuIS Schema that enforces PARENT_SAMPLE_CODE population based on the RT_SAMPLE_TYPE.NEEDS_PARENT_SAMPLE for the RT_SAMPLE_TYPE.SAMPLE_TYPE_CODE.

 

This means that field QC or lab QC would not be associated with a parent record. EQuIS has a database trigger that identifies a parent sample code by the values entered in the RT_SAMPLE_TYPE.NEEDS_PARENT_SAMPLE. Valid values for this field are “Y” and “N”. The solution is not ideal but will work with GeoTracker data. Updates to this field may may cause undesirable results for data entered via other EQuIS formats, such as EQEDD. The GeoTracker solution would be to change all values in the RT_SAMPLE_TYPE.NEEDS_PARENT_SAMPLE to “N”. This is typically not recommended and is poor data management and governance practices. This is an unfortunate GeoTracker limitation.

 

EarthSoft will continue to seek a proper solution. If clients report the LABREFID as being consistently populated, EarthSoft may add checks to enforce population of LADREFID for non-client samples in the future. However, this does not solve the issue of missing field QC parent sample codes.

 

LABREFID Definition from GeoTracker Website:

The Laboratory Reference Sample ID (LABREFID) is the laboratory-assigned sample ID of the sample upon which the QC sample is referenced to calculate the QC result. A reference sample is used in conjunction with a QC sample (LABQCID) to determine precision and accuracy.

 

Attributes: C12 included in Tables: QC EDFFLAT Guidelines & Restrictions:

•LABREFID may not be left blank when QCCODE = “MS/SD” or “LR,” and must be left blank in all other cases.

•Enter the LABSAMPID of the client sample that was spiked or replicated in the LABREFID field.

 

 

Known Issue: Special Considerations for Tentatively Identified Compounds

 

EarthSoft assumes Tentatively Identified Compounds (TICs) are not being reported in the EDDs sent by the laboratory. If the laboratory does send TICs in the EDD, then the GetResultType methods may need to be adjusted.

 

TIC Discussion from GeoTracker Website:

3.3.2.2 Special Considerations for Tentatively Identified Compounds (TICs):

•PARVQ requires an entry of “TI.”

•Chemical Abstract Service (CAS) numbers may be used (for TICs only) instead of PARLABELs to identify the parameter being reported. It is recommended that TICs without CAS numbers have PARLABEL valid values.

•LABDL and REPDL should be blank. REPDLVQ and SRM requires entry of “NA.”

•RT is a recommended entry field for TIC results.

 

 

GeotrackerEDF Export

 

The GeotrackerEDF format has the following options to export data from EQuIS to send to GeoTracker:

•GeoTrackerEDF EDD – Populates EDFSAMP, EDFTEST, EDFRES, EDFQC, EDFCL, and GEO_FIELD.

•GeoTrackerEDF FLAT EDD – Populates EDFFlat. (A known issue is that this export does not populate the EDFCL section.)

•GeoTrackerEDF FLAT EDD (by EDD) – Provides users with a list of EBATCH numbers for exporting specific EDDs. The list contains all EBATCH numbers and may be difficult to select from long lists. (Note that more testing of this option is required.)

•GeoTrackerGEO XYZ_SURVEY EDD – Provides users with Location selection (Individual and Groups). (Note that more testing of this option is required.)

•GeoTrackerGEO WELL CONSTRUCTION EDD – Provides users with Location selection (Individual and Groups). (Note that more testing of this option is required.)

•GeoTrackerGEO WELL EDD – Provides users with Location selection (Individual and Groups). (Note that more testing of this option is required.)

 

 

 

Known Issues with EDFFlat Export:

•EDFRes – If PARVAL values were added on import but were not added to the RT_LOOKUP table, they may need to be added to the RT_LOOKUP table so that they do not show as invalid reference values in the export.

•EDFCL does not get populated with the EDFFlat export.

 

Known Issues Analytical Data Export:

•EDFCL does get populated with the EDFSAMP, EDFTEST, EDFRES, and EDFQC sections, however, it is missing values for required fields CLREVDATE and CLCODE. As a workaround, these values can be obtained from the original EDFCL file.

•The GeoTrackerEDF EDD will export the Analytical Data sections and exports to the GEO_FIELD section. This needs review. If specific locations are not selected to export, the GEO_FIELD section includes laboratory data. The laboratory samples do not contain FIELD_PT_NAME data and the export should not be trying to export locations for laboratory samples.