Background

I needed to create a function that returns a compass point from a whole circle bearing.

Requirements

The function creates a compass point in text from an input 360 degree bearing which is fully described eg NorthEast or abbreviated eg NE.

Oracle Function

The function is shown below.

Parameters

The CompassPoint function takes two parameters:

• _bearing is a number between 0 and 360. Any decimal places are removed.
• p_abbreviation if 1 then an abbreviated word is created ie NE, otherwise the full term is returned ie NorthEast.

   FUNCTION CompassPoint(p_bearing      IN NUMBER,
                         p_abbreviation IN INTEGER DEFAULT 1)
     RETURN varchar2
   AS
     v_bearing NUMBER;
   BEGIN
     IF ( p_bearing IS NULL ) THEN
       RETURN NULL;
     END IF;
     v_bearing := round(abs(p_bearing),2);
     RETURN CASE WHEN NVL(p_abbreviation,1) <> 0
                 THEN CASE WHEN v_bearing BETWEEN   0.00 AND  11.25 THEN 'North'
                           WHEN v_bearing BETWEEN  11.25 AND  33.75 THEN 'North-NorthEast'
                           WHEN v_bearing BETWEEN  33.75 AND  56.25 THEN 'NorthEast'
                           WHEN v_bearing BETWEEN  56.25 AND  78.75 THEN 'East-NorthEast'
                           WHEN v_bearing BETWEEN  78.75 AND 101.25 THEN 'East'
                           WHEN v_bearing BETWEEN 101.25 AND 123.75 THEN 'East-SouthEast'
                           WHEN v_bearing BETWEEN 123.75 AND 146.25 THEN 'SouthEast'
                           WHEN v_bearing BETWEEN 146.25 AND 168.75 THEN 'South-SouthEast'
                           WHEN v_bearing BETWEEN 168.75 AND 191.25 THEN 'South'
                           WHEN v_bearing BETWEEN 191.25 AND 213.75 THEN 'South-SouthWest'
                           WHEN v_bearing BETWEEN 213.75 AND 236.25 THEN 'SouthWest'
                           WHEN v_bearing BETWEEN 236.25 AND 258.75 THEN 'West-SouthWest'
                           WHEN v_bearing BETWEEN 258.75 AND 281.25 THEN 'West'
                           WHEN v_bearing BETWEEN 281.25 AND 303.75 THEN 'West-NorthWest'
                           WHEN v_bearing BETWEEN 303.75 AND 326.25 THEN 'NorthWest'
                           WHEN v_bearing BETWEEN 326.25 AND 348.75 THEN 'North-NorthWest'
                           WHEN v_bearing BETWEEN 348.75 AND 360.00 THEN 'North'
                           ELSE NULL
                       END
                 ELSE CASE WHEN v_bearing BETWEEN   0.00 AND  11.25 THEN 'N'
                           WHEN v_bearing BETWEEN  11.25 AND  33.75 THEN 'NNE'
                           WHEN v_bearing BETWEEN  33.75 AND  56.25 THEN 'NE'
                           WHEN v_bearing BETWEEN  56.25 AND  78.75 THEN 'ENE'
                           WHEN v_bearing BETWEEN  78.75 AND 101.25 THEN 'E'
                           WHEN v_bearing BETWEEN 101.25 AND 123.75 THEN 'ESE'
                           WHEN v_bearing BETWEEN 123.75 AND 146.25 THEN 'SE'
                           WHEN v_bearing BETWEEN 146.25 AND 168.75 THEN 'SSE'
                           WHEN v_bearing BETWEEN 168.75 AND 191.25 THEN 'S'
                           WHEN v_bearing BETWEEN 191.25 AND 213.75 THEN 'SSW'
                           WHEN v_bearing BETWEEN 213.75 AND 236.25 THEN 'SW'
                           WHEN v_bearing BETWEEN 236.25 AND 258.75 THEN 'WSW'
                           WHEN v_bearing BETWEEN 258.75 AND 281.25 THEN 'W'
                           WHEN v_bearing BETWEEN 281.25 AND 303.75 THEN 'WNW'
                           WHEN v_bearing BETWEEN 303.75 AND 326.25 THEN 'NW'
                           WHEN v_bearing BETWEEN 326.25 AND 348.75 THEN 'NNW'
                           WHEN v_bearing BETWEEN 348.75 AND 360.00 THEN 'N'
                           ELSE NULL
                       END
            END;
   END CompassPoint;

Testing

 SET linesize 500 pagesize 5000
 SELECT 2*level AS bearing,
        CAST(Frontage.CompassPoint(2*level,0) AS varchar2( 4)) AS compassPoint,
        CAST(Frontage.CompassPoint(2*level,1) AS varchar2(20)) AS compassPointFull
   FROM dual
 CONNECT BY level < 181;

    BEARING COMP COMPASSPOINTFULL
 ---------- ---- --------------------
          2 N    North
          4 N    North
          6 N    North
          8 N    North
         10 N    North
         12 NNE  North-NorthEast
         14 NNE  North-NorthEast
         16 NNE  North-NorthEast
         18 NNE  North-NorthEast
         20 NNE  North-NorthEast
         22 NNE  North-NorthEast
         24 NNE  North-NorthEast
         26 NNE  North-NorthEast
         28 NNE  North-NorthEast
         30 NNE  North-NorthEast
         32 NNE  North-NorthEast
         34 NE   NorthEast
         36 NE   NorthEast
         38 NE   NorthEast
         40 NE   NorthEast
         42 NE   NorthEast
         44 NE   NorthEast
         46 NE   NorthEast
         48 NE   NorthEast
         50 NE   NorthEast
         52 NE   NorthEast
         54 NE   NorthEast
         56 NE   NorthEast
         58 ENE  East-NorthEast
         60 ENE  East-NorthEast
         62 ENE  East-NorthEast
         64 ENE  East-NorthEast
         66 ENE  East-NorthEast
         68 ENE  East-NorthEast
         70 ENE  East-NorthEast
         72 ENE  East-NorthEast
         74 ENE  East-NorthEast
         76 ENE  East-NorthEast
         78 ENE  East-NorthEast
         80 E    East
         82 E    East
         84 E    East
         86 E    East
         88 E    East
         90 E    East
         92 E    East
         94 E    East
         96 E    East
         98 E    East
        100 E    East
        102 ESE  East-SouthEast
        104 ESE  East-SouthEast
        106 ESE  East-SouthEast
        108 ESE  East-SouthEast
        110 ESE  East-SouthEast
        112 ESE  East-SouthEast
        114 ESE  East-SouthEast
        116 ESE  East-SouthEast
        118 ESE  East-SouthEast
        120 ESE  East-SouthEast
        122 ESE  East-SouthEast
        124 SE   SouthEast
        126 SE   SouthEast
        128 SE   SouthEast
        130 SE   SouthEast
        132 SE   SouthEast
        134 SE   SouthEast
        136 SE   SouthEast
        138 SE   SouthEast
        140 SE   SouthEast
        142 SE   SouthEast
        144 SE   SouthEast
        146 SE   SouthEast
        148 SSE  South-SouthEast
        150 SSE  South-SouthEast
        152 SSE  South-SouthEast
        154 SSE  South-SouthEast
        156 SSE  South-SouthEast
        158 SSE  South-SouthEast
        160 SSE  South-SouthEast
        162 SSE  South-SouthEast
        164 SSE  South-SouthEast
        166 SSE  South-SouthEast
        168 SSE  South-SouthEast
        170 S    South
        172 S    South
        174 S    South
        176 S    South
        178 S    South
        180 S    South
        182 S    South
        184 S    South
        186 S    South
        188 S    South
        190 S    South
        192 SSW  South-SouthWest
        194 SSW  South-SouthWest
        196 SSW  South-SouthWest
        198 SSW  South-SouthWest
        200 SSW  South-SouthWest
        202 SSW  South-SouthWest
        204 SSW  South-SouthWest
        206 SSW  South-SouthWest
        208 SSW  South-SouthWest
        210 SSW  South-SouthWest
        212 SSW  South-SouthWest
        214 SW   SouthWest
        216 SW   SouthWest
        218 SW   SouthWest
        220 SW   SouthWest
        222 SW   SouthWest
        224 SW   SouthWest
        226 SW   SouthWest
        228 SW   SouthWest
        230 SW   SouthWest
        232 SW   SouthWest
        234 SW   SouthWest
        236 SW   SouthWest
        238 WSW  West-SouthWest
        240 WSW  West-SouthWest
        242 WSW  West-SouthWest
        244 WSW  West-SouthWest
        246 WSW  West-SouthWest
        248 WSW  West-SouthWest
        250 WSW  West-SouthWest
        252 WSW  West-SouthWest
        254 WSW  West-SouthWest
        256 WSW  West-SouthWest
        258 WSW  West-SouthWest
        260 W    West
        262 W    West
        264 W    West
        266 W    West
        268 W    West
        270 W    West
        272 W    West
        274 W    West
        276 W    West
        278 W    West
        280 W    West
        282 WNW  West-NorthWest
        284 WNW  West-NorthWest
        286 WNW  West-NorthWest
        288 WNW  West-NorthWest
        290 WNW  West-NorthWest
        292 WNW  West-NorthWest
        294 WNW  West-NorthWest
        296 WNW  West-NorthWest
        298 WNW  West-NorthWest
        300 WNW  West-NorthWest
        302 WNW  West-NorthWest
        304 NW   NorthWest
        306 NW   NorthWest
        308 NW   NorthWest
        310 NW   NorthWest
        312 NW   NorthWest
        314 NW   NorthWest
        316 NW   NorthWest
        318 NW   NorthWest
        320 NW   NorthWest
        322 NW   NorthWest
        324 NW   NorthWest
        326 NW   NorthWest
        328 NNW  North-NorthWest
        330 NNW  North-NorthWest
        332 NNW  North-NorthWest
        334 NNW  North-NorthWest
        336 NNW  North-NorthWest
        338 NNW  North-NorthWest
        340 NNW  North-NorthWest
        342 NNW  North-NorthWest
        344 NNW  North-NorthWest
        346 NNW  North-NorthWest
        348 NNW  North-NorthWest
        350 N    North
        352 N    North
        354 N    North
        356 N    North
        358 N    North
        360 N    North
  180 ROWS selected

I hope this is useful to someone out there.