Find distance between two points using latitude and longitude in SQL Server

Question

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

SQL Server Management Studio is a client that you use to connect to SQL Server. For distance computations have a look at the Spacial types https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/spatial-types-geography?view=sql-server-ver15 — squillman, Sep 16 '21 at 12:08
The above would work in any SSMS that supports the version of SQL Server you are using, and where that version supports spatial data. SSMS 18 supports SQL Server 2008-2019, and certainly 2012+ all support spatial queries. I can't recall for SQL Server 2008, however, that's been completely unsupported for over 2 years now, so you shouldn't be using it. TL;DR: the above likely already works in SSMS18. (I can't see the SQL, because you posted it off site, not in your question, and I can't access the site.) — Thom A, Sep 16 '21 at 12:15
@DougCoats you know that "I didn't bother researching" isn't a valid close reason, right? It's (often) a reason to downvote, but it's not a reason to close. — Thom A, Sep 16 '21 at 12:17
@larnu OK the guidelines have changed significantly since I last read them. They used to be a bit more rigid. So my bad — Doug Coats, Sep 16 '21 at 12:23
I don't think it has *ever* been a close vote reason, @DougCoats . There's probably a question about it somewhere on [meta]. — Thom A, Sep 16 '21 at 12:27
@larnu i went to the way back machine and there is a section that says "show your work" and "dont ask questions that you havent tried". https://web.archive.org/web/20150207013653/http://stackoverflow.com/tour So I guess when i google it and find the answer immediately i just assumed they didnt bother trying it themselves or bother researching. I could dig further into the way back machine and try and find other instances of the guidelines changing but that would be a waste of time. — Doug Coats, Sep 16 '21 at 12:37
Hello sorry for the lazy answer but i just wanted a quick response @DougCoats i have researched it and i found the same thing you did but the page doesn't explain the background of the code and it is giving me wrong numbers , i have updated the question now and would be of for talking about it a bit more as i have not found a answer — jay dublin, Sep 16 '21 at 13:11
@Larnu i have updated the code please have a look and see if you know the answer — jay dublin, Sep 16 '21 at 13:12

score 0 · Answer 1 · answered Sep 16 '21 at 13:35

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

Find distance between two points using latitude and longitude in SQL Server

1 Answers1