Summary

This project involved designing and building the spatial database backend for a proposed mobile WebGIS application for the Munich Oktoberfest. The goal was to create a robust database that could store and manage various types of spatial and temporal data relevant to the festival, such as the locations of tents, rides, and amenities, as well as event schedules. The final database is capable of answering complex, real-time user queries to enhance the visitor experience, for example, by finding nearby events or amenities based on a user’s current location and time.

Key Points

Data Preparation and Digitization: Since no official spatial data was available, the initial phase involved data creation. Base data for the Theresienwiese area was downloaded from OpenStreetMap (OSM) and then cleaned. Additional features were manually digitized in QGIS using an official Oktoberfest map as a reference, resulting in a complete and accurate set of spatial layers for all festival entities.

Comparison of the official map and the manually digitized data

On the left is the official Oktoberfest map used for reference; on the right is the final, manually mapped data in QGIS.

Database Design and Structure: A physical data model was designed to organize the various festival entities, including tents, rides, events, toilets, ATMs, and visitors. A central locations table was created to manage all physical entities, simplifying relationships and queries. The database schema was implemented in PostgreSQL with PostGIS, using pgAdmin’s ERD tool to visualize the final structure.

Physical data model of the Oktoberfest database

A graphical overview of the database schema, showing the tables and their relationships.

Spatio-Temporal Queries: The database was specifically designed to handle spatio-temporal queries from a visitor’s perspective. Tables for visitors, events, and locations include timestamp and geometry columns, enabling dynamic queries that consider both the user’s location and the current time.

Query to find nearby events that have not ended yet

An example query that finds all events within 500m of a visitor ("Anna") that are happening on the same day and have not yet finished.

Practical Application Scenarios: Several example queries were developed to demonstrate the database’s functionality. These scenarios showcase how the app could help a visitor find a tent with a specific capacity level, locate family-friendly rides within a certain area (“Oide Wiesn”), or discover all points of interest within a short walking distance.

Query to find tents based on capacity and distance

A query for a visitor ("Maria") looking for tents with a capacity utilization between 65% and 91%, ordered by distance.

A map-based visualization of the query result, showing Maria's position and the suitable tents colored by distance.

Full documentation: