查找邮政编码指定距离内的所有邮政编码 [英] Find all Zipcodes within specified distance of a zipcode
问题描述
我知道这个问题听起来像是几个问题的重复，但是这些问题都没有回答我想要的. 我想知道是否有人知道如何在指定邮政编码范围内查找其他邮政编码.我有纬度为n的邮政编码数据库，但不确定如何在VB.net中做到这一点
I know this question sounds like a repeat of few questions here, but none of those questions answered what i wanted. I am interested in knowing if someone know how to find other zipcodes within the radius of a specified zipcode. I have the zipcode database with me with latitudes n longitudes but am not sure how to do this in VB.net
例如-90069是一个邮政编码，如果有人说半径5英里，那么我希望弹出所有邮政编码，例如90210、90045、90034等.
e.g - 90069 is a zipcode and if someone says 5 miles radius then i want all the zipcodes like 90210,90045,90034 etc to pop up.
代码示例将不胜感激
我有MySQL服务器数据库.
I have MySQL server database.
推荐答案
这是我很久以前写的东西，可能会使您朝正确的方向前进.
Here's something I wrote quite a while back that may start you in the correct direction.
当您请求VB.Net时，您真正需要的是一个执行"好"的查询圆距离"计算，以确定经纬度标识的两个点之间的距离.
While you asked for VB.Net, what you really need is a query that does a "Great Circle Distance" calculation to determine the distance between two points identified by latitude and longitude.
因此，请进行以下假设:
So, making the following assumptions:
- 您的邮政编码数据在一个表中.
- 所说的表具有lat和lon的属性，它们是邮政编码的近似质心
您可以使用LINQ to SQL查询，并使用类似的方法生成所需的结果集
You could use a LINQ to SQL query that produces the desired result set using something like this
Const EARTH_RADIUS As Single = 3956.0883313286095
Dim radCvtFactor As Single = Math.PI / 180
Dim zipCodeRadius As Integer = <Your Radius Value>
Dim zipQry = From zc In db.ZipCodes
Where zc.Zip = "<Your Zip Code>" _
Select zc.Latitude,
zc.Longitude,
ZipLatRads = RadCvtFactor * zc.Latitude,
ZipLonRads = RadCvtFactor * zc.Longitude
Dim zipRslt = zipQry.SingleOrDefault()
If zipRslt IsNot Nothing Then
Dim zcQry = From zc In db.ZipCodes _
Where zc.Latitude >= (zipRslt.Latitude - 0.5) And zc.Latitude <= (zipRslt.Latitude + 0.5) _
And zc.Longitude >= (zipRslt.Longitude - 0.5) And (zc.Longitude <= zipRslt.Longitude + 0.5) _
And Math.Abs(EARTH_RADIUS * (2 * Math.Atan2(Math.Sqrt(Math.Pow(Math.Sin(((RadCvtFactor * zc.Latitude) - zipRslt.ZipLatRads) / 2), 2) + _
Math.Cos(zipRslt.ZipLatRads) * Math.Cos(RadCvtFactor * zc.Latitude) * _
Math.Pow(Math.Sin(((RadCvtFactor * zc.Longitude) - zipRslt.ZipLonRads) / 2), 2)), _
Math.Sqrt(1 - Math.Pow(Math.Sin(((RadCvtFactor * zc.Latitude) - zipRslt.ZipLatRads) / 2), 2) + _
Math.Cos(zipRslt.ZipLatRads) * Math.Cos(RadCvtFactor * zc.Latitude) * _
Math.Pow(Math.Sin((RadCvtFactor * zc.Longitude) / 2), 2))))) <= zipCodeRadius _
Select zc
End If
它看起来很复杂，因为确实如此. SO上有更聪明的人可以解释算法.我只是从在互联网上找到的一些SQL代码中实现了这一点-我不记得在哪里. Google搜索可以带您到达那里.
It looks complicated, because it is. There are far smarter people here on SO that can explain the algorithm. I merely implemented this from some SQL code I found on the internet - I can't recall from where. A Google search should get you there.
第一个查询(zipQry)以度和弧度返回起始邮政编码的纬度和经度.然后将这些结果用于执行第二个查询.
The first query (zipQry) returns the lat and lon of the starting zip code in both degrees and radians. These results are then used to execute the second query.
第二个查询中WHERE子句的第一部分:
The first part of the WHERE clause in the second query:
Where zc.Latitude >= (zipRslt.Latitude - 0.5) And zc.Latitude <= (zipRslt.Latitude + 0.5) _
And zc.Longitude >= (zipRslt.Longitude - 0.5) And (zc.Longitude <= zipRslt.Longitude + 0.5) _
只需缩小要检查的邮政编码列表，即可使查询运行得更快.它会向纬度和经度添加任意数量，以便在加利福尼亚搜索半径时不会检查俄亥俄州的所有邮政编码.其余都是上述大圆距离"算法的一部分.
Just narrowed down the list of zip codes to be examined, making the query run much faster. It adds an arbitrary amount to the lat and lon so that you're not checking all the zipcodes in Ohio when searching for a radius in California. The rest is all part of the aforementioned Great Circle Distance algorithm.
可能可以在一个查询中完成此操作，以提高效率，但当时我需要这种方式，原因现在就消失了.
This could probably have been done in one query for greater efficiency, but I needed it in this fashion at the time, the reasons now lost to me.
这篇关于查找邮政编码指定距离内的所有邮政编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
