This study investigates the design and implementation of a spatial database system developed to support the management, monitoring, and analysis of urban green areas. The research emphasizes the importance of structured spatial data organization, relational integrity, and automated spatial operations for improving the efficiency of urban environmental management. The database was created using PostgreSQL with the PostGIS extension, providing advanced spatial capabilities directly within the relational model. The proposed schema includes entities such as parcels, trees, tree species (scientific and popular names), roads, utilities, electricity networks, flowers, and bushes. The design follows normalization principles up to the third normal form (3NF), ensuring data consistency and reducing redundancy. To enhance automation, spatial triggers were implemented to perform key operations, including centroid generation through the ST_Centroid() function, coordinate extraction with ST_X() and ST_Y(), area calculation for polygons using ST_Area(), and spatial intersections via ST_Intersects() to define spatial relationships among multiple layers. These automated processes maintain accurate spatial attributes and relationships whenever geometry data is updated, minimizing manual intervention. Integration with QGIS and QField enables synchronized data visualization and field data acquisition, ensuring real-time updates within the spatial database. The results demonstrate that the use of a normalized and trigger-driven spatial database structure provides a reliable, scalable, and efficient foundation for sustainable urban green area management and contributes to data-driven decision-making in smart city initiatives.

spatial database, PostgreSQL, PostGIS, QGIS, QField, urban green areas, database normalization, spatial triggers, centroid calculation, spatial automation, area computation.

geodesy engineering

Presentation: poster