EQuIS as the Key to Unlocking the Potential of Data - Case Study

0

Courtesy of EarthSoft, Inc

Polycyclic Aromatic Hydrocarbon (PAH) History Reilly Tar and Chemical

  • 80 acre property, operated 1917-1972
  • Coal tar and distilled by-products discharged to ditch and peat bog through 1939
  • Onsite industrial well with leaky casing

PAH History Reilly Tar and Chemical

  • 1934 the city’s first water supply well started to draw creosote after two weeks of pumping
  • Well shut down after 8 months
  • 1970 MPCA/Attorney General filed lawsuit
  • 1972 Reilly closed

2004: Chlorinated Solvent History

  • PCE and TCE Industrial solvents commonly used to degrease metals during manufacturing process
  • Chemical dechlorination process: PCETCE  cDCE  VC
  • 2004 Vinyl Chloride detected in Edina Municipal Well #7

Complex Geology

  • Valley in bedrock surface
  • Multiple distinct aquifers
  • Plume extends 2.5 miles from source
  • Multiple pumping stresses

Goal: Need Better Conceptual Site Model

  • Understand why Reilly Tar remedy was not operating as planned
  • Need to tie cVOC contamination detected in municipal wells back to alleged source area and identify the responsible parties.

Data Sources

  • >40 years PAH data.
    • Monitoring data since 1980s in consultant’s databases, including EQuIS.
    • Not available in one place.
  • cVOC data from focused investigation projects, plus sampling of supply and PAH monitoring wells.
    • No comprehensive repository, many different “data tables” from project reports.
    • Lab EDDs obtainable for much of the work since 2007.

Site Understanding & Communication

  • Turn tables of numbers into visuals that account for:
    • Time
    • COPCs
    • Regulatory criteria
    • Horizontal extent
    • Vertical extent
    • Geologic context/Aquifer

Key: MPCA EQuIS

  • Learn more later today…
  • Specifications online
  • Easily queried
  • Output in flexible formats
  • Formats for lab data (EDP) and field data (EDGE)
  • Lab’s role vs. data provider’s role

Problem: Too Many Data Repositories

  • Commercial & State lab EDDs in Lab_MN format
    • Not complete: locations, field QC
  • Other data in Excel summary tables
    • Crosstabbed, minimal metadata
  • PAH data in consultant’s database

Solution: Load into EQuIS

  • Commercial & State lab EDDs in Lab_MN format
    • Edit in Excel & EDP, create MPCA EDGE EDD for “new” locations
  • Other data in Excel summary tables
    • Excel macros to reformat to MPCA EDDs
  • Database ETL transfer

PAH Criteria Based on Sums Calculate with Crosstab-of-Crosstab Report

  • Analytical Results Query
    • Use MAGs for PAH Groups: Carcinogenic, Other, & Quinoline
    • Set reporting units in MAG to convert to common units
    • ND Multiplier = 0
  • Crosstab 1: Reduce Multiple Results per Sample
    • Column Headers = Facility ID just need a constant
    • Row Headers = Well, Date, MAG, Analyte
    • Tabbed Data = Max(report result value)
  • Crosstab 2: Calculate Sums
    • Column Headers = MAG
    • Row Headers = Well, Date
    • Tabbed Data = Sum(max value)
  • Final Step: Deal with quinoline
    • Export to Excel
    • For all rows
    • If carcinogenic > 0 add quinoline to carcinogenic, else add to other

Geology

  • Not [currently] in MPCA EQuIS—boring locations not enough anyway
  • Subsurface geologic mapping done by MNGS Bedrock Geology of the Twin Cities Ten-County Metropolitan Area Miscellaneous Map Series M-194
  • GIS to convert contact contour maps into rasters and export for 3D solid modeling

3D Visualization Considerations

  • To place results in 3D, combine coordinates, depths/elevations, sampling results: X, Y, Start Depth, End Depth, Result Value, Well ID, GS Elevation
    • surf_elev not included in standard EQuIS reports…
  • Coordinates consistent both in X/Y (UTM = m) & Z (feet) and with well screen & geologic data
    • Make sure units are always stored (custom EDP checks!?!)
  • Dates—how to deal with multiple, incomplete sampling rounds:
    • Most recent / Maximum
    • Ever observed or within a defined time window
    • Different EQuIS report(s) to run depending on choices

Conclusion

  • When data is not managed from the beginning of the project, much more time is spent on data compilation and reformatting than on reporting and understanding.
  • EQuIS is a comprehensive repository for environmental data, with tools that make repurposing data easy for analysis and communication.
  • Visualization leveraging comprehensive datasets enables efficient understanding of site conditions and processes.

Customer comments

No comments were found for EQuIS as the Key to Unlocking the Potential of Data - Case Study. Be the first to comment!