Calculating every store's distance is easy to write and expensive to repeat. Spatial indexes narrow the candidate search before exact geography comparisons. They help only when the query uses a supported spatial predicate shape.
Give the Spatial Column a Valid Home
A geography value represents a location under a spatial reference system. For latitude and longitude, SRID 4326 is a common choice. Keep that reference consistent across stored points and search values.
STDistance returns NULL for incompatible references. An index won't repair a swapped latitude and longitude or identify which coordinate system the source intended. Validate inputs before tuning access.
I inspect the base table's key and coordinates first. SQL Server needs a clustered primary key for this spatial index design. Use a disposable database for the sample table below.
Its coordinates are invented demonstration input. A few points illustrate syntax, not the read savings of a production location catalog. Test representative volumes and geographic distributions for your own comparison.
CREATE TABLE dbo.SpatialDistanceDemo
(
LocationId int NOT NULL PRIMARY KEY CLUSTERED,
Position geography NOT NULL
);
INSERT dbo.SpatialDistanceDemo VALUES
(1,geography::Point(47.61,-122.33,4326)),
(2,geography::Point(47.59,-122.32,4326)),
(3,geography::Point(47.65,-122.30,4326));Capture the Search Before Adding Spatial Indexes
Run the range query before creating the spatial index. Include the actual plan and STATISTICS IO messages. That gives you a baseline under the same predicate.
The distance for this SRID is expressed in meters. Keep the unit in the output alias and the input parameter name. A bare value such as 5000 isn't a complete distance requirement without its unit.
The distance method is called on the stored spatial column. That detail matters for supported index usage. Keep the radius comparison direct instead of wrapping the expression in a function that changes its recognizable form.
The output can still include the computed distance. Inspect reads from your own server and save the plan. Don't infer a scan count from the number of returned points.
DECLARE @Search geography = geography::Point(47.6062,-122.3321,4326);
DECLARE @RadiusMeters float = 5000;
SET STATISTICS IO ON;
SELECT LocationId, Position.STDistance(@Search) AS DistanceMeters
FROM dbo.SpatialDistanceDemo
WHERE Position.STDistance(@Search) <= @RadiusMeters;
SET STATISTICS IO OFF;See How Spatial Indexes Use the Grid to Filter Candidates
Spatial indexes map shapes into cells using tessellation. Think of the grid as a way to identify promising regions. It doesn't replace the exact spatial test.
Candidate rows still need evaluation to establish the final answer. Grid resolution affects how closely the index approximates the shape. A more detailed approximation also takes index resources and maintenance work.
SQL Server offers manual grid settings and automatic geography tessellation. Start with the supported automatic option unless evidence calls for tuning. Cells per object controls how much representation an object can use.
A point has a different tessellation profile from a complex service-area polygon. Don't copy a setting tuned for one shape collection into another merely because both columns use geography.
CREATE SPATIAL INDEX SIX_SpatialDistanceDemo
ON dbo.SpatialDistanceDemo(Position)
USING GEOGRAPHY_AUTO_GRID
WITH (CELLS_PER_OBJECT = 16);
Compare the Same Query After Creation
Repeat the original radius query and preserve the actual plan. Look for spatial index access and the work remaining for exact filtering. The optimizer can choose a scan for a small table or a broad radius.
Index existence doesn't guarantee index selection. The sample's small size is especially unlikely to establish a meaningful performance win. That is an optimizer decision to inspect.
I compare several search points and radii rather than one favorable center. Dense downtown data and sparse regional data exercise different candidate sets. Keep parameter values with the saved plans.
Compare logical reads and CPU under controlled conditions. Don't force an index simply to make its name appear. The chosen access path should earn its place through representative measurements.
Use the Built-In Geography Diagnostic
The sp_help_spatial_geography_index procedure reports index properties and sample-query tessellation information. Supply the table, index, and representative geography sample. A buffered search point describes a sample search area for these diagnostics.
Use it alongside the actual query plan, not as a replacement for runtime evidence. The procedure explains the index's representation while the plan explains the optimizer's chosen work.
The code below uses a sample circle matching the earlier radius. Inspect the returned property names and values on your server. Record the settings before experimenting with cells per object or manual grids.
Change one setting at a time in a test copy. A pile of altered settings gives you a new index definition without explaining which change helped the search.
DECLARE @Area geography = geography::Point(47.6062,-122.3321,4326).STBuffer(5000);
EXEC sys.sp_help_spatial_geography_index
@tabname = N'dbo.SpatialDistanceDemo',
@indexname = N'SIX_SpatialDistanceDemo',
@verboseoutput = 1,
@query_sample = @Area;Preserve a Supported Nearest-Neighbor Shape
A nearest-neighbor query needs a particular form for spatial index consideration. Use TOP and filter NULL distances. Put the stored column's STDistance expression first in ascending ORDER BY.
Additional predicates need to preserve the supported form. A stable key after distance provides consistent ties. Sorting a converted text distance changes the comparison and undermines the purpose of the query.
For range predicates, distance comparisons and supported intersection methods are useful index candidates. Arbitrary calculations over every spatial value don't automatically gain the same access path. Match the documented syntax for the method and type.
Where does your query need to stop searching? A clear radius or TOP contract tells both the optimizer and the reader what the request actually needs.
DECLARE @Search geography = geography::Point(47.6062,-122.3321,4326);
SELECT TOP (5) LocationId, Position.STDistance(@Search) AS DistanceMeters
FROM dbo.SpatialDistanceDemo
WHERE Position.STDistance(@Search) IS NOT NULL
ORDER BY Position.STDistance(@Search), LocationId;Pay for Spatial Indexes Only When They Help
Locations that change require spatial index maintenance. Include ingestion and coordinate updates in the evaluation. Also measure index storage and build cost before production rollout.
Read savings from spatial indexes have to justify those responsibilities. An index can make a frequent selective search economical, while doing little for an occasional query covering nearly every location. Treat those workloads as different cases.
Save the baseline, index definition, diagnostic output, and plans from your own server. Recheck after data distribution changes. Keep coordinate validation in the loading path.
A spatial index is clever about cells, but it has no opinion about a store accidentally placed in the ocean. Correct points and supported predicates make the performance evidence worth trusting.
Keep query points outside the densest region in the test set. They expose broad candidate searches and empty results. A selective downtown example alone doesn't represent regional requests. The index choice should remain useful under the geographic coverage the application actually promises, including its edges.
Related reading on this blog: Finding the Nearest Location With a Spatial Index and Spatial Data Types for Beginners.
A spatial index is not an exact-distance shortcut, it is a faster route to relevant candidates.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
