Distance Between Two Points on the Earth With the geography Type

The distance between two points on the earth takes one method call once you store them as geography values. STDistance does the measuring. This post builds a small tour table and checks the answers against two do-it-yourself formulas.

The Sample Table

Five stops on a tour of US cities, each with a latitude and a longitude. The Spot column starts empty and will hold the geography point. The coordinates are city-center values rounded to four decimals. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE TourStops (
  StopID INT NOT NULL PRIMARY KEY,
  City VARCHAR(20) NOT NULL,
  Latitude FLOAT NOT NULL,
  Longitude FLOAT NOT NULL,
  Spot GEOGRAPHY NULL
);
INSERT INTO TourStops (StopID, City, Latitude, Longitude) VALUES
  (1, 'Seattle', 47.6062, -122.3321), (2, 'Spokane', 47.6588, -117.4260), (3, 'Chicago', 41.8781, -87.6298),
  (4, 'Boston', 42.3601, -71.0589), (5, 'Philadelphia', 39.9526, -75.1652);
GO

From Two Numbers to One Point

GEOGRAPHY::Point takes the latitude first, then the longitude, then an SRID. The SRID 4326 names WGS 84, the system that GPS uses. One UPDATE fills the Spot column for every stop.

UPDATE TourStops SET Spot = GEOGRAPHY::Point(Latitude, Longitude, 4326);

Look at the stored points. STAsText writes each point as text.

SELECT City, Latitude, Longitude, Spot.STAsText() AS WellKnownText FROM TourStops ORDER BY StopID;

The text form lists the longitude first: POINT (-122.3321 47.6062) is Seattle. That’s the standard text format. Point( ) wants the latitude first, so the two orders are easy to mix up.

Distance Between Two Points

STDistance measures the shortest distance between two geography values. Put Seattle and Boston in two variables and ask.

DECLARE @Seattle GEOGRAPHY = (SELECT Spot FROM TourStops WHERE City = 'Seattle');
DECLARE @Boston GEOGRAPHY = (SELECT Spot FROM TourStops WHERE City = 'Boston');
SELECT @Seattle.STDistance(@Boston) AS Meters,
  CAST(@Seattle.STDistance(@Boston) / 1000 AS DECIMAL(8,1)) AS Kilometers,
  CAST(@Seattle.STDistance(@Boston) / 1609.344 AS DECIMAL(8,1)) AS Miles;

The answer is 4,010,960 meters, which is 4,011.0 kilometers or 2,492.3 miles. STDistance always returns meters for SRID 4326, so divide by 1,000 for kilometers and by 1,609.344 for miles. You can confirm the unit in the system view of spatial reference systems.

SELECT spatial_reference_id, unit_of_measure FROM sys.spatial_reference_systems WHERE spatial_reference_id = 4326;

The unit of measure for 4326 is the metre.

Distance From One Stop to All

A cross join with one row gives the distance from Seattle to every stop in a single query.

SELECT t.City,
  CAST(t.Spot.STDistance(s.Spot) / 1000 AS DECIMAL(8,1)) AS Kilometers,
  CAST(t.Spot.STDistance(s.Spot) / 1609.344 AS DECIMAL(8,1)) AS Miles
FROM TourStops AS t
CROSS JOIN (SELECT Spot FROM TourStops WHERE City = 'Seattle') AS s
ORDER BY Kilometers;

Spokane is 368.7 km away, Chicago 2,796.1 km, Philadelphia 3,829.0 km and Boston 4,011.0 km. Seattle shows 0 for itself.

Why Not a Flat Formula?

A flat map treats longitude and latitude as x and y on a sheet of paper. This query applies the Pythagorean formula to the degrees, then converts using 111.195 km per degree.

SELECT CAST(SQRT(POWER(a.Latitude - b.Latitude, 2) + POWER(a.Longitude - b.Longitude, 2)) * 111.195 AS DECIMAL(8,0)) AS FlatMapKm,
  CAST(a.Spot.STDistance(b.Spot) / 1000 AS DECIMAL(8,0)) AS GeographyKm
FROM TourStops AS a
JOIN TourStops AS b ON a.City = 'Seattle' AND b.City = 'Boston';

The flat formula says 5,731 km, and geography says 4,011.0 km. A degree of longitude gets shorter as you move away from the equator, and the flat formula ignores that.

A Sphere Formula Comes Close

A great-circle formula treats the earth as a sphere with a radius of 6,371 km. That’s much better, but not identical.

SELECT CAST(6371 * ACOS(SIN(RADIANS(a.Latitude)) * SIN(RADIANS(b.Latitude))
  + COS(RADIANS(a.Latitude)) * COS(RADIANS(b.Latitude)) * COS(RADIANS(b.Longitude - a.Longitude))) AS DECIMAL(8,1)) AS SphereKm,
  CAST(a.Spot.STDistance(b.Spot) / 1000 AS DECIMAL(8,1)) AS EllipsoidKm
FROM TourStops AS a
JOIN TourStops AS b ON a.City = 'Seattle' AND b.City = 'Boston';

The sphere gives 4,000.1 km, and STDistance gives 4,011.0 km, a gap of 10.9 km. The earth is slightly flattened, and the geography type measures on that real shape. If your own formula differs a little from STDistance, the shape is the reason.

Swapped Arguments

What if you pass the longitude first? A longitude of -122.3321 is not a valid latitude, so SQL Server refuses.

SELECT GEOGRAPHY::Point(-122.3321, 47.6062, 4326);

The error is message 6522, and its text says latitude values must be between -90 and 90 degrees. A swap only fails like this when the longitude is outside that range. Otherwise you get a valid point in the wrong place, so check a few known cities after loading data.

What STDistance Does Not Do

STDistance measures a straight line over the surface of the earth, as a bird would fly. It knows nothing about roads, so a drive between the same two cities is longer.

Clean Up

Remove the sample table when you finish.

DROP TABLE TourStops;

What to Remember

Store latitude and longitude as a geography point with Point(latitude, longitude, 4326). Call STDistance for the distance in meters. Expect a flat formula to be far off, and a sphere formula to be a few kilometers off.

To go further, read geography Point: Latitude Comes Before Longitude, Finding the Nearest Store With the geography Type and Spatial Indexes: Speeding Up Distance Searches.

A distance is not a line on a flat map, it is a path over a curved surface.

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