Parallelizable Gridded Geospatial Join for RDBMS
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing geospatial join operations in relational database systems are costly due to massive table duplication and inefficient processing, especially as the number of Application Module Processors (AMPs) increases, leading to spool space and transmission issues.
Innovation Solution
The Parallelized Gridded Geospatial Join (PGGJ) technique normalizes statistics for two tables, generates a grid with overlapping cells, and dynamically constructs an R-Tree for high-entry grid cells to facilitate efficient join operations, reducing duplication and scaling with the number of AMPs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a full product join is performed on Store and State tables, then all possible join combinations are generated, but the operation becomes extremely costly in terms of spool space and transmission cost
Solution Approach 1:
The patent divides the large State table into multiple smaller partitions distributed across different AMPs. Instead of duplicating the entire State table on all AMPs, each AMP holds only a partition of the State table, significantly reducing spool space requirements while maintaining the ability to perform complete joins through coordinated processing across partitions.
Solution Approach 2:
The patent introduces a spatial dimension by creating a grid overlay on the geospatial data. This grid structure allows the system to filter and process only relevant spatial regions rather than performing a complete product join across all rows, reducing the volume of data that needs to be joined while preserving join completeness for matching spatial relationships.
2Productivity
If the State table is fully duplicated on all AMPs, then geospatial join operations can be performed in parallel, but transmission cost and spool space requirements become prohibitively high
Solution Approach 1:
The State table is segmented into multiple partitions that are distributed across different AMPs. Each AMP processes its local partition in parallel with other AMPs, enabling parallel join processing without requiring full table duplication. This segmentation reduces both transmission cost (each AMP receives only its partition) and spool space requirements while maintaining parallel processing capability.
Solution Approach 2:
The patent introduces a coordinator component that acts as an intermediary between AMPs. The coordinator manages the distributed join operation by coordinating data exchange between AMPs, allowing parallel processing without requiring each AMP to have a complete copy of the State table. This intermediary enables efficient inter-AMP communication and result aggregation.
3Productivity
If geospatial index search is performed on Store.location, then candidate rows are narrowed down, but other single table predicates on Store table must be executed afterward, reducing overall efficiency
Solution Approach 1:
The patent performs spatial filtering using the grid structure as a preliminary action before executing other single-table predicates. By first identifying candidate rows through spatial region matching in the grid, the system narrows down the dataset early in the processing pipeline, so that subsequent predicate evaluations are performed on a smaller subset of rows, reducing total execution time.
Solution Approach 2:
The patent applies different processing strategies to different regions of the data space using the grid structure. Each grid cell can be processed with optimized local predicates tailored to that region's characteristics, allowing more restrictive predicates to be applied early in high-selectivity regions while maintaining flexibility in other areas.
Data Source
AI summary
Statistics for a Relational Database Management System (RDBMS) set of tables are normalized and a grid computed. A particular grid cell having above threshold number of entries from the tables is determined and an R-Tree is dynamically constructed for that grid cell for use when performing a geospatial join operation against the tables.