Skip to main content
  1. Projects/

Spatial Databases: WebGIS Database Backend

2021 words·10 mins·
Adian Dawuda
Master’s student in Applied Geoinformatics at the University of Salzburg’s Department of Geoinformatics (Z_GIS)

The use of a WebGIS is of great help for visualizing and organizing spatial and non-spatial data when planning and managing large events. It can allow visitors to easily access data and query information through an application on their phone. This project presents the creation of a spatial database backend for a WebGIS system to support a fictional city festival in the city of Klagenfurt, Austria. A variety of events and related facilities are organized throughout the old town center of Klagenfurt. The goal is to develop a well-structured and efficient database that can support the festival’s data requirements. This includes spatial properties such as the location of stands, rides, stages, bins, and toilets. The database is created to be in the third normal form and allows for user-specific dynamic queries that can return information about places and events, for which example queries will be showcased.

Database Creation #

The created database backend for the city festival is a relational PostgreSQL database created using pgAdmin as a management tool. As a first step, the schema of the database was modeled using an adapted Barker’s/Crow’s Foot notation with the pgAdmin ERD Tool. This allowed for the identification of errors or inconsistencies at a very early stage of the development, thus saving a lot of time and effort that would have been needed to fix these at later stages. Due to its purpose as a WebGIS backend and the spatial nature of the data, the database needs to support spatial datatypes, indexes, coordinate reference systems, filtering, and other operations. The open-source PostGIS extension for PostgreSQL databases provides a solution to this by enabling support for all of the aforementioned features. After a well-thought-out structure of the database had been established, the database was populated with data. As the festival is entirely fictional, synthetic data of the events and facilities was created, either in QGIS or directly using INSERT statements within pgAdmin. For spatial data creation and import, QGIS proved particularly useful. The QGIS DB Manager utility allowed for the import of spatial data into the database.

During the creation process, all database tables were ensured to comply with the third normal form of database design, to minimize data redundancy and maximize data integrity.

Resulting Database #

The database contains five tables. Each table is used to store physical or virtual objects. Spatial data (geom columns) is stored in the well-known binary (WKB) format enabled by PostGIS. A graphical representation of the database can be found in the Addendum section. The tables consist of the following:

  • areas — Virtual constructs containing other entities. The areas table contains a name and geom column. The names stored in the name column are chosen by the city’s festival planning committee and are based on the geographical location of the areas within the city. As all names are unique this column is used as the table’s primary key. The geom column stores the geometry and location of the areas as polygons.

  • bins — Bins for rubbish of all sorts to be discarded in. The bins table contains the columns id and geom. The values in the id column are unique identifiers for each bin. This column is used as the primary key for the table. The geom column stores the location of the bins. These are spread around the area of the fair and are modeled as points.

  • events — Events taking place on stages at the festival. This table contains the columns id, category, start_time, end_time, and stage_name. The id column is an automatically generated unique identifier and serves as the primary key of this table. The category column stores the category of event that is taking place, such as a magic show, a comedy show, or different genres of live music. The start_time and end_time columns store the start and end times of each event, respectively, as a time without time zone datatype (Klagenfurt local time). The stage_name column indicates the name of the stage where the event is taking place and is a foreign key referencing the stages table. The events table does not have a geom column since event information is not inherently tied to a specific location within the festival.

  • rides — Rides that visitors of the festival can pay to use. This table contains the columns geom, name, price, and type. The geom column stores the location and shape of the rides, which are modeled as polygons. The column name stores the name of the ride given to it by the ride provider. The price column indicates the price in Euro that visitors must pay to use the according ride. The datatype is a double precision numeric rounded to two decimal places, which makes it suitable for monetary values. The type indicates the type of ride. Values may be rollercoaster, ferris wheel, fun house, or bumper cars. If needed, other ride types may also be added. The rides table does not use only the name column as the primary key because some ride providers may have rides of the same name. Tables that are for providers of services where the city is not in control of the names of the entities (rides and stands), use the geometry and name as a composite primary key. Therefore, the primary key of the rides table is made up of the geom and name columns.

  • stages — Stages for live music/entertainment organized by the city. This table contains the columns geom, name, show_start, and show_end. All stages are modeled as polygons which are stored in the geom column. The name column stores the name given to each stage. Only the name column is used as the primary key of this table, as the city’s festival planning committee gives every stage a unique name by which it can be identified. The stages’ show times differ from the general opening times of the festival, to which the stands, rides, and toilets adhere. They may also differ from stage to stage. Therefore, the columns show_start and show_end store the beginning and ending times of the show for each stage. The datatype used is time without time zone (all times are Klagenfurt local time).

  • stands — Stands offering goods or services to customers. The stands table contains the columns geom, name, and type. Just like stagesand rides, the geometries of the stands are stored as polygons in the geom column. The name column stores the name of the stand, as defined by the owner. Together with the geom column, the name column makes up the primary key of this table. Similarly to rides, there are also different types of stands at the festival. These are specified in the type column of the stands table. There are food and drink stands, merchandise stands and one information stand.

  • toilets — Public restrooms for visitors to the festival. These may be facilities constructed specifically for the festival or they may be located inside public buildings/buildings for which the city has obtained permission to use. The table consists of an id and a geom column. The id column uniquely identifies each column and is used as the table’s primary key. The geom column stores the location of the toilets.

Spatial indexes are placed on the spatial (geom) columns of all tables containing spatial data. This improves the performance of spatial operations, by allowing the database to quickly identify which rows in the table contain spatial data and avoid performing a resource-intensive full table scan. The image below shows all indexes in the database. Spatial indexes are prefaced by the letters sidx.

Figure 1: List of indexes

The database also contains views, which can be used to store complex queries. Figure 2 shows all of the database’s views. In the scope of this project, the views aim to showcase the functionality of the database. If the database were to be deployed, the use of views for highly specific queries should be reconsidered.

Figure 2: List of views

The definitions of each view can be found in the Addendum section. The following briefly explains the purpose of each view:

  • closest_5_food_and_drink: Selects the five closest stands of the type Food & Drink to a geographical location. This location used is a placeholder for a user’s actual location that can be obtained from the GNSS signal from their phone.

  • crash_and_burn_bumper_cars: Selects rides named Crash and Burn Bumper Cars of the type bumper cars. This query could return multiple entries as it only searches by name. The user could then choose from the list of results to select the appropriate one if multiple rides with the same name should exist.

  • events_between_12_and_17: Lists all events that take place (including partially) between 12:00 and 17:00 o’clock and selects the name and location of the stage they take place on.

  • main_stage_show_end: Selects the closing time of the main stage.

  • schubertpark_area_entertainment: Selects all stands, rides, and stages inside the Schubertpark area.

Normalization #

As mentioned in the Database Creation section, the database is in the third normal form to reduce data redundancy and improve data integrity. The following section outlines each of the first three normal forms and how the database adheres to them.

  1. The first normal form (1NF) requires each column in a table to be atomic, meaning that it should contain only a single value, and that each column should have a unique name. All tables of the Klagenfurt city festival database contain only atomic values and therefore meet the requirements of the first normal form.

  2. The second normal form (2NF) requires that a table should be in 1NF and should not have any partial dependencies. This means that all non-key attributes should depend on the entire primary key, not just a part of it. This holds true for all tables. Example: In the rides table, the price and type depend on both parts of the composite key (geom and name) Therefore the requirements for the second normal form are met.

  3. The third normal form (3NF) requires that a table should be in 2NF and should not have any transitive dependencies i.e., no non-key attributes should depend on other non-key attributes. This holds true for all tables. Example: In the rides table, the type does not determine the price and the price does not determine the type. Therefore, there are no transitive dependencies and the table is in the third normal form.

Security #

To keep the data inside the database secure a user named simple_user has been created to be used by visitors of the festival/users of the app. This user is granted only SELECT privileges (Figure 3) and is to be the default user for people interacting with the database backend. Therefore, users of the app will not be able to edit or delete any data.

Figure 3: simple_user's privileges

Afterthoughts #

A spatial database backend for a WebGIS system to support a fictional city festival (Figure 4) in Klagenfurt has been successfully planned and implemented in this project. The resulting database contains seven tables that store a mix of physical and virtual objects as well as a mix of spatial and non-spatial data in the third normal form. Spatial data is stored in the WKB format enabled by PostGIS. Each table has a primary key, and natural keys were used where possible to further reduce data redundancy. As there are no confidential/private tables in this version of the festivals database, the simple_user is allowed access to the entirety of the public schema. Furthermore, the database does not contain any rows or columns that should not be visible to the users of the app. If this were the case, row-level and column-level security measures should be implemented. In the case of users entering input of any form that interacts with the database backend, the queries should be sanitized in the final implementation. Future work could include extending the database to include more features and functionality or creating the application frontend.

Figure 4: Map of the Klagenfurt City Festival

Addendum #

View Definitions #

Figure 5: closest_5_food_and_drink
Figure 6: crash_and_burn_bumper_cars
Figure 7: events_between_12_and_17
Figure 8: main_stage_show_end
Figure 9: schubertpark_area_entertainment

Graphical Database Representation #

Figure 10: Graphical representation of the database