title: SQL Server - Calculating Distance Between Two Latitude's & Longitude's
body: <p>Using SQL Server 2014.</p>
  
  <p>I'm trying to get SQL Server to calculate the distance, in Kilometres, Miles and Nautical Miles between airports from around the world. Mostly, the method below works, but it appears to fail when the distance goes over 10,000Km (Although this is just an assumption). </p>
  
  <pre><code>    DECLARE @Radius_Km AS FLOAT
  DECLARE @Radius_Mi AS FLOAT
  DECLARE @Radius_Nm AS FLOAT
  DECLARE @Pi AS FLOAT
  DECLARE @D2R AS FLOAT
  
  SET @Radius_Km = '6370.97327862'
  SET @Radius_Mi = '3958.73926185'
  SET @Radius_Nm = '3440.05036642'
  SET @Pi = '3.14159265358979'
  SET @D2R = @Pi / 180;
  
  
  WITH RawData AS
  (
      SELECT DISTINCT Sched.AirlineName, Sched.AirlineIATA, Sched.AirlineICAO, Sched.FlightNo,
  
              Sched.DepartureAirportName, Sched.DepartureAirportIATA, DepAprt.AirportICAO AS [DepartureAirportICAO], 
  
              DepAprt.Latitude AS [DepartureAirportLatitude], DepAprt.Longitude AS [DepartureAirportLongitude],
  
              Sched.ArrivalAirportName, Sched.ArrivalAirportIATA, ArrAprt.AirportICAO AS [ArrivalAirportICAO], 
  
              ArrAprt.Latitude AS [ArrivalAirportLatitude], ArrAprt.Longitude AS [ArrivalAirportLongitude]
  
      FROM VAS_Live.dbo.RawData_FR24 AS Sched
  
          LEFT JOIN VAS_Live.dbo.ReferenceData_Airports AS DepAprt
              ON Sched.DepartureAirportIATA = DepAprt.AirportIATA
  
          LEFT JOIN VAS_Live.dbo.ReferenceData_Airports AS ArrAprt
              ON Sched.ArrivalAirportIATA = ArrAprt.AirportIATA
  
  ),
  
  RadianConvert AS
  (
      SELECT *,
  
          ((DepartureAirportLatitude / 180) * @Pi) AS [DALat],
          ((DepartureAirportLongitude / 180) * @Pi) AS [DALon],
          ((ArrivalAirportLatitude / 180) * @Pi) AS [AALat],
          ((ArrivalAirportLongitude / 180) * @Pi) AS [AALon]
  
      FROM RawData
  ),
  
  CentralSphericalAngle AS
  (
      SELECT *,
  
      (Sin(DALat) * Sin(AALat)) + (Cos(DALat) * Cos(AALat) * Cos(AALon - DALon)) AS [Test]
  
      FROM RadianConvert
  ),
  
  TestCTE AS
  (
      SELECT *,
  
          @Radius_Mi * ATAN(SQRT(1 - POWER(Test, 2)) / Test) AS [Test2]
  
      FROM CentralSphericalAngle
  )
  
  SELECT *
  FROM TestCTE
  WHERE DepartureAirportIATA = 'LHR' AND ArrivalAirportIATA = 'SIN'
  </code></pre>
  
  <p>For the example above, the LHR Lat &amp; Lon are as follows:</p>
  
  <p>51.4775
  -0.461389</p>
  
  <p>The SIN Lat &amp; Long are as follows:</p>
  
  <p>1.35019
  103.994</p>
  
  <p>I already know the distance is roughly:</p>
  
  <p>Km - 10,883</p>
  
  <p>Mi - 6,762</p>
  
  <p>Nm - 5,876</p>
  
  <p>Any idea why SQL is giving me -5674?</p>
  
  <p>I used to use VBA and Excel to calculate this data, which worked. I noticed that the Arrival AALat &amp; DALon are incorrectly calculating when converting to Radians - but I have no idea why.</p>
  
  <p>Thanks</p>
  
  <p>Matthew</p>