地理位置geo处理之mysql函数

目前越来越多的业务都会基于LBS，附近的人，外卖位置，附近商家等等，现就讨论离我最近这一业务场景的解决方案。目前已知解决方案有:mysql 自定义函数计算mysql geo索引mongodb geo索引postgresql PostGis索引redis geoElasticSearch本文测试下mysql 函数运算的性能准备工作创建数据表CREATE TABLE driver ( id int(11) unsigned NOT NULL AUTO_INCREMENT, lng float DEFAULT NULL, lat float DEFAULT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;创建测试数据在创建数据之前先了解下基本的地理知识:全球经纬度的取值范围为: 纬度-9090，经度-180180中国的经纬度范围大约为： 纬度3.8653.55，经度73.66135.05北京行政中心的纬度为39.92，经度为116.46越北面的地方纬度数值越大，越东面的地方经度数值越大度分转换： 将度分单位数据转换为度单位数据，公式：度=度+分/60分秒转换： 将度分秒单位数据转换为度单位数据，公式：度 = 度 + 分 / 60 + 秒 / 60 / 60在纬度相等的情况下：经度每隔0.00001度，距离相差约1米在经度相等的情况下：纬度每隔0.00001度，距离相差约1.1米mysql函数计算DELIMITER //CREATE DEFINER=root@localhost FUNCTION getDistance( lng1 float(10,7) , lat1 float(10,7) , lng2 float(10,7) , lat2 float(10,7)) RETURNS double COMMENT ‘计算2坐标点距离’BEGIN declare d double; declare radius int; set radius = 6371000; #假设地球为正球形，直径为6371000米 set d = (2*ATAN2(SQRT(SIN((lat1-lat2)*PI()/180/2) *SIN((lat1-lat2)PI()/180/2)+ COS(lat2PI()/180)COS(lat1PI()/180) *SIN((lng1-lng2)*PI()/180/2) *SIN((lng1-lng2)*PI()/180/2)), SQRT(1-SIN((lat1-lat2)*PI()/180/2) *SIN((lat1-lat2)PI()/180/2) +COS(lat2PI()/180)COS(lat1PI()/180) *SIN((lng1-lng2)*PI()/180/2) *SIN((lng1-lng2)*PI()/180/2))))*radius; return d;END//DELIMITER ;创建数据python脚本# coding=utf-8from orator import DatabaseManager, Modelimport loggingimport randomimport threading""" 中国的经纬度范围 纬度3.8653.55，经度73.66135.05。大概0.00001度差距1米 “”"# 创建 日志 对象logger = logging.getLogger()handler = logging.StreamHandler()formatter = logging.Formatter( ‘%(asctime)s %(name)-12s %(levelname)-8s %(message)s’)handler.setFormatter(formatter)logger.addHandler(handler)logger.setLevel(logging.DEBUG)# Connect to the databaseconfig = { ‘mysql’: { ‘driver’: ‘mysql’, ‘host’: ’localhost’, ‘database’: ‘dbtest’, ‘user’: ‘root’, ‘password’: ‘’, ‘prefix’: ’’ }}db = DatabaseManager(config)Model.set_connection_resolver(db)class Driver(Model): table = ‘driver’ timestamps = False passdef ins_driver(thread_name,nums): logger.info(‘开启线程%s’ % thread_name) for _ in range(nums): lng = ‘%.5f’ % random.uniform(73.66, 135.05) lat = ‘%.5f’ % random.uniform(3.86, 53.55) driver = Driver() driver.lng = lng driver.lat = lat driver.save()thread_nums = 10for i in range(thread_nums): t = threading.Thread(target=ins_driver, args=(i, 400000)) t.start()以上脚本创建10个线程，10个线程插入4万条数据。耗费150.18s执行完,总共插入40万条数据测试测试环境系统：mac os内存：16Gcpu: intel core i5硬盘: 500g 固态硬盘测试下查找距离(134.38753,18.56734)这个坐标点最近的10个司机select *,getDistance(134.38753,18.56734,lng,lat) as dis from driver ORDER BY dis limit 10耗时：18.0sexplain:全表扫描我测试了从1万到10万间隔1万和从10万到90万每间隔10万测试的结果变化结论此方案在数据量达到3万条查询耗时就会超过1秒大约每增加1万条就会增加0.4秒的耗时