postgresql error - ERROR: input is out of range

The function below keeps returning this error message.  I thought that maybe the double_precision field type was what was causing this, and I tried to use CAST, but either that's not it, or I didn't do it right...  Help?

Here's the error:

ERROR:  input is out of range
CONTEXT:  PL/pgSQL function "calculate_distance" line 7 at RETURN

********** Error **********

ERROR: input is out of range
SQL state: 22003
Context: PL/pgSQL function "calculate_distance" line 7 at RETURN

CREATE OR REPLACE FUNCTION calculate_distance(character varying, double precision, double precision, double precision, double precision)
  RETURNS double precision AS
$BODY$ 
	DECLARE earth_radius double precision; 
	
	BEGIN 
		earth_radius := 3959.0; 
		
		RETURN earth_radius * acos(sin($2 / 57.2958) * sin($4 / 57.2958) + cos($2/ 57.2958) * cos($4 / 57.2958) * cos(($5 / 57.2958) - ($3 / 57.2958)));
	END; 
	$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION calculate_distance(character varying, double precision, double precision, double precision, double precision) OWNER TO postgres;



//I tried changing (unsuccessfully) that RETURN line to: 

RETURN CAST( (earth_radius * acos(sin($2 / 57.2958) * sin($4 / 57.2958) + cos($2/ 57.2958) * cos($4 / 57.2958) * cos(($5 / 57.2958) - ($3 / 57.2958))) ) AS text);

Open in new window

n00b0101Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
I can show you this calculation in PHP.  Not sure what the syntax might be for PostGreSQL.  I have also encountered a "not-a-number" condition in some of my distance calculations.  It seems to come in from some of the trigonometric functions.  The PHP function is_nan() can trap that condition.
0
Ray PaseurCommented:
The test for the same point at line 18 seems to eliminate the not-a-number condition in the PHP version of this computation.  HTH, ~Ray
<?php // RAY_compute_distance.php
error_reporting(E_ALL);
echo "<pre>\n";

// COMPUTE THE DISTANCE BETWEEN TWO LAT/LON PAIRS

// MAN PAGE: http://en.wikipedia.org/wiki/Haversine_formula
function compute_distance($from_lat, $from_lon, $to_lat, $to_lon, $units='KM')
{
    $units = strtoupper(substr(trim($units),0,1));
    // ENSURE THAT ALL ARE FLOATING POINT VALUES
    $from_lat = floatval($from_lat);
    $from_lon = floatval($from_lon);
    $to_lat   = floatval($to_lat);
    $to_lon   = floatval($to_lon);

    // IF THE SAME POINT
    if ( ($from_lat == $to_lat) && ($from_lon == $to_lon) )
    {
        return 0.0;
    }

    // COMPUTE THE DISTANCE WITH THE HAVERSINE FORMULA
    $distance = acos( sin(deg2rad($from_lat))
              * sin(deg2rad($to_lat))
              + cos(deg2rad($from_lat))
              * cos(deg2rad($to_lat))
              * cos(deg2rad($from_lon - $to_lon)) );

    $distance = rad2deg($distance);

    // DISTANCE IN MILES AND KM - ADD OTHERS IF NEEDED
    $miles = (float) $distance * 69.0;
    $km    = (float) $miles * 1.61;

    // RETURN MILES
    if ($units == 'M') return round($miles,1);

    // RETURN KILOMETERS = MILES * 1.61
    if ($units == 'K') return round($km,2);
}

if (!empty($_GET))
{
    $distance = compute_distance($_GET["a_lat"], $_GET["a_lon"], $_GET["b_lat"], $_GET["b_lon"], $_GET["units"]);
    echo $distance . ' ' . $_GET["units"];
}
?>


<a target="_blank" href="http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=camp+david+geolocation&sll=39.648333,-77.465&sspn=0.025972,0.057807&ie=UTF8&z=15&iwloc=lyrftr:m,10141899780758213082,39.648361,-77.465029">Camp David</a>
<a target="_blanl" href="http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=White+House+Washington,+DC&sll=39.806296,-77.097044&sspn=0.414606,0.924911&ie=UTF8&hq=White+House&hnear=White+House,+Washington,+DC&ll=38.898047,-77.036562&spn=0.025417,0.057807&z=15&iwloc=A">White House</a>
TEST IT HERE:
<form>
POINT A LAT <input name="a_lat" value="38.898047"> LON <input name="a_lon" value="-77.036562" />
POINT B LAT <input name="b_lat" value="39.737554"> LON <input name="b_lon" value="-77.464943" />
<input type="radio" name="units" value="miles" checked="checked" />Miles
<input type="radio" name="units" value="km" />Kilometers
<input type="submit" />
</form>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
Thanks for the points!  Best, ~Ray
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.