See also:
You can determine the distance between ZIP codes which is useful for determining, for instance, how many enrolled students have a permanent address within some range of Winona.
The ZipCodes table in InstResearch database includes the latitude and longitude for each ZIP code. Then, you can use a mathematical formula to determine the distance between to points. In SQL parlance:
declare @lat1 float
declare @long1 float
declare @lat2 float
declare @long2 float
select @lat1=Latitude, @long1=Longitude from ZipCodes where ZIP=88414
select @lat2=Latitude, @long2=Longitude from ZipCodes where ZIP=55987
select 3963.1*acos(round(sin(radians(@lat1))*sin(radians(@lat2)) + cos(radians(@lat1))*cos(radians(@lat2))*cos(radians(@long1)-radians(@long2)),15)) Distance
You can also use the GEOGRAPHY data type in SQL
;with cte_Geography as (
select *
,geography::Parse('POINT(' + cast([Longitude] as varchar(20)) + ' ' + cast([Latitude] as varchar(20)) + ')') as GeographyData
from InstResearch.dbo.ZipCodes
where [State] is not null
)
select *
-- distance from Rochester Campus
,GeographyData.STDistance(geography::Parse('POINT(92.421885 44.014423)')) * 0.000621371 as Miles
from cte_Geography