V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
v2eb
V2EX  ›  问与答

mysql 根据经纬度检索排序的问题

  •  
  •   v2eb · 2022-04-12 21:15:01 +08:00 via Android · 1502 次点击
    这是一个创建于 934 天前的主题,其中的信息可能已经有所发展或是发生改变。
    关键字:mysql 经纬度 附近 1km 距离排序

    有 v 友做过类似的需求嘛,如何实现效率好点.
    第 1 条附言  ·  2022-04-26 00:07:10 +08:00

    使用的方案:

    1. 新增时:保存经纬度所在的地区行政区划编码
    2. 查询时:根据请求参数中的经纬度,获得所在地区行政区划编码,like 查询缩小范围. 再依据 mysql st_distance() 函数计算距离

    屏幕前可能的疑问:

    1. 经纬度获取地区行政编码依赖于腾讯地图 WebServie 逆地址解析.
    2. MySQL 5.6以下版本不支持 st_distance 函数.需要自己创建函数或升级版本.
    CREATE FUNCTION `st_distance`(`lat1` double,`lng1` double,`lat2` double,`lng2` double)
    RETURNS double
    BEGIN
    	DECLARE distance double;
    	SELECT
    	round(
    		6378.138 * 2 * asin(sqrt(pow(sin(
    						(lat1 * pi() / 180 - lat2 * pi() / 180) / 2
    					),2) + cos(lat1 * pi() / 180) * cos(lat2 * pi() / 180) * pow(
    					 sin((lng1 * pi() / 180 - lng2 * pi() / 180) / 2),2))
    		) * 1000
    	) INTO distance;
    	RETURN distance;
    END;
    

    其他参考资料

    1. https://tech.meituan.com/2014/09/05/lucene-distance.html
    2. https://github.com/kungfoo/geohash-java
    19 条回复    2022-05-14 08:39:26 +08:00
    SjwNo1
        1
    SjwNo1  
       2022-04-12 23:17:58 +08:00
    redis geo
    em70
        2
    em70  
       2022-04-12 23:21:28 +08:00
    用 mongodb 比较适合,直接支持经纬度索引
    liuzhaowei55
        3
    liuzhaowei55  
       2022-04-12 23:42:51 +08:00 via iPhone
    终极解决方案 pgsql ,可以上生产的方案 mongodb ,勉强用 redis ,不太推荐用 mysql 自身的地理空间解决方案。
    老手艺的话就根据已知点和距离算个正方形出来,直接和库里数据比较一下,取出来再比较距离。
    需要注意的是 mongodb redis 两点间距离计算并没有根据墨卡托投影优化,两点距离越远算出来的值偏差就越大
    chenliang0571
        4
    chenliang0571  
       2022-04-12 23:48:29 +08:00
    geohash
    des
        5
    des  
       2022-04-12 23:54:58 +08:00 via iPhone
    geohash 前缀匹配正解
    CEBBCAT
        6
    CEBBCAT  
       2022-04-13 01:46:37 +08:00
    提问前 Google 的好处不止「减少社区帖子」一个好处,对自己还能锻炼自学能力,以及「兼听则明」,实在是好处多多
    v2eb
        7
    v2eb  
    OP
       2022-04-13 08:42:25 +08:00 via Android
    @CEBBCAT 基本的技术方案已经有了,不过想听下百家之言,看看有无更好的方式而已。你上来就给我带个帽子,挺无聊的。
    yggd
        8
    yggd  
       2022-04-13 08:55:49 +08:00
    geohash, S2, H3
    zhangwugui
        9
    zhangwugui  
       2022-04-13 09:07:07 +08:00
    mysql 我记得很早就支持的,5.6 还是 5.7 之后进行了优化,有专门的函数,应该主要是 st_distance 还是 st_distance_spehre 函数,以前我还写过的;
    这个方法 一个是返回距离的度数,一个是返回距离的米;

    大概长这样吧:
    SELECT s.*, (st_distance(point(lng, lat), point(122.00, 37.00) ) * 111195) AS distance
    FROM test s ORDER BY distance
    zhangwugui
        10
    zhangwugui  
       2022-04-13 09:08:13 +08:00
    不过如果说 mysql 之外的方案,那可就多了去了,之前我们生产用 pgsql 库,pgsql 天然就适合这种操作;
    CEBBCAT
        11
    CEBBCAT  
       2022-04-13 09:29:18 +08:00 via iPhone   ❤️ 3
    @v2eb 既然已经有了方案那放上来供大家检查岂不一步到位?抛砖引玉说的不就是这种情况吗?
    tool2d
        12
    tool2d  
       2022-04-13 09:34:08 +08:00
    如果静态化数据,可以自己提出来到内存,专门做地理索引。数据量大了后,应该会比 st_distance 好用一点。
    dzdh
        13
    dzdh  
       2022-04-13 10:30:11 +08:00
    elasticsearch > postgresql > mongodb > redis

    除了 mysql 外随便挑
    dzdh
        14
    dzdh  
       2022-04-13 10:31:24 +08:00
    postgresql+postgis 1.2e 经纬度搜索排序 无压力
    dzdh
        15
    dzdh  
       2022-04-13 10:31:44 +08:00
    postgresql yyds
    liuguangxuan
        16
    liuguangxuan  
       2022-05-13 20:46:03 +08:00
    @dzdh #14 老哥,在这里 E 是个什么单位?

    另外我用 PostgreSQL+PostGIS 测试了一下,随机在经度(-180°~180°),纬度(-90°~90°)生成 1 亿个点,查询 32°*32°的矩形区域,搜索出来的点数大约为 150 万个,耗时需要 15 分钟。这个属于正常的吗?有没有什么优化手段可以把查询时间降下去?
    v2eb
        17
    v2eb  
    OP
       2022-05-13 21:30:00 +08:00 via Android
    @liuguangxuan 150w 个点都要展示出来吗?
    dzdh
        18
    dzdh  
       2022-05-13 23:31:46 +08:00
    @liuguangxuan 同问。150w 个点都 select 出来吗
    liuguangxuan
        19
    liuguangxuan  
       2022-05-14 08:39:26 +08:00
    @v2eb #17
    @dzdh #18 不需要全部都展示出来。

    我的需求跟 OP 的有点儿类似,都属于地理空间查询。但是我可能比 OP 多了一些其它的字段属性,比如高度,速度,老哥们可以理解为飞机的轨迹,我不太需要关注每个实时的点,我更关注飞机的整体航迹。然后在地图上快速的从 1 亿个点里面把飞机的整体轨迹筛选出来。老哥们有没有比较快速的办法?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2859 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 13:34 · PVG 21:34 · LAX 06:34 · JFK 09:34
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.