2011/08/13

postgres tips: calculate degree with stored procedure

-- User-Defined-Function for calculate degree
-- ------------------------------------------

-- This function calculates cos from vector(0,1)

-- .. code-block:: sql
--    :linenos:

CREATE OR REPLACE FUNCTION GET_COS
    (v2_x FLOAT, v2_y FLOAT)
RETURNS FLOAT AS
$$
DECLARE
    v1_x FLOAT;
    v1_y FLOAT;
    v1_v2 FLOAT;
    length_v1 FLOAT;
    length_v2 FLOAT;
    cos FLOAT;
BEGIN
    v1_x := 0.;
    v1_y := 1.;
    v1_v2 := (v1_x * v2_x) + (v1_y * v2_y);
    length_v1 := sqrt(power(v1_x,2) + power(v1_y,2));
    length_v2 := sqrt(power(v2_x,2) + power(v2_y,2));
    cos := v1_v2 / (length_v1 * length_v2);
    RETURN cos;
END;
$$ language plpgsql;

-- This function calculates sin from vector(0,1)

-- .. code-block:: sql
--    :linenos:

CREATE OR REPLACE FUNCTION GET_SIN
    (v2_x FLOAT, v2_y FLOAT)
RETURNS FLOAT AS
$$
DECLARE
    v1_x FLOAT;
    v1_y FLOAT;
    det FLOAT;
    length_v1 FLOAT;
    length_v2 FLOAT;
    sin FLOAT;
BEGIN
    v1_x := 0.;
    v1_y := 1.;
    det := (v1_x * v2_y) - (v1_y * v2_x);
    length_v1 := sqrt(power(v1_x,2) + power(v1_y,2));
    length_v2 := sqrt(power(v2_x,2) + power(v2_y,2));
    sin := det / (length_v1 * length_v2);
    RETURN sin;
END;
$$ language plpgsql;

-- This function calculates degree from vector(0,1)

-- .. code-block:: sql
--    :linenos:

CREATE OR REPLACE FUNCTION GET_DEGREE
    (x FLOAT, y FLOAT)
RETURNS FLOAT AS
$$
DECLARE
    sin FLOAT;
    cos FLOAT;
    degree FLOAT;
BEGIN
    sin := GET_SIN(x, y);
    cos := GET_COS(x, y);
    IF sin >= 0 AND cos >= 0 THEN
        degree := degrees(acos(cos));
    ELSEIF sin >= 0 AND cos < 0 THEN
        degree := degrees(acos(cos));
    ELSEIF sin < 0 AND cos < 0 THEN
        degree := 180. - degrees(asin(sin));
    ELSEIF sin < 0 AND cos >= 0 THEN
        degree := 360. + degrees(asin(sin));
    END IF;
    RETURN degree;
END;
$$ language plpgsql;

No comments:

Post a Comment

100