Keywords
database
PostgreSQL
R
reporting
ZMŚP
How to Cite
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.
References
30th Annual Report. 2021. Convention on long-range transboundary air pollution. In: Kleemola S., Forsius M. (eds), International cooperative programme on integrated monitoring of air pollution effects on ecosystems. Finnish Environment Institute: 1-68. Online: http://hdl.handle.net/10138/333713 (accessed 11 April 2025).
Albers S. J. 2018. Tidyhydat: Extract and tidy Canadian hydrometric data. Journal of Open Source Software 3(21): 511. DOI: https://doi.org/10.21105/joss.00511
Bajkiewicz-Grabowska E., Magnuszewski A., Mikulski Z., 1993. Hydrometria. Wydawnictwo Naukowe PWN, Warszawa.
Berrahou L., Lalande N., Serrano E., Molla G., Berti-Équille L., Bimonte S., Bringay S., Cernesson F., Grac C., Ienco D., Le Ber F., Teisseire M., 2015. A quality-aware spatial data warehouse for querying hydroecological data. Computers and Geosciences 85: 126-135. DOI: https://doi.org/10.1016/j.cageo.2015.09.012
Bochenek W., Gudowicz J., 2021. Modelowanie zmian bilansu wodnego i biogeochemicznego dla zlewni reprezentatywnych ZMŚP (Modelling changes in water and biogeochemical balance for the representative catchments of the IMNE). In: Kostrzewski A., Majewski M. (eds), Zintegrowany Monitoring, organizacja, system pomiarowy, metody badań. Wytyczne do realizacji. Biblioteka Monitoringu Środowiska, Bogucki Wydawnictwo Naukowe, Warszawa: 327-343.
Bollen E., Brianna R.P., Kuijpers B., van Hoey S., Desmet N., Hendrix R., Dams J., Seuntjens P., 2022. A database system for querying of river networks: Facilitating monitoring and prediction applications. Water Supply 22(3): 2832-2846. DOI: https://doi.org/10.2166/ws.2021.433
Boulil K., Bimonte S., Pinet F., 2015. Conceptual model for spatial data cubes: A UML profile and its automatic implementation. Computer Standards and Interfaces 38: 113-132. DOI: https://doi.org/10.1016/j.csi.2014.06.004
Boulil K., Le Ber F., Bimonte S., Grac C., Cernesson F., 2014. Multidimensional modeling and analysis of large and complex watercourse data: An OLAP-based solution. Ecological Informatics 24: 90-106. DOI: https://doi.org/10.1016/j.ecoinf.2014.07.001
Budka A., Kayzer D., Pietruczuk K., Szoszkiewicz K., 2013. Zastosowanie wybranych procedur do wykrywania obserwacji nietypowych w ocenia jakości rzek (Application of selected procedures to detect abnormal observations in river quality assessment). Infrastruktura i Ekologia Terenów Wiejskich 3/II, Polska Akademia Nauk, Oddział w Krakowie, Komisja Technicznej Infrastruktury wsi: 85-95.
Carleton J.C., Dahlgren R.A., Tate K.W., 2005. A relational database for the monitoring and analysis of watershed hydrologic functions: I. Database design and pertinent queries. Computers and Geosciences 31: 393-402. DOI: https://doi.org/10.1016/j.cageo.2004.10.007
Conway J., Eddelbuettel D., Nishiyama T., Prayaga S.K., Tiffin N. 2025. RPostgreSQL: R interface to the ‘PostgreSQL’ database system. R package version 0.7.8. Online: https://cran.r-project.org/web/packages/RPostgreSQL/RPostgreSQL.pdf (accessed 17 April 2025).
Czernecki B., Głogowski A., Nowosad J., 2020. Climate: An R package to access free in-situ meteorological and hydrological datasets for environmental assessment. Sustainability 12(1): 394. DOI: https://doi.org/10.3390/su12010394
De Cicco, L.A., Hirsch, R.M., Lorenz, D., Watkins, W.D., Johnson, M., 2025. dataRetrieval: R packages for discovering and retrieving water data available from U.S. federal hydrologic web services.
Directive 2000/60/EC of the European Parliament and of the Council of 23 October 2000 establishing a framework for Community action in the field of water policy, Official Journal L 327, 22.12.2000: 1-73.
Dynowska I., 1997. Reżim odpływu rzecznego (River discharge regime). In: Atlas Rzeczypospolitej Polskiej. Główny Geodeta Kraju, Warszawa.
Gebhardt S., Wehrmann T., Klinger V., Schettler I., Huth J., Künzer C., Dech S., 2010. Improving data management and dissemination in web based information systems by semantic enrichment of descriptive data aspects. Computers and Geosciences 36: 1362-1373. DOI: https://doi.org/10.1016/j.cageo.2010.03.010
Goetz J., Schwarz C.J., 2024. fasstr: Analyze, Summarize, and Visualize Daily Streamflow Data. R package v.0.5.3. Online: cran.r-project.org/package=fasstr (accessed 09 June 2025).
Johansson K., Söderman G., Bremer P., Juntto S., Laurila T., Westling O., Bringmark L., Bringmark E., Eriksson L., Bråkenhielm S., 1990. Annual synoptic report 1990. Pilot programme on integrated monitoring. Convention on long-range transboundary air pollution. Environment Data Centre National Board of Waters and the Environment, Helsinki.
Kejna M., Strzyżewski T., 2014. Water resources of the Struga Toruńska River (Central Poland) in the context of climatic changes in 1994-2012. International Journal of Earth Sciences and Engineering 7(1): 74-79.
Kijowska M., Bochenek W., 2011. Dynamics of suspended material carried out from the Flysch Bystrzanka catchment during selected rainfall events in the period 1997-2008. Quaestiones Geographicae 30(1): 47-56. DOI: https://doi.org/10.2478/v10117-011-0004-2
Kostrzewski A., 1993. Geoekosystem obszarów nizinnych. Koncepcja metodologiczna (Geoecosystem of lowland areas. A methodological concept). In: Kostrzewski A. (ed.), Geoekosystem obszarów nizinnych, Vol. 6. Komitet Naukowy przy Prezydium PAN Człowiek i Środowisko, Ossolineum, Wrocław, Kraków: 11-17.
Kostrzewski A., Dmowska A., Stach A., Mazurek M., Zwoliński Z., 2016. Spatial variability of physical and chemical properties of river water in the upper Parsęta catchment during rising spring flows. Prace Geograficzne 144: 49-67.
Kostrzewski A., Kruszyk R. (eds), 2006. Stan, przemiany i funkcjonowanie geoekosystemów Polski w latach 1994-2004 na podstawie Zintegrowanego Monitoringu Środowiska Przyrodniczego (The state, changes and functioning of the geoecosystems of Poland in the years 1994-2004 on the basis of Integrated Monitoring of the Natural Environment). Biblioteka Monitoringu Środowiska, Warszawa.
Kostrzewski A., Majewski M. (eds), 2018. Stan i przemiany środowiska przyrodniczego Polski w latach 1994-2015 w oparciu o realizację programu Zintegrowanego Monitoringu Środowiska Przyrodniczego (The state and transformation of Poland’s natural environment in 1994-2015 based on the implementation of Integrated Monitoring of the Natural Environment). Biblioteka Monitoringu Środowiska, Warszawa.
Kostrzewski A., Majewski M. (eds), 2021. Zintegrowany Monitoring Środowiska Przyrodniczego: organizacja, system pomiarowy, metody badań, wytyczne do realizacji (Integrated Monitoring of the Natural Environment: Organisation, measurement system, survey methods, implementation guidelines). Bogucki Wydawnictwo Naukowe, Warszawa.
Kostrzewski A., Mazurek M., Stach A., 1995. Zintegrowany Monitoring Środowiska Przyrodniczego. Zasady organizacji i system pomiarowy, wybrane metody badań (Integrated Monitoring of the Natural Environment. Principles of organisation and measurement system, selected survey methods). Biblioteka Monitoringu Środowiska, Warszawa.
Kostrzewski A., Mazurek M., Zwoliński Z., 1994. Dynamika transportu fluwialnego górnej Parsęty jako odbicie funkcjonowania systemu zlewni (Fluvial transport dynamics of the upper Parsęta as a reflection of the functioning of the catchment system). Bogucki Wydawnictwo Naukowe, Poznań.
Kostrzewski A., Mizgajski A., Stępniewska M., Tylkowski J., 2014. The use of integrated environmental programme for ecosystem services assessment. Ekonomia i Środowisko 4(51): 94-101.
Kostrzewski A., Szpikowski J., Szpikowska G., 2011. Geoecosystems Polish state – An assessment based on selected geoindicators in the Integrated Monitoring of the Natural Environment programme. Monitoring Środowiska Przyrodniczego 12: 69-83.
Kruszyk R., 2023. Architektura i eksploracja wielkich wolumenów danych w monitoringu Środowiska przyrodniczego (Architecture and exploration of large data volumes in environmental monitoring). Studia i Prace z Geografii 96. Bogucki Wydawnictwo Naukowe, Poznań.
Lenartowicz M., 2021. Program H1-Wody powierzchniowe-rzeki (Programme H1-Surface water-rivers). In: Kostrzewski A., Majewski M. (eds), Zintegrowany Monitoring, organizacja, system pomiarowy, metody badań. Wytyczne do realizacji. Biblioteka Monitoringu Środowiska, Bogucki Wydawnictwo Naukowe, Warszawa: 195-228.
Likens G.E., Lindenmayer D.B., 2018. Effective ecological monitoring. CSIRO Publishing. DOI: https://doi.org/10.1071/9781486308934
Lovett G.M., Burns D.A., Driscoll C.T., Jenkins J.C., Mitchell M.J., Rustad L., Shanley J.B., Likens G.E., Haeuber R., 2007. Who needs environmental monitoring? Frontiers in Ecology and the Environment 5(5): 253-260. DOI: https://doi.org/10.1890/1540-9295(2007)5[253:WNEM]2.0.CO;2
Marks L., 2005. Pleistocene glacial limits in the territory of Poland. Przegląd Geologiczny 53(10/2): 988-993.
Mazurek M., 2011. Geomorphological processes in channel heads initiated by groundwater outflows (The Parsęta catchment, north-western Poland). Quaestiones Geographicae 30: 33-45. DOI: https://doi.org/10.2478/v10117-011-0025-x
Mazurek M., Paluszkiewicz R., Zwoliński Z., 2024. Glacial and postglacial landforms of the Drawsko Lakeland. In: Migoń P., Jancewicz K. (eds), Landscapes and landforms of Poland. Springer, Cham. DOI: https://doi.org/10.1007/978-3-031-45762-3_35
McGuire M., Gangopadhyay A., Komlodi A., Swan Ch., 2008. A user-centered design for a spatial data warehouse for data exploration in environmental research. Ecological Informatics 3: 273-285. DOI: https://doi.org/10.1016/j.ecoinf.2008.08.002
Mocek A., 1994. Toposekwencje gleb użytkowanych rolniczo w zlewni górnej Parsęty (Toposequences of agriculturally used soils in the upper Parsęta catchment area). In: Kostrzewski A. (ed.), Zintegrowany Monitoring Środowiska Przyrodniczego. Stacja Bazowa Storkowo. Biblioteka Monitoringu Środowiska, Warszawa: 105-113.
Ozga-Zielinska M., Brzeziński J., 1997. Hydrologia stosowana. Wydawnictwo Naukowe PWN, Warszawa.
Pinet F., Miralles A., Bimonte S., Vernier F., Carluer N., Gouy V., Bernard S., 2010. The use of UML to design agricultural data warehouses. AgEng: Agricultural Engineering, Clermont-Ferrand, France. .
Pinet F., Schneider M., 2010. Precise design of environmental data warehouses. Operational Research 10: 349-369. DOI: https://doi.org/10.1007/s12351-009-0069-z
R Core Team 2024. R: A language and environment for statistical computing. R Foundation for Statistical Computing, Vienna, Austria.
Sievert C., 2020. Interactive web-based data visualization with R, plotly, and shiny. Chapman and Hall/CRC, New York. DOI: https://doi.org/10.1201/9780429447273
Solon J., Borzyszkowski J., Bidłasik M., Richling A., Badora K., Balon J., Brzezińska-Wójcik T., Chabudziński Ł, Dobrowolski R., Grzegorczyk I., Jodłowski M., Kistowski M., Kot R., Krąż P., Lechnio J., Macias A., Majchrowska A., Malinowska E., Migoń P., Myga-Piątek U., Nita J., Papińska E., Rodzik J., Strzyż M., Terpiłowski S., Ziaja W., 2018. Physico-geographical mesoregions of Poland: Verification and adjustment of boundaries on the basis of contemporary spatial data. Geographia Polonica 91(2): 143-170. DOI: https://doi.org/10.7163/GPol.0115
Szpikowski J., Majewski M., Madaj W., 2018. Conditions for soil erosion by water in the upper Parsęta catchment. Landform Analysis 36: 55-69. DOI: https://doi.org/10.12657/landfana.036.006
Tylkowski, J., Kostrzewski, A.; Rachlewicz, G. 2017. The state of geoecosystems in Poland in the year 2016 based on IMNE program. In: Kleemola S., Forsius, M. (eds), 26th Annual Report 2017. Finnish Environment Institute, Helsinki: 60-70.
Vuorenmaa J., Augustaitis A., Beudert B., Bochenek W., Clarke N., de Wit H.A., Dirnböck T., Frey J., Hakola H., Kleemola S., Kobler J., Krám P., Lindroos A.J., Lundin L., Löfgren S., Marchetto A., Pecka T., Schulte-Bisping H., Skotak K., Srybny A., Szpikowski J., Ukonmaanaho L., Váňa M., Åkerblom S., Forsius M., 2018. Long-term changes (1990-2015) in the atmospheric deposition and runoff water chemistry of sulphate, inorganic nitrogen and acidity for forested catchments in Europe in relation to changes in emissions and hydrometeorological conditions. The Science of the Total Environment 625: 1129-1145. DOI: https://doi.org/10.1016/j.scitotenv.2017.12.245
White D.L., Wolf D., Porter D.E., Sanger D.M., Riekerk G.H.M., DiDonato G., Holland A.F., Dabney D., 2008. Development of a data management framework in support of southeastern tidal creek research. Environmental Monitoring and Assessment 150: 323-331. DOI: https://doi.org/10.1007/s10661-008-0233-6
Wickham H., 2016. Ggplot2: Elegant graphics for data analysis. Springer-Verlag, New York. DOI: https://doi.org/10.1007/978-3-319-24277-4_9
Wickham H., Averick M., Bryan J., Chang W., McGowan L.D., François R., Grolemund G., Hayes A., Henry L., Hester J., Kuhn M., Pedersen T.L., Miller E., Bache S.M., Müller K., Ooms J., Robinson D., Seidel D.P., Spinu V., Takahashi K., Vaughan D., Wilke C., Woo K., Yutani H., 2019. Welcome to the Tidyverse. The Journal of Open Source Software 4(43): 1686. DOI: https://doi.org/10.21105/joss.01686
Wrzesiński D., 2018. Typologia reżimu odpływu rzek w Polsce w różnych fazach oscylacji północnoatlantyckiej (Typology of the outflow regime of rivers in Poland in different phases of the North Atlantic Oscillation). Badania Fizjograficzne 9(A69): 249-261. DOI: https://doi.org/10.14746/bfg.2018.9.19
Xie Y., 2015. Dynamic documents with R and knitr. Chapman and Hall/CRC, Boca Raton, London, New York. DOI: https://doi.org/10.1201/b15166
Xie Y., Allaire J., Grolemund G., 2018. R Markdown: The definitive guide. Chapman and Hall/CRC, Boca Raton, FL, USA. DOI: https://doi.org/10.1201/9781138359444
Zambrano-Bigiarini M., 2024. hydroTSM: Time series management, analysis and interpolation for hydrological modelling. R package version 0.7-0.1. Online github.com/hzambran/hydroTSM (accessed 09 June 2025).
Zhu H., 2024. kableExtra: Construct complex table with ‘kable’ and pipe syntax. R package version 1.4.0. Online: https://doi.org/10.32614/CRAN.package.kableExtra (accessed 9 June 2025). DOI: https://doi.org/10.32614/CRAN.package.kableExtra
License
Copyright (c) 2025 Robert Kruszyk , Anna Dmowska, Mikołaj Majewski, Józef Szpikowski
This work is licensed under a Creative Commons Attribution 4.0 International License.