How Can SQL Server Accurately Calculate the Distance Between Two Latitude/Longitude Points?

Precisely Calculating Distances Between Geographic Points in SQL Server

Accurate distance calculations between map coordinates are crucial. While some formulas offer approximations, SQL Server's built-in features provide superior precision.

Leveraging SQL Server's geography Data Type (SQL Server 2008 and later)

The geography data type (available in SQL Server 2008 and later versions) offers a robust solution for calculating distances. Here's how:

DECLARE @source geography = 'POINT(0 51.5)'
DECLARE @target geography = 'POINT(-3 56)'

SELECT @source.STDistance(@target)

This query returns the distance in meters.

Integrating the geography Type with Existing Data

If you're working with an established database schema, you can seamlessly integrate the geography type. The Point method facilitates this:

DECLARE @orig_lat DECIMAL(12, 9)
DECLARE @orig_lng DECIMAL(12, 9)
SET @orig_lat=53.381538; SET @orig_lng=-1.463526;

DECLARE @orig geography = geography::Point(@orig_lat, @orig_lng, 4326);

SELECT *,
    @orig.STDistance(geography::Point(dest.Latitude, dest.Longitude, 4326)) AS distance
FROM #orig dest;

This example preserves your existing data structure while utilizing the accuracy of the geography data type for distance computations.

The above is the detailed content of How Can SQL Server Accurately Calculate the Distance Between Two Latitude/Longitude Points?. For more information, please follow other related articles on the PHP Chinese website!