查找两个经纬度点之间距离的最快方法

2021-11-20 00:00:00 gis location mysql

我目前在 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 值创建您的点.从 Mysql 5.7.5 开始，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(@lat)))
          , ' '
          , @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).

      这实际上不是一个盒子，而是一个球面矩形:球体的经纬度边界段.这可能与弗朗茨约瑟夫地上的普通矩形不同，但在大多数有人居住的地方非常接近.

      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)

相关文章