Home » SQL & PL/SQL » SQL & PL/SQL » Procedure to calculate distance
Procedure to calculate distance [message #343579] Wed, 27 August 2008 10:12 Go to next message
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 #343580 is a reply to message #343579] Wed, 27 August 2008 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should have a look at Oracle Spatial option, it is built to solve this kind of problem.

Regards
Michel
Re: Procedure to calculate distance [message #343683 is a reply to message #343579] Wed, 27 August 2008 16:44 Go to previous message
Barbara Boehmer
Messages: 8620
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);

Previous Topic: UTL_FILE and csv [merged]
Next Topic: Query everything, everywhere? (all rows, columns)
Goto Forum:
  


Current Time: Fri Dec 02 14:27:10 CST 2016

Total time taken to generate the page: 0.19430 seconds