mycheckpoint does not actually call on Google to do the chart rendering, but invokes its own JavaScript code to visualize the URL locally.

Here are some downsides for using Google charts:

The URL cannot be as long as you like. 2048 characters is an upper bound you'll want to keep behind. [Google charts POST method calls are available, which leads to 16K equivalent of URL length -- this is still not too helpful due to the nature of POST calls]

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.

Google charts are not too friendly. Taking the ticks and grids example from above, there really shouldn'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.

There is no support for time-series. One must translate time as x-axis values.

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.

Anyway, let's build a very simple chart. Since I will not cover everything in this post, we make for some relaxed conditions.

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 mycheckpoint I do that. In this blog post, we skip it.

Axis boundaries

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.

We will be using views in this solution, and here is the first view:

CREATE OR REPLACE VIEW
chart_data_minmax
AS
SELECT MIN(data) AS min_value, MAX(data) AS max_value
FROM
chart_data
;

In our example we will just settle with the min/max values. In real worlds we may:

Data values

Next, we have some 244 rows of data. Data is double. Can we be certain this all fits within 2048 characters? What if we had more rows?

Alas, we must make a trade-off. We will sacrifice accuracy in favor of a short URL. We will use simple encoding for our data. This means just one character per value, encoded A-Za-z0-9. This means just 62 distinct values.

So we must translate each data value into the range 0..61, and then into a character. First step is:

3 Comments to "Generating Google line charts with SQL, part I"

[...] Generating Google line charts with SQL, part I | code.openark.org 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 mycheckpoint I do that. [...]