Prologue
Few weeks ago we’ve decided to make our translator directory a little bit cooler. Google Chart API was the tool for the job. Reading few posts about it before we’ve realized that they have really cool ability to create map-based charts. Generated charts look pretty and they can also be useful.
Problem
So we wanted to create a map showing where our translators are from and also showing how many translators we have in a certain country. This map lives now in our translator directory and the translators table less boring.
Implementation
Our application is written in Ruby using Ruby on Rails framework. We have UserProfile model that contains all relevant translators data. So we just need to a add a method to collect per country statistics from the database. I didn’t think long and used very basic ActiveRecord call to run arbitrary query to get it done.
class UserProfile < ActiveRecord
........
def self.get_country_stats
self.connection.select_all("select count(1) as cnt, country from user_profiles group by country")
end
........
end
This simply returns an array of result set rows. Now in controller we can use that method together with some pretty simple and not very readable code to generate parameters we pass to Google in order to generate our chart. We will need to generate three important parameters to be used in the view: chld=<country or state codes> chd=<color levels> chds=<minimum, maximum levels> chld and chd represent two arrays with the same number of items so that corresponding items match. Items of chld are ISO country codes and we have a very handy class CountryLanguageCodes to generate them by country name. Items of chld are just counts of UserProfiles per country. We have to provide chds parameter with maximum and minimum counts in order to let Google to normalize the colors.
class PeopleController < ActiveRecord
.........
def index
..........
@chld = ""
@chd_arr = []
@chmax = 0
@chmin = 10000000
UserProfile.get_country_stats.each do |row|
code = CountryLanguageCodes.code_by_country(row['country'])
next unless code
@chld << CountryLanguageCodes.code_by_country(row['country'])
cc = row['cnt'].to_i
@chd_arr << cc @chmax = cc if cc > @chmax
@chmin = cc if cc < @chmin
end
@chd = "t:" + @chd_arr.join(',')
@chds = "#{@chmin},#{@chmax}"
........
end
............
end
Hard part is over and now we can simply pass generated parameter names to Google to generate our map.
<img src="http://chart.apis.google.com/chart?cht=t&chs=440x220&chds=&chd=&chco=FFFFFF,FF0000,FFFF00,00FF00&chld=&chtm=world&chf=bg,s,EAF7FE" width="440" height="220" alt="Country graph" border="0" />
Caching
Certainly using ‘group by’ query can affect your system performance . It may be pretty heavy on the database if you have many users. So I would recommend to use Rails fragment caching if performance becomes a problem. We didn’t have to do any of that yet. In our case we don’t see much impact on the system performance. MySQL does pretty good job caching count query results and the parameter generation code isn’t that slow. Vincent Pearson – ProWSS Engineer April 28, 2009
Links
- Google Chart API – http://code.google.com/apis/chart/
- ISO Country Codes – http://www.iso.org/iso/english_country_names_and_code_elements
- 99translations – http://99translations.com
Tags: chart, google api, i18n, maps
