php/mysql邮政编码邻近搜索 [英] php/mysql zip code proximity search

问题描述

我只是在寻找有关执行此操作的最佳方法的建议...

我需要创建一个搜索功能，以在邮政编码50英里半径内搜索用户".我有一个邮政编码表，其中包含所有美国邮政编码及其纬度/经度，但我只是想找出构造和查询数据的最佳方式...

我应该在用户表中添加纬度/经度列，并在给定邮政编码范围内的所有用户查询它吗?还是应该在邮政编码表中查询半径范围内的所有邮政编码，然后在用户表中查询所有具有结果(邮政编码)的用户?或者... ???此时，我愿意接受任何建议！

谢谢！

解决方案

这是我找到的最好方法.当然，这需要您在数据库中对所有经纬度的邮政编码进行编码.

// 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;
}

您应该能够使用此功能.将所需半径的邮政编码的$ lat和$ lon传递给它，包括可选的半径，并返回一个邮政编码列表.

您可以很容易地对其进行修改，以使所有用户的邮政编码都位于(radius_sql)，并将您的列表用户带回来.

快乐编码！

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...

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!

解决方案

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.

You could very easily modify this to get all users where zipcode IN (radius_sql) and get your list users back.

Happy Coding!

这篇关于php/mysql邮政编码邻近搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！