根据坐标计算两点距离的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;
/