 --
 -- This routine calculates the distance between two points (given the
 -- latitude/longitude of those points). It is being used to calculate
 -- the distance between two ZIP Codes.
 --
 -- Calculate distance between two points lat1,lon1 and lat2,lon2
 -- Uses radius of earth in kilometers or miles as an argurments
 --
 -- Typical radius:  3963.0 (miles) (Default if no value specified)
 --                  6387.7 (km)
 --
 -- Note: NVL function is used on all variables to replace NULL values with 0 (zero).
 --
 -- United States ZIP Code databases with latitude & longitude are available
 -- at http://www.zipwise.com   
 --


CREATE OR REPLACE FUNCTION distance (Lat1 IN NUMBER,
                                     Lon1 IN NUMBER,
                                     Lat2 IN NUMBER,
                                     Lon2 IN NUMBER,
                                     Radius IN NUMBER DEFAULT 3963) RETURN NUMBER IS
 -- Convert degrees to radians
 DegToRad NUMBER := 57.29577951;
 
BEGIN
  RETURN(NVL(Radius,0) * ACOS((sin(NVL(Lat1,0) / DegToRad) * SIN(NVL(Lat2,0) / DegToRad)) +
        (COS(NVL(Lat1,0) / DegToRad) * COS(NVL(Lat2,0) / DegToRad) *
         COS(NVL(Lon2,0) / DegToRad - NVL(Lon1,0)/ DegToRad))));
END;
.
RUN