Calculating a geographic radius from a zip code 4

[adventurous1]

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.

 

[johnherman]

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.

 

[johnherman]

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.

 

[SantaMufasa]

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:

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)

 

[adventurous1]

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

 

[SantaMufasa]

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)

 

[adventurous1]

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

 

[SantaMufasa]

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:

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:

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):

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:

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)

 

[SantaMufasa]

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:

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)

 

[adventurous1]

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

 

[SantaMufasa]

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)

 

[adventurous1]

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

 

[SantaMufasa]

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):

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:

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:

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):

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)

 

[adventurous1]

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

 

[SantaMufasa]

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:

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)

 

[adventurous1]

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)