本文介绍了查找两个纬度/经度点之间距离的最快方法的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我目前在mysql数据库中的位置不足一百万,所有位置都包含经度和纬度信息.

I currently have just under a million locations in a mysql database all with longitude and latitude information.

我正在尝试通过查询查找一个点与许多其他点之间的距离.它的速度不如我希望的那么快,尤其是每秒100次以上的命中率.

I am trying to find the distance between one point and many other points via a query. It's not as fast as I want it to be especially with 100+ hits a second.

是否有更快的查询,或者可能是比mysql更快的系统?我正在使用此查询:

Is there a faster query or possibly a faster system other than mysql for this? I'm using this query:

SELECT
  name,
   ( 3959 * acos( cos( radians(42.290763) ) * cos( radians( locations.lat ) )
   * cos( radians(locations.lng) - radians(-71.35368)) + sin(radians(42.290763))
   * sin( radians(locations.lat)))) AS distance
FROM locations
WHERE active = 1
HAVING distance < 10
ORDER BY distance;

注意:提供的距离以英里为单位.如果需要公里,请使用6371代替3959.

Note: The provided distance is in Miles. If you need Kilometers, use 6371 instead of 3959.

推荐答案

  • 使用MyISAM表中Geometry数据类型的Point值创建点. InnoDB表现在还支持SPATIAL索引.

    • Create your points using Point values of Geometry data types in MyISAM table. As of Mysql 5.7.5, InnoDB tables now also support SPATIAL indices.

      在这些点上创建SPATIAL索引

      使用MBRContains()查找值:

      SELECT  *
      FROM    table
      WHERE   MBRContains(LineFromText(CONCAT(
              '('
              , @lon + 10 / ( 111.1 / cos(RADIANS(@lon)))
              , ' '
              , @lat + 10 / 111.1
              , ','
              , @lon - 10 / ( 111.1 / cos(RADIANS(@lat)))
              , ' '
              , @lat - 10 / 111.1
              , ')' )
              ,mypoint)
      

    • ,或者在MySQL 5.1及更高版本中:

      , or, in MySQL 5.1 and above:

          SELECT  *
          FROM    table
          WHERE   MBRContains
                          (
                          LineString
                                  (
                                  Point (
                                          @lon + 10 / ( 111.1 / COS(RADIANS(@lat))),
                                          @lat + 10 / 111.1
                                        ),
                                  Point (
                                          @lon - 10 / ( 111.1 / COS(RADIANS(@lat))),
                                          @lat - 10 / 111.1
                                        )
                                  ),
                          mypoint
                          )
      

      这将大约在(@lat +/- 10 km, @lon +/- 10km)框中选择所有点.

      This will select all points approximately within the box (@lat +/- 10 km, @lon +/- 10km).

      这实际上不是一个盒子,而是一个球形矩形:球体的经度和纬度绑定段.这可能与 Franz Joseph Land 上的普通矩形不同,但在大多数人居住的地方都非常接近.

      This actually is not a box, but a spherical rectangle: latitude and longitude bound segment of the sphere. This may differ from a plain rectangle on the Franz Joseph Land, but quite close to it on most inhabited places.

      • 应用其他过滤条件以选择圆内的所有内容(而不是正方形)

      • Apply additional filtering to select everything inside the circle (not the square)

      可能会应用其他精细过滤来考虑大圆距(对于大距离)

      Possibly apply additional fine filtering to account for the big circle distance (for large distances)

      这篇关于查找两个纬度/经度点之间距离的最快方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-28 22:16