地理位置geo处理之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;

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(lat2*PI()/180)*COS(lat1*PI()/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(lat2*PI()/180)*COS(lat1*PI()/180)           *SIN((lng1-lng2)*PI()/180/2)           *SIN((lng1-lng2)*PI()/180/2))))*radius;    return d;END//DELIMITER ;

# coding=utf-8from orator import DatabaseManager, Modelimport loggingimport randomimport threading""" 中国的经纬度范围 纬度3.86~53.55，经度73.66~135.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()

select *,`getDistance`(134.38753,18.56734,`lng`,`lat`) as dis from driver ORDER BY dis limit 10