Improving Management of Spatial Data through Spatial Database

Proceeding Paper Improving Management of Spatial Data through Spatial Database † Nikola Kranjčić 1,*, Bojan Đurin 2, Dragana Dogančić 1 and Lucija Plantak 1 1 Faculty of Geotechnical Engineering, University of Zagreb, 42000 Varaždin, Croatia; [email protected] (D.D.); [email protected] (L.P.) 2 Department of Civil Engineering, University North, 42000 Varaždin, Croatia; [email protected] * Correspondence: [email protected] † Presented at the 3rd International Electronic Conference on Geosciences, 7–13 December 2020; Available online: https://iecg2020.sciforum.net/. Abstract: Entering the European Union, the Republic of Croatia took over the INSPIRE directive called the National Spatial Data Infrastructure. A large amount of spatial data can be found through the National Spatial Data Infrastructure Geoportal. Data are available for viewing or downloading via different services, such as a web mapping service or web feature service. Although different spatial data are available, it is hard to access useful information through Geoportal. The aim of this paper is to prepare a spatial database which will gather different spatial data related to environ- mental engineering and present different queries and the visualization of the results. The main data used are related to protected areas in the Republic of Croatia, which register the environmental pollutants, air quality, exploitation and research fields of mineral resources, waste management, water management, and so forth. Alongside the national spatial data, the Copernicus Land moni- toring service EU-DEM, the digital elevation model, is used. The classification of Sentinel-2 MSI data is used to provide land cover. Remotely sensed data are used in queries where aspect, slope, and land cover affect the results. Two predefined SQL queries are discussed. The first query discusses Citation: Kranjčić, N.; Đurin, B.; the danger of landslides, and the second query discusses threats from illegal landfills and the effect Dogančić, D.; Plantak, L. Improving that they have on the environment. Predefined SQL queries enable users to quickly access needed management of spatial data through data, even when the original data is updated. All data, databases, visualization, and results are pre- spatial database. Environ. Sci. Proc. sented in open access software. 2021, 5, 5. https://doi.org/10.3390/ IECG2020-08865 Keywords: INSPIRE directive; Copernicus mission; spatial database; predefined queries; environ- mental engineering Academic Editor: Jesus Martinez Frias Published: 2 December 2020 1. Introduction Publisher’s Note: MDPI stays neu- Today, satellite missions (Copernicus, Landsat, etc.) allow continuous monitoring of tral with regard to jurisdictional the Earth’s surface, which allows us to quickly collect data on space [1]. The negative im- claims in published maps and institu- pact on the environment and ecosystems and the emergence of climate change are very tional affiliations. current topics in the last five years. There is growing evidence that the Republic of Croatia is vulnerable to climate change because it largely belongs to the Mediterranean region where changes are most noticeable in the economic sector (agriculture, forestry, fisheries, energy, tourism) because the success of the same sectors depends on the climate factors [2]. Copyright: © 2020 by the authors. Various tools can help us to continuously monitor the state of the environment. In Licensee MDPI, Basel, Switzerland. This article is an open access article this area of research, a science that can be very useful in environmental engineering, called distributed under the terms and geoinformatics, is increasingly being used. Geoinformatics is considered a profession of conditions of the Creative Commons the future and is increasingly advancing in terms of information infrastructure. Geoinfor- Attribution (CC BY) license matics technologies include the Geographic Information System (GIS), the Global Navi- (http://creativecommons.org/licenses gation Satellite System (GNSS), remote sensing, and spatial databases. /by/4.0/). Environ. Sci. Proc. 2021, 5, 5. https://doi.org/10.3390/IECG2020-08865 www.mdpi.com/journal/environsciproc Environ. Sci. Proc. 2021, 5, 5 2 of 6 In most countries, environmental management requires development projects for en- vironmental impact assessment—EIA (Environmental Impact Assessment), which entails the need for basic research and data collection that can be useful in predicting environ- mental impact with respect to the proposed project. Thus, the collection of data on basic research requires a model of monitoring. The authors of [3] presented the Before-After- Control-Impact (BACI) model, a model that allows for easier assessment when deciding on the impact of a particular activity on the environment at a particular location. Due to the growth of the human population and the pressure that has a negative effect on the Earth’s resources, the planet’s environment is changing at an alarming rate, which requires the establishment of monitoring measures. Environmental monitoring serves to assess the effectiveness of environmental legislation or policy, to monitor and assess compliance with regulatory legislation established for environmental protection, (e.g., to monitor that the discharge from a particular plant flowing into a given river is treated as default standard) and to detect changes in the environment (e.g., vegetation change for early warning purposes) [4]. For this reason, it is important to build a solid database with all data on vegetation, soil type, fire frequency, and area temperature to ensure easier monitoring and control of the area. The Copernicus Atmospheric Monitor- ing Service has provided the results of an atmosphere monitoring survey following a fire in the African Circle. Total carbon emissions by 2019 are equivalent to Sweden’s total an- nual emissions of 50 megatons of CO2. The average carbon emissions caused by fires per year are 7.7 gigatons, which is approximately 25% of the total annual carbon emissions from fossil fuel combustion [5]. The European Union’s Copernicus program is designed to enable monitoring of the Earth and the state of the environment. The program’s data policy ensures full, open, and free access to data and information in accordance with the international principles for data exchange of the Group for Earth Observation (GEO) [6]. The Copernicus program supports a variety of applications in several domains that potentially impact companies and organizations in day-to-day activities: agriculture, the blue economy, climate change and the environment, development and collaboration, en- ergy and natural resources, forestry, health, quality assurance and management, security and defense, tourism, transport, and urban planning. The European Space Commission is responsible for the operation and safety of satellite systems, and the European Environ- ment Agency is responsible for the in situ component of the work [6]. With the help of spatial data taken from the Copernicus service and the PostgreSQL database system, the existing problem with waste disposal in the Republic of Croatia will be investigated and potential locations for the construction of a regulated landfill will be determined given the number of people in each county. 2. Experiments National Spatial Data Infrastructure Geoportal (NSDI Geoportal) is the starting point for finding spatial data of the Republic of Croatia. The State Geodetic Administration of the Republic of Croatia presented Geoportal in 2014 for the first time. The portal consists of a Metadata Catalog and a spatial data viewer that facilitates the process of searching and retrieving spatial data. The ultimate goal of the NSDI Geoportal is to consolidate the described information on all spatial data and make it accessible and shared in a simple way using the catalog service. Geoportal belongs to the category of open source technology and currently 415 sources with metadata are available through network services, which are free to down- load [7]. In addition to the data from the Copernicus service, data from the website of NSDI Geoportal were also collected. Copernicus service data that is downloaded is used to fur- ther visualize results and expand queries with additional data such as the digital terrain model or land cover. The data used for this research belong to the branch of Environmental Engineering [8]: Environ. Sci. Proc. 2021, 5, 5 3 of 6  Air quality in the Republic of Croatia—WFS  Central Landfill Information Management System—WFS  Census of Population, Households and Dwellings 2011—WMS For the purposes of working with spatial data in PostgreSQL, the following steps were followed: Step 1—download spatial data; Step 2—format-save download .wfs (Web Feature Service) data in ESRI .shp (ShapeFile) format; Step 3—install PostGIS and create a spatial database Step 4—load spatial data in pgAdmin-PostGIS ShapeFile Import, QGIS; Step 5—make spatial queries with real data Step 6—visualize spatial data After all the data are uploaded into spatial database, different spatial queries can be made. For the purpose of this paper only two queries will be presented. The first query needs to determine which biogas plants are not licensed and are located within counties with waste volumes greater than 2000 tons during the summer time. The second query should answer which landfills are currently being rehabilitated and to which counties do they belong? 3. Results First query results are shown in Figure 1, where green dots present unlicensed biogas plants. Figure 1. Biogas plants that are not licensed. Second query results are presented in Figure 2, where blue dots present landfills. Environ. Sci. Proc. 2021, 5, 5 4 of 6 Figure 2. Rehabitated landfills. Results from the two queries show that collected data can be visually presented. However, what can not be seen from the results is that all the queries can be saved and used after the data is updated. For these types of queries, users are usually administrative counties which are responsible for decision making. They can use this data in order to improve their analytics and select the best solution. Data can be updated per day, per month, or for any other period of time when user consider it is necessary to update the database. Furthermore, the advantage of the spatial database is that it can be filled with different data types and accessed from any part of the administrative area. However, there are also limitations to this type of analysis, since as mentioned before, data can be updated in various time frames. Therefore, users should be careful while using these types of data to provide a correct decision. A spatial database, as previously mentioned, can be filled with Copernicus imagery in an RGB color composition. Figure 3 presents an RGB view of one of the rehabitated landfills presented in Figure 2, where the red circle presents the rehabitated landfill. From Figure 3 can be seen a landfill and its surroundings. In that way, Copernicus data are utilized in order to provide the user with visual terrain presentation and gain more information about specific area (rivers, lakes, forests, or any other objects). Environ. Sci. Proc. 2021, 5, 5 5 of 6 Figure 3. Copernicus Sentinel-2 RGB imagery. 5. Conclusions Given the results, we can conclude that SQL is very useful and easy to use in working with spatial data. The great advantage of this type of SUBP is that it belongs to the group of open source software and thus facilitates the data loading, manipulation, and visuali- zation of spatial data. Working with spatial data is not demanding because spatial data- base management systems are treated the same as other data types. It can be concluded that PostGIS contains a very good graphical user-friendly interface. It allows easy work with the database and, in a way, hides the main background about working with data (e.g., indexing, coordinate notation, and data display). Today, there are many tips on the internet related to learning the basic functions of databases and types of data manipula- tion. Designing a database, changing the structure of a database, and performing SQL queries with simple syntax are the basics that are sufficient for the purposes of creating this paper. For this reason, the PostgreSQL database system is suitable for use in practice when solving important environmental problems. In order to create useful queries that will give us an answer to a particular environmental problem, it is important to find or create quality spatial data, and it is necessary to understand the syntax of the SQL pro- gramming language. Without basic spatial bases such as spatial units, settlements, coun- ties, and so forth, it is difficult to imagine spatial analyzes, and for this reason, data syn- ergy is important. PostgreSQL shows with practical examples that it represents a serious competition to commercial databases in the market because it is dynamic, easy to use by users, and interesting to work with. Author Contributions: N.K. conceived and designed the experiments; N.K. performed the experi- ments; N.K. and B.Đ. analyzed the data; D.D. and L.P. contributed reagents/materials/analysis tools; N.K. wrote the paper. All authors have read and agreed to the published version of the manuscript. Conflicts of Interest: The authors declare no conflict of interest. Environ. Sci. Proc. 2021, 5, 5 6 of 6 References 1. Land Monitoring Service. Available online: https://land.copernicus.eu/ (accessed on 25 November 2020). 2. Mahečić Bajović, I. Climate Change in Croatia: New Human Development Report Launched. Available online: https://www. undp.org/content/undp/en/home/presscenter/pressreleases/2009/02/16/climate-change-in-croatia-new-human-development- report-launched.html (accessed on 22 November 2020). 3. Downes, B.J.; Barmuta, L.A.; Fairweather, P.G.; Faith, D.P.; Keough, M.J.; Lake, P.S.; Mapstone, B.D.; Quinn, G.P. Monitoring Ecological Impacts: Concepts and Practise in Flowing Waters, 1st ed.; Cambridge University Press: Cambridge, UK, 2002; ISBN 9780521771573. 4. Awange, J.L.; Kyalo Kiema, J.B. Environmental Geoinformatics: Monitoring and Managent. Environ. Geoinform. Environ. Sci. Eng. 2013, 541, doi:10.1007/978-3-642-34085-7. 5. Greenpeace International. Lost in Smoke: Wildland Fire Climate Impact Case Studies of Brazil, Indonesia and Russia; Greenpeace International: Amsterdam, Netherlands, 2018; Volume 28. 6. European Union Earth Observation Programme-Copernicus. What Is Copernicus? Available online: https://emergency.copernicus.eu/mapping/ems/what-copernicus (accessed on 16 April 2021) 7. State Geodetic Administration. Geoportal National Spatial Data Infrastructure; State Geodetic Administration, Zagreb, Croatia: 2014. 8. State Geodetic Administration. Geoportal National Spatial Data Infrastructure Spatial data for Republic of Croatia; State Geodetic Administration. Available online: https://geoportal.nipp.hr/ (accessed on 16 April 2020).