PHP MySql和地理位置 [英] PHP MySql and geolocation

问题描述

我很想知道这是怎么回事会工作吗？

我会传递一个lat和long到scrip，并且它只会拉出距纬度25英里内的位置，并且距离我的位置数据库。

编辑：
我发现这个代码用于计算2点之间的距离。

pre $ $ $ $ $ $ $ $ $ $ $ $ $ $ {

$ theta = $ lon1 - $ lon2;
$ dist = sin（deg2rad（$ lat1））* sin（deg2rad（$ lat2））+ cos（deg2rad（$ lat1））* cos（deg2rad（$ lat2））* cos（deg2rad（$ theta） ）;
$ dist = acos（$ dist）;
$ dist = rad2deg（$ dist）;
$ miles = $ dist * 60 * 1.1515;
$ unit = strtoupper（$ unit）;

if（$ unit ==K）{
return（$ miles * 1.609344）;
} else if（$ unit ==N）{
return（$ miles * 0.8684）;
} else {
return $ miles;




$ b是其中一种方法在MYSQL中执行此计算向上看，所以我只能返回里程=< 25？

解决方案

使用该函数计算距离的计算相当昂贵，因为它涉及一大堆超越函数。当你有大量的行进行过滤时，这将会产生问题。

这是一种替代方法，它的计算成本更低：

以英里为单位的近似距离：

  sqrt（x * x + y * y）
 
其中x = 69.1 *（lat2  -  lat1）
和y = 53.0 *（lon2  -  lon1）
  

您可以通过添加余弦数学函数来提高此近似距离计算的准确性：

改进的英里距离： p>

  sqrt（x * x + y * y）
 
其中x = 69.1 *（lat2  -  lat1） 
和y = 69.1 *（lon2  -  lon1）* cos（lat1 / 57.3）
  

来源： http://www.meridianworlddata.com/Distance-Calculation.asp p>

---

我使用随机生成的数据集进行了一系列测试。

3种算法在精度上的差异很小，特别是在短距离时

最慢的算法当然是，带有trig函数的函数（在你的问题中的函数）。它比另外两个慢4倍。

绝对不值得。只需要近似。

代码在这里： http://pastebin.org/424186

---

要在MySQL上使用它，请创建一个存储过程，它接受坐标参数并返回距离，然后您可以执行下列操作：

  SELECT列
 FROM表
 WHERE DISTANCE（col_x，col_y，target_x，target_y）< 25 
  

I am writing a site that basically looks for places within a 25 mile radius of a lat and long using php and mysql.

I am wondering how something like this would work?

I would pass a lat and long to the scrip and have it pull out only locations that are within 25 miles of the lat and long from my Database of locations.

What is the best way to do this?

EDIT: I found this code for calculating the distance between 2 points.

    function distance($lat1, $lon1, $lat2, $lon2, $unit) { 

  $theta = $lon1 - $lon2; 
  $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); 
  $dist = acos($dist); 
  $dist = rad2deg($dist); 
  $miles = $dist * 60 * 1.1515;
  $unit = strtoupper($unit);

  if ($unit == "K") {
    return ($miles * 1.609344); 
  } else if ($unit == "N") {
      return ($miles * 0.8684);
    } else {
        return $miles;
      }
}

Is ther a way to do this calc in the MYSQL look up so I can only return if miles =< 25?

解决方案

Calculating the distance using that function there is pretty computationally expensive, because it involves a whole bunch of transcendental functions. This is going to be problematic when you have a large number of rows to filter on.

Here's an alternative, an approximation that's way less computationally expensive:

Approximate distance in miles:

sqrt(x * x + y * y)

where x = 69.1 * (lat2 - lat1) 
and y = 53.0 * (lon2 - lon1) 

You can improve the accuracy of this approximate distance calculation by adding the cosine math function:

Improved approximate distance in miles:

sqrt(x * x + y * y)

where x = 69.1 * (lat2 - lat1) 
and y = 69.1 * (lon2 - lon1) * cos(lat1/57.3) 

Source: http://www.meridianworlddata.com/Distance-Calculation.asp

---

I ran a bunch of tests with randomly generated datasets.

• The difference in accuracy for the 3 algorithms is minimal, especially at short distances
• The slowest algorithm is, of course, the one with the trig functions (the one on your question). It is 4x slower than the other two.

Definitely not worth it. Just go with an approximation.
Code is here: http://pastebin.org/424186

---

To use this on MySQL, create a stored procedure that takes coordinate arguments and returns the distance, then you can do something like:

SELECT columns 
  FROM table 
 WHERE DISTANCE(col_x, col_y, target_x, target_y) < 25

这篇关于PHP MySql和地理位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！