EXPLAIN EXTENDED

How to create fast database queries

Happy New Year: drawing clock in SQL

with 9 comments

Some say SQL is not good at graphics.

Well, they have some point. Database engines lack scanner drivers, there is no easy way to do sepia, and magic wand, let's be honest, is just poor.

However, you can make some New Year paintings with SQL.

Let's make a New Year clock showing 12 o'clock in Oracle.

#1. Circle

First, we need a circle shape. In Photoshop, you could just select it from the toolbar, but in SQL, we need to use some math.

In math, a circle is defined by this formula: x2 + y2 = R2.

x2 + y2 is, as we know, the square of a distance from the center of the coordinate grid, and R is the radius. This means all points at distance R from the center, which of course is a circle shape.

SQL outputs data in tabular format. We will draw out art string by string, and for each line we need to know where to put the paint (just some ASCII symbols). Since it's a circle, every line intersects it at most twice, and we shall put the symbols at the places where the circle and the line intersect. To know how many symbols are there from the beginning of the string, this formula is used:

ROUND(SQRT(1 - POWER((level - 21) / 20, 2)) * 20) AS angle

To make our circle more circle-shaped, we need to choose the characters that correspond to the angle of the circle line in each given place. These characters are the most similar to the lines: =/|\

Different characters correspond to different angles. To figure out the angle from the line number, we should use ACOS:

ROUND(ACOS(angle / 20) / 3.1415926 * 4) * SIGN((line - 21) / 20) + 3 AS sign

This would split the circle into several sectors, and for each of these sectors a corresponding character will be chosen.

Finally, we need to avoid the gaps in the lines. To do this, we will not only fill the intersections, but all space from the previous (or next) intersection to the current. To calculated the width of the string on each line that will be filled with the characters, we will use LAG and LEAD, the analytic functions.

Now, let's put it all together:

WITH    circle AS
(
SELECT  1 AS layer,
line,
LPAD(RPAD(SUBSTR('=/|\=', sign, 1), width, SUBSTR('=/|\=', sign, 1)), 20 + width - angle, ' ') ||
RPAD(' ', (angle - width) * 2, ' ') ||
RPAD(RPAD(SUBSTR('=\|/=', sign, 1), width, SUBSTR('=\|/=', sign, 1)), 20 + width - angle, ' ') AS drawing
FROM    (
SELECT  line,
angle,
ABS
(
angle -
CASE
WHEN line < 21 THEN
                                COALESCE(LAG(angle) OVER (ORDER BY line), 0)
                        WHEN line > 21 THEN
COALESCE(LEAD(angle) OVER (ORDER BY line), 0)
ELSE
angle
END
) + 1 AS width,
ROUND(ACOS(angle / 20) / 3.1415926 * 4)  * SIGN((line - 21) / 20) + 3 AS sign
FROM    (
SELECT  level AS line,
ROUND(SQRT(1 - POWER((level - 21) / 20, 2)) * 20) AS angle
FROM    dual
CONNECT BY
level <= 41
                        ) q
                ) q
        )
SELECT  *
FROM    circle

LAYER LINE DRAWING
1 1 ==
1 2 ==============
1 3 //// \\\\
1 4 /// \\\
1 5 // \\
1 6 // \\
1 7 // \\
1 8 // \\
1 9 // \\
1 10 // \\
1 11 / \
1 12 // \\
1 13 / \
1 14 || ||
1 15 | |
1 16 | |
1 17 || ||
1 18 | |
1 19 | |
1 20 | |
1 21 | |
1 22 | |
1 23 | |
1 24 | |
1 25 || ||
1 26 | |
1 27 | |
1 28 || ||
1 29 \ /
1 30 \\ //
1 31 \ /
1 32 \\ //
1 33 \\ //
1 34 \\ //
1 35 \\ //
1 36 \\ //
1 37 \\ //
1 38 \\\ ///
1 39 \\\\ ////
1 40 ==============
1 41 ==

#2. Dial

Now, we shall draw the clock dial on a separate layer (yes, you can draw on layers in SQL).

We will use Roman numerals for the dial. Oracle has an internal function to format the numbers as Roman numerals, but SQL graphics is the first time I'm using this feature in practice.

The principle is the same: we should calculate the angle of each number, then the line and the column that intersect the dial at the given angle and put the number there.

We will only output the lines containing actual numbers. Each line will contain one or two numbers (1 and 6 go on their own lines, the others go in pairs). We can use MIN and MAX to distinguish them.

Here's what the lines of our dial will look like:

WITH    dial AS
(
SELECT  2,
line,
RPAD(' ', 20 - angle, ' ') ||
RPAD(rnf, angle * 2 - LENGTH(rns)) ||
RPAD(rns, 20 - angle + DECODE(rnf, NULL, 0, LENGTH(rns)) + 1)
FROM    (
SELECT  line, angle,
DECODE(MAX(h), MIN(h), NULL, TRIM(TO_CHAR(MAX(h), 'RN'))) AS rnf,
TRIM(TO_CHAR(MIN(h), 'RN')) AS rns
FROM    (
SELECT  level + 2 AS line,
ROUND(SQRT(1 - POWER((level - 19) / 18, 2)) * 18) AS angle
FROM    dual
CONNECT BY
level <= 37
                        ) lines
                JOIN    (
                        SELECT  level AS h, ROUND(-COS(3.141592 * level / 6) * 18) + 21 AS hline
                        FROM    dual
                        CONNECT BY
                                level <= 12
                        ) hours
                ON      hline = line
                GROUP BY
                        line, angle
                ) q
        )
SELECT  *
FROM    dial
ORDER BY
        line

2 LINE RPAD('',20-ANGLE,'')||RPAD(RNF,ANGLE*2-LENGTH(RNS))||RPAD(RNS,20-ANGLE+DECODE(RNF,NULL,0,LENGTH(RNS))+1)
2 3 XII
2 5 XI I
2 12 X II
2 21 IX III
2 30 VIII IV
2 37 VII V
2 39 VI

Note that the shape is distorted: this is because of the gaps in the line numbers. It will be taken care of later.

#3. Hands and the center pin

Hands and the center pin are simple: we will use ||| for the hour hand, | for the minute hand and O for the pin. On a New Year clock the hands, fortunately, are vertical and directed in one way.

Here's the hour hand:

WITH    hourhand AS
(
SELECT  3 AS layer,
level + 10 AS line,
RPAD(' ', 20, ' ') || '|||' || RPAD(' ', 18, ' ') AS drawing
FROM    dual
CONNECT BY
level <= 10
        )
SELECT  *
FROM    hourhand
ORDER BY
        line
LAYER LINE DRAWING
3 11 |||
3 12 |||
3 13 |||
3 14 |||
3 15 |||
3 16 |||
3 17 |||
3 18 |||
3 19 |||
3 20 |||

, the minute hand:

WITH    minutehand AS
(
SELECT  4 AS layer,
level + 4 AS line,
RPAD(' ', 21, ' ') || '|' || RPAD(' ', 19, ' ') AS drawing
FROM    dual
CONNECT BY
level <= 16
        )
SELECT  *
FROM    minutehand
ORDER BY
        line
LAYER LINE DRAWING
4 5 |
4 6 |
4 7 |
4 8 |
4 9 |
4 10 |
4 11 |
4 12 |
4 13 |
4 14 |
4 15 |
4 16 |
4 17 |
4 18 |
4 19 |
4 20 |

and the pin:

WITH    pin AS
(
SELECT  5 AS layer,
21 AS line,
RPAD(LPAD('O', 22, ' '), 41, ' ') AS drawing
FROM    dual
)
SELECT  *
FROM    pin
ORDER BY
line
LAYER LINE DRAWING
5 21 O

#4. Merging the layers.

Now we need to merge the layers.

The last layers should go on the foreground, the first ones on the background. Space symbol should be transparent: if there is a character on the background layer, it should be visible through it.

To do it we we apply this trick:

  1. Output all rows from all layers in a single query, using UNION ALL
  2. Split each row into the individual characters, one character per record
  3. Using ROW_NUMBER(), the analytical function, order the characters within each line and columns so that visible characters from higher layers go first, and spaces and characters from the lower layers go last:

    ROW_NUMBER() OVER (PARTITION BY line, col ORDER BY DECODE(cc, ' ', 1, 0), layer DESC) AS rn

  4. Filter the characters on rn so that only the topmost character from the each line and column is returned
  5. Concatenate all characters line-wise, using a recursive query and SYS_CONNECT_BY_PATH. This function requires a separator, and we will use a question mark, ?, for this purpose, since it's not used in our art.
  6. Remove the separator from the concatenated strings, using REPLACE
  7. Output the result

#5. Result

Here's our query and drawing:

WITH    circle AS
(
SELECT  1 AS layer,
line,
LPAD(RPAD(SUBSTR('=/|\=', sign, 1), width, SUBSTR('=/|\=', sign, 1)), 20 + width - angle, ' ') ||
RPAD(' ', (angle - width) * 2, ' ') ||
RPAD(RPAD(SUBSTR('=\|/=', sign, 1), width, SUBSTR('=\|/=', sign, 1)), 20 + width - angle, ' ') AS drawing
FROM    (
SELECT  line,
angle,
ABS
(
angle -
CASE
WHEN line < 21 THEN
                                COALESCE(LAG(angle) OVER (ORDER BY line), 0)
                        WHEN line > 21 THEN
COALESCE(LEAD(angle) OVER (ORDER BY line), 0)
ELSE
angle
END
) + 1 AS width,
ROUND(ACOS(angle / 20) / 3.1415926 * 4)  * SIGN((line - 21) / 20) + 3 AS sign
FROM    (
SELECT  level AS line,
ROUND(SQRT(1 - POWER((level - 21) / 20, 2)) * 20) AS angle
FROM    dual
CONNECT BY
level <= 41
                        ) q
                ) q
        ),
        dial AS
        (
        SELECT  2,
                line,
                RPAD(' ', 20 - angle, ' ') ||
                RPAD(rnf, angle * 2 - LENGTH(rns)) ||
                RPAD(rns, 20 - angle + DECODE(rnf, NULL, 0, LENGTH(rns)) + 1)
        FROM    (
                SELECT  line, angle,
                        DECODE(MAX(h), MIN(h), NULL, TRIM(TO_CHAR(MAX(h), 'RN'))) AS rnf,
                        TRIM(TO_CHAR(MIN(h), 'RN')) AS rns
                FROM    (
                        SELECT  level + 2 AS line,
                                ROUND(SQRT(1 - POWER((level - 19) / 18, 2)) * 18) AS angle
                        FROM    dual
                        CONNECT BY
                                level <= 37
                        ) lines
                JOIN    (
                        SELECT  level AS h, ROUND(-COS(3.141592 * level / 6) * 18) + 21 AS hline
                        FROM    dual
                        CONNECT BY
                                level <= 12
                        ) hours
                ON      hline = line
                GROUP BY
                        line, angle
                ) q
        ),
        hourhand AS
        (
        SELECT  3 AS layer,
                level + 10 AS line,
                RPAD(' ', 20, ' ') || '|||' || RPAD(' ', 18, ' ') AS drawing
        FROM    dual
        CONNECT BY
                level <= 10
        ),
        minutehand AS
        (
        SELECT  4 AS layer,
                level + 4 AS line,
                RPAD(' ', 21, ' ') || '|' || RPAD(' ', 19, ' ') AS drawing
        FROM    dual
        CONNECT BY
                level <= 16
        ),
        pin AS
        (
        SELECT  5 AS layer,
                21 AS line,
                RPAD(LPAD('O', 22, ' '), 41, ' ') AS drawing
        FROM    dual
        ),
        m AS
        (
        SELECT  line, col, cc
        FROM    (
                SELECT  line, col, cc,
                        ROW_NUMBER() OVER (PARTITION BY line, col ORDER BY DECODE(cc, ' ', 1, 0), layer DESC) AS rn
                FROM    (
                        SELECT  cols.*, layers.*, SUBSTR(drawing, col, 1) AS cc
                        FROM    (
                                SELECT  level AS col
                                FROM    dual
                                CONNECT BY
                                        level <= 50
                                ) cols
                        CROSS JOIN
                                (
                                SELECT  *
                                FROM    circle
                                UNION ALL
                                SELECT  *
                                FROM    dial
                                UNION ALL
                                SELECT  *
                                FROM    hourhand
                                UNION ALL
                                SELECT  *
                                FROM    minutehand
                                UNION ALL
                                SELECT  *
                                FROM    pin
                                ) layers        
                        )
                )
        WHERE   rn = 1
        )
SELECT  REPLACE(drawing, '?') AS drawing
FROM    (
        SELECT  SYS_CONNECT_BY_PATH(cc, '?') AS drawing, line, CONNECT_BY_ISLEAF AS leaf
        FROM    m mi
        START WITH
                mi.col = 1
        CONNECT BY
                mi.line = PRIOR mi.line
                AND mi.col = PRIOR mi.col + 1
        )
WHERE   leaf = 1
ORDER BY
        line
DRAWING
==
==============
//// XII \\\\
/// \\\
// XI | I \\
// | \\
// | \\
// | \\
// | \\
// | \\
/ ||| \
//X ||| II\\
/ ||| \
|| ||| ||
| ||| |
| ||| |
|| ||| ||
| ||| |
| ||| |
| ||| |
| IX O III |
| |
| |
| |
|| ||
| |
| |
|| ||
\ /
\\VIII IV//
\ /
\\ //
\\ //
\\ //
\\ //
\\ //
\\ VII V //
\\\ ///
\\\\ VI ////
==============
==
Happy New Year!

Written by Quassnoi

December 31st, 2010 at 11:00 pm

Posted in Oracle

Tagged with , ,

9 Responses to 'Happy New Year: drawing clock in SQL'

Subscribe to comments with RSS

  1. You are definitely insane ;)
    Happy New Year (if is not 14 for you ;)

    ts

    31 Dec 10 at 23:35

  2. :)

    This is awesome. You are a MySQL rockstar!

    Andy

    1 Jan 11 at 01:49

  3. WOW! You are the SQL god!

    :-O

    1 Jan 11 at 05:23

  4. You are a mysql wizard.

    Mo

    1 Jan 11 at 19:14

  5. Thank you all guys and happy New Year!

    @ts: no, it’s 1st, like all over the world :) It’s Christmas, a religious holiday, that is on Jan 7th (Dec 24th Julian style).

    @Andy and @Mo: it’s actually Oracle. It’s a little bit harder on MySQL (though of course still possible!)

    Quassnoi

    2 Jan 11 at 01:44

  6. LOL Alex! You definitely know how to impress. I’m not sure if this wins you SQL innovation award of the year, or nerd of the year! Happy Holidays mate.

    Ian

    2 Jan 11 at 10:46

  7. @Ian and all those interested: the previous New Year article had 4 similar paintings: https://explainextended.com/2009/12/31/happy-new-year/

    Quassnoi

    2 Jan 11 at 12:45

  8. Love the fireworks! Can’t say I would have ever thought, “hey, let’s use SQL to draw firework glyphs!” :)

    Perhaps you could title yourself “SQL Artist Extraordinaire”.

    Ian

    2 Jan 11 at 14:19

  9. very very nice. you are always rock.

    Halim

    4 Jan 11 at 14:17

Leave a Reply