vba 邮政编码距离计算器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6612943/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Zip code distance calculator
提问by MAW74656
I have a spreadsheet of addresses and I need to calculate the distance between all of their zip codes and my zip code. I'm fairly flexible on the method used, but I'm hoping for some sort of webservice or mathematic algorithm. US addresses only. Basically I need to feed in 2 zip codes and get out the distance between them.
我有一个地址电子表格，我需要计算他们所有邮政编码和我的邮政编码之间的距离。我对使用的方法相当灵活，但我希望有某种网络服务或数学算法。仅限美国地址。基本上我需要输入 2 个邮政编码并拉出它们之间的距离。
I'm willing to use Excel formulas or VBA, and I can even code something in C#.net if needed.
我愿意使用 Excel 公式或 VBA，如果需要，我什至可以在 C#.net 中编写代码。
How would you go about calculating these distances?
你会如何计算这些距离？
采纳答案by MichaelP
It's pretty simple actually. Download a database of zip codes' GPS coordinates, there's plenty of sites that have this data available for download. They would list the coordinates for the center of the zip code.
其实很简单。下载邮政编码的 GPS 坐标数据库，有很多网站可以下载这些数据。他们会列出邮政编码中心的坐标。
Use a formula to calculate shortest distance: (ex: http://www.movable-type.co.uk/scripts/latlong.html)
使用公式计算最短距离：（例如：http: //www.movable-type.co.uk/scripts/latlong.html）
回答by sealz
You could use Latitude and Longitude.
您可以使用纬度和经度。
Excel:
电子表格：
=IF(SIN(Lat1) * SIN(Lat2) + COS(Lat1) * COS(Lat2) * COS(Long1 - Long2) > 1,
RadiusofEarth * ACOS(1), RadiusofEarth *
ACOS(SIN(Lat1) * SIN(Lat2) + COS(Lat1) * COS(Lat2) * COS(Long1-Long2)))
VB.net imports System.Math
VB.net 导入 System.Math
Private Function Distance(ByVal lat1 As Double, ByVal lon1 As Double, ByVal lat2 As Double, ByVal lon2 As Double, ByVal unit As String) As Double
Dim theta As Double = lon1 - lon2
Dim dist = System.Math.Sin(deg2rad(lat1)) * System.Math.Sin(deg2rad(lat2)) + System.Math.Cos(deg2rad(lat1)) * System.Math.Cos(deg2rad(lat2)) * System.Math.Cos(deg2rad(theta))
dist = System.Math.Acos(dist)
dist = rad2deg(dist)
dist = dist * 60 * 1.1515
Select Case unit
Case "K"
dist = dist * 1.609344
Case "N"
dist = dist * 0.8684
End Select
Return dist
End Function
Other Useful Links(First one also mentions VBA alternatives)
其他有用的链接（第一个还提到了 VBA 替代品）
ExcelLatLong(Also mentions VBA alternatives)
ExcelLatLong（还提到了 VBA 替代品）
EDIT: Link added due to comment discussion
编辑：由于评论讨论而添加链接
