postgresql 错误 - 错误 : input is out of range

Question

下面的函数一直返回这个错误信息。我认为可能是 double_precision 字段类型导致了这种情况，我尝试使用 CAST，但要么不是这样，要么我没有做对...帮助？

这是错误:

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

Answer 1

看看这个解决方案:

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?

在你的查询中，我能想象到的唯一超出范围的是 ACOS() 需要介于 -1 和 1 之间(否则结果 会很复杂)。

我会尝试看看 ACOS 的论据是什么，但它可能是 一些四舍五入的极端情况。

希望对您有所帮助，

我在计算坐标之间的距离时遇到了同样的错误，但上面的提示解决了我的问题。