Database Reference
In-Depth Information
Listing 14-13.
Customer-based POI Lookup: Query utilizing bounding box
declare
@Lat decimal(9,6) = 47.620309
,@Lon decimal(9,6) = -122.349563
,@CompanyId int = 15
declare
@g geography = geography::Point(@Lat,@Lon,4326)
select count(*)
from dbo.LocationsGeo2
where
Location.STDistance(@g) < 1609 and
CompanyId = @CompanyId and
@Lat between MinLat and MaxLat and
@Lon between MinLon and MaxLon
Figure 14-10.
Customer-based POI Lookup: Execution plan (bounding box approach)
As you see, the last query calculated the distance 15 times. This is a significant improvement compared to the
241,402 calculations from the original query. The execution times are shown in Table
14-3
.
Table 14-3.
Customer-based POI Lookup: Execution times
Clustered index seek
Spatial index
Bounding box
10,412 ms
26 ms
7 ms
As you see, the bounding box outperforms both the clustered index seek and spatial index lookup. Obviously,
this would be the case only when the bounding box reduces the number of the calculations to a degree that offsets the
overhead of the nonclustered index seek and key lookup operations. It is also worth mentioning that you do not need
a spatial index with such an approach.
You can also use a bounding box for the other use-cases; for example, when you are checking to see if a position
belongs to the area defined by a polygon. The bounding box corner coordinates should store minimum and
maximum latitude and longitude coordinates of the polygon's corner points. Like the distance calculation, you would
filter-out the locations outside of the box before performing an expensive spatial method call that validates if the point
is within the polygon area.
HierarchyId
The
HierarchyId
data type helps you work with hierarchical data structures. It is optimized to represent trees, which
are the most common type of hierarchical data.
