| 1 | | Phase 5 – Advanced Topic: Spatial Database with PostGIS |
| | 1 | = Advanced Topic: Spatial Database with PostGIS = |
| | 2 | |
| | 3 | 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. |
| | 4 | |
| | 5 | 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. |
| | 6 | |
| | 7 | The implementation includes: |
| | 8 | |
| | 9 | * Installation and configuration of the PostGIS extension. |
| | 10 | * Creation of a geographic column (`geom`) in the `coordinates` table. |
| | 11 | * Population of the spatial column using existing latitude and longitude values. |
| | 12 | * Creation of a GiST spatial index for efficient spatial queries. |
| | 13 | * Implementation of three spatial queries demonstrating practical tourism scenarios: |
| | 14 | * Finding attractions within 5 km of a hotel. |
| | 15 | * Finding the nearest airport for every hotel. |
| | 16 | * Finding the nearest transport station for every hotel. |
| | 17 | |
| | 18 | The complete implementation is provided in the attached files: |
| | 19 | |
| | 20 | * '''phase5.sql''' – SQL implementation containing the PostGIS setup and all spatial queries. |
| | 21 | * '''phase5.pdf''' – Documentation describing the implementation, design decisions, and explanation of the spatial queries. |
