Integrating Postgresql and R: Open-Source Tools For Processing and Reporting Monitoring Data

Kruszyk Robert (), Dmowska Anna, Majewski Mikołaj and Szpikowshi Józef
Additional contact information
Kruszyk Robert: Institute of Geoecology and Geoinformation, Adam Mickiewicz University in Poznań, Poznań, Poland
Dmowska Anna: Institute of Geoecology and Geoinformation, Adam Mickiewicz University in Poznań, Poznań, Poland
Majewski Mikołaj: Institute of Geoecology and Geoinformation, Adam Mickiewicz University in Poznań, Poznań, Poland
Szpikowshi Józef: Institute of Geoecology and Geoinformation, Adam Mickiewicz University in Poznań, Poznań, Poland

Quaestiones Geographicae, 2025, vol. 44, issue 3, 159-173

Abstract: Environmental monitoring requires effective data collection, management and presentation. With the increasing amount of monitoring data, it is becoming increasingly important to develop tools for effective data management and visualisation. This paper explores the potential of integrating the PostgreSQL database system with the R environment to automate the processing, analysis and reporting of multidimensional environmental data. The results of hydrological monitoring conducted as part of the Integrated Monitoring of the Natural Environment (ZMŚP) programme were used as a case study. The basic component of the ZMŚP programme’s IT system is a relational database, where the results of environmental monitoring are stored. This database serves as a data source for the data warehouse. The data processing process, which includes archiving, verification and aggregation, uses Structured Query Language (SQL) and the procedural language PL/pgSQL. In order to generate interactive visualisations and automate reporting, the R programming environment was used in conjunction with the R Markdown tool and the plotly library. The combination of the PostgreSQL system with the plotly package in the R environment offers a number of benefits in terms of data visualisation and analysis, while also serving as an example of the use of Online Analytical Processing (OLAP) tools in the analysis and presentation of environmental data. The use of open-source solutions not only significantly reduces implementation costs but also increases the availability of technology to a wide range of users, including public institutions involved in environmental monitoring.

Keywords: hydrological monitoring; database; PostgreSQL; R; reporting; ZMŚP (search for similar items in EconPapers)
Date: 2025
References: View complete reference list from CitEc
Citations:

Downloads: (external link)
https://doi.org/10.14746/quageo-2025-0032 (text/html)

Related works:
This item may be available elsewhere in EconPapers: Search for items with the same title.

Export reference: BibTeX RIS (EndNote, ProCite, RefMan) HTML/Text

Persistent link: https://EconPapers.repec.org/RePEc:vrs:quageo:v:44:y:2025:i:3:p:159-173:n:1011

DOI: 10.14746/quageo-2025-0032

Access Statistics for this article

Quaestiones Geographicae is currently edited by Andrzej Kostrzewski

More articles in Quaestiones Geographicae from Sciendo
Bibliographic data for series maintained by Peter Golla ().