Database Reference
In-Depth Information
Figure 14-7.
Customer-based POI Lookup: Execution plan for the first query
The execution plan for the second query, which is shown in Figure
14-8
, indicates that the spatial index lookup
returned 550 rows; that is, all POI in the area, regardless of to which company they belong. SQL Server had to join the
rows with the clustered index before evaluating the
CompanyId
predicate.
Figure 14-8.
Customer-based POI Lookup: Execution plan for the second query
One of the ways to solve such a problem is called the
Bounding Box
approach. This method lets us minimize the
number of calculations by filtering out POIs that are outside of the area of interest.
As you see in Figure
14-9
, all points that we need to select reside in the circle with the location at the center
point and radius specified by the distance. The only points that we need to evaluate reside within the box that
surrounds the circle.
Figure 14-9.
Customer-based POI Lookup: Bounding box
We can calculate the coordinates of the corner points of the box, persist them in the table, and use a regular
nonclustered index to pre-filter the data. This lets us minimize the number of expensive distance calculations to be
performed.
