I've written (adapted from c#) some SQL to do spherical trigonometry, working out distance from 2 grid references. The example below calculates Hyde Park To Regent's Park in London, as the crow flies 1.76 miles.
Declare @Lat1 As Real = -0.167919 Declare @Long1 As Real = 51.5072682 Declare @Lat2 As Real = -0.1591581 Declare @Long2 As Real = 51.5312705 Declare @dLat1InRad As Real = @Lat1 * (Pi() / 180.0) Declare @dLong1InRad As Real = @Long1 * (Pi() / 180.0) Declare @dLat2InRad As Real = @Lat2 * (Pi() / 180.0) Declare @dLong2InRad As Real = @Long2 * (Pi() / 180.0) Declare @dLongitude As Real = @dLong2InRad - @dLong1InRad Declare @dLatitude As Real = @dLat2InRad - @dLat1InRad Declare @a As Real = Power(Sin(@dLatitude / 2.0), 2.0) + Cos(@dLat1InRad) * Cos(@dLat2InRad) * Power(Sin(@dLongitude / 2.0), 2.0) Declare @c As Real = 2.0 * Asin(Sqrt(@a)) Declare @kEarthRadiusMiles As Real = 3956.0 Declare @dDistance As Real = Round(@kEarthRadiusMiles * @c, 2) SELECT @dDistance
Executing by its self it works fine, but I'm really struggeling to work out how to integrate it with a query example, how would I calculate Hyde Park to an employees location??
Declare @Lat1 As Real = -0.167919 Declare @Long1 As Real = 51.5072682 Declare @Lat2 As Real = 0 Declare @Long2 As Real = 0 SELECT Name, Address1, Address2, Postcode, Geolocation.Longitude, Geolocation.Latitude FROM Employees FULL OUTER JOIN Geolocation ON Geolocation.Postcode = REPLACE (Postcode, RIGHT(Postcode, 3), '') WHERE EmpID = 1001