Finding the Nearest Store With the geography Type

Your customer wants a location that can actually serve the request. A nearest store query uses geography points and distance in meters. Validate the coordinates before trusting the first row.

Pick the Spatial Type Before the Search

For latitude and longitude on Earth, geography is usually the right spatial type. It models a round-earth coordinate system. The geometry type suits planar coordinates and measures distance differently. 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. The block below returns POINT (-122.3321 47.6062), with longitude first.

What is the search scope? Finding a store in one city and an asset inside a building can require different coordinate models. Pick the coordinate model before tuning the index.

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

These coordinates are invented sample input. Create this table in a disposable database before running the searches. The clustered primary key provides the row identity required by the spatial index.

For a real store lookup, keep the coordinates, opening status, and stable identifier under the same data-quality checks. Distance alone doesn't establish eligibility.

CREATE TABLE dbo.StoreLocation
(
    StoreId int NOT NULL PRIMARY KEY CLUSTERED,
    StoreName nvarchar(60) NOT NULL,
    Location geography NOT NULL,
    IsOpen bit NOT NULL
);
INSERT dbo.StoreLocation VALUES
(1,N'North Store',geography::Point(47.61,-122.33,4326),1),
(2,N'South Store',geography::Point(47.59,-122.32,4326),1);

Check Every Store Point and Its SRID

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 list rows with a missing point or an unexpected SRID before relying on a nearest query. The 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;

Shape the Nearest Store Query for the Index

A nearest-neighbor spatial index needs a supported query shape. Use TOP and a WHERE predicate calling the spatial column's STDistance method. Put that distance expression first in ascending ORDER BY.

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;

Add a Spatial Index on the Store Locations

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;

Prove the Plan Uses the Spatial Index

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';

Limit the Nearest Store to a Radius and Open Sites

Nearest does not always mean 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;

Rank the Nearest Store by Business Rules Too

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 store search 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.

The closest closed store is a fine answer to the wrong question. Keep the customer-facing result limited to the locations that can serve the request. Validate the empty result too. A clear no-match response is better than displaying a distant location as if it were nearby.

Related reading on this blog: Finding the Nearest Location With a Spatial Index and Fixing Invalid Geography Polygons and Ring Orientation.

A nearest store is not a lucky first row, it is a valid location under a clear distance rule.

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