Q: Find distance between two points using latitude and longitude in SQL Server I have been looking at this question and just want to know if this can be done in SQL Server.
Find distance between two points using latitude and longitude in mysql
This was the completed answer in a SQL Fiddle : http://sqlfiddle.com/#!9/21e06/412/0
(in miles use 69.0 instead of 111.1111)
and it works they way I want it to.
I just need it to work in SQL Server Management Studio v18
Update: sorry for the lazy Question
CREATE TABLE #city
    (id int, city int, Latitude float, Longitude float)
;
    
INSERT INTO #city
    (id, city, Latitude, Longitude)
VALUES
    (1, 3, 34.44444, 84.3434),
    (2, 4, 42.4666667, 1.4666667),
    (3, 5, 32.534167, 66.078056),
    (4, 6, 36.948889, 66.328611),
    (5, 7, 35.088056, 69.046389),
    (6, 8, 36.083056, 69.0525),
    (7, 9, 31.015833, 61.860278)
;


SELECT a.city AS from_city, b.city AS to_city, 
   --111.1111 *
   -- DEGREES(ACOS(LEAST(1.0, COS(RADIANS(a.Latitude))
   --      * COS(RADIANS(b.Latitude))
   --      * COS(RADIANS(a.Longitude) - RADIANS(b.Longitude))
   --      + SIN(RADIANS(a.Latitude))
   --      * SIN(RADIANS(b.Latitude))))) AS distance_in_km
   SQRT(POWER(69.1 * ( a.Latitude - b.Latitude),  
2) + POWER(69.1 * ( b.Longitude  
- a.Latitude )  
* COS(a.Latitude / 57.3), 2))  
  FROM #city AS a
  JOIN #city AS b ON a.id <> b.id
 WHERE a.city = 3 AND b.city = 7

 DECLARE @sourceLatitude FLOAT = 31.015833;  
DECLARE @sourceLongitude FLOAT = 61.860278;  
DECLARE @destinationLatitude FLOAT = 32.534167;  
DECLARE @destinationLongitude FLOAT = 66.078056;  
DECLARE @Location FLOAT  
SET @Location = SQRT(POWER(69.1 * ( @destinationLatitude - @sourceLatitude),  
2) + POWER(69.1 * ( @sourceLongitude  
- @destinationLongitude )  
* COS(@destinationLatitude / 57.3), 2))  
PRINT @Location 

i have tried both ways
the 1st way LEAST does not work in SSMS
and the 2nd way is giving out different answers to when i try the last bit of code and when i run it in my sql

A: drop table #city
CREATE TABLE #city
    (id int, city int, Latitude float, Longitude float)
;
    
INSERT INTO #city
    (id, city, Latitude, Longitude)
VALUES
    (1, 3, 34.44444, 84.3434),
    (2, 4, 42.4666667, 1.4666667),
    (3, 5, 32.534167, 66.078056),
    (4, 6, 36.948889, 66.328611),
    (5, 7, 35.088056, 69.046389),
    (6, 8, 36.083056, 69.0525),
    (7, 9, 31.015833, 61.860278)
;


SELECT a.city AS from_city, b.city AS to_city, 
   SQRT(POWER(69.0 * ( a.Latitude - b.Latitude),2) 
   + POWER(69.0 * ( b.Longitude  - a.Longitude )  
   * COS(a.Latitude / 57.3), 2))  as Miles
  FROM #city AS a
  JOIN #city AS b ON a.id <> b.id
 WHERE a.city = 3 AND b.city = 7drop table #city
CREATE TABLE #city
    (id int, city int, Latitude float, Longitude float)
;
    
INSERT INTO #city
    (id, city, Latitude, Longitude)
VALUES
    (1, 3, 34.44444, 84.3434),
    (2, 4, 42.4666667, 1.4666667),
    (3, 5, 32.534167, 66.078056),
    (4, 6, 36.948889, 66.328611),
    (5, 7, 35.088056, 69.046389),
    (6, 8, 36.083056, 69.0525),
    (7, 9, 31.015833, 61.860278)
;


SELECT a.city AS from_city, b.city AS to_city, 
   SQRT(POWER(69.0 * ( a.Latitude - b.Latitude),2) 
   + POWER(69.0 * ( b.Longitude  - a.Longitude )  
   * COS(a.Latitude / 57.3), 2))  as Miles
  FROM #city AS a
  JOIN #city AS b ON a.id <> b.id
 WHERE a.city = 3 AND b.city = 7

got it to work lol Thanks Doug and lanru
