oracle计算距离,依据坐标计算两点距离的Oracle函数

根据坐标计算两点距离的Oracle函数

CREATE OR REPLACE FUNCTION ITS_KK.GPSDistance(lon1 in FLOAT, lat1 in FLOAT, lon2 in FLOAT,

lat2 in FLOAT) RETURN FLOAT

AS  v_addr FLOAT;

a1     FLOAT;

b      FLOAT;

f      FLOAT;

rlat1  FLOAT;

rlat2  FLOAT;

L      FLOAT;

U1     FLOAT;

U2     FLOAT;

sinU1  FLOAT;

cosU1  FLOAT;

sinU2  FLOAT;

cosU2  FLOAT;

lambda FLOAT;

lambdap FLOAT;

iterLimit INT;

sinLambda FLOAT;

cosLambda FLOAT;

sinSigma  FLOAT;

cosSigma  FLOAT;

Sigma     FLOAT;

sinAlpha  FLOAT;

cosSqAlpha FLOAT;

cos2SigmaM FLOAT;

c         FLOAT;

uSq       FLOAT;

aA        FLOAT;

bB        FLOAT;

deltaSigma FLOAT;

s         FLOAT;

begin

a1 := 6378137.0;

b := 6356752.3142;

f :=  1 / 298.257223563;

rlat1 := Rad(lat1);

rlat2 := Rad(lat2);

L := Rad(lon2-lon1);

U1 := atan((1-f)*tan(rlat1));

U2 := atan((1-f)*tan(rlat2));

sinU1 := sin(U1);

cosU1 := cos(U1);

sinU2 := sin(U2);

cosU2 := cos(U2);

lambda := L;

lambdap := 0.0;

iterLimit := 100;

while(abs(lambda - lambdap)>0.000000000001 and iterLimit > 0) LOOP

sinLambda := sin(lambda) ;

cosLambda := cos(lambda) ;

sinSigma  := sqrt(power((cosU2 * sinLambda), 2)+power((cosU1 * sinU2 - sinU1 * cosU2 * cosLambda), 2));

if(sinSigma=0.0)

then return 0.0;

else

cosSigma := sinU1 * sinU2 + cosU1 * cosU2 * cosLambda;

Sigma := atan2(sinSigma, cosSigma);

sinAlpha := cosU1 * cosU2 * sinLambda / sinSigma ;

cosSqAlpha := 1.0 - power(sinAlpha, 2);

cos2SigmaM := cosSigma - 2 * sinU1 * sinU2 / cosSqAlpha  ;

select nvl(cos2SigmaM,0) into cos2SigmaM from dual;

C := f / 16.0 * cosSqAlpha * (4.0 + f * (4.0 - 3.0 * cosSqAlpha)) ;

lambdap := lambda;

lambda := L + (1 - C) * f * sinAlpha * (Sigma + C * sinSigma * (cos2SigmaM +

C * cosSigma * (-1 + 2.0 * power(cos2SigmaM , 2)))) ;

iterLimit := iterLimit - 1  ;

end if;

end LOOP;

if(iterLimit = 0)

then return 111;

else

uSq := cosSqAlpha*(power(a1,2)-power(b,2))/(power(b,2));

aA  := 1 + uSq / 16384.0 * (4096.0 + uSq *

(-768.0 + uSq * (320.0 - 175.0 * uSq)));

bB  := uSq / 1024.0 * (256.0 + uSq * (-128.0 + uSq * (74.0 - 47.0 * uSq)))  ;

deltaSigma := bB * sinSigma * (cos2SigmaM + bB / 4.0 * (cosSigma

* (-1 + 2 * power(cos2SigmaM,2)) -bB / 6.0 * cos2SigmaM

* (-3 + 4 * power(sinSigma , 2)) * (-3 + 4 * power(cos2SigmaM , 2))));

s := b * aA * (Sigma - deltaSigma) ;

return s;

end if;

END GPSDistance;

/

CREATE OR REPLACE FUNCTION ITS_KK.RAD(lon in NUMBER) RETURN NUMBER

AS  v_addr NUMBER;

begin

select acos(-1)*lon/180.0 into v_addr from dual ;

return v_addr;

END RAD;

/