Apache Hive SQRT

SQL 语句查询经纬度范围

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

经度:113.914619
纬度:22.50128
范围:2km
longitude 为数据表经度字段
latitude 为数据表纬度字段

• SQL在mysql && HiveSql

SQL语句如下: 

select * from 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;

$sql='select * from 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 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 
    test as a
having 
    distance <= 1500 order by distance asc