{"id":1097,"date":"2009-08-11T14:24:32","date_gmt":"2009-08-11T12:24:32","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1097"},"modified":"2009-08-11T14:24:32","modified_gmt":"2009-08-11T12:24:32","slug":"sql-graphics","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/sql-graphics","title":{"rendered":"SQL graphics"},"content":{"rendered":"

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.<\/p>\n

I’d like to explain the basics of working SQL graphics: it is actually possible to do whatever you like. How?<\/p>\n

Coordinates system<\/h4>\n

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.<\/p>\n

To start with generating coordinates, I’ll need a helper table: a numbers table (tinyint_asc<\/a>, example, with numbers ranging 0..255).<\/p>\n

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.<\/p>\n

\n
SELECT\r\n  *\r\nFROM\r\n  tinyint_asc numbers1,\r\n  tinyint_asc numbers2,\r\n  (select @size := 10) sel_size\r\nWHERE\r\n  numbers1.value < @size\r\n  AND numbers2.value < @size\r\n;\r\n+-------+-------+-------------+\r\n| value | value | @size := 10 |\r\n+-------+-------+-------------+\r\n|     0 |     0 |          10 |\r\n|     1 |     0 |          10 |\r\n|     2 |     0 |          10 |\r\n|     3 |     0 |          10 |\r\n|     4 |     0 |          10 |\r\n|     5 |     0 |          10 |\r\n|     6 |     0 |          10 |\r\n|     7 |     0 |          10 |\r\n|     8 |     0 |          10 |\r\n|     9 |     0 |          10 |\r\n|     0 |     1 |          10 |\r\n|     1 |     1 |          10 |\r\n|     2 |     1 |          10 |\r\n|     3 |     1 |          10 |\r\n|     4 |     1 |          10 |\r\n|     5 |     1 |          10 |\r\n|     6 |     1 |          10 |\r\n...\r\n|     7 |     8 |          10 |\r\n|     8 |     8 |          10 |\r\n|     9 |     8 |          10 |\r\n|     0 |     9 |          10 |\r\n|     1 |     9 |          10 |\r\n|     2 |     9 |          10 |\r\n|     3 |     9 |          10 |\r\n|     4 |     9 |          10 |\r\n|     5 |     9 |          10 |\r\n|     6 |     9 |          10 |\r\n|     7 |     9 |          10 |\r\n|     8 |     9 |          10 |\r\n|     9 |     9 |          10 |\r\n+-------+-------+-------------+<\/pre>\n<\/blockquote>\n
Next step will be to present the coordinates in a slightly different way (this is just eyecandy):<\/p>\n
\n
SELECT\r\n  CONCAT(numbers2.value, '.', numbers1.value)\r\nFROM\r\n  tinyint_asc numbers1,\r\n  tinyint_asc numbers2,\r\n  (select @size := 10) sel_size\r\nWHERE\r\n  numbers1.value < @size\r\n  AND numbers2.value < @size\r\n;\r\n+---------------------------------------------+\r\n| CONCAT(numbers2.value, '.', numbers1.value) |\r\n+---------------------------------------------+\r\n| 0.0                                         |\r\n| 0.1                                         |\r\n| 0.2                                         |\r\n| 0.3                                         |\r\n| 0.4                                         |\r\n| 0.5                                         |\r\n| 0.6                                         |\r\n| 0.7                                         |\r\n| 0.8                                         |\r\n| 0.9                                         |\r\n| 1.0                                         |\r\n| 1.1                                         |\r\n| 1.2                                         |\r\n| 1.3                                         |\r\n| 1.4                                         |\r\n| 1.5                                         |\r\n| 1.6                                         |\r\n| 1.7                                         |\r\n...<\/pre>\n<\/blockquote>\n
Time to turn this into a coordinates system:<\/p>\n
\n
SELECT\r\n  GROUP_CONCAT(\r\n    CONCAT(numbers2.value, '.', numbers1.value)\r\n    ORDER BY numbers1.value\r\n    SEPARATOR ' ') AS coordinates\r\nFROM\r\n  tinyint_asc numbers1,\r\n  tinyint_asc numbers2,\r\n  (select @size := 10) sel_size\r\nWHERE\r\n  numbers1.value < @size\r\n  AND numbers2.value < @size\r\nGROUP BY numbers2.value\r\n;\r\n+-----------------------------------------+\r\n| coordinates                             |\r\n+-----------------------------------------+\r\n| 0.0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 |\r\n| 1.0 1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 1.9 |\r\n| 2.0 2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 |\r\n| 3.0 3.1 3.2 3.3 3.4 3.5 3.6 3.7 3.8 3.9 |\r\n| 4.0 4.1 4.2 4.3 4.4 4.5 4.6 4.7 4.8 4.9 |\r\n| 5.0 5.1 5.2 5.3 5.4 5.5 5.6 5.7 5.8 5.9 |\r\n| 6.0 6.1 6.2 6.3 6.4 6.5 6.6 6.7 6.8 6.9 |\r\n| 7.0 7.1 7.2 7.3 7.4 7.5 7.6 7.7 7.8 7.9 |\r\n| 8.0 8.1 8.2 8.3 8.4 8.5 8.6 8.7 8.8 8.9 |\r\n| 9.0 9.1 9.2 9.3 9.4 9.5 9.6 9.7 9.8 9.9 |\r\n+-----------------------------------------+<\/pre>\n<\/blockquote>\n
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<\/em> the coordinates, use them<\/em> to decide what should be presented.<\/p>\n
Proof of concept<\/h4>\n
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:<\/p>\n
(x-x0)\u00b2 + (y-y0)\u00b2 = rad\u00b2<\/p>\n
x0<\/strong>, y0<\/strong> being the circle’s central coordinates, rad<\/strong> being the radius.<\/p><\/blockquote>\n
To apply:<\/p>\n
\n
SELECT\r\n  GROUP_CONCAT(\r\n    IF(\r\n      ROUND(SQRT(POW(numbers1.value-(@size-1)\/2, 2) + POW(numbers2.value-(@size-1)\/2, 2))) <= @radius,\r\n      '#', '-')\r\n    ORDER BY numbers1.value\r\n    SEPARATOR ' ') AS circle\r\nFROM\r\n  tinyint_asc numbers1,\r\n  tinyint_asc numbers2,\r\n  (select @size := 15) sel_size,\r\n  (select @radius := 6) sel_radius\r\nWHERE\r\n  numbers1.value < @size\r\n  AND numbers2.value < @size\r\nGROUP BY numbers2.value\r\n;\r\n+-------------------------------+\r\n| circle                        |\r\n+-------------------------------+\r\n| - - - - - - - - - - - - - - - |\r\n| - - - - - # # # # # - - - - - |\r\n| - - - # # # # # # # # # - - - |\r\n| - - # # # # # # # # # # # - - |\r\n| - - # # # # # # # # # # # - - |\r\n| - # # # # # # # # # # # # # - |\r\n| - # # # # # # # # # # # # # - |\r\n| - # # # # # # # # # # # # # - |\r\n| - # # # # # # # # # # # # # - |\r\n| - # # # # # # # # # # # # # - |\r\n| - - # # # # # # # # # # # - - |\r\n| - - # # # # # # # # # # # - - |\r\n| - - - # # # # # # # # # - - - |\r\n| - - - - - # # # # # - - - - - |\r\n| - - - - - - - - - - - - - - - |\r\n+-------------------------------+<\/pre>\n<\/blockquote>\n
Remove the space separator, play with ratio, apply your own formula, and generate SQL-ascii graphics!<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[21],"class_list":["post-1097","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-hH","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1097","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=1097"}],"version-history":[{"count":15,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1097\/revisions"}],"predecessor-version":[{"id":1172,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1097\/revisions\/1172"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=1097"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=1097"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=1097"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}