Skip to content

# SQL graphics

MySQL SQL August 11, 2009

SQL is not meant to generate graphics, for sure; but I see some cases where generating non-tabular output can be desirable, as I will show in future posts.

I’d like to explain the basics of working SQL graphics: it is actually possible to do whatever you like. How?

#### Coordinates system

We’ll now develop a coordinates system using SQL. By producing this, I will have proven my point that anything is possible, and will provide an additional proof of concept.

To start with generating coordinates, I’ll need a helper table: a numbers table (tinyint_asc, example, with numbers ranging 0..255).

We’ll strive to produce a 10×10 coordinate matrix. To do this, we’ll self-join the numbers table against itself, and use a helper variable to set the size of the matrix.

>     SELECT
>       *
>     FROM
>       tinyint_asc numbers1,
>       tinyint_asc numbers2,
>       (select @size := 10) sel_size
>     WHERE
>       numbers1.value < @size
>       AND numbers2.value < @size
>     ;
>     +-------+-------+-------------+
>     | value | value | @size := 10 |
>     +-------+-------+-------------+
>     |     0 |     0 |          10 |
>     |     1 |     0 |          10 |
>     |     2 |     0 |          10 |
>     |     3 |     0 |          10 |
>     |     4 |     0 |          10 |
>     |     5 |     0 |          10 |
>     |     6 |     0 |          10 |
>     |     7 |     0 |          10 |
>     |     8 |     0 |          10 |
>     |     9 |     0 |          10 |
>     |     0 |     1 |          10 |
>     |     1 |     1 |          10 |
>     |     2 |     1 |          10 |
>     |     3 |     1 |          10 |
>     |     4 |     1 |          10 |
>     |     5 |     1 |          10 |
>     |     6 |     1 |          10 |
>     ...
>     |     7 |     8 |          10 |
>     |     8 |     8 |          10 |
>     |     9 |     8 |          10 |
>     |     0 |     9 |          10 |
>     |     1 |     9 |          10 |
>     |     2 |     9 |          10 |
>     |     3 |     9 |          10 |
>     |     4 |     9 |          10 |
>     |     5 |     9 |          10 |
>     |     6 |     9 |          10 |
>     |     7 |     9 |          10 |
>     |     8 |     9 |          10 |
>     |     9 |     9 |          10 |
>     +-------+-------+-------------+

Next step will be to present the coordinates in a slightly different way (this is just eyecandy):

>     SELECT
>       CONCAT(numbers2.value, '.', numbers1.value)
>     FROM
>       tinyint_asc numbers1,
>       tinyint_asc numbers2,
>       (select @size := 10) sel_size
>     WHERE
>       numbers1.value < @size
>       AND numbers2.value < @size
>     ;
>     +---------------------------------------------+
>     | CONCAT(numbers2.value, '.', numbers1.value) |
>     +---------------------------------------------+
>     | 0.0                                         |
>     | 0.1                                         |
>     | 0.2                                         |
>     | 0.3                                         |
>     | 0.4                                         |
>     | 0.5                                         |
>     | 0.6                                         |
>     | 0.7                                         |
>     | 0.8                                         |
>     | 0.9                                         |
>     | 1.0                                         |
>     | 1.1                                         |
>     | 1.2                                         |
>     | 1.3                                         |
>     | 1.4                                         |
>     | 1.5                                         |
>     | 1.6                                         |
>     | 1.7                                         |
>     ...

Time to turn this into a coordinates system:

>     SELECT
>       GROUP_CONCAT(
>         CONCAT(numbers2.value, '.', numbers1.value)
>         ORDER BY numbers1.value
>         SEPARATOR ' ') AS coordinates
>     FROM
>       tinyint_asc numbers1,
>       tinyint_asc numbers2,
>       (select @size := 10) sel_size
>     WHERE
>       numbers1.value < @size
>       AND numbers2.value < @size
>     GROUP BY numbers2.value
>     ;
>     +-----------------------------------------+
>     | coordinates                             |
>     +-----------------------------------------+
>     | 0.0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 |
>     | 1.0 1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 1.9 |
>     | 2.0 2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 |
>     | 3.0 3.1 3.2 3.3 3.4 3.5 3.6 3.7 3.8 3.9 |
>     | 4.0 4.1 4.2 4.3 4.4 4.5 4.6 4.7 4.8 4.9 |
>     | 5.0 5.1 5.2 5.3 5.4 5.5 5.6 5.7 5.8 5.9 |
>     | 6.0 6.1 6.2 6.3 6.4 6.5 6.6 6.7 6.8 6.9 |
>     | 7.0 7.1 7.2 7.3 7.4 7.5 7.6 7.7 7.8 7.9 |
>     | 8.0 8.1 8.2 8.3 8.4 8.5 8.6 8.7 8.8 8.9 |
>     | 9.0 9.1 9.2 9.3 9.4 9.5 9.6 9.7 9.8 9.9 |
>     +-----------------------------------------+

The above is all we need to generate any graphics we like. Just use your favorite mathematical function, apply analytic geometry and make it happen. Instead or presenting the coordinates, use them to decide what should be presented.

#### Proof of concept

To show that the above sentence is actually very simple, I’ll show how to draw a circle using the SQL coordinates. The well known circle formula says:

> (x-x0)² + (y-y0)² = rad²

> x0, y0 being the circle’s central coordinates, rad being the radius.

To apply:

>     SELECT
>       GROUP_CONCAT(
>         IF(
>           ROUND(SQRT(POW(numbers1.value-(@size-1)/2, 2) + POW(numbers2.value-(@size-1)/2, 2))) <= @radius,
>           '#', '-')
>         ORDER BY numbers1.value
>         SEPARATOR ' ') AS circle
>     FROM
>       tinyint_asc numbers1,
>       tinyint_asc numbers2,
>       (select @size := 15) sel_size,
>       (select @radius := 6) sel_radius
>     WHERE
>       numbers1.value < @size
>       AND numbers2.value < @size
>     GROUP BY numbers2.value
>     ;
>     +-------------------------------+
>     | circle                        |
>     +-------------------------------+
>     | - - - - - - - - - - - - - - - |
>     | - - - - - # # # # # - - - - - |
>     | - - - # # # # # # # # # - - - |
>     | - - # # # # # # # # # # # - - |
>     | - - # # # # # # # # # # # - - |
>     | - # # # # # # # # # # # # # - |
>     | - # # # # # # # # # # # # # - |
>     | - # # # # # # # # # # # # # - |
>     | - # # # # # # # # # # # # # - |
>     | - # # # # # # # # # # # # # - |
>     | - - # # # # # # # # # # # - - |
>     | - - # # # # # # # # # # # - - |
>     | - - - # # # # # # # # # - - - |
>     | - - - - - # # # # # - - - - - |
>     | - - - - - - - - - - - - - - - |
>     +-------------------------------+

Remove the space separator, play with ratio, apply your own formula, and generate SQL-ascii graphics!

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
6
Leave a Reply

6 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
3 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Sheeri

This is very neat! Thank you for sharing!

SQL pie chart | code.openark.org

[…] SQL graphics […]

Guest
David Mac

This is absolutely brilliant! Creative and lateral thinking, plus a healthy dose of SQL smarts.

Dibujos SQL « /var/log/jynus

[…] de usar SQL que leí a través del Planet MySQL. Éste es el primer post de la serie. Podéis leer la entrada original en inglés en su […]

Guest
Daniel Ríos

O_o

It´s Amazing!

Gildus» Blog Archive » Como dibujar usando SQL

[…] PlanetMySQL han publicado unos articulo de como hacer dibujos o generar gráficos usando SQL. Lo hacen usando ASCII y es una buena forma de ejercitar los […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.