问题描述
我试图找出最有效的方式(相对于加载时间),以比较一个邮政编码(用户提供)和一大堆其他邮政编码(大约200个邮政编码)之间的距离代码,但它会随着时间的推移而增加).我不需要任何事情,只是在球场上.
I'm trying to figure out what would be the most efficient (with respect to load time) to compare the distance between one zip code (which the user provides) and a whole bunch of other zip codes (there's approximately 200 zip codes right now, but its subject to increase over time). I don't need anything exact just in the ball park.
我在美国各地下载了一个邮政编码csv文件,并且有一个函数可以生成两个邮政编码之间的距离(以弧度为单位).我不需要显示我只需要的距离对200个邮政编码进行排序,最接近的是第一个.
I downloaded a zip code csv file for all of the US, and I've got a function which produces the distance between two zip codes (in radians i believe.) I don't need to display the distance i just need to sort the 200 zip codes with the closest being the first of the results.
我将csv文件上传到mysql表.我当时想我可以遍历所有200个邮政编码,并为每个包含距离的字段更新一个字段.然后使用ORDER BY,从最远到最远对它们进行排序.
I uploaded the csv file to a mysql table. I was thinking I could cycle through all the 200 zip codes, and update a field for each one containing the distance. Then using ORDER BY, sort them from closest to furthest.
有人知道更有效的方法吗?这样,每次运行搜索查询时,都需要更新整个邮政编码数据库.现在只有200个邮政编码,这并不是什么大问题,但是我想随着数据库的建立,它会减慢加载时间.谢谢您的任何建议,深表感谢!
Does anyone know of a more efficient way to do this? This way would require updating the entire db of zip codes every time a search query is run. With only 200 zip codes its not a big deal now, but i imagine it will slow down the load time as the db builds. Thanks ahead for any advice, its much appreciated!
推荐答案
在Java语言中:
var R = 6371; // km
var dLat = (lat2-lat1).toRad();
var dLon = (lon2-lon1).toRad();
var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
Math.cos(lat1.toRad()) * Math.cos(lat2.toRad()) *
Math.sin(dLon/2) * Math.sin(dLon/2);
var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
var d = R * c;
其中d =两点之间的距离
where d = distance between two points
这是 Haversine公式.
这篇关于MySQL PHP邮政编码比较具体距离的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!