Last modified on 9 December 2010, at 15:47

XQuery/Google Chart Sparkline

MotivationEdit

You want to create an easy-to-use specialized chart using a generic charting service such as Google Charts

MethodEdit

The GoogleChart API creates PNG-format charts from data passed in the URL line.

One use of the service would be to generate a Tufte sparkline. This script uses random data to generate a small sparkline-like graphic. With a bit more work, the additional features such as minimum, maximum and normal bands should be able to be added. A line chart (cht=lc) includes axes but these can be removed by using an undocumented feature in which the chart type is specified as lfi [1]

The script uses function overloading in XQuery which allows two functions to have the same name but different numbers of parameters. The more general function has parameters for the sequence of values and the min and max to be used in scaling the values. The second function (with the same name) accepts only the values and calculates the min and max from the data before calling the more general function to complete the task.


(:  This script illustrates the use of the GoogleChart API to generate a sparkline-like graphic
 
:)
declare option exist:serialize "method=html media-type=text/html";
 
declare function local:simple-encode(
         $vals as xs:decimal* ,
         $min as xs:decimal,
         $max as xs:decimal) 
     as xs:string {
(: encode the sequence of numbers as a string according to the simple encoding scheme.
    the data values are encoded in the characters A-Z,a-z,0-9 giving a range from 0 to 61
:)
  let $scale := 62.0 div ($max - $min) 
  let $simpleEncode := "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"
  return  
      string-join( for $x in $vals
      let $n := floor( ($x - $min) * $scale)
      return substring($simpleEncode,$n+1,1)
      ,"")
};
 
declare function local:simple-encode($vals as xs:decimal*) as xs:string {
(: compute the minimulm and maximum values, then call the more general  function to encode :)
  let $min := min($vals)
  let $max := max($vals)
  return local:simple-encode($vals,$min,$max)
};
 
declare function local:sparkline(
            $data as xs:decimal* ,
            $fontHeight as xs:integer, 
            $pointSize as xs:integer, 
            $label as xs:string )
        as element(span) {
(: create a span element containing the line chart of the data, the name of the data set 
   and the last data value
   fontHeight and pointSize are  defined in pixels 
:)
let $codeString := local:simple-encode($data)
let $width := count($data) * $pointSize
let $last := $data[last()]
let $title :=concat( "Graph of  ",$label, " data: ",count($data)," values, min ",min($data), " max ", max($data))
return
   <span>
          <img src="http://chart.apis.google.com/chart?chs={$width}x{$fontHeight}&amp;chd=s:{$codeString}&amp;cht=lfi"
alt="{$title}"  title="{$title}"
                    />
          <font style="font-size:{$fontHeight}px"> {$label}&#160;{$last}</font>
    </span>
 
};
 
(: generate some random data :)
let $data := for $i in (1 to 100) return floor(math:random() * 10)
return local:sparkline($data,15,1,"Random")

Random Sparkline