SQL Server calculate distances between 2 sets of columns of latitude / longitude

Question

I'm trying to calculate the closest weather station to a given zip code. I have data with the lat/lon for the weather station and the zip codes. I also have a way to calculate distance for a 2 points (here). What I'm looking for is a way to create a column that has the distances for multiple instances.

StationNum  lat     lon     Zip     lat      lon   distance
123         34.66   98.32   12345   33.78   91.91   ???
456         33.03   96.8    23456   35.23   92.23   ???
789         32.29   96.85   34567   33.09   92.68   ???

Is there a way to do that or do I need to write out the math by hand?

Answer 1

With the GEOGRAPHY type

You had duplicate field names so I renamed them to Lat1/Lon1 and Lat2/Lon2. If this is a join, add the alias a.lat/a.lon b.lat/b.lon.

Example

Declare @YourTable Table ([StationNum] varchar(50),[lat1] float,[lon1] float,[Zip] varchar(50),[lat2] float,[lon2] float)
Insert Into @YourTable Values 
 (123,34.66,98.32,12345,33.78,91.91)
,(456,33.03,96.8,23456,35.23,92.23)
,(789,32.29,96.85,34567,33.09,92.68)

Select * 
      ,InMeters = GEOGRAPHY::Point([Lat1], [Lon1], 4326).STDistance(GEOGRAPHY::Point([Lat2], [Lon2], 4326))
      ,InMiles  = GEOGRAPHY::Point([Lat1], [Lon1], 4326).STDistance(GEOGRAPHY::Point([Lat2], [Lon2], 4326)) / 1609.344
 from @YourTable

Returns

Edit - For your consideration

Consider adding a Geography field to your source table(s). This will eliminate the redundant GEOGRAPHY::Point() function calls

Update YourTable Set GeoPoint = GEOGRAPHY::Point([Lat], [Lon], 4326)

So then the calculation for distance would simply be

  ,InMeters  = A.GeoPoint.STDistance(B.GeoPoint) 
  ,InMiles   = A.GeoPoint.STDistance(B.GeoPoint) / 1609.344