Monday, October 23, 2023

SQL Server Calculate Distance (Latitude & Latitude)

To calculate the distance between two points on the Earth's surface using SQL Server, you can use the Haversine formula. The Haversine formula is a well-known method for calculating distances between two points given their latitude and longitude coordinates. Here's an example of how you can calculate the distance between two points using SQL Server:

```sql

DECLARE @lat1 FLOAT = 52.5200; -- Latitude of the first point

DECLARE @lon1 FLOAT = 13.4050; -- Longitude of the first point

DECLARE @lat2 FLOAT = 48.8566; -- Latitude of the second point

DECLARE @lon2 FLOAT = 2.3522;  -- Longitude of the second point


DECLARE @R FLOAT = 6371; -- Earth's radius in kilometers


-- Convert degrees to radians

SET @lat1 = RADIANS(@lat1);

SET @lon1 = RADIANS(@lon1);

SET @lat2 = RADIANS(@lat2);

SET @lon2 = RADIANS(@lon2);


-- Haversine formula

DECLARE @dlat FLOAT = @lat2 - @lat1;

DECLARE @dlon FLOAT = @lon2 - @lon1;

DECLARE @a FLOAT = SIN(@dlat / 2) * SIN(@dlat / 2) + COS(@lat1) * COS(@lat2) * SIN(@dlon / 2) * SIN(@dlon / 2);

DECLARE @c FLOAT = 2 * ATN2(SQRT(@a), SQRT(1 - @a));

DECLARE @distance FLOAT = @R * @c;


-- Result in kilometers

SELECT @distance AS DistanceInKilometers;

```

In this example, we have two sets of latitude and longitude coordinates (lat1, lon1) and (lat2, lon2). The Haversine formula is used to calculate the distance between these two points on the Earth's surface, and the result is in kilometers.

You can modify the values of `@lat1`, `@lon1`, `@lat2`, and `@lon2` to calculate the distance between different pairs of coordinates. The result will be the distance between the two points in kilometers.

No comments:

Post a Comment