SQL Server: how to find closest location?
Finding the Closest Location in SQL Server

Learn how to efficiently query and retrieve the nearest geographical points in SQL Server using spatial data types and functions.
Locating the closest point to a given reference point is a common requirement in many applications, such as finding the nearest store, service provider, or point of interest. SQL Server, particularly versions 2008 and later, offers robust spatial data types and functions that make these calculations efficient and straightforward. This article will guide you through the process of setting up your data and performing these proximity queries.
Understanding SQL Server Spatial Data Types
SQL Server introduces two primary spatial data types: GEOMETRY
and GEOGRAPHY
. Choosing the correct type is crucial for accurate distance calculations.
GEOMETRY
: Represents data in a planar, or flat, coordinate system. Distances are calculated using Euclidean (straight-line) geometry. This is suitable for small areas where the curvature of the Earth can be ignored, or for abstract coordinate systems.GEOGRAPHY
: Represents data in a round-earth coordinate system (like WGS 84, used by GPS). Distances are calculated along the surface of the Earth, providing more accurate results for real-world geographical locations over larger distances.
For finding the closest location on Earth, GEOGRAPHY
is almost always the correct choice.
flowchart TD A[Start] --> B{Geographical Data?} B -- Yes --> C[Use GEOGRAPHY Type] B -- No --> D{Planar Data?} D -- Yes --> E[Use GEOMETRY Type] D -- No --> F[Define Coordinate System] C --> G[Distance Calculation on Sphere] E --> G F --> G G --> H[Find Closest Point] H --> I[End]
Decision flow for choosing SQL Server spatial data types.
Setting Up Your Data for Spatial Queries
Before you can query for closest locations, your data needs to be stored in a spatial column. Let's assume you have a table of locations, each with a latitude and longitude. You'll need to add a GEOGRAPHY
column and populate it.
CREATE TABLE dbo.Locations (
LocationID INT PRIMARY KEY IDENTITY(1,1),
LocationName NVARCHAR(100) NOT NULL,
Latitude DECIMAL(9,6) NOT NULL,
Longitude DECIMAL(9,6) NOT NULL,
GeoLocation GEOGRAPHY
);
GO
INSERT INTO dbo.Locations (LocationName, Latitude, Longitude)
VALUES
('Eiffel Tower', 48.8584, 2.2945),
('Louvre Museum', 48.8606, 2.3376),
('Notre Dame Cathedral', 48.8529, 2.3499),
('Arc de Triomphe', 48.8738, 2.2950),
('Sacre-Cœur Basilica', 48.8867, 2.3431);
GO
-- Populate the GeoLocation column
UPDATE dbo.Locations
SET GeoLocation = GEOGRAPHY::Point(Latitude, Longitude, 4326);
GO
Creating a locations table and populating the GEOGRAPHY column.
4326
in GEOGRAPHY::Point(Latitude, Longitude, 4326)
refers to the Spatial Reference Identifier (SRID) for WGS 84, which is the standard for GPS coordinates. Always use SRID 4326 for GEOGRAPHY
data unless you have a specific reason not to.Querying for the Closest Location
Once your data is set up, finding the closest location is straightforward using the STDistance()
method of the GEOGRAPHY
data type. This method returns the shortest distance between two GEOGRAPHY
instances in meters.
Let's say we want to find the closest landmark to a hypothetical starting point, for example, a hotel near the Louvre Museum.
DECLARE @MyCurrentLocation GEOGRAPHY;
SET @MyCurrentLocation = GEOGRAPHY::Point(48.8600, 2.3350, 4326); -- A point near the Louvre
SELECT TOP 1
LocationName,
Latitude,
Longitude,
GeoLocation.STDistance(@MyCurrentLocation) AS DistanceInMeters
FROM
dbo.Locations
ORDER BY
DistanceInMeters;
GO
SQL query to find the single closest location.
To find the closest N locations, simply change TOP 1
to TOP N
.
DECLARE @MyCurrentLocation GEOGRAPHY;
SET @MyCurrentLocation = GEOGRAPHY::Point(48.8600, 2.3350, 4326); -- A point near the Louvre
SELECT TOP 3
LocationName,
Latitude,
Longitude,
GeoLocation.STDistance(@MyCurrentLocation) AS DistanceInMeters
FROM
dbo.Locations
ORDER BY
DistanceInMeters;
GO
SQL query to find the top 3 closest locations.
GeoLocation
column. This can significantly speed up proximity queries. Example: CREATE SPATIAL INDEX SPATIAL_Locations_GeoLocation ON dbo.Locations(GeoLocation);