×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Calculating a geographic radius from a zip code
4

Calculating a geographic radius from a zip code

Calculating a geographic radius from a zip code

(OP)
Help!

I have a listing of hotel addresses, each with a zip code, latitude and longitude.  I have another listing of customers with zip code, lat & longitude.  I need to select all the customers that are within a 100 mile radius of each hotel.

Can anyone help me do this in Oracle SQL or in PL/SQL?  I am a novice so any code provided will be appreciated!!!

Thanks.

RE: Calculating a geographic radius from a zip code

The distance calculation is based on the Pythagorean theorem for right triangles. The distance is the hypoteneuse of a right triangle with longitude and latitude as the legs which intersect in a right angle. The tricky part is that longitude lines get closer together as you leave the equator and approach the poles.

From the below listed reference:
one degree of latitude =69.172 miles (average)
one degree of longitude = 69.172 miles * cos(latitude)

Applying the Pythagorean theorem:
distance between two points = square root of
((lat1 - lat2)*69.172) ^2 (squared) +
((long1 - long2) * 69.172 * cos ((lat1 + lat2)/2)) ^2 (sqrd)

critical reference: http://www.ncgia.ucsb.edu/education/curricula/giscc/units/u014/u014.html

Sometimes the grass is greener on the other side because there is more manure there - original.

RE: Calculating a geographic radius from a zip code

Oops - just looked up the cos function in Oracle and it wants the latitude to be in radians.
There are "pi" radians in 180 degrees, so to convert from degrees to radians, multiply the latitude by pi (3.14159) and divide by 180.

Thanks for the star.

Sometimes the grass is greener on the other side because there is more manure there - original.

RE: Calculating a geographic radius from a zip code

3
Adventurous,

As an alternative to John's excellent solution, I'm posting code below which contains two functions: 1) "GET_DISTANCE"  calculates "air distance" or "as-the-crow-flies" distance between two points (so long as you pass consistent units-of-measure). 2) "CONV_LONGLAT" converts longitude or latitude to conventional units of measure: feet, meters, statute miles or nautical miles. The two functions should take care of your hotel distances with no problem; just create a query using the functions "WHERE <function result> <= 100;". Following is the code. Copy and past it to a script named "Distance.sql" so you can remember what it does:

CODE

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor "Dasages, LLC" makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM
REM Please contact the author via email (dave@dasages.com) when
REM you have comments, suggestions, and/or difficulties with these
REM scripts.
REM
REM [Please keep the above disclaimer and the embedded electronic
REM  documentation with this script.]
REM **************************************************************
REM About this script/file:
REM
REM NAME: Distance.SQL - PL/SQL code to create two functions:
REM      1) Get_Distance: Function that calculates the distance
REM          between two points given the "x,y" co-ordinates of
REM          two points on a grid. (Uses
REM
REM          Format: get_distance (<X of Point-1>, <y of Point-1>,
REM                   <x of Point-2>, <y of Point-2>)
REM
REM          Example: select get_distance(1,2,2,1) from dual;
REM                   GET_DISTANCE(1,2,2,1)
REM                   ---------------------
REM                   1.41421356
REM
REM        
REM       2) Conv_LongLat: Function that converts Longitude or
REM          Latitude (from Degrees, Minutes, and Seconds) into a
REM          Displacement from either the Equator (Latitude) or
REM          from the Prime Meridian (Longitude). The user specifies
REM          in the function call whether the returning results
REM          are in units of Feet: "F", Meters: "M",
REM          Statute Miles: "SM", or Nautical Miles: "NM".
REM
REM          Format: conv_longlat(<Degrees>,<Minutes>,<Seconds>,
REM                  <'N','S' for Latitude;'E','W' for Longitude>,
REM                  <to_units: 'F','M','SM','NM'>
REM
REM          Example:
REM          col a heading "Central Park|Statute Miles|from Greenwich" format 99,999.9
REM          col b heading "Central Park|Statute Miles|from Equator" format 99,999.9
REM          select conv_LongLat( 73,58,0,'W','SM') a, -- NYC Central Park Longitude
REM                 conv_LongLat( 40,47,0,'N','SM') b  -- NYC Central Park Latitude
REM          from dual;
REM
REM            Central Park  Central Park
REM           Statute Miles Statute Miles
REM          from Greenwich  from Equator
REM          -------------- -------------
REM                -5,115.6       2,802.1
REM
REM          Note: "South latitudes" and "West longitudes" appear as negative numbers.
REM
REM       3) Combinations of the two functions (example: distances between two
REM          "earth-points" given in longitude and latitude):
REM
REM       col a heading "NYC to Sydney|(Long-way around)" format a25
REM       Select to_char(
REM              get_distance (conv_LongLat( 73,58,0,'W','SM')
REM                           ,conv_LongLat( 40,47,0,'N','SM')  -- New York City (Central Park)
REM                           ,conv_LongLat(151, 0,0,'E','SM')
REM                           ,conv_LongLat( 34, 0,0,'S','SM')) -- Sydney, Australia
REM                      ,'99,999.9')
REM              ||' statute miles' a
REM       from dual;
REM
REM       NYC to Sydney
REM       (Long-way around)
REM       -------------------------
REM        16,385.2 statute miles
REM
REM       col a heading "Distance|between|Kennedy &|LaGuaria|Airports" format a2
REM       Select to_char(
REM              get_distance (conv_LongLat(73,47,0,'W','M')
REM                           ,conv_LongLat(40,39,0,'N','M')  -- Kennedy Airport
REM                           ,conv_LongLat(73,54,0,'W','M')
REM                           ,conv_LongLat(40,46,0,'N','M')) -- LaGuardia Airport
REM                           ,'99,999.9')
REM              ||' meters' a
REM       from dual;
REM       
REM       Distance
REM       between
REM       Kennedy &
REM       LaGuaria
REM       Airports
REM       -------------------------
REM        18,304.1 meters
REM
REM       Note: Geographers accept 1 degree of Longitude to be 728 meters farther than
REM             1 degree of Latitude at the equator. Accuracy of linear distances of
REM             of longitudes degrades when approaching the poles.
REM
REM AUTHOR: Dave Hunt
REM         Co-principal, Dasages, LLC
REM         1-801-733-5333
REM
REM **************************************************************
REM Maintenance History:
REM
REM 17-MAR-2004: Original Code
REM **************************************************************
Create or replace function get_distance
    (p1x in number
    ,p1y in number
    ,p2x in number
    ,p2y in number)
    return number
is
begin
    return    power(power(greatest(p1x,p2x)-least(p1x,p2x),2)+
        power(greatest(p1y,p2y)-least(p1y,p2y),2),(1/2));
end;
/
create or replace function Conv_LongLat
    (Degrees in number
    ,Minutes in number
    ,Seconds in number
    ,Compass in varchar2
    ,Conversion_Units in varchar2
    )
    return number
is
    Degree_in_output_units    number;
    PosNeg            number;
begin
    if    upper(Compass) in ('N','E') then
            PosNeg    := 1;
    elsif    upper(Compass) in ('S','W') then
        PosNeg    := -1;
    else
        raise_application_error(-20002,
            'Error: Hemisphere indicator, "'||Compass||
            '", must be "N","S","E",or "W".');
    end if;
    if    minutes > 59 then
        raise_application_error(-20004,'Error: Minutes ('||minutes||') must be < 60."');
    end if;
    if    seconds > 59 then
        raise_application_error(-20005,'Error: Seconds ('||seconds||') must be < 60."');
    end if;
    if    upper(Compass) in ('E','W') then     -- Longitude
        if    (degrees > 180) or (degrees = 180 and (minutes > 0 or seconds > 0))then
            raise_application_error(-20003,
                'Error: Longitude ('||degrees||' deg. '||minutes||'` '||seconds||'") must <= 180 deg.');
        end if;
        if upper(Conversion_Units) = 'M'     -- 'Meters' per degree
            then Degree_in_output_units := 111303;
        elsif upper(Conversion_Units) = 'F'    -- 'Feet' per degree
            then Degree_in_output_units := 365166;
        elsif upper(Conversion_Units) = 'SM'    -- 'Statute Miles' per degree
            then Degree_in_output_units := 69.16022727272727;
        elsif upper(Conversion_Units) = 'NM'    -- 'Nautical Miles' per degree
            then Degree_in_output_units := 60.098594281230234;
        else
            raise_application_error(-20000,
            'Error: Conversion-Unit indicator must be "M","F","SM",or "NM"');
        end if;
    elsif    upper(Compass) in ('N','S') then    -- Latitude
        if    (degrees > 90) or (degrees = 90 and (minutes > 0 or seconds > 0))then
            raise_application_error(-20006,
                'Error: Latitude ('||degrees||' deg. '||minutes||'` '||seconds||'") must <= 90 deg.');
        end if;
        if upper(Conversion_Units) = 'M'    -- 'Meters' per degree
            then Degree_in_output_units := 110575;
        elsif upper(Conversion_Units) = 'F'     -- 'Feet' per degree
            then Degree_in_output_units := 362778;
        elsif upper(Conversion_Units) = 'SM'    -- 'Statute Miles' per degree
            then Degree_in_output_units := 68.70795454545454;
        elsif upper(Conversion_Units) = 'NM'    -- 'Nautical Miles' per degree
            then Degree_in_output_units := 59.70558002704562;
        else
            raise_application_error(-20000,
            'Error: Conversion-Unit indicator must be "M","F","SM",or "NM"');
        end if;
    end if;
    Return    PosNeg*((nvl(Degrees,0)*Degree_in_output_units)
        +  (nvl(Minutes,0)*(Degree_in_output_units/60))
        +  (nvl(Seconds,0)*(Degree_in_output_units/60/60)));
end;
/
REM **************************************************************
REM End of scripts
REM **************************************************************

Let me know if this is useful to you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:20 (17Mar04) UTC (aka "GMT" and "Zulu"), 15:20 (17Mar04) Mountain Time)

RE: Calculating a geographic radius from a zip code

(OP)
Mufasa,

Thanks to both you and John for the assistance.

The code worked great with one exception (unless I am using it incorrectly).  To calculate the distance, the latitude/longitude values have to be entered manually for each comparison.  

Is there a way to do this programmatically for each record via a loop or something?

Example:  List of 20 Hotels with Lat/Long in Table 1 and a list of 100 customers with Lat/Long in Table 2.

Select
to_char(get_distance (conv_LongLat(HOTEL.LONG,'W','M'), conv_LongLat(HOTEL.LAT,'N','M'),
conv_LongLat(CUST.LONG,'W','M'), -- Kennedy Airport
conv_LongLat(CUST.LAT,'N','M')), -- LaGuardia Airport
,'99,999.9')||' meters' a
from dual;


Preferred Output:

Hotel ID   Cust ID   Distance
5          1         12.3 miles
5          2         64.0 miles
6          1          2.0 miles
6          2         17.0 miles
7          1          8.0 miles
7          2          3.0 miles
...n       ...n      ...n

Sorry to be such a pain!

Adventurous1

RE: Calculating a geographic radius from a zip code

Adventurous,

You are certainly not a pain. And yes, just as certainly we can programmatically produce the results you want without having to manually enter data. Please post a "describe <hotel table>", a "describe <customer table>" and 1 or 2 rows of sample data from each table. (I'm looking most importantly for how you store your longitude and latitude values.) After I see that posting, I shall post a working solution that gives what you want.

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:54 (19Mar04) UTC (aka "GMT" and "Zulu"), 18:54 (18Mar04) Mountain Time)

RE: Calculating a geographic radius from a zip code

(OP)
Mufasa,

You're a testament that some goodness still is left in this world... Your help is very much appreciated.  :)  

Sample hotel table contains the following fields (actual table contains about 20 more fields):  Hotel ID, Hotel Name, Chain Name, Address1, Address2, City, State, Postal Code, Country, Latitude, Longitude.

Mock Sample hotel records:

1, Ritz Carlton, Ritz, 1 Main Street, <Null>, Denver, CO, 85723, USA, 73'47"0, 40'39"0

2, Wyndham Hotel, Wyndham, 18 Biltmore Drive, <Null>, Phoenix, AZ, 35627, USA, 52'39"3, 60'15"5

3, Fairfield Inn, Mariott, 12 Michigan Avenue, Suite 1260, Chicago, IL, 60601, USA, 82'32"0, 40'36"6


Customer Table contains the following fields (actual table contains about 17 more fields; poor design, not denormalized but i didnt build...):  Customer ID, First Name, Middle Name, Last Name, Address1 - 4, City, State, Postal Code, Country, Latitude, Longitude.

Mock sample customer records:

18, Joe, A., Smith, 1 West 4th St, (null), (null), (null), Chicago, IL, 60606, USA, 38'49"3, 51'21"5

1098, Sally, Beth, Rivers, 2601 Glasgow Street, Apt. 6, (null), (null), Joliet, IL, 60635, USA, 43'18"3, 51'21"5

1000482, Robert, (null), Bogdan, 18 Paradise Drive, (null), Building 2, (null), Paradise Valley, AZ, 80257, USA, 62'14"3, 78'33"0


RE: Calculating a geographic radius from a zip code

Adventurous,

You are very gracious. I actually enjoy doing this type of thing...it's relaxing and recreational.

I must say, however, that your hotels and customers probably don't believe you are thoughtful: based upon your longitudes and latitudes, you had all of them drowning in the Atlantic Ocean except for Robert Bogdan, who, instead of being in Paradise Valley, Arizona, ended up on the northern peninsula of Quebec, jutting into frigid Hudson Bay. So it actually took me longer to cleanup your longitude and latitude data than it did to write the code to produce your results.

Section 1 - Hotel table and data:

CODE

Create table hotel
    (Hotel_ID    number
    ,Hotel_Name    varchar2(50)
    ,Chain_Name    varchar2(50)
    ,Address1    varchar2(50)
    ,Address2    varchar2(50)
    ,City        varchar2(50)
    ,State        varchar2(10)
    ,Postal_Code    varchar2(10)
    ,Country    varchar2(30)
    ,Latitude    varchar2(10)
    ,Longitude    varchar2(10)
    );
insert into hotel values
(1,'Ritz Carlton','Ritz','1 Main Street','','Denver'
,'CO','85723','USA', '39''45"3', '104''52"17');
insert into hotel values
(2,'Wyndham Hotel','Wyndham','18 Biltmore Drive',''
,'Phoenix','AZ','35627','USA','33''26"20','112''1"35');
insert into hotel values
(3,'Fairfield Inn','Mariott','12 Michigan Avenue','Suite 1260'
,'Chicago','IL','60601','USA','41''53"18','87''38"16');

Section 2 - Customer table and data:

CODE

CREATE table Customer
    (Customer_ID    number
    ,First_Name    varchar2(50)
    ,Middle_Name    varchar2(50)
    ,Last_Name    varchar2(50)
    ,Address1    varchar2(50)
    ,Address2    varchar2(50)
    ,Address3    varchar2(50)
    ,Address4    varchar2(50)
    ,City        varchar2(50)
    ,State        varchar2(10)
    ,Postal_Code    varchar2(10)
    ,Country    varchar2(30)
    ,Latitude    varchar2(10)
    ,Longitude    varchar2(10)
    );
insert into customer values
(18,'Joe','A.','Smith','1 Wacker','','','','Chicago'
,'IL','60606','USA','41''53"45','87''38"35');
insert into customer values
(1098,'Sally','Beth','Rivers','2601 Glasgow Street'
,'Apt. 6','','','Joliet','IL','60635','USA','41''31"18','87''14"16');
insert into customer values
(1000482,'Robert','','Bogdan','18 Paradise Drive','','Building 2'
,'','Paradise Valley','AZ','80257','USA','33''33"20','111''54"35')
/

Section 3 - The "Distance" function (which parses your longitudes and latitudes, then leverages the functions I posted on 17 March @ 22:20 Zulu):

CODE

create or replace function distance
    (p1lon varchar2
    ,p1lat varchar2
    ,p2lon varchar2
    ,p2lat varchar2)
    return number
is
    p1londeg    number;
    p1lonmin    number;
    p1lonsec    number;
    p1latdeg    number;
    p1latmin    number;
    p1latsec    number;
    p2londeg    number;
    p2lonmin    number;
    p2lonsec    number;
    p2latdeg    number;
    p2latmin    number;
    p2latsec    number;
    procedure DegMinSec
        (LonLatIn in varchar2
        ,degout out number
        ,minout out number
        ,secout out number)
    is
        min_loc    number;
        sec_loc number;
        Apostrophe    char    := '''';
        Double_quote    char    := '"';
    begin
        min_loc := instr(LonLatIn,Apostrophe);
        sec_loc := instr(LonLatIN,Double_quote);
        degout    := substr(LonLatIn,1,min_loc-1);
        minout    := substr(LonLatIn,min_loc+1,sec_loc-(min_loc+1));
        secout    := substr(LonLatIn,sec_loc+1);
    end;
begin
    DegMinSec(p1lon,p1londeg,p1lonmin,p1lonsec);
    DegMinSec(p1lat,p1latdeg,p1latmin,p1latsec);
    DegMinSec(p2lon,p2londeg,p2lonmin,p2lonsec);
    DegMinSec(p2lat,p2latdeg,p2latmin,p2latsec);
    return    get_distance
        (conv_LongLat(p1londeg,p1lonmin,p1lonsec,'W','SM')
        ,conv_LongLat(p1latdeg,p1latmin,p1latsec,'N','SM')
        ,conv_LongLat(p2londeg,p2lonmin,p2lonsec,'W','SM')
        ,conv_LongLat(p2latdeg,p2latmin,p2latsec,'N','SM')
        );
end;
/

Section 4 - Query to access Hotel and Customer tables and the Distance function, and the results you wanted:

CODE

col a heading "Hotel|ID" format 99
col b heading "Hotel|Name" format a13
col c heading "Hotel|City" format a7
col d heading "Customer|ID" format 9999999
col e heading "Last|Name" format a6
col f heading "Customer|City/State" format a20
col g heading "Distance|In Miles" format 99,999.9
break on a on b on c
select     hotel_ID    a
    ,Hotel_name    b
    ,h.city        c
    ,customer_id    d
    ,last_name    e
    ,c.City||', '||c.state    f
    ,distance(h.longitude,h.latitude,c.longitude,c.latitude) g
from hotel h, customer c
-- where distance(h.longitude,h.latitude,c.longitude,c.latitude) <= 100
order by hotel_ID,last_name
/
Hotel Hotel         Hotel   Customer Last   Customer              Distance
   ID Name          City          ID Name   City/State            In Miles
----- ------------- ------- -------- ------ -------------------- ---------
    1 Ritz Carlton  Denver   1000482 Bogdan Paradise Valley, AZ      646.6
                                1098 Rivers Joliet, IL             1,225.6
                                  18 Smith  Chicago, IL            1,200.6
    2 Wyndham Hotel Phoenix  1000482 Bogdan Paradise Valley, AZ       11.4
                                1098 Rivers Joliet, IL             1,802.1
                                  18 Smith  Chicago, IL            1,783.7
    3 Fairfield Inn Chicago  1000482 Bogdan Paradise Valley, AZ    1,773.6
                                1098 Rivers Joliet, IL                37.4
                                  18 Smith  Chicago, IL                 .6

9 rows selected.
Note: So that you could see ALL the possible results, I remarked out the "WHERE" clause, which gives you only those customers that are within 100 miles of a hotel.

Let me know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:08 (19Mar04) UTC (aka "GMT" and "Zulu"), 00:08 (19Mar04) Mountain Time)

RE: Calculating a geographic radius from a zip code

Sorry...My bad...Sally Rivers's longitude in Joliet, IL, should be [88'10"16], not [87'14"16], which had her about 65 miles too far east. That correction produces these improved results:

CODE

Hotel Hotel         Hotel   Customer Last   Customer              Distance
   ID Name          City          ID Name   City/State            In Miles
----- ------------- ------- -------- ------ -------------------- ---------
    1 Ritz Carlton  Denver   1000482 Bogdan Paradise Valley, AZ      646.6
                                1098 Rivers Joliet, IL             1,161.4
                                  18 Smith  Chicago, IL            1,200.6
    2 Wyndham Hotel Phoenix  1000482 Bogdan Paradise Valley, AZ       11.4
                                1098 Rivers Joliet, IL             1,740.8
                                  18 Smith  Chicago, IL            1,783.7
    3 Fairfield Inn Chicago  1000482 Bogdan Paradise Valley, AZ    1,773.6
                                1098 Rivers Joliet, IL                44.7
                                  18 Smith  Chicago, IL                 .6

9 rows selected.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:54 (19Mar04) UTC (aka "GMT" and "Zulu"), 00:54 (19Mar04) Mountain Time)

RE: Calculating a geographic radius from a zip code

(OP)
Mufasa,

Shoot me now; I provided the wrong format for the latitude and longitude fields.  (Our database is definitely not best practice and much of the data is suspect; several tables containing this information and the format was not consistent).  For the hotel and customer tables that will be used for the analysis that possess latitude and longitude information, it is in the following format:

Latitude     Longitude
40.8144      -73.0472
40.8141      -73.048
18.1667      -66.7247
18.384       -67.1849
29.7566      -97.7777

Again, I can only offer big thanks for your patience and help.

Thanks,

Adventurous1

RE: Calculating a geographic radius from a zip code

Adventurous,

Although I cannot speak for the quality of the data in your database, the format is not bad: using one digital value for each of longitude and latitude is MUCH simpler to program for than Degrees, Minutes, and Seconds. I'm happy to adjust the code accordingly, but I cannot post the adjustments until later this afternoon (my time) following  a meeting I must attend soon.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:46 (19Mar04) UTC (aka "GMT" and "Zulu"), 10:46 (19Mar04) Mountain Time)

RE: Calculating a geographic radius from a zip code

(OP)
Mufasa,

Thanks for such a speedy reply.  This has been so much help and I anticipate using this bit of code quite a bit.

I cant believe you find this relaxing.. :)

Thanks,

Adventurous1

RE: Calculating a geographic radius from a zip code

Adventurous,

Following are the adjustments to my earlier code. The adjustments reflect your use of a single digital value to represent longitude and latitude instead of three values (degree, minutes, and seconds) to represent longitude and latitude. The original "GET_DISTANCE" function does not need to change (so it does not appear below). Both the "CONV_LONGLAT" and "DISTANCE" functions are now simpler.

Section 1 - Revised Hotel and Customer table values (side-by-side with original longitude/latitude values):

CODE

Hotel Name                        Revised    Revised Original   Original
Plus                                Hotel      Hotel Hotel      Hotel
City/State                       Latitude  Longitude Latitude   Longitude
------------------------------ ---------- ---------- ---------- ----------
Fairfield Inn: Chicago, IL       41.88833  -87.63778 41'53"18   87'38"16
Ritz Carlton: Denver, CO         39.75083 -104.87139 39'45"3    104'52"17
Wyndham Hotel: Phoenix, AZ       33.43889 -112.02639 33'26"20   112'1"35

Customer                          Revised    Revised Original   Original
Plus                             Customer   Customer Customer   Customer
City/State                       Latitude  Longitude Latitude   Longitude
------------------------------ ---------- ---------- ---------- ----------
Bogdan: Paradise Valley, AZ      33.55556 -111.90972 33'33"20   111'54"35
Rivers: Joliet, IL               41.52167  -88.17111 41'31"18   88'10"16
Smith: Chicago, IL               41.89583  -87.64306 41'53"45   87'38"35

Section 2 - Simplified "CONV_LONGLAT" function code:

CODE

create or replace function Conv_LongLat
    (LongLat    in number
    ,LongOrLat    in varchar2
    ,Conversion_Units in varchar2
    )
    return number
is
    LoLa        Char(2);
    Degree_in_output_units    number;
begin
    LoLa    := upper(substr(LongOrLat,1,2));
    if    LoLa not in ('LO','LA') then
        raise_application_error(-20000,
            'Error: Longitude/Latitude indicator, "'||LongOrLat||
            '", must begin with "LO" or "LA".');
    end if;
    if    LoLa = 'LO' then     -- Longitude
        if    LongLat > 180 then
            raise_application_error(-20001,
                'Error: Longitude ('||LongLat||') must <= 180 degrees.');
        end if;
        if upper(Conversion_Units) = 'M'     -- 'Meters' per degree
            then Degree_in_output_units := 111303;
        elsif upper(Conversion_Units) = 'F'    -- 'Feet' per degree
            then Degree_in_output_units := 365166;
        elsif upper(Conversion_Units) = 'SM'    -- 'Statute Miles' per degree
            then Degree_in_output_units := 69.16022727272727;
        elsif upper(Conversion_Units) = 'NM'    -- 'Nautical Miles' per degree
            then Degree_in_output_units := 60.098594281230234;
        else
            raise_application_error(-20002,
            'Error: Conversion-Unit indicator must be "M","F","SM",or "NM"');
        end if;
    elsif    LoLa = 'LA' then    -- Latitude
        if    LongLat > 90 then
            raise_application_error(-20003,
                'Error: Latitude ('||LongLat||') must <= 90 degrees.');
        end if;
        if upper(Conversion_Units) = 'M'    -- 'Meters' per degree
            then Degree_in_output_units := 110575;
        elsif upper(Conversion_Units) = 'F'     -- 'Feet' per degree
            then Degree_in_output_units := 362778;
        elsif upper(Conversion_Units) = 'SM'    -- 'Statute Miles' per degree
            then Degree_in_output_units := 68.70795454545454;
        elsif upper(Conversion_Units) = 'NM'    -- 'Nautical Miles' per degree
            then Degree_in_output_units := 59.70558002704562;
        else
            raise_application_error(-20002,
            'Error: Conversion-Unit indicator must be "M","F","SM",or "NM"');
        end if;
    end if;
    Return    LongLat*Degree_in_output_units;
end;
/

Section 3 - Simplified "DISTANCE" function code:

CODE

create or replace function distance
    (p1lon number
    ,p1lat number
    ,p2lon number
    ,p2lat number)
    return number
is
begin
    return    get_distance
        (conv_LongLat(p1lon,'LO','SM')
        ,conv_LongLat(p1lat,'LA','SM')
        ,conv_LongLat(p2lon,'LO','SM')
        ,conv_LongLat(p2lat,'LA','SM')
        );
end;
/

Section 4 - Query using new data and simplified functions, producing identical results to previous posted results (Only difference is "HOTEL2" and "CUSTOMER2" tables, which contain single values for longitude and latitude):

CODE

col a heading "Hotel|ID" format 99
col b heading "Hotel|Name" format a13
col c heading "Hotel|City" format a7
col d heading "Customer|ID" format 9999999
col e heading "Last|Name" format a6
col f heading "Customer|City/State" format a20
col g heading "Distance|In Miles" format 99,999.9
select     hotel_ID    a
    ,Hotel_name    b
    ,h.city        c
    ,customer_id    d
    ,last_name    e
    ,c.City||', '||c.state    f
    ,distance(h.longitude,h.latitude,c.longitude,c.latitude) g
from hotel2 h, customer2 c
-- where distance(h.longitude,h.latitude,c.longitude,c.latitude) <= 100
order by hotel_ID,last_name
/

Hotel Hotel         Hotel   Customer Last   Customer              Distance
   ID Name          City          ID Name   City/State            In Miles
----- ------------- ------- -------- ------ -------------------- ---------
    1 Ritz Carlton  Denver   1000482 Bogdan Paradise Valley, AZ      646.6
    1 Ritz Carlton  Denver      1098 Rivers Joliet, IL             1,161.4
    1 Ritz Carlton  Denver        18 Smith  Chicago, IL            1,200.6
    2 Wyndham Hotel Phoenix  1000482 Bogdan Paradise Valley, AZ       11.4
    2 Wyndham Hotel Phoenix     1098 Rivers Joliet, IL             1,740.8
    2 Wyndham Hotel Phoenix       18 Smith  Chicago, IL            1,783.7
    3 Fairfield Inn Chicago  1000482 Bogdan Paradise Valley, AZ    1,773.6
    3 Fairfield Inn Chicago     1098 Rivers Joliet, IL                44.7
    3 Fairfield Inn Chicago       18 Smith  Chicago, IL                 .6

Let me know if this works properly against your existing production tables.

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:11 (20Mar04) UTC (aka "GMT" and "Zulu"), 00:11 (20Mar04) Mountain Time)

RE: Calculating a geographic radius from a zip code

(OP)
Mufasa,

Thanks again for the help.

When I tried to execute the code, I got the following error message below.

What did I do wrong?

Thanks,

Adventurous1

SQL> create or replace function distance
  2      (p1lon number
  3      ,p1lat number
  4      ,p2lon number
  5      ,p2lat number)
  6      return number
  7  is
  8  begin
  9      return    get_distance
 10          (conv_LongLat(p1lon,'LO','SM')
 11          ,conv_LongLat(p1lat,'LA','SM')
 12          ,conv_LongLat(p2lon,'LO','SM')
 13          ,conv_LongLat(p2lat,'LA','SM')
 14          );
 15  end;
 16  /

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION DISTANCE:

LINE/COL ERROR
-------- ---------------------------------------------------
9/5      PL/SQL: Statement ignored
9/15     PLS-00201: identifier 'GET_DISTANCE' must be declared

RE: Calculating a geographic radius from a zip code

Adventurous,

Remember, I said in my (Mar 20) post, 'The original "GET_DISTANCE" function does not need to change (so it does not appear below)' ? The presumption is that "GET_DISTANCE" still resides in your schema, untouched. I should have clarified that you ensure that you use the "GET_DISTANCE" function that I posted on Mar 17:

CODE

Create or replace function get_distance
    (p1x in number
    ,p1y in number
    ,p2x in number
    ,p2y in number)
    return number
is
begin
    return    power(power(greatest(p1x,p2x)-least(p1x,p2x),2)+
        power(greatest(p1y,p2y)-least(p1y,p2y),2),(1/2));
end;
/

You must successfully compile this function before its first usage reference. So, run it, then re-attempt your "DISTANCE" compilation, and let me know.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:24 (25Mar04) UTC (aka "GMT" and "Zulu"), 10:24 (25Mar04) Mountain Time)

RE: Calculating a geographic radius from a zip code

(OP)
Mufasa,

Testing this now and things look good.  Big thanks for all your help!!!

Adventurous

P.S.  Next challenge? (Spotting/flagging weekends for a comparison during two dates)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close