Applying Postgis for Storage and Processing of Geospatial Data in Logistics System

Abstract

The relevance of the study is due to the growing needs of logistics companies for high-performance solutions for processing geospatial data, in particular in the context of delivery routing in a dynamic urban environment. Traditional relational DBMSs show limitations in performance and scalability when working with large amounts of spatial information, which complicates the implementation of adaptive logistics algorithms. At the same time, open technologies, in particular PostGIS, are becoming more widespread, but are not sufficiently empirically evaluated in the applied logistics context. The aim of the study is to develop and empirically test an efficient route construction algorithm based on PostGIS and pgRouting tools to ensure optimization of delivery time in a logistics environment. The methodology is based on an experimental approach, which involves creating a test environment using PostgreSQL 15, PostGIS 3.3, and pgRouting, as well as implementing a routing algorithm with the pgr_dijkstra function. To evaluate the performance, we used real geodata from the OpenStreetMap road network and simulated sets of delivery points of 100, 1000, and 10,000 objects. PostGIS results were compared to MySQL performance under identical conditions. Metrics included average query execution time, peak values, memory usage, and scalability. The results of the study showed a significant advantage of PostGIS in the performance of route queries. In particular, when processing 10,000 points, the average query execution time was 6.5 seconds in PostGIS, while in MySQL it was 58.9 seconds, which indicates a ninefold advantage over. A linear relationship between the amount of data and processing time was found, indicating good scalability. The conclusions suggest that PostGIS with the pgRouting extension is a technologically sound tool for solving routing problems in logistics, providing stable performance, accuracy, and integration with other components of information systems. Prospects for further research include testing the algorithm in real logistics environments, integration with machine learning methods for predicting transport conditions, and expanding the routing functionality by implementing multi-criteria approaches.