Portfolio

SQL + PostGIS Backend for a Music Festival App

This project aims to recreate a database for a music festival, taking as a toy example the existing festival “Lujan Suena” for the distribution of stages, entrances and facilities. It is designed to store data about the festival’s events, including music shows and cultural events, parking and sanitary facilities and food areas. Additionally, it also includes information about security points and a table for storing the usersID, considering that the database could be used for creating a mobile app, accessible by different users. The report aims to explain the structure of the database and how it was created, describing the tables and their relationships, as well as the views that were created to simplify data retrieval. Overall, the goal of the project is to create a functional and efficient database that could be used to manage the festival’s data in a way that is easy to use and maintain.
1. Database structure
The database was created using SQL, through the PgAdmin software, and QGIS plus the PostGIS plugin for the spatial attributes of the data. With these tools, the steps followed for the datasets creation were:

1. Create tables and columns with the ERD tool in PGAdmin

2. Create geometry columns for all the tables with spatial attributes

3. Create spatial entities in QGIS for the tables that require it (e.g. bathrooms and emergency points as points, stages and food areas as polygons, etc)

4. Insert values through SQL Query Tool in PGAdmin for the non-spatial tables (e.g. cultural and music events)
2. Database Querying and Views Creation
Once having defined the database structure and its content, it is time to create views for showing the different use possibilities that the database offers. These different queries are based on hypothetical use cases. Considering that the database is designed for the development of a mobile app, the different views would be some possible “searches” that the different users (identified with a userID in the table my_user) could do through the app, having access to the user’s location through the cellphone’s gps.

All the developed queries and views are shown in the report below.

©2022 Candela Sol Pelliza. All rights reserved