Home » SQL & PL/SQL » SQL & PL/SQL » Procedure to calculate distance
Procedure to calculate distance [message #343579] 
Wed, 27 August 2008 10:12 
Hsingh85
Messages: 1 Registered: August 2008 Location: USA

Junior Member 


Hello Everyone,
I need some help in creating one procedure to calculate distance using latitude and longitudes. I have created one function as:
CREATE OR REPLACE FUNCTION calc_distance (lat1 IN NUMBER, long1 IN NUMBER, lat2 IN NUMBER, long2 IN NUMBER)
RETURN NUMBER IS
v_r NUMBER :=3963.0;
BEGIN
IF lat1=lat2 and long1=long2 THEN
RETURN 0;
END IF;
RETURN v_r * ACOS ( SIN(lat1/57.3) * SIN(lat2/57.3) +
COS(lat1/57.3) * COS(lat2/57.3) * COS(abs(long2/57.3)  abs(long1/57.3))
);
EXCEPTION WHEN OTHERS THEN
RETURN NULL;
END calc_distance;
/
This functions works good when I manually supply latitude and longitude values, it gives me correct value for distance b/w these points. But my problem is of creating a procedure as follows:
I have a table, lets say LOCATION_TBL with following columns:
_____________________________
ABC  LAT1  LONG1  Region 
_____________________________
1  39.98 112.21 
_____________________________
2  24.36 102.36 
_____________________________
Region Column here is empty. I have another lookup table as LOOKUP_TBL with following columns:
__________________________
Region  LAT2  LONG2 
__________________________
xyz  65.35  98.32 
__________________________
klm  29.85  121.95 
__________________________
mno  45.69  98.99 
__________________________
jkl  47.32  94.35 
__________________________
Now, what i want to do is to create a procedure/package to pick LAT1 and LONG1 from LOCATION_TBL and calculate distance (using the formula above in the function) by picking up LAT2 and LONG2 from LOOKUP_TBL. It should work like this: As in above tables LOCATION_TBL has two rows and LOOKUP_TBL has 4 rows. The procedure should pick LAT1 and LONG1 and calculate distance with respect to all 4 rows from lookup table. Where ever distance is less than 80 miles it should insert that REGION value from LOOKUP_TBL into LOCATION_TBL.
My apologies for any confusion or unclear points. I know this may be hard to understand the problem here. Kindly write back if anything not clear.
Thanks in Advance.




Re: Procedure to calculate distance [message #343683 is a reply to message #343579] 
Wed, 27 August 2008 16:44 

Barbara Boehmer
Messages: 8694 Registered: November 2002 Location: California, USA

Senior Member 


UPDATE location_tbl loc
SET region =
(SELECT MIN (region) KEEP
(DENSE_RANK FIRST ORDER BY calc_distance (loc.lat1, loc.long1, look.lat2, look.long2))
FROM lookup_tbl look
WHERE calc_distance (loc.lat1, loc.long1, look.lat2, look.long2) < 80);



Goto Forum:
Current Time: Sun Mar 26 19:44:49 CDT 2017
Total time taken to generate the page: 0.18196 seconds
