Tree-Structured Spatial Index for Database Query Processing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing spatial data indexing methods in databases are inefficient when dealing with large numbers of records matching spatial filter conditions, leading to high CPU and IO overhead, resulting in low query efficiency for spatial data.
Innovation Solution
A database index processing method that generates a tree-structured spatial index for database tables with spatial fields, storing spatial data meeting filter conditions in leaf nodes, and includes non-spatial fields for composite indexing to reduce table access operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If existing spatial indexing methods are used to store only minimum bounding rectangle information, then the index structure remains simple and compact, but query efficiency deteriorates when many records match the spatial filter condition due to high CPU and IO overhead for returning to primary key table
Solution Approach 1:
The patent merges the spatial index with the primary key table by storing complete record data directly in the index leaf nodes. This combining approach eliminates the need for separate table access, resolving the contradiction by improving query efficiency while maintaining a unified (though more complex) index structure that integrates both spatial and non-spatial data.
Solution Approach 2:
The patent performs preliminary action by pre-filtering and storing only the necessary fields (spatial data and required non-spatial fields) in the index leaf nodes during index creation. This preliminary preparation reduces the amount of data transferred during queries while maintaining efficient spatial indexing, thus improving productivity without proportionally increasing complexity.
2Productivity
If the index stores complete record data in leaf nodes, then query efficiency improves by eliminating table access, but the index size and storage requirements increase
Solution Approach 1:
The patent extracts only the necessary fields from complete records and stores them in the index leaf nodes. By taking out only the essential spatial data and required non-spatial fields rather than storing entire records, the solution improves query efficiency while controlling index data volume to a reasonable level.
Solution Approach 2:
The patent applies partial action by storing only the necessary subset of fields (spatial data and selected non-spatial fields) in the index rather than complete records. This partial storage approach provides sufficient query functionality while minimizing the increase in index data volume, achieving a balance between productivity improvement and storage requirements.
3Measurement precision
If spatial index stores only minimum bounding rectangle, then storage space is saved, but filtering precision and completeness of spatial data is insufficient
Solution Approach 1:
The patent applies local quality by storing complete spatial data and required non-spatial fields specifically in the index leaf nodes where filtering and querying occur. This localized storage of high-quality data only where needed improves filtering precision while minimizing the overall storage space consumption compared to storing complete records in the entire index structure.
Data Source
AI summary
A method including determining a database table for which a database index is to be created, wherein the database table comprises a spatial field for storing spatial data; acquiring, for the database table, a spatial filter condition comprising a spatial field ID; and generating, according to the spatial filter condition, a tree-structured spatial index for the database table, wherein a leaf node of the tree-structured spatial index stores therein spatial data meeting the spatial filter condition and its primary key ID. The efficiency of queries related to spatial data is thus enhanced.