Parallel algorithm for improving the performance of spatial queries in SQL: The use cases of SQLite/SpatiaLite and PostgreSQL/PostGIS databases

• Published in: Computers & geosciences Vol. 155; p. 104840
• Main Author: Ilba, Mateusz
• Format: Journal Article
• Language: English
• Published: Elsevier Ltd 01.10.2021
• Subjects: Algorithms; buffers; computers; Parallel and high performance computing; PostGIS; processing time; Spatial data processing; SpatiaLite; testing; yields; Spatial data processing; Parallel and high performance computing; SpatiaLite; Algorithms; PostGIS
• ISSN: 0098-3004; 1873-7803
• DOI: 10.1016/j.cageo.2021.104840

This paper proposes an open-source algorithm that performs parallel processing of spatial queries, during which an initial selection of objects to be subjected to spatial relationship tests is done using a spatial index. These data are then further subdivided by the use of the OFFSET and LIMIT clauses into still smaller subgroups, to which spatial relationship tests utilizing complex calculations are assigned, thereby creating multiple processes running in parallel. This algorithm was tested using data from the SQLite/SpatiaLite and PostgreSQL/PostGIS database. In processing spatial relationship queries involving six threads, the algorithm yielded a 3.6X maximum speed-up increase in performance compared to single-thread processing on SQLite/SpatiaLite database and 5.1X maximum speed-up on PostgreSQL/PostGIS database. In single-layer analyses (e.g., area calculation, buffer generation), a 5X speed-up time in query processing was observed. •New method of multithreaded use of the SQLite/SpatiaLite and PostgreSQL/PostGIS database.•Open source Python code for parallel spatial relationship queries in SQL.•Intersects, touches, within and crosses spatial relationship was tested.•5.1X maximum speed-up time was observed on spatial relationship query.