The development of geographical information systems (GIS) software programs has increased the availability of a number of computer-aided methods suitable for developing estimations of the shoreline change. Most of the solutions provide ready GIS-tools dedicated to shoreline analysis, while the development of the programming languages, spatial databases, and programming libraries also allows researchers to create their own tools and to perform estimations with full control throughout the code. The main aim of this study is to introduce a new method of estimation of shoreline change and to present an example of its use and the results obtained. The selected shoreline of Polish part of Uznam Island and Wolin Island as case study has been subject of many previous studies (Kostrzewski and Zwoliński 1988, 1995, Kostrzewski et al. 2015, Terefenko et al. 2018), that confirmed distinct cliff retreat in Wolin Island.
The GIS tool presented here enables similar estimations of the rate of shoreline change from multiple historical shorelines as it is calculated by the Digital Shoreline Analysis System (DSAS) software extension of ArcGIS (Thieler et al. 2009) or the Analysing Moving Boundaries Using R (AMBUR) package of the R software environment (Jackson et al. 2012). There are many studies that present examples of the application of the DSAS tool in the detection of shoreline changes in different parts of the world (Ahmad and Lakhan 2012, Chaaban et al. 2012, Montreuil and Bullard 2012, Liu et al. 2013, Río et al. 2013, Kolega 2015). The questions in social forums about an open-GIS tool that works similarly to DSAS highlight the need to develop tools and methods that perform estimation of the shoreline change, especially in open-source GIS applications, while allowing users to control and modify the processes of the calculation and that are available without a commercial license. The solution presented is based on Structured Query Language (SQL) and the PostgreSQL database system (Obe and Hsu 2012) with a PostGIS spatial extension.
PostgreSQL is a well-known open source database management system that allows users to store data in a database structure, perform management and analysis using the SQL statements, and create analytical functions. PostGIS is a spatial database extender for PostgreSQL system that has the ability to store spatial data in the tables and perform spatial analysis. The analytical possibilities of PostGIS make the database a powerful GIS tool. The calculations performed were based on the
The
A similar approach to measure changes in shoreline position was performed by Chaaban et al. (2012) using ArcGIS commercial software. The presented attempt does not require a commercial license and installation of additional packages but only the open-source database system with the spatial extension and, optionally, the R statistical environment. The user have access to the whole source code and also can modify the code to suit his needs. The output of the program is the transects table, which stores the geometry of the transects and all results of calculations. Such a solution offers the user a fast and simple visualisation of the results and the option of performing additional calculations. Furthermore, the transect lines are a result of cutting along the shortest lines the envelope containing all analysed shorelines. The envelope is created from the baseline and their offset line. The shape of the baseline is independent from the shape of the analysed shorelines but depends only on the points along the shore prepared by the user, and thereby measurements are not affected by interruption of the shoreline at estuaries and the curved shape of the shoreline.
The methodology for estimation of shoreline movement is based on the SQL functions executed in the presented example of the spatial database. The code of functions and the execution procedure are detailed below.
The example shoreline comprises 37 km of the western part of the Polish coastline along the Baltic Sea (Fig. 1). The digitalisation of the shoreline was performed in the QGIS open geosciences application. The shorelines were retrieved from topographic map at 1:10,000 scale re-projected from the geodetic spatial reference system 1965/3 (SRID: 2173) with actualization in the years 1987 to 1989 and the third orthophoto map was made from aerial photos from 2010. All maps are available at the Polish Geoportal as the Web Map Service (WMS) and are provided by the Polish Head Office of Geodesy and Cartography.
The presented example of shoreline changes analysis was based on the lines retrieved from different data sources of varying quality: topographic maps and orthophotographic images. The dune/cliff foot line in the maps and orthophotographic images was identified according to erosion reference features such as the top edge of a bluff, dune escarpment, or vegetation line (Crowell et al. 2005). Digitalised shorelines from scanned and georeferenced topographic maps at 1:10,000 scale provide a position error of ± 10 m, while digitalisation of orthophoto maps with a pixel size of 0.5 m provides a maximal error of ± 5 m. It should be taken into account that the error margin doubled when comparing the results of shoreline position from different data sources, and in the presented case the total position error was estimated as a maximum of around ± 15 m.
The geospatial data were stored in the PostgreSQL database management system with the spatial extension PostGIS (Obe and Hsu 2015). The spatial extension enables the database to store spatial geometry types and execute spatial analysis with the support of the special functions and SQL statements. The first step in the preparation of the database was the creation of spatial tables suitable for storing the geometry of the shoreline with attributes. The spatial tables were used as the store of the vector lines instead of the ESRI shapefile format method that is often used. Obviously the user can import previously prepared vector data in the shapefile format to the database system. The result of the import will be stored as a spatial table. The shoreline table should be prepared for each time period.
In the presented case, the three tables were prepared to store shorelines digitalised from maps from three time periods and one table to store kilometric points of the shoreline. All the tables were stored in the schema container called
The analytical procedure and outputs are presented in a workflow diagram (Fig. 2), while all necessary statements are presented in Figure 3. The first step of the procedure is the creation of a table to store the transect lines and calculation results, called for example
The presented functions were written in the PL/SQL procedural language. Before starting the computations it was necessary to load into the database the following functions:
The input arguments of the
The example of the resultant transect lines is presented in Figure 5. The resultant transects are labelled by kilometre names using the function
The function
The best way to visualise the results is through the R statistical environment (Kabacoff 2011) connected to the PostgreSQL database. The database driver and standard R graphical functions perform data visualisation techniques loaded into the R data frame from a database table. The measurements of the rate of shoreline changes per year between different shoreline positions were made using the end-point rate method (Dolan et al. 1991). The necessary statements executed in the R environment are presented in Figure 9. Graphical plots of the results retrieved from the database table are presented in Figure 10 as plot of change during analysed period with error margin of ±15 m and rate of annual shoreline change with error margin of ±0.65 m.
Another method of visualisation is presenting of shoreline evolution at the map as coloured transects styled according value of determined change during observed period (Fig. 11). The colours represents intervals of adopted classification: strong advance – above 50 m, advance from 15 m to 50 m, fluctuation of advance and retreat in error margin from −15 m to 15 m and retreat below −15 m. The westernmost part of analysed shoreline between 428 and 412 km experienced distinct advance. This section is characterised as a typical foredune coast, with high accretion rates from 1 to 4 m a−1. The position of the breakwater of the Świnoujście harbour (425–424 km) is visible as a gap in the plot (Fig. 10) and displays lower accumulation rates than the rest of the area. The eastern part of the breakwater (424–423 km) is the location of the gas terminal built in 2006 and confirmed distinct increase of accumulation processes in this part of the coast. The strong accumulation in this section of the shoreline was confirmed in previous investigations for a longer analysed period of 58 years (Dudzińska-Nowak and Furmańczyk 2005). The shoreline between 391 and 412 km is the cliff coast of Wolin Island and revealed slight retreat of shoreline position particularly between 411 and 408 km and between 405 and 397 km. Some interesting characteristic of this section include the high accretion of shoreline at the mouth of the Dziwna Inlet at 391 km, where the distinct advance of the shore was estimated at 3 m a−1.
The presented study has introduced a new solution for calculations of shoreline change based on the database system PostgreSQL and using spatial functions PostGIS and SQL. The presented methodology was tested on the Polish western coast of the Baltic Sea. Application of the spatial database system to the estimation of shoreline changes highlights the great potential and flexibility of such tools in geospatial analyses, which were previously underestimated. The most important advantages of the presented solution are the database storage method, the open source nature of the applied applications, and full control of the analysis throughout the SQL code. Thanks to this, the user can define custom values of parameters of analysis such as the position and geometry of transects or type of analysed line. In the presented solution nothing is a