EDSA7 Table and Field Changes
Updated August 2012

Each of the three tables (DTST, HZSAMPLE and HZRESULT) have undergone changes, based on commonly-encountered problems related to inadequate field sizes, changes to data collection requirements by the SRP and implementation of the Licensed Site Remediation Professional (LSRP) program. SRP hosted a group of LSRPs who provided comments on the structure and implementation of the HazSite data collection program during much of 2011 and into 2012. One of the accomplishments of this partnership is an EDD process that includes a significantly improved EDSA7.

Summary of changes to DTST

The DTST table had 4 fields added: CONTACTNAM, CONTACTTEL, CONTACTEXT and CONTACTEMA.

These fields have become necessary due to elimination of case managers and implementation of the LSRP program. Since there are no longer case managers to run hard media submissions of EDSA7 data, SRP is implementing a system, whereby the Electronic Data Deliverable (EDD) can be e-mailed to srpedd@dep.nj.gov and automatically run by the automated EDSA7 program. Contact information becomes critical because there is no single individual assigned to a given case, therefore, if problems arise with the submitted dataset, it is necessary to have the name, phone and e-mail of the data preparer or a person with full knowledge of how the data was prepared.

Summary of changes to HZSAMPLE

HZSAMPLE has undergone extensive revisions. The order of the fields, however, has been retained so that any older EDDs or existing templates can be cut and pasted into the new format without loss of information due to changes in field locations. Highlights of these changes are as follows:

A brief summary of changes to fields is as follows:
SRPID has been changed to accommodate identifiers that may be longer than the 16-character date fields. The New Jersey Environmental Management System (NJEMS) has made use of larger SRPIDs possible and EDSA7 has followed the NJEMS lead in order to facilitate integration of HazSite data to NJEMS data.

SAMPNUM has been increased due to complaints from a large number of end-users that the data collection system was inadequate to hold large identifiers often used in field collection. Often such identifiers were placed into the FIELDID field, resulting in problems because this field is intended to be a unique identifier of the location for a sample point in the field (See FIELDID below for further details). SAMPNUM should now accommodate highly descriptive text strings that field and laboratory personnel use to designate samples making up the EDD.

SAMPTIME has been changed to accommodate a Date/Time string.

Latitude and Longitude fields (LAT_DEGREE, LAT_MINUTE, LAT_SECOND; LON_DEGREE, LON_MINUTE, LON_SECOND) have been changed to accommodate Decimal Degrees and Decimal Minutes because it has come to SRP’s attention that many data preparers have been attempting to submit data in these formats by distributing the result into the existing fields. This results in data points that are often far from the intended location (See the example of NJDEP’s Trenton building in the main webpage). The Latitude and Longitude fields have traditionally collected Decimal Second data and will continue to default to that format. If, however, the data being sent to SRP uses one of the acceptable alternate formats, the submitter must input Decimal Degrees or Decimal Minutes in the COORDMETH field.
SRP will also be conducting a check of the data locations to determine whether they are in New Jersey. If not, an error message will be issued for the row. Further evaluations of coordinate locations and validity are being considered and will be implemented in the coming months. Be sure to check this webpage for updates. In order to assist the end-user with these changes, SRP has created an application that produces a KML file (viewable in ARC-GIS Explorer or Google Earth). EDSA7 will attempt to open the file, if one of these programs is installed and selected as a default KML reader. Data contained in the HZSAMPLE file under evaluation are converted to KML and the file opened by EDSA7. It is SRP’s intention that the final dataset being evaluated must be reviewed by the end-user, due to the extraordinarily poor quality of locational data that has been submitted over the years.

SAMPNOTE has been changed to a 254-character field. If notes exceed the 254-charactes, contact SRP for instructions as to how to submit an equivalent document in XML format

QAQC has been expanded to 15 characters. Many end-users are already familiar with this change, due to the requirements of TO-15 Ambient Air Reporting (see section 11.3.5 for more details). This expansion of the field is intended to hold the Sample Delivery Group (SDG) number for Field/Laboratory EDD identification purposes.

COORDMETH is a new field that is used to track the coordinate method, by which the location of data points are being presented to SRP. Because of data discrepancies related to the submission of Lat/Long data, two methods are currently acceptable for input to this field:
DECIMAL DEGREES = Degrees with up to 7 decimals
DECIMAL MINUTES = Degrees + Minutes with up to 5 decimals
There are currently no additional valid values for this field. If either of these methods are used the lower fields (Minutes and Seconds for Decimal Degrees or Seconds for Decimal Minutes) must not be filled out!

COORDNOTE is a new field that allows the user to convey useful information related to how data points were collected. For sampling data, three methods are currently recognized: They are Survey, GPS and Dig. Image. The interpretation is as follows:
Survey At least one point was collected by surveying from a known point (e.g. a benchmark or well – surveyed from a known benchmark).
GPS  Geographical Positioning System – Electronically collected from a GPS Unit
Dig. Image  Digital Image – Using Geographical Information Systems (GIS) images such as orthophotoquads in ESRI’s ARC-Map application; NJGeoWeb or Google Earth. Points can be determined against Aerial Photography and input to datasets, usually with sufficient accuracy to meet SRP standards.

GWDEPTHPRI is a measure of the depth to ground water before purging a well. It is reported as the absolute value of feet below the ground surface (NOTE: GROUNDELEV – the measure of ground surface – is measured in feet above mean sea level).

GWDEPTHPOS is a measure of the depth to ground water after purging a well. It is reported as the absolute value of feet below the ground surface (NOTE: GROUNDELEV – the measure of ground surface – is measured in feet above mean sea level).

SCREENTOP is a measure of the depth to the top of a well screen (or packer interval or passive diffusion bag) as the absolute value of feet below the ground surface (NOTE: GROUNDELEV – the measure of ground surface – is measured in feet above mean sea level).

SCREENBOT is a measure of the depth to the bottom of a well screen (or packer interval or passive diffusion bag) as the absolute value of feet below the ground surface (NOTE: GROUNDELEV – the measure of ground surface – is measured in feet above mean sea level).

WELLPERMIT is the Well Permit Number. This is now required for Monitoring Wells. The current Well Permit Number consists of 10 alphanumeric characters, with the following rules:
If the well is unpermitted or grandfathered and does not require a permit use "NO PERMIT" in this field. Any other text will cause an error. All wells except for domestic wells per-dating 1949 should have a valid permit.

• Well permits are formatted according to a system that uses the Atlas grid designation for the first two characters. The acceptable numbers are: 21, 22, 23, 24 , 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36 and 37. 
• Following the two characters, will be a minimum of three zeros
• Finally five or fewer numeric digits that are a sequential numbering system for the wells permitted for that Atlas Grid sheet.
• For wells drilled starting in 2008, the first character will be either “E” or “P” (depending upon whether locational data were uploaded Electronically by the driller or from Paper submitted by the well driller and entered by NJDEP staff. Following E or P will be the 4 numerical digits of the year (e.g. 2009). Finally 5 digits (a sequential number indicating the numerical count of the well permit issued for that well in that year). For example, P200802014 would be the 2014th well permitted in 2008.

In the old system a hyphen was used in the place of the padding zeros. If we compare numbers from the old system to the new system, they might look like:
Old: 31-17647
New: 3100017647
If a permit number is encountered with a second hyphen and additional numbers, ignore everything from the second hyphen on for the purposes of the well permit. Permits were often issued for multiple wells and the final numeric portion helped drillers track which well was which in submitting a single permit for multiple wells. The well record number ultimately became the permit number when the well was recorded in the database and the permit information will show up for all relevant wells. If the well permit 31-17647 above were issued for multiple wells, the “permit number” might have been reported as 31-17647-3.
In short, all well permit numbers now have 10 characters. An algorithm that accounts for as much as possible from this description will allow all potentially correct Well Permit Numbers to pass; Those using the hyphen will generate a warning that states that SRP will replace the hyphen with the appropriate number of “padding zeros” required to reach 10 characters. Finally, any well permit that does not follow these requirements will generate an error.

To find unknown Well Permit Numbers, run the following Data Miner Report:

Select XY Permit Well Search (the half-mile radius is recommended to start)

This report returns permits, records and decommissionings within the specified search radius. Select and copy the contents of the HTML table into a spreadsheet and filter for the appropriate location(s). To do this, it is important to note that the Document, Well Use and Physical Address are the most important fields. It is recommended that Well Use be filtered to Monitoring and Monitoring Replacement (and any other relevant types). Next, the Document can be filtered to Record (looking for wells that were actually installed). If the address or block(s) and lot(s) are known, use of filters on these fields can be very helpful. Using a combination of address, block, lot and Well Name, it should be possible to find Permit numbers related to site wells. For help with this query, contact Henry J Kindervatter or by phone (609) 633-1419.

SRP_DIR is used by SRP for cataloging datasets that come into the system. It is the Catalog Number for EDSA7 datasets. It was decided that any dataset returned for whatever reason would have the SRP_DIR tracked in HZSAMPLE, so that when corrections are made to the dataset, SRP can make a definitive correlation between those dataset(s) and the corrected EDD data. From time to time, SRP personnel extract multiple datasets from the HazSite data to correct multiple datasets at one time. This system allows SRP to track the source of several datasets simultaneously so that one EDD can correct multiple problem datasets, if necessary.

SAMPLABID is a new field that allows the laboratory to track information between HZSAMPLE and HZRESULT using the HZRESULT.LABID as a secondary key. When the laboratory creates a diluted sample from one or more serial dilutions, if no additional (properly numbered) row in HZSAMPLE is created, all results for the main sample and the diluted sample are tied to the main sample in HZSAMPLE. This violates the requirements of the primary key between the two tables and results in “duplicates” that are flagged as errors. Where this, or similar conditions exist, EDSA7 will now check the SAMPLABID as a check on the primary key – if present, it will be included to sort between the original sample and the serial dilution sample(s).

Summary of changes to HZRESULT

HZRESULT has undergone extensive revisions. The order of the fields, however, has been retained so that any older EDDs or existing templates can be cut and pasted into the new format without loss of information due to changes in field locations. Highlights of these changes are as follows:

SRPID has been changed to accommodate identifiers that may be longer than the 16-character date fields. The New Jersey Environmental Management System (NJEMS) has made use of larger SRPIDs possible and EDSA7 has followed the NJEMS lead in order to facilitate integration of HazSite data to NJEMS data.

SAMPNUM has been increased due to complaints from a large number of end-users that the data collection system was inadequate to hold large identifiers often used in field collection. Often such identifiers were placed into the FIELDID field, resulting in problems because this field is intended to be a unique identifier of the location for a sample point in the field (See FIELDID below for further details). SAMPNUM should now accommodate highly descriptive text strings that field and laboratory personnel use to designate samples making up the EDD.

LABID was increased in size from 12 characters to 20 characters due to complaints by various laboratories that the size of the field was insufficient.

TDANALYZ was formerly DANALYZ. This field was changed from a date field to a text field that contains the string associated with the typical Microsoft Excel Date/Time field. The format allows for Year/Month/Day/Hours/Minutes/Seconds

NJDLABCERT is the Laboratory Certification number. Formerly, it was 5 characters, however now it has been re-set to 7 characters to account for certain labs whose ID numbers exceed the 5-character length.

RESULTTYPE has remained the same, in that it is still one character in length, however SRP has added to the valid values recognized for this field. In addition to the traditional A(nalyte), P(arameter) and T(IC), S(urrogate), I(nternal Standard) and L(aboratory generated quality control sample) have been added to the list. In particular, in the past, surrogates that were included as Analytes could have created serious false positive results when looking at the data. This had the potential to represent the site as contaminated when, in reality, it is clean.

MDL is now required for all analytes. This field must be entered for all rows in HZRESULT that are considered ANALYTES (HZRESULT.RESULTTYPE field = 'A'). Parameters require a MDL where applicable(HZRESULT.RESULTTYPE field = 'P').

QUANTTYPE is now required for all rows in HZRESULT that are considered ANALYTES (HZRESULT.RESULTTYPE field =A). The value entered for QUANTTYPE (a majority of the time) will be the reporting limit.

QUANTLEVEL A numerical value must be entered in QUANTLEVEL for all QUANTTYPEs.

ANLYS_MTHD has been changed from 15 to 35 characters in length, in response to numerous complaints about the lack of space for methods to be properly detailed.

QAQC was expanded in 2003 as part of the initiative for tracking Sample Delivery Groups as part of the Indoor Air requirements.

UNCOR_CONC & UNCOR_UNIT are both required fields for Indoor Air sampling. These fields provide part of the data required by the TO-15 Method (see webpage section 11) for additional details. These fields have been required for some time for the Indoor Air sampling EDDs and should be relatively familiar to experienced EDD preparers.

RETEN_TIME is currently flagged as a warning only, however SRP intends to move toward its inclusion as a required field when reporting Tentatively Identified Compounds (TICs). Prior to the creation of this field, the TIC could be identified by T in the RESULTTYPE field plus the retention time concatenated to the ANALTPARAM name portion of what has been tentatively identified (Example: ANALTPARAM = PENTANE, 2,3,3-TRIMETHYL- 7.48; CAS = 560-21-4). The 7.48 means the TIC was observed as a peak coming off the Gas Chromatography (GC) column at 7.48 minutes (7 minutes, 28.8 seconds). The TIC is matched to a library of known compounds by Mass Spectroscopy and the compound assigned the name and CAS number of the closest matching compound. The current interpretation of this example would be as follows:
ANALTPARAM = PENTANE, 2,3,3-TRIMETHYL
CAS = 560-21-4
RETEN_TIME = 7.48

DILUT_FAC is included for those instances where there has been a dilution of the original sample. This usually occurs for samples that have contaminants in concentrations above the expected range for the GC column. The original sample is suitably diluted so that the expected levels of contamination are within the limits of the column. The sample that is re-run in this manner may be diluted by a factor of 2 or perhaps 10 or more. This field is intended to capture the magnitude of the dilution relative to the undiluted sample. If an additional record is not included for each dilution in HZSAMPLE, at least one error will result for each missing HZSAMPLE record.

PREP_MTHD is included to track the method employed for those samples requiring special preparation (not implied in the method citation) prior to analysis. It is not required for standard analyses.

CLNUP_MTHD is included to track various methods that may be employed to decrease interference of a non-target compound on a target compound’s analytical results. Certain compounds may break down in such a way that they change the amplitude of a peak for a target analyte in the GC analysis, resulting in a “false positive” (peak is too large) or a “false negative” (peak is too low). The cleanup method is designed (for these cases) to affect the non-target compound without changing the target compound.