SQL Server: how to find closest location?

Learn sql server: how to find closest location? with practical examples, diagrams, and best practices. Covers sql, sql-server-2008, geo development techniques with visual explanations.

Finding the Closest Location in SQL Server

Hero image for SQL Server: how to find closest location?

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.

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.