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 r开发者_开发知识库ange
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);

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.

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

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;