Trying to find distance between two sets of Lat-longs

Question

I have a dataset that has a 100,000+ addresses, and each of the addresses have two sets of Latitude and Longitude (basically x_lat, x_lon, y_lat, y_lon) on MSSQL. The lat-longs are for the same address but from two different sources, and I am trying to find the difference in distance between the two. I've done some research, and am trying to use the code below (from AakashM on Stackoverflow) but I'm getting an error saying that the subquery returned more than 1 value, which is not permitted.

   DECLARE @orig_lat DECIMAL(12, 9)
    DECLARE @orig_lng DECIMAL(12, 9)
    SET @orig_lat=(SELECT x_lat FROM #Distance) SET @orig_lng= (SELECT x_lon FROM #Distance)
    
    DECLARE @orig geography = geography::Point(@orig_lat, @orig_lng, 4326);
    
    SELECT *,
        @orig.STDistance(geography::Point(y_Lat, y_Lon, 4326)) 
           AS distance
    --INTO #includeDistances
    FROM #Distance 

The error makes sense because the @orig_lat and @orig_lon need to be set to a specific lat-long, but is there a way I can set it to the column instead so I can get the distance for each of the addresses, without having to manually input the lat-longs? I've included an image of the first two rows of the #Distance dataset below:

enter image description here

Answer 1

If I understand you correctly, you're try to calculate the distinct between x and y lat/lng's.

Example

SELECT *
      ,Distance = geography::Point(x_Lat, x_Lon, 4326).STDistance(geography::Point(y_Lat, y_Lon, 4326)) 
 From  YourTable 
 Where x_lat<>y_lat
    or x_lon<>y_lon

There WHERE is optional.