問題描述

我目前在 mysql 數(shù)據(jù)庫中有不到一百萬個位置，所有位置都包含經(jīng)度和緯度信息.

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 更快的查詢或可能更快的系統(tǒng)?我正在使用這個查詢:

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 數(shù)據(jù)類型的Point 值創(chuàng)建您的點.從 Mysql 5.7.5 開始，InnoDB 表現(xiàn)在也支持 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.

    在這些點上創(chuàng)建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)框內(nèi)的所有點.

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

  這實際上不是一個盒子，而是一個球面矩形:球體的經(jīng)緯度邊界段.這可能與弗朗茨約瑟夫地上的普通矩形不同，但在大多數(shù)有人居住的地方非常接近.

  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.

  • 應(yīng)用額外的過濾來選擇圓圈內(nèi)的所有內(nèi)容(不是正方形)

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

  可能應(yīng)用額外的精細過濾來解釋大圓距離(對于大距離)

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

  這篇關(guān)于查找兩個經(jīng)緯度點之間距離的最快方法的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！

  【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請聯(lián)系我們刪除處理，感謝您的支持！