오라클 거리계산 함수
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
CREATE OR REPLACE FUNCTION RTIS_DBA.FN_DISTANCE (
inLat1 NUMBER
,inLon1 NUMBER
,inLat2 NUMBER
,inLon2 NUMBER
)
RETURN NUMBER
IS
/*
* Author : youngkwon.kim
* Purpose : 지점간 거리 계산
* Date : 2015-03-23
*/
cSpherRad CONSTANT NUMBER := 6367;
vTemp NUMBER;
vLat NUMBER;
vLat1Rad NUMBER;
vLat2Rad NUMBER;
vLon NUMBER;
vLon1Rad NUMBER;
vLon2Rad NUMBER;
BEGIN
vLat1Rad := inLat1 * 0.017453293;
vLat2Rad := inLat2 * 0.017453293;
vLon1Rad := inLon1 * 0.017453293;
vLon2Rad := inLon2 * 0.017453293;
vLon := vLon2Rad - vLon1Rad;
vLat := vLat2Rad - vLat1Rad;
vTemp := POWER(SIN(vLat/2),2) + COS(vLat1Rad) * COS(vLat2Rad) * POWER(SIN(vLon/2),2);
RETURN ROUND(cSpherRad * 2 * ATAN2(SQRT(vTemp), SQRT(1-vTemp)),1);
EXCEPTION
WHEN OTHERS THEN
RETURN 999;
END FN_DISTANCE;