PostgreSQL计算影响某地的台风

create or replace function getdistance
(
    ilngbegin real,
    ilatbegin real,
    ilngend real,
    ilatend real
)
returns float
as
$body$

declare
    vdistance real;
    vearthradius real;
    vradlatbegin real;
    vradlatend real;
    vradlatdiff real;
    vradlngdiff real;
begin
    --地球半径
    vearthradius:=6378.137;
    -经纬度转换
    vradlatbegin := ilatbegin * pi()/180.0;
    vradlatend := ilatend * pi()/180.0;
    vradlatdiff := vradlatbegin - vradlatend;
    vradlngdiff := ilngbegin * pi()/180.0 - ilngend * pi()/180.0;
    --求距离
    vdistance :=  * asin(sqrt(power(sin(vradlatdiff / ), ) + cos(vradlatbegin) * cos(vradlatend) * power(sin(vradlngdiff/),)));
    vdistance := vdistance * vearthradius*;
    return vdistance;
end;
$body$
language 'plpgsql' volatile;

CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;

select a.tfid,a.pointlat,pointlng,getdistance(a.pointlat,a.pointlng,19.137154,109.876876) as distance,a.* 
  from typhoon_his_point_info a
 where getdistance(a.pointlat,pointlng,19.137154,109.876876)<=  
 order  by a.tfid,a.pointtime;

select a.tfid,a.pointlat,pointlng,earth_distance(ll_to_earth (109.876876, 19.137154),ll_to_earth(a.pointlng, a.pointlat)) as distance,a.*
  from typhoon_his_point_info a
 where getdistance(a.pointlat,pointlng,19.137154,109.876876)<=  
 order  by a.tfid,a.pointtime;