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 **************************************************************
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
