mySQL在y范围内的x公里/英里内选择邮政编码 [英] mySQL select zipcodes within x km/miles within range of y

问题描述

注意:尽管我使用的是带有荷兰邮政编码的邮政编码数据库，但这个问题与国家/地区无关.

我有一个数据库，其中包含荷兰的每个邮政编码及其x和y坐标(经/纬度).

I have a database with every zipcode in the Netherlands + its x and y coordinate (lat/long).

例如，我的邮政编码:$baseZipCode = 1044;具有以下坐标:

I have for example zipcode: $baseZipCode = 1044; with the following coordinates:

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

现在，我想从$baseZipCode中找到所有其他带有$range的邮政编码.

Now, I want to find all other zipcodes with $range from $baseZipCode.

例如:

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

问题在于地球并不完全是圆形的.我发现很多有关from a to b计算的教程，但这不是我所需要的.

The problem is that the earth is not completely round. I find a lot of tutorials with from a to b calculations but that's not what I need.

有人有什么主意吗?

更新 多亏了Captaintokyo，我发现了这一点:

UPDATE Thanks to Captaintokyo I found this:

是否要查找所有邮政编码和距另一个邮政编码或某个点的特定英里/公里半径内的对应距离?这个问题需要经纬度坐标来解决.对地址进行地理编码可为您提供地址的纬度/经度坐标.

Want to find all zipcodes and corresponding distances within a certain mile/kilometer radius from another zipcode or point? This problems require latitude and longitude coordinates to solve. Geocoding the address gives you latitude/longitude coordinates from an address.

首先，您需要一个包含所有邮政编码及其对应的纬度和经度坐标的数据库:

First you will need a database of all zipcodes and their corresponding latitude and longitude coordinates:

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`)
)

因此，一旦有了数据库，您便要查找中心点一定英里半径内的所有邮政编码.如果中心点是另一个邮政编码，只需在数据库中查询该邮政编码的纬度和经度坐标即可.然后代码如下:

So once you have the database you want to find all zipcodes within a certain mile radius of a central point. If the central point is another zipcode, simply query the database for the latitude and longitude coordinates of that zipcode. Then the code is as follows:

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

}

(Yahoo和Google都提供免费的地理编码服务.)

(Both Yahoo and Google offer free geocoding services.)

推荐答案

您要执行以下操作:

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

如果您的邮政编码表很大，可能会很慢.您可能还想查看MySQL的地理空间扩展.

It may be slow if your table of zip codes is large. You may also want to check out the geospatial extensions for MySQL.

这篇关于mySQL在y范围内的x公里/英里内选择邮政编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！