Home » SQL & PL/SQL » SQL & PL/SQL » Procedure to calculate distance
Procedure to calculate distance Wed, 27 August 2008 10:12
 Hsingh85 Messages: 1Registered: 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.

Re: Procedure to calculate distance [message #343580 is a reply to message #343579] Wed, 27 August 2008 10:14
 Michel Cadot Messages: 65135Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Barbara Boehmer Messages: 8735Registered: 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: Wed Aug 16 18:49:31 CDT 2017

Total time taken to generate the page: 0.06414 seconds