Finding the Nearest Location With a Spatial Index

A customer asks for the closest store, and sorting every store by distance looks simple. Finding the nearest location with a spatial index takes a particular query shape to avoid broad work.

Choose geography for Earth Coordinates

For latitude and longitude on Earth, geography is usually the right spatial type. It models a round-earth coordinate system. geometry is suited to planar coordinates and has different distance meaning. Do not mix them casually.

I check how the source stores latitude and longitude. SQL Server geography::Point takes latitude first, then longitude, with an SRID. Well-known text POINT syntax places longitude first. That difference can put a point in the wrong place.

What is the search scope? Nearest store across a city and nearest asset inside one building can need different types and units. Pick the coordinate model before tuning the index.

DECLARE @search geography = geography::Point(47.6062, -122.3321, 4326);
SELECT @search.STAsText() AS SearchPoint;

Validate Points and SRIDs

Each indexed location should use the expected SRID. STDistance returns NULL for incompatible SRIDs. A query filtering NULL distances can silently omit rows with bad metadata. Validate input during loading.

Keep original source coordinates and a stable StoreId. They help diagnose a point that appears far away. Swapped latitude and longitude can be valid numeric data and still map to the wrong region. Use range checks and visual sampling.

I count mismatched SRIDs before relying on a nearest location query. A spatial index cannot fix coordinates that refer to different systems. Data quality comes before tuning.

SELECT StoreId, Location.STSrid AS SrsId
FROM dbo.StoreLocation
WHERE Location IS NULL
   OR Location.STSrid <> 4326;

Write the Index-Friendly Nearest Location Query

SQL Server considers a spatial index for a nearest-neighbor query only in one shape. Use TOP, a WHERE predicate that calls STDistance on the spatial column, and ORDER BY that same distance, ascending and first. Filter NULL distances. Join other predicates with AND.

Add a stable StoreId tie breaker after distance so equally distant locations present consistently. Do not wrap the first STDistance sort expression in unrelated arithmetic. The documented query shape matters.

I test a point near known stores and one near the service edge. Correct results in one test do not prove index use. Inspect the plan too.

DECLARE @search geography = geography::Point(47.6062, -122.3321, 4326);
SELECT TOP (5) StoreId, StoreName,
       Location.STDistance(@search) AS DistanceMeters
FROM dbo.StoreLocation
WHERE Location.STDistance(@search) IS NOT NULL
ORDER BY Location.STDistance(@search) ASC, StoreId;

Create a Spatial Index

A spatial index on the geography column narrows candidate locations. The table needs an appropriate key and valid values. Use a supported geography tessellation option, then review index build time and storage.

I create the index in a planned window for a large table. A spatial index costs maintenance during inserts and updates. If locations rarely change and nearest searches run frequently, the tradeoff can be favorable. Measure it.

Start with documented settings rather than tuning grids by instinct. The actual nearest query and plan tell you whether the index participates. The index name proves only that an object exists.

CREATE SPATIAL INDEX SIX_StoreLocation_Location
ON dbo.StoreLocation(Location)
USING GEOGRAPHY_AUTO_GRID;

Confirm Index Use for the Nearest Location Search

Inspect the actual execution plan. Look for spatial index access and compare reads and duration with a representative baseline. SQL Server can choose a scan when the table is small or estimates favor it. Do not force a hint before understanding the choice.

I keep query shape fixed during comparison. Removing WHERE STDistance can preserve result correctness while preventing the intended spatial index pattern. That regression is easy when someone tidies SQL.

Check again after data volume or distribution changes. A design that works for a small set can behave differently later. Save the test point and plan for comparison.

SELECT i.name, i.type_desc, i.is_disabled
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID(N'dbo.StoreLocation')
  AND i.type_desc = 'SPATIAL';

Handle Radius and Eligibility for the Nearest Location

The nearest location is not always eligible. A store can be closed or outside a service area. Add those predicates with AND while keeping distance in WHERE. Test whether the index plan still helps under typical filters.

A maximum radius prevents a distant store from being called nearby. The radius unit must match the spatial reference. For SRID 4326 geography, STDistance returns meters. Document the unit in the interface.

I ask what happens when no store qualifies. Return an empty result with a clear message or use a documented fallback. Do not quietly return the nearest store across a continent.

DECLARE @search geography = geography::Point(47.6062, -122.3321, 4326);
SELECT TOP (5) StoreId, StoreName
FROM dbo.StoreLocation
WHERE Location.STDistance(@search) <= 10000
  AND IsOpen = 1
ORDER BY Location.STDistance(@search), StoreId;

Keep Distance and Business Meaning Separate

The index helps find nearby candidates. Business rules decide ranking when distance is only one factor. Availability, hours, and service area can matter more. A two-stage design can get nearby candidates and then apply a documented rank.

I do not call a point closest without showing unit and search location. A wrong coordinate or SRID can return plausible but false results. Keep request coordinates in a protected diagnostic log when support needs them.

Nearest-neighbor queries are straightforward once data and query shape are correct. Choose the spatial type, validate points, use TOP and STDistance, then verify index use in the actual plan.

Nearest is a business term as well as a spatial calculation. Does the application mean straight-line distance, road travel, or a location inside a service area? SQL Server geography distance is useful for surface distance, while a route engine answers a different question. State the coordinate system and units in the interface. I test known points before trusting a sorted list of candidates.

A spatial index needs the right query shape and a useful selective predicate. Compare the plan and runtime on representative data, then test the no-match case. Do not force an index because the result sounds geographic. Some queries are small enough that a scan is simpler. Which maximum radius is acceptable? A radius can keep the candidate search bounded and give the user a clear message when no location qualifies.

A nearest-neighbor query also needs a defined tie rule. Two sites can be the same distance after rounding, so add a stable secondary key for predictable output. Keep the distance as a measured value rather than formatting it into text before sorting. I compare a candidate near the date line and one near a pole if the service area reaches those regions. Geographic edge cases deserve deliberate tests.

Related reading on this blog: Geography Data Type: Calculating Distance Between Two Points on the Earth: Day 18 of 35 and Finding Shortest Distance between Two Shapes using Spatial Data Classes: Ramsetu or Adam's Bridge.

A nearest result is not just a sorted distance, it is a validated point and an index-friendly query.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.