Spatial Queries in SQL Server | Colin Mackay

SQL Server includes built-in support for spatial data through the geography and geometry data types. These enable you to store location information and perform spatial operations like finding nearby points, calculating distances, and testing whether regions overlap.

This article introduces spatial data types in SQL Server, demonstrates common query patterns, and covers practical considerations for working with geographic data.

Introduction to Spatial Data

Spatial data represents the location and shape of objects. Common use cases include:

• Finding nearby stores, restaurants, or services
• Calculating driving distances or straight-line distances
• Determining if a point falls within a region (geofencing)
• Visualising data on maps
• Routing and logistics applications

SQL Server has supported spatial data since SQL Server 2008, with improvements in subsequent versions.

Spatial Data Types

SQL Server provides two spatial data types:

Geography

Represents data on an ellipsoidal (round-earth) coordinate system. Use this for real-world locations with latitude and longitude.

• Coordinates are in degrees (latitude/longitude)
• Accounts for Earth's curvature in calculations
• Distance results are in metres by default
• Appropriate for most mapping and GIS applications

Geometry

Represents data on a flat (planar) coordinate system. Use this for CAD data, floor plans, or when you need Euclidean calculations.

• Coordinates are in a flat plane
• Calculations are Euclidean (straight lines)
• Units depend on your coordinate system
• Faster for some operations but less accurate for geographic distances

Choosing Between Them

For most location-based applications with real-world coordinates, use geography. Use geometry when working with flat representations (building plans, game maps) or when you need specific planar operations.

Creating Spatial Data

Table with Geography Column

CREATE TABLE Locations (
    LocationId INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100) NOT NULL,
    Address NVARCHAR(200),
    Coordinates GEOGRAPHY
);

Inserting Points

Points are created using Well-Known Text (WKT) format. Note that geography expects longitude first, then latitude:

-- Insert a point (longitude, latitude)
INSERT INTO Locations (Name, Address, Coordinates)
VALUES (
    'Edinburgh Castle',
    'Castlehill, Edinburgh EH1 2NG',
    geography::Point(55.9486, -3.1999, 4326)  -- lat, long, SRID
);

-- Using STPointFromText (WKT format: POINT(longitude latitude))
INSERT INTO Locations (Name, Address, Coordinates)
VALUES (
    'Glasgow Central Station',
    'Gordon Street, Glasgow G1 3SL',
    geography::STPointFromText('POINT(-4.2583 55.8597)', 4326)
);

SRID (Spatial Reference ID)

The SRID specifies the coordinate system. SRID 4326 (WGS 84) is standard for GPS coordinates and most mapping applications. Always use consistent SRIDs within your application.

Other Geometry Types

-- LineString (a path or road)
DECLARE @path GEOGRAPHY = geography::STLineFromText(
    'LINESTRING(-3.1999 55.9486, -4.2583 55.8597)', 4326
);

-- Polygon (a region/area)
DECLARE @region GEOGRAPHY = geography::STPolyFromText(
    'POLYGON((-3.5 55.7, -3.5 56.0, -3.0 56.0, -3.0 55.7, -3.5 55.7))', 
    4326
);

Basic Spatial Queries

Retrieve Coordinates

SELECT 
    Name,
    Coordinates.Lat AS Latitude,
    Coordinates.Long AS Longitude
FROM Locations;

Convert to Text

SELECT 
    Name,
    Coordinates.STAsText() AS WKT
FROM Locations;

Distance Calculations

Distance Between Two Points

DECLARE @point1 GEOGRAPHY = geography::Point(55.9486, -3.1999, 4326);  -- Edinburgh
DECLARE @point2 GEOGRAPHY = geography::Point(55.8597, -4.2583, 4326);  -- Glasgow

SELECT @point1.STDistance(@point2) AS DistanceInMetres;
-- Returns approximately 70,000 metres (70 km)

Find Locations Within Distance

-- Find all locations within 10km of a point
DECLARE @searchPoint GEOGRAPHY = geography::Point(55.9533, -3.1883, 4326);
DECLARE @radiusMetres FLOAT = 10000;  -- 10 km

SELECT 
    Name,
    Address,
    Coordinates.STDistance(@searchPoint) AS DistanceMetres
FROM Locations
WHERE Coordinates.STDistance(@searchPoint) <= @radiusMetres
ORDER BY Coordinates.STDistance(@searchPoint);

Find Nearest Locations

-- Find 5 nearest locations
DECLARE @searchPoint GEOGRAPHY = geography::Point(55.9533, -3.1883, 4326);

SELECT TOP 5
    Name,
    Address,
    Coordinates.STDistance(@searchPoint) / 1000 AS DistanceKm
FROM Locations
ORDER BY Coordinates.STDistance(@searchPoint);

Spatial Relationships

Point in Polygon (Geofencing)

-- Define a region
DECLARE @scotland GEOGRAPHY = geography::STPolyFromText(
    'POLYGON((-7.5 54.5, -7.5 61.0, -0.5 61.0, -0.5 54.5, -7.5 54.5))', 
    4326
);

-- Check if a point is within the region
DECLARE @testPoint GEOGRAPHY = geography::Point(55.9486, -3.1999, 4326);

SELECT 
    CASE WHEN @scotland.STContains(@testPoint) = 1 
         THEN 'Inside' ELSE 'Outside' 
    END AS Location;

Intersecting Regions

-- Check if two regions overlap
DECLARE @region1 GEOGRAPHY = geography::STPolyFromText(...);
DECLARE @region2 GEOGRAPHY = geography::STPolyFromText(...);

SELECT @region1.STIntersects(@region2) AS DoTheyOverlap;

Common Spatial Methods

Method	Description
STDistance()	Distance between two spatial objects
STIntersects()	Returns 1 if objects intersect
STContains()	Returns 1 if first object contains second
STWithin()	Returns 1 if first object is within second
STBuffer()	Creates a buffer zone around an object
STArea()	Returns the area of a polygon
STLength()	Returns length of a line

Spatial Indexing

Spatial queries can be slow on large tables without proper indexing. SQL Server uses a grid-based spatial index.

Creating a Spatial Index

CREATE SPATIAL INDEX IX_Locations_Coordinates
ON Locations(Coordinates)
USING GEOGRAPHY_AUTO_GRID
WITH (CELLS_PER_OBJECT = 16);

Index Options

• GEOGRAPHY_AUTO_GRID – SQL Server automatically determines optimal grid settings
• GEOGRAPHY_GRID – Manual grid configuration (advanced)
• CELLS_PER_OBJECT – How many grid cells can cover a single object (higher = more precision, larger index)

Query Hints for Index Usage

SELECT Name
FROM Locations WITH (INDEX(IX_Locations_Coordinates))
WHERE Coordinates.STDistance(@searchPoint) <= 10000;

Practical Examples

Store Locator

CREATE PROCEDURE FindNearbyStores
    @Latitude FLOAT,
    @Longitude FLOAT,
    @RadiusKm FLOAT = 10,
    @MaxResults INT = 20
AS
BEGIN
    DECLARE @searchPoint GEOGRAPHY = geography::Point(@Latitude, @Longitude, 4326);
    DECLARE @radiusMetres FLOAT = @RadiusKm * 1000;
    
    SELECT TOP (@MaxResults)
        StoreId,
        StoreName,
        Address,
        ROUND(Location.STDistance(@searchPoint) / 1000, 2) AS DistanceKm
    FROM Stores
    WHERE Location.STDistance(@searchPoint) <= @radiusMetres
    ORDER BY Location.STDistance(@searchPoint);
END;

Delivery Zone Check

CREATE FUNCTION IsInDeliveryZone(
    @Latitude FLOAT,
    @Longitude FLOAT
)
RETURNS BIT
AS
BEGIN
    DECLARE @customerLocation GEOGRAPHY = geography::Point(@Latitude, @Longitude, 4326);
    DECLARE @inZone BIT = 0;
    
    IF EXISTS (
        SELECT 1 FROM DeliveryZones 
        WHERE ZoneArea.STContains(@customerLocation) = 1
    )
        SET @inZone = 1;
    
    RETURN @inZone;
END;

Route Length Calculation

-- Calculate total length of a delivery route
SELECT 
    RouteId,
    RoutePath.STLength() / 1000 AS LengthKm
FROM DeliveryRoutes
WHERE ScheduledDate = @date;

Common Issues

Coordinate Order Confusion

Geography::Point() takes latitude first, then longitude. WKT format (POINT) takes longitude first, then latitude. This is a common source of errors.

-- These are equivalent:
geography::Point(55.9486, -3.1999, 4326)  -- lat, long
geography::STPointFromText('POINT(-3.1999 55.9486)', 4326)  -- long, lat

SRID Mismatch

Operations between spatial objects with different SRIDs will fail. Always use the same SRID consistently.

Polygon Ring Direction

For geography polygons, the ring direction matters. Points should be ordered so the interior is on the left. If your polygon appears to cover the whole world except your intended area, reverse the point order.

Performance with Large Data

Without spatial indexes, distance queries scan the entire table. Always create spatial indexes for tables with many rows. Consider using a bounding box filter before precise distance calculations.

Frequently Asked Questions