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

Precise Distance Calculation Between Two Geographic Points in SQL Server

SQL Server often requires calculating distances between latitude and longitude coordinates. While the Haversine formula is common, its accuracy limitations necessitate a more robust solution.

Leveraging the geography Data Type

SQL Server 2008 and later versions offer the geography data type, ideal for efficient and accurate spatial data handling, including distance computations. The following code demonstrates its use:

<code class="language-sql">DECLARE @source geography = 'POINT(0 51.5)';
DECLARE @target geography = 'POINT(-3 56)';

SELECT @source.STDistance(@target);</code>

This returns the distance (in kilometers) between the two points. For example, the above will return approximately 538 km. The geography type's extensive spatial methods make it the recommended approach for geographical data in SQL Server.

Integrating with Existing Data Structures

Maintaining your current data structure is possible while still benefiting from the STDistance method. Convert your latitude and longitude values into geography instances:

<code class="language-sql">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;</code>

This approach ensures accurate distance calculations without major data restructuring.

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