通過經緯度計算距離實現附近、附近的人等功能

下午喝什麼茶發表於2022-01-28

需求：附近功能在很多生活類的App或軟體中經常出現？那他們是怎麼實現的呢？如果資料量不是很大，且功能比較簡單，基於MySQL就可以實現。然而很多時候資料量很大且功能複雜，那麼我們就需要使用Elasticsearch這種資料庫了，不僅功能豐富，而且效能強大，大資料量情況下效能不再是問題。


一、基於MySQL實現（8.0.28）
1、建表

DROP TABLE IF EXISTS `t_city`;
CREATE TABLE `t_city` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`city_code` varchar(20) NOT NULL DEFAULT '' COMMENT '城市編碼',
`city_name` varchar(20) NOT NULL DEFAULT '' COMMENT '城市名稱',
`addr` varchar(255) NOT NULL DEFAULT '' COMMENT '詳細地址',
`lat` decimal(12,8) DEFAULT NULL COMMENT '緯度',
`lon` decimal(12,7) DEFAULT NULL COMMENT '經度',
`efficiency_level` varchar(2) DEFAULT '' COMMENT '效率評級',
`credit_level` varchar(2) DEFAULT NULL COMMENT '信用評級',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=937 DEFAULT CHARSET=utf8mb4;

2、插入測試資料

INSERT INTO `hb-test`.`t_city` (`city_code`, `city_name`, `addr`, `lat`, `lon`, `efficiency_level`, `credit_level`) VALUES ('1', '北京', '北海公園', null, null, 'A', '1');
INSERT INTO `hb-test`.`t_city` (`city_code`, `city_name`, `addr`, `lat`, `lon`, `efficiency_level`, `credit_level`) VALUES ('2', '西安', '西安火車站', null, null, 'A', '2');
INSERT INTO `hb-test`.`t_city` (`city_code`, `city_name`, `addr`, `lat`, `lon`, `efficiency_level`, `credit_level`) VALUES ('1', '北京', '北海公園', '39.93463300', '116.3927710', 'A', '1');
INSERT INTO `hb-test`.`t_city` (`city_code`, `city_name`, `addr`, `lat`, `lon`, `efficiency_level`, `credit_level`) VALUES ('2', '西安', '西安火車站', '34.28405900', '108.9688710', 'A', '2');
INSERT INTO `hb-test`.`t_city` (`city_code`, `city_name`, `addr`, `lat`, `lon`, `efficiency_level`, `credit_level`) VALUES ('2', '西安', '西安北站', '34.38171600', '108.9452810', 'B', '3');
INSERT INTO `hb-test`.`t_city` (`city_code`, `city_name`, `addr`, `lat`, `lon`, `efficiency_level`, `credit_level`) VALUES ('2', '西安', '電子正街', '34.21045200', '108.9257140', 'C', '2');
INSERT INTO `hb-test`.`t_city` (`city_code`, `city_name`, `addr`, `lat`, `lon`, `efficiency_level`, `credit_level`) VALUES ('2', '西安', '零壹廣場', '34.23076900', '108.8875360', 'D', '1');
INSERT INTO `hb-test`.`t_city` (`city_code`, `city_name`, `addr`, `lat`, `lon`, `efficiency_level`, `credit_level`) VALUES ('2', '西安', '軟體新城', '34.21228800', '108.8411110', 'D', '2');

3、第一種查詢sql：基於球面距離公式

緯度最大是90度，大於90度的一定是經度。
百度地圖上的座標拾取。精度在前，和經緯度的字面意思一致
地球平均半徑約：6371km   最大半徑（赤道半徑）約：6378km
#以西安北站（108.945281,34.381716）為原點，計算出有經緯度的城市距離當前遠點的距離並正序排列
SELECT
    id,
    city_name,
    addr,
    lat,
    lon,
    efficiency_level,
    credit_level,
    ROUND(
        6378 * 2 * ASIN(
            SQRT(
                POW(
                    SIN(
                        (
                            34.381716 * PI() / 180 - lat * PI() / 180
                        ) / 2
                    ),
                    2
                ) + COS(34.381716 * PI() / 180) * COS(lat * PI() / 180) * POW(
                    SIN(
                        (
                            108.945281 * PI() / 180 - lon * PI() / 180
                        ) / 2
                    ),
                    2
                )
            )
        ) * 1000
    ) AS distance
FROM
    t_city
WHERE lat IS NOT NULL
ORDER BY
    distance ASC,
    efficiency_level DESC,
    credit_level ASC
LIMIT 2000

4、第二種查詢sql：基於mysql自帶函式st_distance

#以西安北站（108.945281,34.381716）為原點，計算出有經緯度的城市距離當前遠點的距離並正序排列
SELECT
    *, (
        st_distance (
            point (lon, lat),
            point (108.945281, 34.381716)
        ) * 6378*1000*PI()/180
    ) AS distance
FROM
    t_city
where lon IS NOT NULL
ORDER BY
    distance ASC
LIMIT 2000

二、通過Elasticsearch（7.15.1）的geo_point實現
1、建立索引mapping

PUT t_city
{
  "settings": {
    "number_of_shards": 1,
    "number_of_replicas": 1
  },
  "mappings": {
    "properties": {
      "addr": {
        "type": "keyword"
      },
      "city_code": {
        "type": "keyword"
      },
      "city_name": {
        "type": "keyword"
      },
      "id": {
        "type": "keyword"
      },
      "location": {
        "type": "geo_point"
      },
      "time": {
        "type": "date"
      }
    }
  }
}

2、插入測試資料

POST /t_city/_bulk
{"index":{}}
{"city_code":"1","city_name":"北京","addr":"北海公園","time":"2022-01-23","location":{"lat":39.934633,"lon":39.934633}}
{"index":{}}
{"city_code":"2","city_name":"西安","addr":"西安火車站","time":"2022-01-23","location":{"lat":34.284059,"lon":108.96887}}
{"index":{}}
{"city_code":"2","city_name":"上海","addr":"上海虹橋機場","time":"2022-01-23","location":{"lat":31.203347,"lon":121.346817}}

3、geo_distance 詢問：查詢在中心點指定距離內的地理點（更多用法參考官方文件）

#以西安北站（108.94528,34.381716）為原點，查詢距離西安北站100km以內的10個點
GET /t_city/_search
{
  "size": 10,
  "query": {
    "bool": {
      "filter": [
        {
          "geo_distance": {
            "distance": "100km",
            "location": {
              "lat": 34.381716,
              "lon": 108.945281
            }
          }
        }
      ]
    }
  }
}

4、距離範圍內的桶聚合（更多功能參考官方文件）

#以西安北站（108.94528,34.381716）為原點

　#查詢落在(*-100000),(100000-300000),(300000-*)的點

GET /t_city/_search
{
  "size": 1,
  "track_total_hits": true,
  "query": {
    "match_all": {}
  },
  "aggs": {
    "distance": {
      "geo_distance": {
        "field": "location",
        "unit": "m", 
        "origin": {
          "lat": 34.381716,
          "lon": 108.945281
        },
        "ranges": [
          {
            "to": 100000
          },
          {
            "from": 100000,
            "to": 300000
          },
          {
            "from": 300000
          }
        ]
      }
    }
  }
}

注意：Note that this aggregation includes the from value and excludes the to value for each range.

後記：很多資料庫都有對GEO地理位置資料的處理，除了文中說到的MySQL、Elasticsearch；還有Redis、MongoDB等。

相關文章