Generating Graphs and Charts from Database Queries using SVG

Graciela Gonzalez, Gaurav Dalal
Sam Houston State University
Department of Computer Science
Huntsville, TX, 77341 U.S.A.
[csc_ghg, dalal]@shsu.edu

Data by itself is worthless. It is the information that can be derived from it what is of value. And some information is better conveyed by the use of bar, line or pie charts, like the results of surveys, the month-by-month sales of a particular retail store, or the oscillations of stock market prices.The information contained in such charts, if it were presented in a "spreadsheet" fashion, will not show the relationship among the different pieces of information as clearly as the chart itself. Similarly, a company's organization or the intermingled air routes of an airline's scheduled flights will be dull and very difficult to understand if not presented as a graph.

We present a web service that allows end-users to specify a database query and visualization specifications as charts and graphs data visualizations using SQL+D as a front-end[1], SQL+D is an extension of SQL that allows specification of multimedia presentations from the result set. Two special display classes of SQL+D are graphs and charts. The user specifications are used to obtain an adequate answer set and format it according to the given specifications. For graphs and charts, the output is a standard SVG [3] file embedded in a SMIL that can be stored or displayed using any SVG-enabled browser.

Consider for example a database to keep statistical information about the total sales per month, and want to display it as a barchart, with SALES (month, year, total). To see a chart with the appropriate information for 2003, we might submit Query 1. The resulting display is shown in Figure 1.

SELECT * , STDDEV(total) AS deviation
FROM SALES
WHERE year = "2003"
DISPLAY PANEL main
WITH (month, total) AS barchart ON main.Center
(month, deviation) AS linechart ON main.Center (overlay)
"Sales Throughout the Year" AS text ON main.North

Query 1: Display a categorized bar chart of monthly sales

Graph
Figure 1: SVG file generated from Query 1, embedded into a SMIL document.
  1. Baral C., Gonzalez G., Son. T., "Design and implementation of display specifications for multimedia answers" Proceedings of the 14th International Conference on Data Engineering, IEEE, February 1998.
  2. World Wide Web Consortium on SVG: http://www.w3.org/Graphics/SVG/Overview.htm8
  3. World Wide Web Consortium on SMILhttp://www.w3.org/TR/smil20.