mysql 实现经纬度排序查找功能

时间：2023-12-30 10:12:14

需求如下:

商品有多个门店，用户使用App时需要查找附近门店的商品，商品要进行去重分页。

思路：

1.确认mysql自带经纬度查询函数可以使用。

2.该需求需要利用分组排序，取每个商品最近门店的商品id，之后关联商品表获取商品信息即可。

3.mysql还是有很多限制。10w级数据处理时性能没问题，200ms以下可以接受。后续考虑使用其他方案代替

表结构

-- 建表

CREATE TABLE `store` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `name` varchar(32) NOT NULL,

  `item_id` bigint(20) NOT NULL DEFAULT '0',

  `longitude` double(50,6) NOT NULL COMMENT '经度',

  `latitude` double(50,6) NOT NULL COMMENT '纬度',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- 写入数据

INSERT INTO `store` (`id`, `name`, `item_id`, `longitude`, `latitude`)

VALUES

    (3, '欧美金融城_星爸爸', 1, 120.011496, 30.287637),

    (4, '永乐城_星爸爸', 1, 120.011802, 30.280433),

    (5, '小镇_一鸣', 2, 120.011209, 30.298552),

    (6, '赛银国际', 2, 120.027181, 30.280808),

    (7, '合景天峻_星爸爸', 1, 120.004597, 30.291660),

    (8, '仓溢东苑_星爸爸', 1, 120.008622, 30.292783);

存储过程模拟10w数据

DELIMITER ;;

CREATE DEFINER=`root`@`%` PROCEDURE `insert_store`()

begin

    DECLARE v_i int unsigned DEFAULT 0;

    WHILE v_i < 100000 DO

        INSERT INTO `store` ( `name`, `item_id`, `longitude`, `latitude`, `distance`)

        VALUES     ( concat('合景天峻_星爸爸' , round(RAND() * 10000)) , RAND() * 1000, 120 + round(RAND(),6), 30 + round(RAND(),6), 0);

        SET v_i = v_i+1;

    END WHILE;

end;;

DELIMITER ;

分页10条，查询5公里内门店权益id

1.mysql5.7.5使用st_distance_sphere函数计算距离。

2.若出现sql_mode=only_full_group_by错误请参考下面链接4

select shop.*

from

(

select * ,round(st_distance_sphere(point(120.012484,30.298926),point(`longitude`,`latitude`))) dis

 from test111.store

having dis < 5000

 order by dis limit 100000 ) shop

group by shop.item_id

order by dis

limit 10

查询结果如图 - 查询离我最近的门店商品。按商品分页

mysql 实现经纬度排序查找功能

参考 https://www.cnblogs.com/hdwang/p/9994153.html

使用到的工具和遇到的问题

1.经纬度测试 http://tool.yovisun.com/longlat/index.php?long1=30.298926&lat1=120.012484&long2=30.292783&lat2=120.008622

2.mysql存储过程用于写入数据 https://www.cnblogs.com/jiangxiaobo/p/9214349.html

3.mysql分组排序去最新一条 https://blog.csdn.net/cz596738622/article/details/80253999

4.sql_mode=only_full_group_by错误参考处理 https://www.cnblogs.com/jpfss/p/10401753.html

相关文章