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.
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
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
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
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 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.
(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
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
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.
(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
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
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
(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
(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
(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 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.
Let me know if this is what you wanted.
(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
CODE
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,
(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
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
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.
(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
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
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
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
(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
(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 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.
(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
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
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
(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.
(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
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)