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

And here's the function:

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

Answers


And what query do you execute to get this error?

Did you take a look at the contrib? Has also functions for these type of calculations.


Little late but had the same problem and it happens because acos param is outside [-1,1] (maybe 1.01) before calculating ACOS you should cast as INTEGER that value

like this:

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

Take a look at this solution:

http://www.postgresql.org/message-id/12376732.post@talk.nabble.com

The problem was indeed ACOS() being outside of the [-1,1] range, and this happened because it was calculating the distance between the same LAT,LONG pair (the same location)

I added a WHERE L1.ID <> L2.ID to stop the reflexive calculation.

Martijn van Oosterhout wrote:

On Sat, Aug 18, 2007 at 03:21:02PM -0700, dustov wrote:

My database just had this new error, and I have no idea why (because I haven't intentionally made any changes to this table). Does anyone have an idea which input is out of range-- or what the problem might be?

The only thing in your query that I can imagine being out of range is ACOS() which would need to be between -1 and 1 (otherwise the result would be complex).

I'd try and see what the argument to the ACOS is, but it's probably some corner case where the rounding is getting you.

Hope this helps,

I had the same error with calculating the distance between coordinates but the above tip solved my problem.


It's due to a floating point rounding error taking the value to >1.

Before taking the ACOS limit the value to the range -1 to 1 with least and greatest

acos(least(greatest(...),-1),1));

Full replacement:

 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(least(greatest(
    sin($2 / 57.2958) * 
    sin($4 / 57.2958) + cos($2/ 57.2958) * cos($4 / 57.2958) 
    * cos(($5 / 57.2958) - ($3 / 57.2958))
),-1),1));
            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 had the same problem and I could not install the contrib modules (I was not the admin).

You can have a look here: http://en.wikipedia.org/wiki/Great-circle_distance

A numerically stable formula (for non antipodal points) is the following:

RETURN earth_radius * (2.*asin(sqrt((power(sin(radians(($4-$2)/2.)),2))+
(cos(radians($4))*cos(radians($2))*power(sin(radians(($5-$3)/2.)),2)))));

If you need an universal formula you can try to implement the one of the atan2 that is explained in the Wikipedia page.


If coordinates the same you will get exception "[22003] ERROR: input is out of range". To prevent it just catch exception and return 0, like this:

EXCEPTION
WHEN numeric_value_out_of_range
THEN RETURN 0;

Need Your Help

Is sql soundex query meant for general search?

sql sql-like soundex

I've been using a LIKE query for searching a search string in a knowledge base site. So, the term "phone" would likely turn up all pages that have something with the word "phone" written in it, log...

Manipulating C-strings with multiple null characters in memory

c

I need to search through a chunk of memory for a string of characters, but several of these strings have every character null separated, like this: