Google Charts API

07 Dec 2007

I just noticed that google has published another cool API, this time for charts. It makes very fine looking charts, and has a simple API. Getting the values into the chart is very strange at first, but the constraints that you have on the values actually help in that your chart ends up looking better.

Here's how to use it. First we need some values to chart. A couple of weeks ago I wrote about how to extract some data out of subversion and put in into postgres. I still have those values around, so I for values I can use the number of commits per month in the rails project since it began. If you want to know more about that, read svn tricks. The strange part, as I mentioned before, is that you don't pass numbers to the chart. You pass some kind of encoding which has either 62, 1000, or 4096 values. For many uses, the first one is good enough.

Ok, so first we need to generate the encoding for the values. The Google maps API webpage shows a sample javascript function to generate that. I translated that to ruby. I think it's more or less correct, and goes like this:

    def simpleEncode(values, maxValue)
      simpleEncoding = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';

      chartData = ['s:'];
      values.each do |v|
        val = Float(v)
        if (!val.nan? && v >= 0)   
          chartData.push(simpleEncoding[( (simpleEncoding.length-1) * val / maxValue).round, 1]);
      return chartData.join('');

Right, so now how do we get our values to this function. As mentioned before, our values would come out of the database like this:

    % psql -d work_activity -t -c "select date_trunc('month', date), count(*) from svn_activity group by 1 order by 1;"
     2004-11-01 00:00:00 |    30
     2004-12-01 00:00:00 |   259
     2005-01-01 00:00:00 |   218

In order to generate the graph, we need to stuff those values into the funny URL google wants. So we hack a little chunk of code to parse the stuff.

    a = STDIN.read
    values = []
    dates = []
    a.each do |line|
      next if line.chomp.length < 1
      date, value = line.split('|')
      value.chomp!; date.chomp! #get rid of all the chars
      values << value.to_i 
      dates << date.split[0].split('-')[1] # just take month, legend too busy otherwise

That gives us two arrays, one with the values, and one with the dates. Notice that I chopped off the year and the day off the dates. Before I did that, the labels on the X axis were so busy with text that you could not even make sense out of any of them.

Now that we have our values, we make the img tag with all the funny values in it. We also decide the size of the chart and the 'alt' value here. There are plenty of other things that you can modify, but this does most of what you want:

    image_str =<<-START
    <img src="http://chart.apis.google.com/chart?
    &amp;chd=#{simpleEncode(values, values.max)}
    alt="Rails Subversion Commits" />
    puts image_str

Now we put all those bits of code above into a single file, and call it makegoogleurl.rb. We run the query as before, and pipe it to our nifty new program like this:

    psql -d work_activity -t -c "select date_trunc('month', date), count(*) from svn_activity group by 1 order by 1"| ruby make_google_url.rb 

This is our result:

    <img src="http://chart.apis.google.com/chart?
    alt="Rails Subversion Commits" />

And if you put that in your page, it looks like:

Rails Subversion Commits