Apache Hive SQRT
(经纬度)
Apache Hive SQRT
某天接到一个需求，如何通过一系列的店铺位置，来提取一些数据：
GPS ：经纬度信息
有心无力码字的我，一致遵循着，没有什么事情是一个SQL 解决不了的问题，那就两个。
方案如下
SQL 语句查询经纬度范围
指定一个经纬度，给定一个范围值(单位:千米)，查出在经纬度周围这个范围内的数据。
经度:113.914619
纬度:22.50128
范围:2km
longitude 为数据表经度字段
latitude 为数据表纬度字段
SQL语句如下:
select * from a2data.test 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
and pday = 20190529 and phour = 21 limit 10;
近遇到了一个问题,通过不断的尝试终将某句原本占据近1秒的查询优化到了0.01秒,效率提高了100倍.问题是这样的,有一张存放用户居住地点经纬度信息的MySQL数据表,表结构可以简化 为:id(int),longitude(long),latitude()long. 而业务系统中有一个功能是查找离某个用户近的其余数个用户,通过代码分析,可以确定原先的做法基本是这样的:
//需要查询的用户的坐标
$lat=20; $lon=20;//执行查询,算出该用户与所有其他用户的距离,取出近的10个
$sql='select * from a2data.test 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 a2data.test 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查询范围的方法也可以适用在其他地方.
ROUND(6378.138*2*ASIN(SQRT(POW(SIN((MyLatitude*PI()/180-BiaoLatitude*PI()/180)/2),2)+
COS(MyLatitude*PI()/180)*COS(BiaoLatitude*PI()/180)*
POW(SIN((MyLongitude *PI()/180-BiaoLongitude*PI()/180)/2),2)))*1000) AS distance
---------------------
以上语句求出周围数据到你自己经纬度的距离。MyLatitude，MyLongitude表示自己的纬度、经度，需要自己传入参数
BiaoLatitude，BiaoLongitude表示表中的纬度/经度字段，直接写表中字段名即可
Hive TEZ 优化
set hive.execution.engine=tez;
set tez.queue.name=normal;
select ROUND(6378.138*2*ASIN(SQRT(POW(SIN((31.378145*PI()/180-latitude*PI()/180)/2),2)+
COS(31.378145*PI()/180)*COS(latitude*PI()/180)*
POW(SIN((121.479597 *PI()/180-longitude*PI()/180)/2),2)))*1000) AS distance, a.id
from a2data.test as a
where pday = 20190529
having distance <=50 order by distance asc
limit 10;
select
ROUND(6378.138*2*ASIN(SQRT(POW(SIN(($latitude*PI()/180-latitude*PI()/180)/2),2)+
COS($latitude*PI()/180)*COS(latitude*PI()/180)*POW(SIN(($longitude*PI()/180-
longitude*PI()/180)/2),2)))*1000) AS distance , a.*
FROM
a2data.test as a
having
distance <= 1500 order by distance asc
原文链接：https://mp.weixin.qq.com/s/b5YLtskE2wvrOClPwVvclg
