PHP / MySQL：从数据库中选择靠近给定位置的位置 [英] PHP/MySQL: Select locations close to a given location from DB

问题描述

在PHP中，我有以下代码来计算两个位置之间的距离：

 <？php 
函数距离（$ lat1，$ long1，$ lat2，$ long2）{
 //度数至RADIAN 
 $ latitude1 = $ lat1 / 180 * pi（）; 
 $ longitude1 = $ long1 / 180 * pi（）; 
 $ latitude2 = $ lat2 / 180 * pi（）; 
 $ longitude2 = $ long2 / 180 * pi（）; 
 //公式：e = ARCCOS（SIN（Latitude1）* SIN（Latitude2）+ COS（Latitude1）* COS（Latitude2）* COS（Longitude2-Longitude1））* EARTH_RADIUS 
 $ distance = acos（罪（$ latitude1）*罪（$ latitude2）+ COS（$ latitude1）* COS（$ latitude2）* COS（$ longitude2- $ longitude1））* 6371; 
返回$ distance; 
} 
回波距离（9.9921962,53.5534074,9.1807688,48.7771056）; //德国汉堡 - 斯图加特，DE 
？> 
  

但是现在，我想通过PHP从我的MySQL数据库中选择靠近给定位置的位置：

• 用户进入他的家乡


• 我的脚本通过Google API获取纬度/经度值


• 在我的数据库中，我有大约200个位置，纬度值为一个字段，经度值为一个字段。
  
• 我需要一个PHP代码和MySQL来选择离用户家乡最近的10个位置

我希望你能帮助我。提前致谢！

解决方案

只有200条记录，但是您可能会以及只需加载它们并用代码检查它们。数据集实在太小，不能担心数据库与代码或任何其他此类优化太多。

计算邮政编码在PHP中的距离有几个这种算法的PHP实现。

Geo Proximity Search 几乎与您的问题完全相同。

In PHP, I have the following code for calculating the distance between two locations:

<?php
function distance($lat1, $long1, $lat2, $long2) {
    // DEGREE TO RADIAN
    $latitude1 = $lat1/180*pi();
    $longitude1 = $long1/180*pi();
    $latitude2 = $lat2/180*pi();
    $longitude2 = $long2/180*pi();
    // FORMULA: e = ARCCOS ( SIN(Latitude1) * SIN(Latitude2) + COS(Latitude1) * COS(Latitude2) * COS(Longitude2-Longitude1) ) * EARTH_RADIUS
    $distance = acos(sin($latitude1)*sin($latitude2)+cos($latitude1)*cos($latitude2)*cos($longitude2-$longitude1))*6371;
    return $distance;
}
echo distance(9.9921962, 53.5534074, 9.1807688, 48.7771056); // Hamburg, DE - Stuttgart, DE
?>

But now, I want to select locations close to a given location via PHP from my MySQL database:

• The user enters his hometown
• My script gets the latitude/longitude values via the Google API
• In my database, I have about 200 locations with a field for the latitude value and a field for the longitude value
• I need a code for PHP and MySQL to select the 10 locations which are closest to the user's hometown

I hope you can help me. Thanks in advance!

解决方案

MySQL Great Circle Distance (Haversine formula) does exactly what you need.

With only 200 records however you may as well just load them all and check them with code. The data set is really way too small to be worrying too much about database vs code or any other such optimizations.

Calculating distance between zip codes in PHP has a couple of PHP implementations of this algorithm.

Geo Proximity Search is pretty much the exact same problem you have.

这篇关于PHP / MySQL：从数据库中选择靠近给定位置的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！