代码之家 › 专栏 › 技术社区 › Wouter Dorgelo voy

mySQL选择y范围内x公里/英里内的zipcodes

postal-code zipcode mysql sql php

Wouter Dorgelo voy · 技术社区 · 14 年前

注意:虽然我使用荷兰zipcodes的zipcode数据库,但是这个问题是独立于国家的。

例如zipcode: $baseZipCode = 1044; 坐标如下:

x coordinate = 4,808855
y coordinate = 52,406332

$range 从 $baseZipCode .

SELECT
  zipcode
FROM
  zipcodes
WHERE
  ????? // Need help here

问题是地球并不完全是圆的。我发现很多关于 from a to b 但这不是我需要的。

更新

要查找距离另一个zipcode或点一定英里/公里半径内的所有zipcode和相应距离吗?这个问题需要经纬度坐标来解决。对地址进行地理编码可以从地址中获得纬度/经度坐标。

首先,您需要一个包含所有zipcodes及其相应经纬度坐标的数据库:

CREATE TABLE `zipcodes` (
  `zipcode` varchar(5) NOT NULL DEFAULT '',
  `city` varchar(100) NOT NULL DEFAULT '',
  `state` char(2) NOT NULL DEFAULT '',
  `latitude` varchar(20) NOT NULL DEFAULT '',
  `longitude` varchar(20) NOT NULL DEFAULT '',
  KEY `zipcode` (`zipcode`),
  KEY `state` (`state`)
)

// ITITIAL POINT

$coords = array('latitude' => "32.8", 'longitude' => "-117.17");

//RADIUS

$radius = 30;

// SQL FOR KILOMETERS

$sql = "SELECT zipcode, ( 6371 * acos( cos( radians( {$coords['latitude']} ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( {$coords['longitude']} ) ) + sin( radians( {$coords['latitude']} ) ) * sin( radians( latitude ) ) ) ) AS distance FROM zipcodes HAVING distance <= {$radius} ORDER BY distance";

// SQL FOR MILES

$sql = "SELECT zipcode, ( 3959 * acos( cos( radians( {$coords['latitude']} ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( {$coords['longitude']} ) ) + sin( radians( {$coords['latitude']} ) ) * sin( radians( latitude ) ) ) ) AS distance FROM zipcodes HAVING distance <= {$radius} ORDER BY distance";

// OUTPUT THE ZIPCODES AND DISTANCES

$query = mysql_query($sql);

while($row = mysql_fetch_assoc($query)){

    echo "{$row['zipcode']} ({$row['distance']})<br>\n";

}

(雅虎和谷歌都提供免费的地理编码服务。)

3 回复 | 直到 12 年前

Joshua Martell 14 年前

你想这样做:

SELECT zipcode FROM zipcodes WHERE DistanceFormula(lat, long, 4.808855, 52.406332) < $range

Mischa 14 年前

你必须用一种叫做 Haversine formula :

$sql = "
    SELECT zipcode
    FROM zipcodes
    WHERE ".mysqlHaversine($lat, $lon, $distance)."
";

function mysqlHaversine($lat = 0, $lon = 0, $distance = 0)
{
    if($distance > 0)
    {
        return ('
        ((6372.797 * (2 *
        ATAN2(
            SQRT(
                SIN(('.($lat*1).' * (PI()/180)-latitude*(PI()/180))/2) *
                SIN(('.($lat*1).' * (PI()/180)-latitude*(PI()/180))/2) +
                COS(latitude * (PI()/180)) *
                COS('.($lat*1).' * (PI()/180)) *
                SIN(('.($lon*1).' * (PI()/180)-longitude*(PI()/180))/2) *
                SIN(('.($lon*1).' * (PI()/180)-longitude*(PI()/180))/2)
                ),
            SQRT(1-(
                SIN(('.($lat*1).' * (PI()/180)-latitude*(PI()/180))/2) *
                SIN(('.($lat*1).' * (PI()/180)-latitude*(PI()/180))/2) +
                COS(latitude * (PI()/180)) *
                COS('.($lat*1).' * (PI()/180)) *
                SIN(('.($lon*1).' * (PI()/180)-longitude*(PI()/180))/2) *
                SIN(('.($lon*1).' * (PI()/180)-longitude*(PI()/180))/2)
            ))
        )
        )) <= '.($distance/1000). ')');
    }

    return '';
}

通常我在不理解代码的工作方式的情况下不会先使用它,但是我必须承认这个函数有点过头了。。。

Thomas 12 年前

虽然Captaintokyo的方法是准确的,但它也相当缓慢。我忍不住想,如果使用一个包含所有zipcodes(其边界在范围内)的临时表,然后按距离细化这些结果,会更为有利。