Storing and Querying Locations in SQL Server

SQL Server can store spatial data as locations and shapes rather than unrelated coordinate columns. The first decision is whether those coordinates describe a flat plane or positions on the earth.

Choose Geometry or Geography

Geometry represents coordinates on a flat plane. It can work well for a floor plan or appropriately projected spatial data. Its distance units follow the coordinate system you supplied.

Geography represents round-earth data, commonly latitude and longitude. For ordinary geographic locations, SRID 4326 is a common choice. Distance calculations use the units associated with the spatial reference system.

Do not put longitude and latitude into geometry and then assume the resulting distance is measured in meters. Choosing a data type is part of interpreting the coordinates. It is not merely a storage preference.

DECLARE @a geometry = geometry::Point(0, 0, 0);
DECLARE @b geometry = geometry::Point(3, 4, 0);
SELECT @a.STDistance(@b) AS distance_in_coordinate_units;

This example operates in an undefined planar coordinate system. The units could represent a chosen drawing scale. The function does not know whether you intended meters, feet, or something else.

Keep Coordinate Order Explicit

The geography::Point method takes latitude first and longitude second. Well-known text uses the opposite ordering for a point's coordinates: longitude then latitude. Mixing these conventions can place valid-looking data in the wrong location.

DECLARE @location geography = geography::Point(51.5007, -0.1246, 4326);
SELECT @location.Lat AS latitude,
       @location.Long AS longitude,
       @location.STSrid AS srid,
       @location.STAsText() AS well_known_text;

Label input fields clearly and validate their allowed ranges. Range checks alone do not catch every reversal because some swapped values remain valid. Test known sample locations during ingestion.

Keep the source coordinate system with imported data. Setting an SRID value does not transform the coordinates into another projection. Perform a proper supported transformation before comparing incompatible coordinate systems.

Create a Small Location Table

Run this example in a disposable user database where the demo table does not already exist. It creates a persistent lab object for the spatial index example. The two coordinates are illustrative public locations.

CREATE TABLE dbo.SpatialPlaceDemo
(
    PlaceId int NOT NULL
        CONSTRAINT PK_SpatialPlaceDemo PRIMARY KEY CLUSTERED,
    PlaceName nvarchar(80) NOT NULL,
    Location geography NOT NULL
);
INSERT dbo.SpatialPlaceDemo (PlaceId, PlaceName, Location)
VALUES
(1, N'Point A', geography::Point(51.5007, -0.1246, 4326)),
(2, N'Point B', geography::Point(51.5033, -0.1195, 4326));

Use a stable business identifier separately from the location value. A place can move or have its coordinates corrected. Decide how location history should be represented if earlier positions matter.

Ask a Distance Question

DECLARE @origin geography = geography::Point(51.5007, -0.1246, 4326);
SELECT PlaceId, PlaceName,
       Location.STDistance(@origin) AS distance_meters
FROM dbo.SpatialPlaceDemo
WHERE Location.STDistance(@origin) <= 1000.0
ORDER BY distance_meters, PlaceId;

With these geography values and SRID 4326, the distance is expressed in meters. The query asks for locations within a distance threshold. It does not calculate road distance, travel time, or a route.

Spatial methods require compatible reference identifiers for meaningful comparison. STDistance returns NULL when the SRIDs do not match. A missing result may therefore reflect incompatible data rather than a distant point.

Be explicit about boundary rules and acceptable accuracy. A delivery-zone decision may need different treatment from a rough nearby-location search. Translate the business requirement before choosing the predicate.

Understand the Spatial Index

CREATE SPATIAL INDEX IX_SpatialPlaceDemo_Location
ON dbo.SpatialPlaceDemo(Location)
USING GEOGRAPHY_AUTO_GRID;

A spatial index divides space to help narrow candidate locations. It is different from ordering one ordinary numeric key in a B-tree. SQL Server still evaluates the spatial relationship needed by the query.

Supported predicate forms and query shape affect index use. Inspect the actual execution plan with representative data. Two demo rows are useful for syntax but cannot establish a performance benefit.

A spatial index also adds storage and maintenance work. Test a realistic distribution of points or shapes rather than uniformly scattered values alone. Dense clusters and large shapes can change the work required.

Validate the Data Before Trusting the Result

Check the intended data type, SRID, coordinate order, and source accuracy during ingestion. Include known locations and boundary cases in your tests. A syntactically valid spatial object can still represent the wrong place.

For polygons, also review validity and the geography orientation rules. Keep transformation and correction decisions documented. Location queries become reliable when the coordinate system is treated as part of the data contract.

A coordinate is not a complete location, it is a value that needs a reference system and an interpretation.

This post was rewritten from scratch in September 2026. The original, published on 2010-03-30, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

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