?
Solved

postgresql error - ERROR:  input is out of range

Posted on 2010-03-28
3
Medium Priority
?
1,114 Views
Last Modified: 2013-12-12
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

0
Comment
Question by:n00b0101
  • 3
3 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 28888259
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
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 28888550
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 28906055
Thanks for the points!  Best, ~Ray
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses how to create an extensible mechanism for linked drop downs.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question