PHP MySQL从半径用户的位置获取位置

I have in my database car incidents for example. These incidents have a latitude and longitude. On a mobile using the GPS, I get the user's location with his coordinates. The user can select a radius that he wants to know if there are incidents around him. So let's say he want to know incidents 2 miles around him.

例如，我在我的数据库中发生汽车事故。这些事件具有纬度和经度。在使用GPS的手机上，我用他的坐标获取用户的位置。如果他周围有事件，用户可以选择他想知道的半径。所以，让我们说他想知道身边2英里的事件。

So I send from the phone to a web service the user's latitude, longitude and the radius he selected. I need to make a SQL query to get the incidents 2 miles around the user.

所以我从手机发送用户的纬度，经度和他选择的半径到网络服务。我需要进行SQL查询以获取用户周围2英里的事件。

Do you have any idea how to do that?

你知道怎么做吗？

5 个解决方案

#1

Calculating the distance is pretty computationally expensive, as others have said. Returning huge datasets is also not a very good idea - specially considering PHP isn't that great in performance.

正如其他人所说，计算距离在计算上相当昂贵。返回庞大的数据集也不是一个好主意 - 特别是考虑到PHP在性能上并不是那么好。

I would use a heuristic, like approximating the distance with simple addition and subtraction.

我会使用启发式方法，例如通过简单的加法和减法来近似距离。

1 minute = 1.86 kilometers = 1.15 miles

1分钟= 1.86公里= 1.15英里

Just search the db with incidents within that range (effectively a square, rather than a circle), and then you can work on those with PHP.

只需在该范围内搜索包含事件的数据库（实际上是正方形而不是圆形），然后您可以使用PHP进行处理。

EDIT: 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

资料来源：http：//www.meridianworlddata.com/Distance-Calculation.asp

EDIT 2: I ran a bunch of tests with randomly generated datasets.

编辑2：我用随机生成的数据集运行了一堆测试。

The difference in accuracy for the 3 algorithms is minimal, especially at short distances
3种算法的准确度差异很小，尤其是在短距离时
The slowest algorithm (the one with the whole bunch of trig functions) is 4x slower than the other two.
最慢的算法（具有整组trig函数的算法）比其他两个算法慢4倍。

Definitely not worth it. Just go with an approximation.

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

Code is here: http://pastebin.org/424186

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

#2

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;
    }
  } // end function

$x_lat=center_of_serach;
$x_lon=center_of_serach;
$_distance=some_distance_in_miles;
$query1 = "SELECT * FROM `location_table` WHERE somefield=somefilter";
$result=mysql_db_query($db_conn, $query1);
$max_rows=mysql_num_rows($result); 
if ($max_rows>0)
  {
while ( $data1=mysql_fetch_assoc($result) )
  {
  if ( distance($x_lat,$x_lon,$data1['lat'],$data1['lng'],'m')<$_distance )
    {
    //do stuff
    }
  }

Its faster to fetch all the data and run it through a function, rather than use a query if your database isn't too big.

它更快地获取所有数据并通过函数运行它，而不是在数据库不是太大时使用查询。

It works for Kilos and Nautical miles too. ;)

它也适用于Kilos和Nautical里程。 ;）

#3

There is a formula to compute the distance between two lat/lon coordinates. Beware though -- it's rather computationally expensive, so if you have lots of incidents, you'll want to be smart about it. First up, read about the maths involved.

有一个公式来计算两个纬度/经度坐标之间的距离。但要注意 - 它的计算成本相当昂贵，所以如果你有很多事件，你就会想要聪明一些。首先，阅读所涉及的数学。

As for PHP code, a quick google turned up this link, which looks like it probably works.

至于PHP代码，快速谷歌出现了这个链接，看起来它可能有效。

Now, you probably want to use some more efficient method to divide your incident points into two sets: those points that might be within range (hopefully a smallish set), and those that you can discount entirely. Checking more than a few dozen incident coordinates is likely to be a performance issue.

现在，您可能希望使用一些更有效的方法将事件点分成两组：可能在范围内的点（希望是一个小的集合），以及那些可以完全折扣的点。检查超过几十个事件坐标可能是性能问题。

I don't have any particular insight into that, but if nobody else comes along with something clever, I'll try to come up with something myself later, time permitting.

我对此没有任何特别的了解，但如果没有其他人带来一些聪明的东西，我会在时间允许的情况下尝试自己提出一些事情。

#4

I did a quick search and turned up this blog post which gives a good explanation and SQL to select records in a given radius.

我做了一个快速搜索并打开了这篇博文，给出了一个很好的解释和SQL来选择给定半径的记录。

In the comments, he suggests "For speed on large datasets you probably want to grab a square block around the origin point first by adding a mile or so to and from both lat/lon for origin and then using the above as a subselect to work from the middle out" which sounds to me like the way to go.

在评论中，他建议“对于大型数据集的速度，您可能希望先在原点附近抓取一个方块，然后在原点上从纬度/纬度添加一英里左右，然后使用上面的子选择来工作从中间出来“这对我来说就像是要走的路。

#5

 SELECT 3963 * ACOS(
    SIN(RADIANS($pointAlat)) * SIN(RADIANS($pointAlat)) + COS(RADIANS($pointAlat))  * COS(RADIANS($pointBlat)) * COS(RADIANS($pointAlong) - RADIANS($pointBlong)))
 AS
 distance;

Also, if you're looking for a good read/tutorial on this.. Check here http://www.phpfreaks.com/forums/index.php/topic,208965.0.html

另外，如果你正在寻找一个很好的阅读/教程..请点击这里http://www.phpfreaks.com/forums/index.php/topic,208965.0.html

#1