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. The approach is simplified due to the compatibility of our data model with that of EnviroInsite. We have designed multiple reports and developed multiple analyses and models (identifying redox processes, hydrochemical type of water or average hydraulic gradient).

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 run also 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), * database format of EnviroInsite,
• 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. Coordinate conversion and loading of data to PostGIS is performed by PDI GIS Plugins. Ad-hoc loading is performed by common GIS software (e.g. QGIS). PDI is also used to export data to third-party simulation tools.

Due to the compatibility of our data model with that of EnviroInsiteData 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.

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 O₂, NO₃⁻, Mn²⁺, Fe²⁺, SO₄²⁻ and sulfides) without measured Eh and pH¹⁾.
• 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 ²⁾ 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.

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.

• PDI cheat sheet

Pages only in Czech:

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