{"id":1468,"date":"2011-02-01T10:29:31","date_gmt":"2011-02-01T08:29:31","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1468"},"modified":"2011-03-03T09:39:51","modified_gmt":"2011-03-03T07:39:51","slug":"generating-google-line-charts-with-sql-part-i","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/generating-google-line-charts-with-sql-part-i","title":{"rendered":"Generating Google line charts with SQL, part I"},"content":{"rendered":"<p>In this series of posts I wish to show how <a href=\"http:\/\/code.google.com\/apis\/chart\/image_charts.html\">Google Charts<\/a> can be generated via SQL. We discuss the Google Charts limitations which must be challenged, and work towards a simple chart.<\/p>\n<p>I&#8217;m going to present the algorithm I use in <a href=\"http:\/\/code.openark.org\/forge\/mycheckpoint\">mycheckpoint<\/a>, a MySQL monitoring utility, which generates Google charts by raw data using views. An example of such chart follows:<\/p>\n<blockquote>\n<pre><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Sample Google Chart\" src=\"http:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=370x180&amp;chts=303030,12&amp;chtt=Latest+24+hours:+Nov+9,+05:50++-++Nov+10,+05:50&amp;chf=c,s,ffffff&amp;chdl=Rentals+rate:+custom_1_psec&amp;chdlp=b&amp;chco=ff8c00&amp;chd=s:GDGKGFLFGMJHRLMPPNULJRPLTOPRUMYPPVRNbQUSUSbSNWUOfSWTObVSUVWSVYVPbTPjfTbRTdXReUWhcTQRQZbTWYVYPaVZXdYYWPTabYUTbW99QLgLNIOIRNNMIKRJEHGFHGJGGFIFDFGDK&amp;chxt=x,y&amp;chxr=1,0,8.720000&amp;chxl=0:|+||08:00||+||12:00||+||16:00||+||20:00||+||00:00||+||04:00||&amp;chxs=0,505050,10,0,lt&amp;chg=4.17,25,1,2,0.69,0&amp;chxp=0,0.69,4.86,9.03,13.20,17.37,21.54,25.71,29.88,34.05,38.22,42.39,46.56,50.73,54.90,59.07,63.24,67.41,71.58,75.75,79.92,84.09,88.26,92.43,96.60&amp;tsstart=2010-11-09+05:50:00&amp;tsstep=600\" alt=\"\" width=\"370\" height=\"180\" \/>\r\nhttp:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=370x180&amp;chts=303030,12&amp;chtt=Latest+24+hours:+Nov+9,+05:50++-++Nov+10,+05:50&amp;chf=c,s,ffffff&amp;chdl=Rentals+rate:+custom_1_psec&amp;chdlp=b&amp;chco=ff8c00&amp;chd=s:GDGKGFLFGMJHRLMPPNULJRPLTOPRUMYPPVRNbQUSUSbSNWUOfSWTObVSUVWSVYVPbTPjfTbRTdXReUWhcTQRQZbTWYVYPaVZXdYYWPTabYUTbW99QLgLNIOIRNNMIKRJEHGFHGJGGFIFDFGDK&amp;chxt=x,y&amp;chxr=1,0,8.720000&amp;chxl=0:|+||08:00||+||12:00||+||16:00||+||20:00||+||00:00||+||04:00||&amp;chxs=0,505050,10,0,lt&amp;chg=4.17,25,1,2,0.69,0&amp;chxp=0,0.69,4.86,9.03,13.20,17.37,21.54,25.71,29.88,34.05,38.22,42.39,46.56,50.73,54.90,59.07,63.24,67.41,71.58,75.75,79.92,84.09,88.26,92.43,96.60&amp;tsstart=2010-11-09+05:50:00&amp;tsstep=600<\/pre>\n<\/blockquote>\n<p><em>mycheckpoint<\/em> does not actually call on Google to do the chart rendering, but invokes its own JavaScript code to visualize the URL locally.<\/p>\n<p>Here are some downsides for using Google charts:<\/p>\n<ul>\n<li>The URL cannot be as long as you like. 2048 characters is an upper bound you&#8217;ll want to keep behind. <em>[Google charts POST method calls are available, which leads to 16K equivalent of URL length &#8212; this is still not too helpful due to the nature of POST calls]<\/em><\/li>\n<li>Features are inconsistent. To specify label or tick positions, one must specify exact positions. To specify grid positions, one must supply with step, offset, etc. There are more such inconsistencies.<\/li>\n<li>Google charts are not too friendly. Taking the ticks and grids example from above, there really shouldn&#8217;t be a reason why grids would not be automatically generated according to ticks definitions. But we are required to specify positions for the ticks as well as for the grids.<\/li>\n<li>There is no support for time-series. One must translate time as x-axis values.<\/li>\n<li>Perhaps most intimidating to many people: to generate a Google chart, once must send data to Google. Which is the main reason I used local JavaScript rendering.<\/li>\n<\/ul>\n<p>Anyway, let&#8217;s build a very simple chart. Since I will not cover everything in this post, we make for some relaxed conditions.<!--more--><\/p>\n<h4>Sample data<\/h4>\n<p>Consider the chart_data table, below:<\/p>\n<blockquote>\n<pre>SELECT * FROM chart_data;\r\n+-----+---------------------+-------------------+\r\n| id\u00a0 | ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | data\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-----+---------------------+-------------------+\r\n|\u00a0\u00a0 1 | 2011-02-01 09:59:15 |\u00a0 1.99666833293656 |\r\n|\u00a0\u00a0 2 | 2011-02-01 09:59:15 |\u00a0 7.94677323180245 |\r\n|\u00a0\u00a0 3 | 2011-02-01 09:59:15 |\u00a0 17.7312123996804 |\r\n|\u00a0\u00a0 4 | 2011-02-01 09:59:15 |\u00a0\u00a0 31.153467384692 |\r\n|\u00a0\u00a0 5 | 2011-02-01 09:59:15 |\u00a0 47.9425538604203 |\r\n|\u00a0\u00a0 6 | 2011-02-01 09:59:15 |\u00a0 67.7570968074042 |\r\n|\u00a0\u00a0 7 | 2011-02-01 09:59:15 |\u00a0 90.1904762132767 |\r\n|\u00a0\u00a0 8 | 2011-02-01 09:59:15 |\u00a0 114.776974543924 |\r\n|\u00a0\u00a0 9 | 2011-02-01 09:59:15 |\u00a0 140.998843732947 |\r\n|\u00a0 10 | 2011-02-01 09:59:15 |\u00a0 168.294196961579 |\r\n|\u00a0 11 | 2011-02-01 09:59:15 |\u00a0 196.065619213516 |\r\n|\u00a0 12 | 2011-02-01 09:59:15 |\u00a0 223.689380632134 |\r\n|\u00a0 13 | 2011-02-01 09:59:15 |\u00a0\u00a0 250.52512820847 |\r\n...<\/pre>\n<\/blockquote>\n<p>Find complete SQL script in <a href=\"http:\/\/code.openark.org\/blog\/wp-content\/uploads\/2011\/02\/google_charts.sql_.txt\">google_charts.sql<\/a><\/p>\n<p>In the above table we assume the timestamp values are evenly distributed. This is because of the aforementioned fact that Google Charts do not support time-series. So, if the given timnestamps are not evenly distributed, or maybe some values are skipped, we must manually compensate for that. In <em>mycheckpoint<\/em> I do that. In this blog post, we skip it.<\/p>\n<h4>Axis boundaries<\/h4>\n<p>By default, Google Charts expect data to lie within the range 0..100. We need to reset the scale to match our minimum\/maximum values, and lay out our values within that range.<\/p>\n<p>We will be using views in this solution, and here is the first view:<\/p>\n<blockquote>\n<pre>CREATE OR REPLACE VIEW\r\n chart_data_minmax\r\nAS\r\n SELECT MIN(data) AS min_value, MAX(data) AS max_value\r\nFROM\r\n chart_data\r\n;<\/pre>\n<\/blockquote>\n<p>In our example we will just settle with the min\/max values. In real worlds we may:<\/p>\n<ul>\n<li>Choose <strong>0<\/strong> to be the lower value, even if the minimal value is positive<\/li>\n<li>Choose to round min-max values to a nearest power of <strong>10<\/strong>.<\/li>\n<\/ul>\n<p>The axis boundaries go under the following parameters:<\/p>\n<ul>\n<li><strong>chxt=x,y<\/strong> (we hard code bottom axis, left axis)<\/li>\n<li><strong>chxr=1,<em>min<\/em>,<em>max<\/em><\/strong> (with min, max as computed by the above)<\/li>\n<\/ul>\n<p>The following query produces these params:<\/p>\n<blockquote>\n<pre>SELECT CONCAT('&amp;chxt=x,y&amp;chxr=1,', ROUND(min_value, 1), ',', ROUND(max_value, 1)) AS minmax FROM chart_data_minmax;\r\n+---------------------------------+\r\n| minmax\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------------------------------+\r\n| &amp;chxt=x,y&amp;chxr=1,-4716.6,5340.0 |\r\n+---------------------------------+\r\n<\/pre>\n<\/blockquote>\n<h3>Data values<\/h3>\n<p>Next, we have some <strong>244<\/strong> rows of data. Data is double. Can we be certain this all fits within <strong>2048<\/strong> characters? What if we had more rows?<\/p>\n<p>Alas, we must make a trade-off. We will sacrifice accuracy in favor of a short URL. We will use <a href=\"http:\/\/code.google.com\/apis\/chart\/docs\/data_formats.html#simple\">simple encoding<\/a> for our data. This means just one character per value, encoded <strong>A-Za-z0-9<\/strong>. This means just <strong>62<\/strong> distinct values.<\/p>\n<p>So we must translate each data value into the range 0..61, and then into a character. First step is:<\/p>\n<blockquote>\n<pre>SELECT (data - min_value)\/(max_value - min_value) AS range_01 FROM chart_data, chart_data_minmax;\r\n+---------------------+\r\n| range_01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------------------+\r\n|\u00a0\u00a0 0.469204617275163 |\r\n|\u00a0\u00a0 0.469796281944551 |\r\n|\u00a0\u00a0 0.470769223945374 |\r\n|\u00a0\u00a0 0.472103901937297 |\r\n|\u00a0\u00a0 0.473773369865649 |\r\n|\u00a0\u00a0 0.475743682188048 |\r\n|\u00a0\u00a0 0.477974405550919 |\r\n...\r\n<\/pre>\n<\/blockquote>\n<p>The above translates the data into the 0..1 range. Next is to simply multiply by 61:<\/p>\n<blockquote>\n<pre>SELECT 61*(data - min_value)\/(max_value - min_value) AS range_0_61 FROM chart_data, chart_data_minmax;\r\n+-------------------+\r\n| range_0_61\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-------------------+\r\n|\u00a0 28.6214816537849 |\r\n|\u00a0 28.6575731986176 |\r\n|\u00a0 28.7169226606678 |\r\n|\u00a0 28.7983380181751 |\r\n|\u00a0 28.9001755618046 |\r\n|\u00a0 29.0203646134709 |\r\n|\u00a0 29.1564387386061 |\r\n|\u00a0 29.3055730348683 |\r\n|\u00a0 29.4646269983728 |\r\n|\u00a0 29.6301923928522 |\r\n...<\/pre>\n<\/blockquote>\n<p>To translate into a <em>simple format<\/em> character we parse a constant string:<\/p>\n<blockquote>\n<pre>SELECT\r\n  SUBSTRING(\r\n    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',\r\n    1+61*(data - min_value)\/(max_value - min_value),\r\n    1\r\n  ) AS c FROM chart_data, chart_data_minmax;\r\n+------+\r\n| c\u00a0\u00a0\u00a0 |\r\n+------+\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| e\u00a0\u00a0\u00a0 |\r\n| e\u00a0\u00a0\u00a0 |\r\n...<\/pre>\n<\/blockquote>\n<p>Oh, and we should handle NULLs, as well, so:<\/p>\n<blockquote>\n<pre>SELECT\r\n  IF(\r\n    data IS NULL,\r\n    '_',\r\n    SUBSTRING(\r\n      'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',\r\n      1+61*(data - min_value)\/(max_value - min_value),\r\n      1\r\n    )\r\n  ) AS c FROM chart_data, chart_data_minmax;\r\n<\/pre>\n<pre>+------+\r\n| c\u00a0\u00a0\u00a0 |\r\n+------+\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| d\u00a0\u00a0\u00a0 |\r\n| e\u00a0\u00a0\u00a0 |\r\n| e\u00a0\u00a0\u00a0 |\r\n...<\/pre>\n<\/blockquote>\n<p>And, to present it as a char sequence, we use the <strong>chd<\/strong> parameter and call <strong>GROUP_CONCAT<\/strong> to the rescue:<\/p>\n<blockquote>\n<pre>SELECT\r\n  CONCAT('&amp;chd=s:',\r\n    GROUP_CONCAT(\r\n      IF(\r\n        data IS NULL,\r\n        '_',\r\n        SUBSTRING(\r\n          'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',\r\n          1+61*(data - min_value)\/(max_value - min_value),\r\n          1\r\n        )\r\n      )\r\n      SEPARATOR ''\r\n    )\r\n  ) AS c FROM chart_data, chart_data_minmax\r\n\\G\r\n*************************** 1. row ***************************\r\nc: &amp;chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL<\/pre>\n<\/blockquote>\n<h4>Wrap up<\/h4>\n<p>Combining the above two queries, with some URL necessities, we get:<\/p>\n<blockquote>\n<pre>SELECT\r\n  CONCAT(\r\n    'http:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=400x200&amp;chtt=SQL%20chart&amp;chxt=x,y&amp;chxr=1,',\r\n    ROUND(min_value, 1), ',',\r\n    ROUND(max_value, 1),\r\n    '&amp;chd=s:',\r\n    GROUP_CONCAT(\r\n      IF(\r\n        data IS NULL,\r\n        '_',\r\n        SUBSTRING(\r\n          'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',\r\n          1+61*(data - min_value)\/(max_value - min_value),\r\n          1\r\n        )\r\n      )\r\n      SEPARATOR ''\r\n    )\r\n  ) FROM chart_data, chart_data_minmax\r\n;\r\n<\/pre>\n<\/blockquote>\n<p>And get:<\/p>\n<blockquote>\n<pre><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" title=\"Sample SQL Google Chart\" src=\"http:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=400x200&amp;chtt=SQL%20chart&amp;chxt=x,y&amp;chxr=1,-4716.6,5340.0&amp;chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL\" alt=\"\" width=\"400\" height=\"200\" \/>\r\nhttp:\/\/chart.apis.google.com\/chart?cht=lc&chs=400x200&chtt=SQL%20chart&chxt=x,y&chxr=1,-4716.6,5340.0&chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL\r\n<\/pre>\n<\/blockquote>\n<p>The above chart depicts our visualized data. Alas, x-axis ranges are wrong; no ticks nor grids are yet presented.<\/p>\n<p>Granularity is not the best, either: the different <strong>62<\/strong> values are spread across a <strong>200<\/strong> pixel high chart, making for a <strong>3<\/strong> pixel distinction between two values, evident on the left area of the chart.<\/p>\n<p>Continues on <a href=\"http:\/\/code.openark.org\/blog\/mysql\/generating-google-line-charts-with-sql-part-ii\">Generating Google line charts with SQL, part II<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this series of posts I wish to show how Google Charts can be generated via SQL. We discuss the Google Charts limitations which must be challenged, and work towards a simple chart. I&#8217;m going to present the algorithm I use in mycheckpoint, a MySQL monitoring utility, which generates Google charts by raw data using [&hellip;]<\/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":[41,49,21],"class_list":["post-1468","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-graphs","tag-mycheckpoint","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-nG","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1468","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=1468"}],"version-history":[{"count":31,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1468\/revisions"}],"predecessor-version":[{"id":3341,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1468\/revisions\/3341"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=1468"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=1468"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=1468"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}