Managing environmental data can be a headache. After collecting data at different intervals, from different locations with multiple laboratory analyzed results, managing data in a meaningful way can prove to be a challenging and cumbersome task. Making logical and realistic interpretations and predictions from these data sets can be further complicated by a lack of confidence in your data. So what can you do to make water quality data management easier and more effective for your organization?
1. Store water quality data in a relational database
Groundwater and environmental professionals often start using AquaChem water quality data management and analysis software to manage their data because they are trying to juggle multiple spreadsheets from multiple sampling events. Sometimes they end up with more than 20 spreadsheets of data recorded over several years without any connection between them. The field technician who originally recorded most of the data may have left the company and is no longer available to answer critical questions about the data.
An easy solution to these challenges is to use software with a relational database. This will store all data within a single database in tables and fields that are structured in a logical and consistent way so that the vital pieces of information can be accessed easily. This also ensures that all data are standardized in a common format, making key comparisons and analyses easier to evaluate. Duplicate records are recognized automatically and prevented because of the inherent database structure.
2. Use one centralized, accessible database
Often environmental data is segregated and stored in many different locations. For organizations with many departments this is often a major source of data discontinuity. When it comes time to create a comprehensive report, one of two scenarios usually prevails:
- the data are scattered and much time can be lost waiting to obtain the correct data to complete the report
- the data are partially duplicated between organizational divisions and out-of-date data may be used
Thus, simply centralizing the data into one robust database is not sufficient; the data must also be made accessible to all stakeholders in a simple, user-friendly way. If the data is centralized but it takes too long for key stakeholders to retrieve information, then the use of unapproved and outdated data by stakeholders will persist despite your best efforts.
Permissions to users will dictate what access levels are granted. Some software tools require replication of data from a centralized database into a separate geochemical database or spreadsheet to enable data analysis. While this is an improvement from managing data in multiple excel spreadsheets, the more “moving parts” a system has, the more difficult it is to maintain because there are more points at which it can break. Instead, we recommend that the analysis tools pull directly from one centralized database.
Similarly, there is often a preference to hand craft a customized database using in-house resources rather than invest in a commercial product. Depending on your personal needs, this may be a good place to start and require a lower cost. However, one of the challenges in such systems is the cost to maintain it because often these systems are undocumented and maintained by only one or two people. This makes for a high risk of knowledge loss if the person who built it were to ever leave the company, and making changes may become more difficult once the initial development effort is complete and the original developers are distracted by the next new and exciting project on the horizon.
3. Use a consistent naming convention for well and/or parameter names
A common hurdle when merging data from several sources in a single database is ensuring well names and parameters use consistent names. In databases, MW1 is not the same as MW-1 or MW-01; similarly Ca and calcium may mean the same thing to a user, but a database will not automatically make the same distinction.
It is usually necessary to review the data prior to merging to a central location to avoid any quality issues caused by inconsistent naming. A good geochemical software tool will make this easier by allowing you to enter aliases for parameters, effectively merging data that use both names together for plotting and analysis, and enabling you to work with terms you prefer.
4. Determine what to do with censored data
When managing water quality data, you need to have a meaningful way of handling censored or non-detected data. This occurs when the measurement provided from the lab is either detected but below the limit of quantification or the measurement was below the method detection limit, meaning that the value recorded was below the range measurable by the instrument. These are often recorded in the lab analysis using a ‘<’ qualifier.
Databases can store these qualifiers, but it is up to you to determine how to handle these in further analyses. How these are handled on plots or in calculations can have a large impact on the interpretation of the data. Depending on the nature of the site and the sampling objectives, such values are usually treated in one of three ways:
- The optimistic scenario: considered non-detect and treated as 0 values
- The conservative scenario: treated as detections with a value equal to the detection limit
- The compromise: treated as detections with a value of some percentage of the detection limit (often 50%)
An ideal geochemical software tool will help with censored data by allowing you to define a specific handling preference for these special measurements, making analysis and reporting against analytical data that includes censored values a simple and straightforward task.
About AquaChem
AquaChem water quality data management and analysis software is used globally by groundwater professionals and geochemists to effectively manage water quality data and gain critical insight into water resources. With over 25 geochemical plots, trend analyses, statistical reports and geochemical modeling capabilities, AquaChem delivers invaluable tools that lead to confident and effective decision making.