PostgreSQL经纬度半径查询报Input out of range错误求助

Looks like you ran into a common gotcha between MySQL and PostgreSQL when working with trigonometric functions for distance calculations! Let's break down the problem and fix it step by step.

What's Causing the Error? #

PostgreSQL's acos() function enforces strict input bounds: it only accepts values between -1 and 1. MySQL, on the other hand, will silently return NULL if you pass a value outside this range instead of throwing an error.

In your calculation, floating-point precision errors can produce tiny deviations (like 1.0000000001 or -1.0000000001) that push the input outside acos()'s valid range. On top of that, there's a typo in your formula that's making the distance calculation incorrect even before we get to the precision issue.

Step 1: Fix the Formula Typo #

Looking at your whereRaw clause, you have a repeated cos(${lat} * 0.0175) where you should be using the stored latitude value from your table. The correct spherical distance formula uses the cosine of the record's latitude, not the input latitude twice.

Original (Incorrect) Part: #

cos(${lat} * 0.0175) * cos(${lat} * 0.0175)

Corrected Version: #

cos(latitude * 0.0175) * cos(${lat} * 0.0175)

Step 2: Wrap the Input for acos() to Avoid Range Errors #

To handle the floating-point precision issue in PostgreSQL, use GREATEST() and LEAST() to clamp the input value to the valid [-1, 1] range before passing it to acos().

Corrected Laravel Query: #

$events = Event::whereRaw("
    acos(
        GREATEST(
            LEAST(
                sin(latitude * 0.0175) * sin(? * 0.0175) + 
                cos(latitude * 0.0175) * cos(? * 0.0175) * 
                cos((? * 0.0175) - (longitude * 0.0175)),
                1.0
            ),
            -1.0
        )
    ) * 3959 <= 35
", [$lat, $lat, $lng])->paginate(7);

(Note: I also switched to parameter binding instead of directly interpolating $lat and $lng to avoid SQL injection risks—always a good practice!)

Corresponding Native PostgreSQL SQL: #

SELECT * FROM events 
WHERE acos(
    GREATEST(
        LEAST(
            sin(latitude * 0.0175) * sin(34.1321461 * 0.0175) + 
            cos(latitude * 0.0175) * cos(34.1321461 * 0.0175) * 
            cos((-90.0594395 * 0.0175) - (longitude * 0.0175)),
            1.0
        ),
        -1.0
    )
) * 3959 <= 35;

Why This Works #

• The LEAST(..., 1.0) ensures we never pass a value larger than 1 to acos()
• The GREATEST(..., -1.0) ensures we never pass a value smaller than -1
• The corrected formula now properly calculates the spherical distance between your input coordinates and each record's coordinates
• Parameter binding protects against SQL injection and avoids potential formatting issues with numeric values

内容的提问来源于stack exchange，提问作者John Dek