Skip to content

Fix Similarity formula becasue floating error

Because, historically, positions have been managed using floats sometime the similarity function doesn't work because we try to make acos of a value outside [-1,1]. To correct this, we need to make this sql request who limit the result of a sub request in [-1,1].

Test this with hypothesis. Update postgreSQL to a new version which manages trigonometric functions in decimal and not in float.

New SQL request:

SELECT
    star_id,
    name
FROM (
    SELECT
        "id" AS star_id,
        "name" AS name,
        "dec",
        "ra",
        CAST(
            CAST(
                CAST(
                    sind(CAST("dec" AS decimal))
                AS decimal)
                *
                -0.42196345981438599996
            AS decimal)
            +
            CAST(
                CAST(
                    cosd(CAST("dec" AS decimal))
                AS decimal)
                * 0.90661283830611678104
            AS decimal)
            *
            CAST(
                cosd(
                    CAST(
                        CAST("ra" AS decimal) - 219.3375
                    AS decimal)
                )
            AS decimal)
        AS decimal) AS sub_formula
        FROM "core_star"
) AS sub_req
WHERE
    CAST("dec" AS decimal) >= -24.95888888888888777777777778
    AND
    CAST("dec" AS decimal) <= -24.95833333333333222222222222
AND
    CAST(
        acosd(
            CASE
                WHEN sub_formula > 1 THEN 1
                WHEN sub_formula < -1 THEN -1
                ELSE sub_formula
            END
        )
    AS decimal)
    < 0.0002777777777777777777777777778
;