Google GeoChart, JSON and Perl

The Google API GeoChart Map (https://developers.google.com/chart/interactive/docs/gallery/geochart) is pretty nice widget to generate nice maps based on certain values. It has quite a lot of features and it is very easy to use.

Before we look at the Google API for GeoChart, let’s first set up a script which will get data out of a database and return it in a JSON formatted object.
In this example we will use Perl and three Perl modules:

• DBI
• JSON
• CGI

When converting database values to a JSON object (or text string), it is very important that all data is properly type-casted.
In the following example you will see that we do:

$_->[1] = int($_->[1]) foreach @$data;

This snippet will actually make our INTEGER value into a real integer. The DBI module had returned it as a normal string (that’s just how DBI works).

#!/usr/bin/perl
use strict; use warnings;
use DBI;
use JSON;
use CGI;

my $q = CGI->new;
my $db = DBI->connect("dbi:mysql:host=localhost;db=testdb", 'testuser', 'xxxsecret');

my $sql = "SELECT country_name, count(id) as total from geo_data group by country_name";
my $data = $db->selectall_arrayref($sql);
$_->[1] = int($_->[1]) foreach @$data;
unshift(@$data, ['Country', 'Attacks']);

print $q->header('application/json');
my $json = encode_json($data);
print $json;

The above Perl script will be saved as ”get_countries_data.pl”.

In the below Javascript example, we will use the Google API for the GeoChart Map and jQuery for making the AJAX call to our Perl script. Since the Perl script already provides the data in a JSON format, we do not need to convert or parse it.
Furthermore the Javascript code is pretty straightforward and based on the example found at https://developers.google.com/chart/interactive/docs/gallery/geochart, except for the AJAX part.

    google.load('visualization', '1', {packages: ['geochart']});
    google.setOnLoadCallback(drawVisualization);

    function drawVisualization() {
        var options = {
            height: '500',
            width: '1200',
            colorAxis: {minValue: 0,  colors: ['#FFC26B', 
                                               '#FFAF3B', 
                                               '#FF9700', 
                                               '#C1852F', 
                                               '#A86400']},
            datalessRegionColor: '#FAFAFA',
            backgroundColor: '#F4EFE7',
        };
    
        $.ajax({
            type: 'POST',
            url: "get_countries_data.pl",
            dataType: "json",
            async: false,
            success: function(json_data) {
                var data = google.visualization.arrayToDataTable(json_data);
                var chart = new google.visualization.GeoChart(
                                   document.getElementById('visualization') );

                chart.draw(data, options);
            }
        });
    }

An example of this setup can be found at http://www.moretrix.com/~insaniac/map/map.pl