Advanced Topic: Spatial Database with PostGIS

The advanced topic of our project focuses on integrating the PostGIS extension into PostgreSQL in order to support spatial data processing and location-based queries.

Our implementation extends the existing tourism database by converting latitude and longitude values into geographic points (GEOGRAPHY(Point,4326)), enabling efficient distance calculations and proximity searches.

The implementation includes:

• Installation and configuration of the PostGIS extension.
• Creation of a geographic column (geom) in the coordinates table.
• Population of the spatial column using existing latitude and longitude values.
• Creation of a GiST spatial index for efficient spatial queries.
• Implementation of three spatial queries demonstrating practical tourism scenarios:
  • Finding attractions within 5 km of a hotel.
  • Finding the nearest airport for every hotel.
  • Finding the nearest transport station for every hotel.

The complete implementation is provided in the attached files:

• phase5.sql – SQL implementation containing the PostGIS setup and all spatial queries.
• phase5.pdf – Documentation describing the implementation, design decisions, and explanation of the spatial queries.