mysql查找附近算法_MySQL在存经纬度的数据库查询最近距离的应用

A点经纬度：x1,y1  B点经纬度x2,y2

计算公式：

距离能够用r*arccos[cos(y1)*cos(y2)*cos(x1-x2)+sin(y1)*sin(y2)]来算

r是地球半径6370km，x是经度，y是纬度

以前很啥很天真地觉得无非就是逐个计算距离，而后比较出来就好了，而后当碰到访问用户不少，并且数据库中经纬度信息不少的时候，计算量的迅速增加，能让服务器彻底傻逼掉，仍是老前辈的经验比咱们丰富，给了我很大的启示。

sql语句查询经纬度范围

指定一个经纬度，给定一个范围值(单位:公里)，查出在经纬度周围这个范围内的数据。

经度:113.914619

纬度:22.50128

范围:2km

longitude为数据表经度字段

latitude为数据表纬度字段

SQL在mysql下测试经过,其余数据库可能须要修改

SQL语句以下:

select * from location where sqrt( ( ((113.914619-longitude)*PI()*12656*cos(((22.50128+latitude)/2)*PI()/180)/180) * ((113.914619-longitude)*PI()*12656*cos (((22.50128+latitude)/2)*PI()/180)/180) ) + ( ((22.50128-latitude)*PI()*12656/180) * ((22.50128-latitude)*PI()*12656/180) ) )<2mysql

MySQL性能调优 – 使用更为快速的算法进行距离

最近遇到了一个问题,经过不断的尝试最终将某句本来占据近1秒的查询优化到了0.01秒,效率提升了100倍.git

问题是这样的,有一张存放用户居住地点经纬度信息的MySQL数据表,表结构能够简化 为:id(int),longitude(long),latitude()long. 而业务系统中有一个功能是查找离某个用户最近的其他数个用户,经过代码分析,能够肯定原先的作法基本是这样的:算法

//须要查询的用户的坐标sql

$lat=20; $lon=20;//执行查询,算出该用户与全部其余用户的距离,取出最近的10个 $sql='select * from users_location order by ACOS(SIN(('.$lat.'* 3.1415) / 180 ) *SIN((latitude * 3.1415) / 180 ) +COS(('.$lat.'* 3.1415) / 180 ) * COS((latitude * 3.1415) / 180 ) *COS(('.$lon.'* 3.1415) / 180 - (longitude * 3.1415) / 180 ) ) * 6380 asc limit 10';

而这条sql执行的速度却很是缓慢,用了近1秒的时间才返回结果,应该是由于order里的子语句用了太多的数学计算公式,致使总体的运算速度降低.数据库

而在实际的使用中,不太可能会发生须要计算该用户与全部其余用户的距离,而后再排序的状况,当用户数量达到一个级别时,就能够在一个较小的范围里进行搜索,而非在全部用户中进行搜索.服务器

因此对于这个例子,我增长了4个where条件,只对于经度和纬度大于或小于该用户1度(111千米)范围内的用户进行距离计算,同时对数据表中的经度和纬度两个列增长了索引来优化where语句执行时的速度.性能

最终的sql语句以下测试

$sql='select * from users_location where latitude >'.$lat.'-1 and latitude '.$lon.'-1 and longitude

通过优化的sql大大提升了运行速度,在某些状况下甚至有100倍的提高.这种从业务角度出发,缩小sql查询范围的方法也能够适用在其余地方.优化

全局存储方法的sqlspa

set global log_bin_trust_function_creators=1;

DELIMITER $$

CREATE DEFINER = CURRENT_USER FUNCTION `fn_getDistanceInKilometers`(`lon1` float,`lat1` float,`lon2` float,`lat2` float,`range` int)

RETURNS double

begin

declare d double;

declare radius int;

set radius = 6378140;

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((lon1-lon2)*PI()/180/2)

*SIN((lon1-lon2)*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((lon1-lon2)*PI()/180/2)

*SIN((lon1-lon2)*PI()/180/2))))*radius;

return truncate(d/1000,1);

END $$

DELIMITER $$;