Search code examples
sqlsql-servert-sqlgeolocationgeography

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

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?

Solution

  • 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

    enter image description here

    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