MySQL PHP邮政编码比较具体距离 [英] MySQL PHP zip code comparison specifically distance

问题描述

我试图找出最有效的方式(相对于加载时间)，以比较一个邮政编码(用户提供)和一大堆其他邮政编码(大约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邮政编码比较具体距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！