SQL graphics

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 10x10 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!

tags:
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

6 Comments to "SQL graphics"

  1. Sheeri wrote:

    This is very neat! Thank you for sharing!

  2. SQL pie chart | code.openark.org wrote:

    [...] SQL graphics [...]

  3. David Mac wrote:

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

  4. Dibujos SQL « /var/log/jynus wrote:

    [...] 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 [...]

  5. Daniel Ríos wrote:

    O_o

    It´s Amazing!

  6. Gildus» Blog Archive » Como dibujar usando SQL wrote:

    [...] 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 [...]

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org