php/mysql 邮政编码邻近搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2410529/
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
php/mysql zip code proximity search
提问by mike
I'm just looking for suggestions on the best way to do this...
我只是在寻找有关执行此操作的最佳方法的建议...
I need to create a search function that searches for "users" within a 50 mile radius of a zip code. I have a zip code table that contains all the U.S. zip codes with their latitude/longitude but I'm just trying to figure out the best way to structure and query my data...
我需要创建一个搜索功能来搜索邮政编码 50 英里范围内的“用户”。我有一个邮政编码表，其中包含所有美国邮政编码及其纬度/经度，但我只是想找出构建和查询数据的最佳方法...
Should I add latitude/longitude columns to the users table and query it for all the users within the radius of the given zip code? Or should I query the zip codes table for all the zip codes that fall within the radius then query the users table for all the users with the results(zip codes)? Or... ??? I am open to any suggestions at this point!
我应该将纬度/经度列添加到用户表中并查询给定邮政编码半径内的所有用户吗？或者我应该查询位于半径范围内的所有邮政编码的邮政编码表，然后使用结果（邮政编码）查询所有用户的用户表？或者... ？？？在这一点上，我愿意接受任何建议！
Thanks!
谢谢！
回答by Chuck Burgess
Here is the best way I have found. Of course it will require that you have all of your zipcodes lat/lon encoded in the database.
这是我找到的最好的方法。当然，这将要求您在数据库中对所有邮政编码经纬度进行编码。
// get all the zipcodes within the specified radius - default 20
function zipcodeRadius($lat, $lon, $radius)
{
$radius = $radius ? $radius : 20;
$sql = 'SELECT distinct(ZipCode) FROM zipcode WHERE (3958*3.1415926*sqrt((Latitude-'.$lat.')*(Latitude-'.$lat.') + cos(Latitude/57.29578)*cos('.$lat.'/57.29578)*(Longitude-'.$lon.')*(Longitude-'.$lon.'))/180) <= '.$radius.';';
$result = $this->db->query($sql);
// get each result
$zipcodeList = array();
while($row = $this->db->fetch_array($result))
{
array_push($zipcodeList, $row['ZipCode']);
}
return $zipcodeList;
}
You should be able to just drop in this function. Pass it the $lat and $lon of the zipcode you want the radius for, include the optional radius, and get a list of zipcodes back.
你应该可以直接加入这个功能。将您想要半径的邮政编码的 $lat 和 $lon 传递给它，包括可选的半径，并返回邮政编码列表。
You could very easily modify this to get all users where zipcode IN (radius_sql) and get your list users back.
您可以很容易地修改它以获取所有用户在 zipcode IN (radius_sql) 的位置并返回您的列表用户。
Happy Coding!
快乐编码！
回答by mmundiff
http://www.micahcarrick.com/04-19-2005/php-zip-code-range-and-distance-calculation.html
http://www.micahcarrick.com/04-19-2005/php-zip-code-range-and-distance-calculation.html
I found this very awesome.
我发现这非常棒。
"query the zip codes table for all the zip codes that fall within the radius then query the users table for all the users with the results(zip codes)"
“查询所有落在半径范围内的邮政编码的邮政编码表，然后查询具有结果（邮政编码）的所有用户的用户表”
I found this is the best way to do it unless you need to put the users on a google map. If you're just listing the users in the mileage range it should be pretty easy to query the database (using the class) for a list of zips then select all users in those zipcodes.
我发现这是最好的方法，除非您需要将用户放在谷歌地图上。如果您只是列出里程范围内的用户，那么查询数据库（使用类）以获取邮编列表应该很容易，然后选择这些邮编中的所有用户。
Select * from Users where zip_code IN (19125,19081,19107.........);
That should do it.
那应该这样做。
回答by Introgy
Start here but note that the solution isn't very fast:
从这里开始，但请注意，解决方案不是很快：
PHP Zip Code Range and Distance Calculation
Now to make it fast - we are going to replace the lookup to use a spatial index :)
现在让它更快 - 我们将替换查找以使用空间索引:)
Use MySQL
Add a column to the database called location and make it type POINT
Make sure it accepts nulls right now
Run the following SQL Query
UPDATE zip_code SET location = PointFromText(CONCAT('POINT(',lon,' ',lat,')'));Now, make the column not accept nulls
Add a spatial index to the location column
In the code from the above project replace the function 'get_zips_in_range' with the following:
function get_zips_in_range($zip, $range, $sort=1, $include_base) { // returns an array of the zip codes within $range of $zip. Returns // an array with keys as zip codes and values as the distance from // the zipcode defined in $zip. $this->chronometer(); // start the clock $details = $this->get_zip_point($zip); // base zip details if ($details == false) return false; // This portion of the routine calculates the minimum and maximum lat and // long within a given range. This portion of the code was written // by Jeff Bearer (http://www.jeffbearer.com). This significanly decreases // the time it takes to execute a query. My demo took 3.2 seconds in // v1.0.0 and now executes in 0.4 seconds! Greate job Jeff! // Find Max - Min Lat / Long for Radius and zero point and query // only zips in that range. $lat = $details[0]; $lon = $details[1]; $return = array(); // declared here for scope $first = true; $radius = $range/69.172; $boundary = "POLYGON(("; for($i=0; $i <= 360; $i += 360/24) { if($first) { $first = false; } else { $boundary .= ', '; } $clon = $radius*cos(deg2rad($i)) + $lon; $clat = $radius*sin(deg2rad($i)) + $lat; $boundary .= "$clon $clat" ; } $boundary .= '))'; $sql = "SELECT zip_code, city, county, state_name, state_prefix, area_code, time_zone, lat, lon FROM zip_code WHERE MBRContains(GeomFromText('$boundary'), location);"; //echo $sql; $r = mysql_query($sql); if (!$r) { // sql error $this->last_error = mysql_error(); return false; } else { while ($row = mysql_fetch_row($r)) { // loop through the results to get the milage from src $dist = $this->calculate_mileage($details[0],$row[7],$details[1],$row[8]); if ($this->units == _UNIT_KILOMETERS) $dist = $dist * _M2KM_FACTOR; $return[str_pad($row[0].', '.$row[1], 5, "0", STR_PAD_LEFT)] = round($dist, $this->decimals); } mysql_free_result($r); } // sort array switch($sort) { case _ZIPS_SORT_BY_DISTANCE_ASC: asort($return); break; case _ZIPS_SORT_BY_DISTANCE_DESC: arsort($return); break; case _ZIPS_SORT_BY_ZIP_ASC: ksort($return); break; case _ZIPS_SORT_BY_ZIP_DESC: krsort($return); break; } $this->last_time = $this->chronometer(); if (empty($return)) return false; return $return; }
使用 MySQL
向数据库中添加一个名为 location 的列并使其类型为 POINT
确保它现在接受空值
运行以下 SQL 查询
UPDATE zip_code SET location = PointFromText(CONCAT('POINT(',lon,' ',lat,')'));现在，使列不接受空值
向位置列添加空间索引
在上述项目的代码中，将函数“get_zips_in_range”替换为以下内容：
function get_zips_in_range($zip, $range, $sort=1, $include_base) { // returns an array of the zip codes within $range of $zip. Returns // an array with keys as zip codes and values as the distance from // the zipcode defined in $zip. $this->chronometer(); // start the clock $details = $this->get_zip_point($zip); // base zip details if ($details == false) return false; // This portion of the routine calculates the minimum and maximum lat and // long within a given range. This portion of the code was written // by Jeff Bearer (http://www.jeffbearer.com). This significanly decreases // the time it takes to execute a query. My demo took 3.2 seconds in // v1.0.0 and now executes in 0.4 seconds! Greate job Jeff! // Find Max - Min Lat / Long for Radius and zero point and query // only zips in that range. $lat = $details[0]; $lon = $details[1]; $return = array(); // declared here for scope $first = true; $radius = $range/69.172; $boundary = "POLYGON(("; for($i=0; $i <= 360; $i += 360/24) { if($first) { $first = false; } else { $boundary .= ', '; } $clon = $radius*cos(deg2rad($i)) + $lon; $clat = $radius*sin(deg2rad($i)) + $lat; $boundary .= "$clon $clat" ; } $boundary .= '))'; $sql = "SELECT zip_code, city, county, state_name, state_prefix, area_code, time_zone, lat, lon FROM zip_code WHERE MBRContains(GeomFromText('$boundary'), location);"; //echo $sql; $r = mysql_query($sql); if (!$r) { // sql error $this->last_error = mysql_error(); return false; } else { while ($row = mysql_fetch_row($r)) { // loop through the results to get the milage from src $dist = $this->calculate_mileage($details[0],$row[7],$details[1],$row[8]); if ($this->units == _UNIT_KILOMETERS) $dist = $dist * _M2KM_FACTOR; $return[str_pad($row[0].', '.$row[1], 5, "0", STR_PAD_LEFT)] = round($dist, $this->decimals); } mysql_free_result($r); } // sort array switch($sort) { case _ZIPS_SORT_BY_DISTANCE_ASC: asort($return); break; case _ZIPS_SORT_BY_DISTANCE_DESC: arsort($return); break; case _ZIPS_SORT_BY_ZIP_ASC: ksort($return); break; case _ZIPS_SORT_BY_ZIP_DESC: krsort($return); break; } $this->last_time = $this->chronometer(); if (empty($return)) return false; return $return; }
回答by headShrinker
I would first do a search for all the zipcodes in the radius of the target. Then compare all the returned zipcodes to your user table zipcodes. Pull out the matching users.
我会首先搜索目标半径内的所有邮政编码。然后将所有返回的邮政编码与您的用户表邮政编码进行比较。拉出匹配的用户。
It find the zipcodes in a radius, found this MySQL call:
它找到半径范围内的邮政编码，找到了这个 MySQL 调用：
$query = 'SELECT zzip FROM ' . table .
' WHERE (POW((69.1*(zlongitude-"' .
$long . '")*cos(' . $long .
'/57.3)),"2")+POW((69.1*(zlatitude-"' .
$lat . '")),"2"))<(' . $radius .
'*' . $radius . ')';
MySQL does all the math for you.
MySQL 为您做所有的数学计算。
I found a class that uses this here: http://www.nucleusdevelopment.com/code/do/zipcode
我在这里找到了一个使用它的类：http: //www.nucleusdevelopment.com/code/do/zipcode
Hope that helps.
希望有帮助。
回答by Pekka
Check out the proximity search featured here:
查看此处提供的邻近搜索：
Using PHP/MySQL with Google Maps
If your data is in the same notation / projection / format (whatever that's called), it may work for you.
如果您的数据采用相同的符号/投影/格式（无论它叫什么），它可能对您有用。
回答by Steve Prior
I'd consider reducing the number of candidates with a bounding square first, then worrying about the radius as a second step. You start off with the coordinates of the zipcode, then calculate the long/lat of 50 miles in all 4 directions, then select only candidates within that box using simple greater/less than criteria. If your user base is well spread out this reduces your candidate set considerably, then you only have to do vector distance math to eliminate the "corners".
我会考虑首先减少带有边界正方形的候选者的数量，然后再考虑半径作为第二步。您从邮政编码的坐标开始，然后计算所有 4 个方向上 50 英里的长/纬度，然后使用简单的大于/小于标准仅选择该框中的候选者。如果你的用户群分布得很好，这会大大减少你的候选集，那么你只需要做矢量距离数学来消除“角落”。
回答by Derek Kurth
The lat/long you have for each zip code is a geographic center for that zip, right? So if you first find zip codes with geographic centers within 50 miles, then users in those zip codes, you could easily be returning users more than 50 miles away. So you'd sacrifice some accuracy doing it that way.
每个邮政编码的经纬度是该邮政编码的地理中心，对吗？因此，如果您首先找到地理中心在 50 英里范围内的邮政编码，然后找到这些邮政编码中的用户，您很容易就会返回 50 英里以外的用户。所以你这样做会牺牲一些准确性。
But if you have a lot of users (more than the number of zip codes), this would be faster, since you'd query against the smaller zip codes table first. And you could index zip codes in the users table, so finding users with a particular zip code would be fast.
但是如果你有很多用户（超过邮政编码的数量），这会更快，因为你会先查询较小的邮政编码表。您可以在 users 表中索引邮政编码，因此查找具有特定邮政编码的用户会很快。
Just some thoughts! So, if you are expecting a lot of users and the radius of 50 miles doesn't need to be exact, I would find zip codes within 50 miles, then users within those zip codes.
只是一些想法！因此，如果您期望有很多用户并且 50 英里的半径不需要精确，我会在 50 英里内找到邮政编码，然后在这些邮政编码内找到用户。
