Application of the Spatial Database for Shoreline Change Analysis and Visualisation: Example from the Western Polish Coast, Southern Baltic Sea

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.

Fig. 1

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 gis. The tables included three columns: gid as the unique identifier (ID) of the object, type as the description of the kind of line (the waterline or the footline of the foredune or cliff), and geom as a binary representation of the geometry. The type of geometry was set as linestring in the Polish geodetic spatial reference system 1992 (ETRS89/Poland CS92) with spatial reference identifier (SRID) 2180. The supplementary table consisting of kilometric points of the Polish coast digitalised from topographic maps was called gis.kilometre. The gis.kilometre table contained the following columns: km as a unique ID and geom as a binary representation of the geometry. The table of kilometric points is the base from which the transects are created, and in cases where the shoreline is not a straight line, the points should be placed along the shoreline at a greater density than one per kilometre. Digitalised vector layers of the shorelines were stored in the separate tables shoreline_1987, and shoreline_2010. Each table included the geometry of two lines: the waterline and the footline of the foredune or cliff line. The waterline is an unstable boundary between land and sea that is controlled by the wave conditions, while the best independent proxy for shoreline determination is the footline of the foredune or cliff (Dudzińska-Nowak and Furmańczyk 2005, Río et al. 2013). The functions allow the user to select the type of analysed line. All calculations of shoreline changes were based on the footline.

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 transects_table. The created table should contain at least the fields prepared for the unique ID and geometry of objects. The following steps create the geometry of the transects by inserting statement and function transects with arguments presented in Figure 3 and adding labels to create transect objects using the km_label function in an update statement. An example of the transect lines created is presented in Figure 5. The transects table can also store the results of calculations in the separate fields. In the presented example, the dist_foot double precision field was prepared, ready to store the calculated distances between footlines. The calculations were performed by the function shoreline_dist and the results were inserted using the update statement (see Fig. 3).

Fig. 2

Fig. 3

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: transects (all the names of presented functions and tables used in the SQL statements should contain the prefix of the schema name), which created the geometry transects; coast_diff, which performed the shoreline difference calculations; and km_label, a transect-labelling function. The example of database elements ready to calculations is presented in Figure 4. All required functions and statements are presented in Figures 5–8.

Fig. 4

Fig. 5

Fig. 6

Fig. 7

Fig. 8

The input arguments of the transects function (Fig. 6) are the length as an integer value of the transect length (the transect lines should intersect all analysed shorelines); tinterval as an integer value of the interval between the created transects; from_km as an integer value of the first kilometre of analysed coast (this value requires the km field in the kilometre table); to_km as an integer value of the final kilometre of analysed coast; names of schema containers (sche); and the table (tbl) with digitalised kilometre points along the analysed coast. The output arguments consisted of gid as a unique ID and geom as the geometry of the transect object. The transects function works in the following sequence of the PostGIS functions: ST_Makeline, ST_Segmentize, and ST_DumpPoints. The function ST_Makeline creates the baseline along the shoreline between kilometre points, while the function ST_Segmentize divides the baseline into segments at intervals selected by the user. The segments were transformed into points by the ST_DumpPoints function called point_track. The reference line from the kilometre points should not intersect with the analysed shorelines. The next step of the function is to make an offset line called track using ST_OffsetCurve and ST_Makeline and a value length, which is the distance of the offset. The track and point_track point lines should bound the area that contains all analysed shorelines. The final step of the procedure is to make the transect line geometry using ST_Closest point and the condition of searching ST_DWithin with the selected distance length enlarged by 50 m.

The example of the resultant transect lines is presented in Figure 5. The resultant transects are labelled by kilometre names using the function km_label. The supplemental function km_label made labels for transects according to kilometre markings along the shoreline (Fig. 7). The main task of the function is to calculate distances between transects and assign kilometre labels according to the real kilometre mark supplied by the function start_km. Transects were labelled in ascending order from the right side, as in the case of the Polish coastline. The code of function is flexible and allow select custom parameters of calculation such as density of transects (tinterval), the length of transects lines (length), analysed segment of coast (from_km, to_km) or method of labelling (function km_label). In presented example selected 10 m as density of transects, 250 m as length of transects and segment of coast between kilometre points 391 and 428.

The function shoreline_dist (Fig. 8) requires the following input arguments: sche as the name of the schema container; transects_tbl as the name of the transect table; l1_tbl as the name of the table of older shorelines; l2_tbl as the name of the table of younger shorelines; and type as the description of the kind of shoreline. The output argument of the function consists of an integer variable gid as an ID for the transect and the double precision variable dist as a result of the computations. The first step of the function is to determine the point geometry from the intersection of the transect line with the older shoreline, called variable l1, and the younger shoreline, called variable l2. The next step is to determine the geometry of the start points of the transects, called tpoint, which allows one to perform calculations of the distances. The measured distances between the points tpoint, l1, and l2 were stored as the double precision variables dist1 and dist2. The final results were calculated as an algebraic subtraction of these distances and those stored for the recorded variable dist. If the shoreline moves into the sea, the values of subtraction calculation are positive, while if the shoreline moves inland, the values are negative.