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) ) )<2

 

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

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

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

//需要查询的用户的坐标

$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 < '.$lat.'+1 and longitude > '.$lon.'-1 and longitude < '.$lon.'+1 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大大提高了运行速度,在某些情况下甚至有100倍的提升.这种从业务角度出发,缩小sql查询范围的方法也可以适用在其他地方.



全局存储方法的sql

set global log_bin_trust_function_creators=1;
DELIMITER

CREATEDEFINER=CURRENTUSERFUNCTIONfngetDistanceInKilometers(lon1float,lat1float,lon2float,lat2float,rangeint)RETURNSdoublebegindeclareddouble;declareradiusint;setradius=6378140;setd=(2ATAN2(SQRT(SIN((lat1lat2)PI()/180/2)SIN((lat1lat2)PI()/180/2)+COS(lat2PI()/180)COS(lat1PI()/180)SIN((lon1lon2)PI()/180/2)SIN((lon1lon2)PI()/180/2)),SQRT(1SIN((lat1lat2)PI()/180/2)SIN((lat1lat2)PI()/180/2)+COS(lat2PI()/180)COS(lat1PI()/180)SIN((lon1lon2)PI()/180/2)SIN((lon1lon2)PI()/180/2))))radius;returntruncate(d/1000,1);END

DELIMITER $$;

没有更多推荐了,返回首页

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试