Database Reference
In-Depth Information
The storage space used by the tables from Listing 14-7 is shown in Table
14-1
.
Table 14-1.
Storage space used by the tables Listing 14-6
dbo.Locations
dbo.LocationsGeo
dbo.LocationsGeoIndexed
8,898 KB
12,780 KB
17,320KB
As you see, the binary representation of the spatial type uses more space than the relational format. As expected,
the spatial index requires additional space, although the overhead is not nearly as much as the overhead produced by
XML indexes that you saw in Chapter 11. “XML.”
Let's run tests that measure the performance of queries that calculate the number of locations within one mile of
Seattle city center. In the
dbo.Locations
table, we will use the
dbo.CalcDistanceCLR
function, which was defined in
Chapter 13. For the two other tables, we will call the spatial method:
STDistance
. The test code to accomplish this is
shown in Listing 14-8. The query execution plans are shown in Figure
14-6
.
Listing 14-8.
POI Lookup: Test queries
declare
@Lat decimal(9,6) = 47.620309
,@Lon decimal(9,6) = -122.349563
declare
@G geography = geography::Point(@Lat,@Lon,4326)
select ID
from dbo.Locations
where dbo.CalcDistanceCLR(Latitude, Longitude, @Lat, @Lon) < 1609
select ID
from dbo.LocationsGeo
where Location.STDistance(@G) < 1609
select ID
from dbo.LocationsGeoIndexed
where Location.STDistance(@G) < 1609
