HgIS

Environmental data management and analytics

User Tools

Site Tools

Table of Contents

Documentation

This is simplified technical documentation. User’s documentation is available for customers only.

HgIS is mostly built upon the Pentaho platform. It uses ETL tool Pentaho Data Integration to load data to the database. The database PostgreSQL uses our data model. To formulate conceptual models, data can be explored in 3D hydrogeological visualization software EnviroInsite. We have designed multiple reports and developed multiple analyses and models (identifying redox processes, hydrochemical type of water, or average hydraulic gradient).

ETL

The data are loaded to the database (data warehouse) by the ETL tool PDI. Data transformations in PDI can be implemented without coding through an intuitive graphical user interface and also run in the command-line interface or on the ETL server. We implemented the loading of the following data:

  • assays from LIMS “Labsystém” (xBase files),
  • geologic description and water quality from Czech Geological Survey (MS Access and XML files),
  • RockWorks
  • geology of boreholes (MS Word documents created by a Geobanka software),
  • flat files with precipitation and temperature from a watershed authority (text files via FTP server),
  • general cross-table and
  • formats from some other data vendors (groundwater pumping, river discharges etc.).

Subsequent transformations provide data cleaning, anti-aliasing, validation, and loading to the database. PDI GIS Plugins performs coordinate conversion and loading of data to PostGIS. Ad-hoc loading is performed by common GIS software (e.g. QGIS). PDI is also used to export data to third-party simulation tools.

Visualization

Data can be easily exported to MS Access or MS Excel file and can be visualized in EnviroInsite. This is suitable for professional hydrogeologists to develop conceptual groundwater models. Stakeholders and other nonspecialists can view data in a web application we have developed. The application combines tables, a graph and a map on a single screen. Different map layers are provided as mapping services. We have developed the following reports:

  • The graph and the table of time development of an arbitrary quantity in arbitrary observation points and basic descriptive statistics.
  • Profile of geologically documented borehole.

Analyses and models

We have developed the following analyses and the models that are reusable because of their general purpose and connection to the database. Some analyses are utilizing PDI. Results are stored directly in the database as separate quantities:

  • Data aggregation (e.g. total annual precipitation computed from daily precipitation, minimal monthly discharge in a year). Aggregations can be computed easily using PDI step “Group By”.
  • Computation of the hydrochemical type of water (based on major cations and anions) – e.g. Ca-Mg-HCO3.

Some analyses and a model are utilizing PDI and formulas in Pentaho Reporting (OpenFormula), results are depicted in reports:

  • Identifying redox processes in ground water from chemical composition (dissolved O2, NO3−, Mn2+, Fe2+, SO42− and sulfides) without measured Eh and pH1).
  • Multicriterial analysis assessing water quality trends in correspondence to eutrophication. Aggregated values of quantities (nitrogen/phosphorus ratio, saturation of oxygen, pH etc.) were compared to estimated limits. The trend of the sum of the weighted logical values (overall score) indicates the trend in water quality.

Some analyses were performed in external tools (data were exported with PDI):

  • The average hydraulic gradient was calculated from the hydraulic heads of selected boreholes. Consecutively seepage velocity and retention time were computed. This calculation is performed in MS Excel spreadsheet 2) by matrix formulas.
  • To support the geological interpretation of thousands of exploratory boreholes from a former mining area we tested an automated classification of the detailed text characterizations of strata. We used RapidMiner software.

The above-mentioned analyses support conceptual model developments. The same techniques can be used to implement simple procedural site-specific groundwater models.

Use cases

Components of HgIS are being used in a state enterprise (that carries out recultivation) for data management and for the water balance model of a lake. HgIS is deployed and used within research projects at the Technical University of Liberec.

Further reading

Pages only in Czech:

  • Development.
    However, the whole Czech version of this site is more elaborate than the English version.


HgIS was developed within projects financially supported by the Technology Agency of the Czech Republic:

Technology Agency of the Czech Republic TA02020177 Information system for support of decision making on use of recultived landscape (MARE)
TA04020207 Information system for analysis and assessment of groundwater resources in dependence on human activities and climatic changes (ZAVOD)
TD03000037 Information system for decision support for urban planning of settlement and infrastructure (URBAN)

1)
CHAPELLE, Francis H., Paul M. BRADLEY, Mary Ann THOMAS a Peter B. MCMAHON, 2009. Distinguishing iron-reducing from sulfate-reducing conditions. Ground Water. 47(2), 300–305. ISSN 1745-6584. 10.1111/j.1745-6584.2008.00536.x
2)
DEVLIN, J.F., 2003. A spreadsheet method of estimating best-fit hydraulic gradients using head data from multiple wells. Ground Water. 41(3), 316–320. ISSN 1745-6584. 10.1111/j.1745-6584.2003.tb02600.x
Last modified: 2022-12-20