PHP MySql 和地理位置

2022-01-14 00:00:00 geospatial geolocation php mysql html

我正在编写一个网站，它主要使用 php 和 mysql 查找纬度和经度半径 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?

我会将纬度和经度传递给脚本，让它仅从我的位置数据库中提取距离经度和经度不超过 25 英里的位置.

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.

最好的方法是什么?

我找到了这段代码来计算两点之间的距离.

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

有没有办法在 MYSQL 查找中执行此计算，所以我只能在英里 =< 时返回25?

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)

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

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

I ran a bunch of tests with randomly generated datasets.

  • 这 3 种算法的准确度差异很小，尤其是在短距离时
  • 当然，最慢的算法是带有三角函数的算法(您的问题中的那个).它比其他两个慢 4 倍.

绝对不值得.只用一个近似值.
代码在这里:http://pastebin.org/424186

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

要在 MySQL 上使用它，请创建一个 存储过程接受坐标参数并返回距离，然后你可以这样做:

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

相关文章