选择半径内的纬度和经度点

I'm using a forumla to select lat/long points in a database given a location and radius however I'm falling just short of it working. The only time I can get the query to work is when i use a radius of 6371km(the size of the world), I figure there is an issue with the forumla but I just cant seem to figure out where...

我正在使用forumla来选择数据库中的纬度/长点,给定一个位置和半径,但是我只是在它的工作中。我唯一能让查询起作用的是当我使用6371km(世界大小)的半径时,我认为forumla存在问题,但我似乎无法弄清楚...

Here is my sql query

这是我的SQL查询

   $latitude = $_POST["latitude"]; // latitude of centre of bounding circle in degrees
   $longitude = $_POST["longitude"];; // longitude of centre of bounding circle in degrees
   $rad = $_POST["radius"]; // radius of bounding circle in kilometers
   $R = 6371;// earth's mean radius, km
   $maxLat = $latitude + rad2deg($rad/$R);
   $minLat = $latitude - rad2deg($rad/$R);
    // compensate for degrees longitude getting smaller with increasing latitude
   $maxLon = $longitude + rad2deg($rad/$R/cos(deg2rad($latitude)));
   $minLon = $longitude - rad2deg($rad/$R/cos(deg2rad($latitude)));



   $con = mysqli_connect("localhost","root","Winter#05");

   if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }
   mysqli_select_db($con, "appdatabase");


   $result = mysqli_query($con,"select id, PostTitle, SubmitDate, PostVotes, ImagePath, comments, latitude, longitude, acos(sin($latitude)*sin(radians(latitude)) + cos($latitude)*cos(radians(latitude))*cos(radians(longitude)-($longitude))) * $R as D 
   from ( select id, PostTitle, SubmitDate, PostVotes, comments, ImagePath, latitude, longitude 
   from posts where latitude > $minLat and latitude < $maxLat and longitude > $minLon and longitude < $maxLon ) as first_cut 
   where acos(sin($latitude)*sin(radians(latitude)) + cos($latitude)*cos(radians(latitude))*cos(radians(longitude) - ($longitude))) * $R < $rad order by D") or die('Errant query:');

   while($row = mysqli_fetch_assoc($result))
   {
        $output[]=$row;
   }


   print(json_encode($output));

   mysqli_close($con);

2 个解决方案

#1

One issue is that it looks like $latitude and $longitude are being incorporated into the SQL text in units of degrees. The formula (as it's implemented in the SQL query) looks like expects the values in those positions to be in radians.

一个问题是,看起来$ latitude和$ longitude正以度为单位合并到SQL文本中。公式(因为它在SQL查询中实现)看起来希望这些位置中的值以弧度为单位。

If I had to read/maintain this code, it would be much easier to understand the query if it were written like this:

如果我必须阅读/维护这段代码,如果它是这样编写的,那么理解查询会容易得多:

SELECT p.id
     , p.PostTitle
     , p.SubmitDate
     , p.PostVotes
     , p.ImagePath
     , p.comments
     , p.latitude
     , p.longitude
     , ACOS( SIN(       (  $latitude ))
           * SIN(RADIANS( p.latitude ))
           + COS(       (  $latitude ))
           * COS(RADIANS( p.latitude ))
           * COS(RADIANS( p.longitude ) -        ( $longitude ))
           ) * $R
       AS D
  FROM posts p
 WHERE p.latitude  > $minLat
   AND p.latitude  < $maxLat 
   AND p.longitude > $minLon
   AND p.longitude < $maxLon 
HAVING D < $rad
 ORDER BY D

The great circle distance formula looks okay, except I'm wondering why the $latitude and $longitude are being provided in units of radians, rather than degrees.

大圆距离公式看起来没问题,除了我想知道为什么以弧度为单位而不是度数提供$ latitude和$ longitude。

Then I look in the PHP code, lo and behold, those are actually in units of degrees, and I see that a conversion to radians is missing.

然后我查看PHP代码,瞧,这些实际上是以度为单位的,我发现转换为弧度失踪了。

All that's missing from the expression that returns distance "D" is the RADIANS function around $latitude and $longitude

返回距离“D”的表达式中缺少的是$ latitude和$ longitude周围的RADIANS函数

So this:

     , ACOS( SIN(       (  $latitude ))
           * SIN(RADIANS( p.latitude ))
           + COS(       (  $latitude ))
           * COS(RADIANS( p.latitude ))
           * COS(RADIANS( p.longitude ) -        ( $longitude ))
           ) * $R
       AS D

Gets replaced with

替换为

     , ACOS( SIN(RADIANS(  $latitude ))
           * SIN(RADIANS( p.latitude ))
           + COS(RADIANS(  $latitude ))
           * COS(RADIANS( p.latitude ))
           * COS(RADIANS( p.longitude ) - RADIANS( $longitude ))
           ) * $R
       AS D

If I was maintaining this, I'd also convert this to a prepared statement with bind placeholders. At a minimum, I'd properly escape any potentially unsafe values that being incorporated into the SQL text... mysqli_real_escape_string

如果我维护这个,我也会将它转换为带有绑定占位符的预准备语句。至少,我正确地逃避任何可能不安全的值,这些值被合并到SQL文本中... mysqli_real_escape_string

e.g.

$sql .= "  + COS(RADIANS( " . mysqli_real_escape_string($con,$latitude) . " ))"

I'm suspicious of the calculations of $minLat, $maxLat, $minLon, $maxLon. I'd be sure to test those. For testing just the query without those, I could comment out the entire WHERE clause, and then calculate the distance D for every flipping row in the table, and then filter those through the HAVING clause.

我怀疑$ minLat,$ maxLat,$ minLon,$ maxLon的计算。我一定要测试一下。为了测试没有那些的查询,我可以注释掉整个WHERE子句,然后计算表中每个翻转行的距离D,然后通过HAVING子句过滤它们。

We do expect that having the predicates in the WHERE clause (if done correctly) will give a "bounding box" that will limit the number of rows we need to crank through the great circle calculation.

我们确实希望在WHERE子句中使用谓词(如果正确完成)将给出一个“边界框”,它将限制我们需要通过大圆计算的行数。

Eliminating the inline view should improve performance, since MySQL won't have the overhead of materializing a derived table.

消除内联视图应该可以提高性能,因为MySQL不会有实现派生表的开销。

#2

I'm currently using the following and have been pleased with the (relative) accuracy.

我目前正在使用以下内容并对(相对)准确性感到满意。

( 
    3958*3.1415926 * sqrt( 
        ( table.latitude - $latitude ) * ( table.latitude - $latitude ) 
        + cos( table.latitude / 57.29578 ) 
        * cos( $latitude / 57.29578 ) 
        * ( table.longitude - $longitude ) 
        * ( table.longitude - $longitude ) 
    ) / 180
) <= $radius

#1