SQL Geography Latitude/Longitude distance calc

Question

I have trouble using geography to calculate distance in miles using the format of my table.

Latitude and Longitude of both locations are side by side:

id | A_Latitude | A_Longitude | B_Latitude | B_Longitude

I'm trying to get the point of A, along with the point of B, and return the distance between A and B in miles.

I've tried a few things, including something similar to:

DECLARE @orig geography 
DECLARE @end geography
SELECT
@orig = geography::Point(A_LATITUDE, A_LONGITUDE, 4326) 
,@end = geography::Point(B_LATITUDE, B_LONGITUDE, 4326) 
FROM table1
SELECT 
    ROUND(@END.STDistance(@ORIG)/1609.344,2) AS MILES
    ,@END.STDistance(@ORIG) AS METERS
    ,table1.*
FROM table1;

Where I'm getting a repeating value for miles and meters across all rows. Could anyone please suggest how I should be structuring this query to get what I'm looking for?

EDIT: Thanks SQL Surfer!

WITH X AS 
(SELECT 
 geography::Point(A_LATITUDE, A_LONGITUDE, 4326) A
,geography::Point(B_LATITUDE, B_LONGITUDE, 4326) B
,ID
FROM TABLE1)

SELECT 
ROUND(X.B.STDistance(X.A)/1609.344,2) AS MILES
,X.B.STDistance(X.A) AS METERS
,T.*
FROM TABLE1 T
LEFT JOIN X ON T.ID = X.ID

Two choices. 1: put a where clause to select only one record in your original first query. or 2: do not use @vars but use a temp table to bring all the records from the first part down to the second part of your query. You actually can combine them into one query if you want to. — Sql Surfer, Jun 01 '15 at 02:00
Perfect, thank you @Sql Surfer ! I'm not sure how this works, can you post an answer so I can mark it as the one? — Onohalp, Jun 01 '15 at 02:49

score 6 · Accepted Answer · answered Jun 01 '15 at 03:15

Here's what I'd do:

WITH X AS 
(
   SELECT 
      geography::Point(A_LATITUDE, A_LONGITUDE, 4326) A
     ,geography::Point(B_LATITUDE, B_LONGITUDE, 4326) B
     ,*
   FROM TABLE1
)

SELECT 
   ROUND(B.STDistance(A)/1609.344, 2) AS MILES
   ,B.STDistance(X.A) AS METERS
   ,*
FROM X

If you're thinking of doing this often (and on the fly), consider adding computed columns to your table for not only the geography types, but also the distance between them. At that point, it's just a simple query from the table!

Thaks, It Works for me. – Hardeep Singh Jul 28 '17 at 12:47 — Hardeep Singh, Jul 28 '17 at 12:47

SQL Geography Latitude/Longitude distance calc

1 Answers1