Gauge charts in SQL or How to produce the world's ugliest speedometers

I may be at JavaOne 2007, the important work still has to go on. Hence the latest in a series of SQL powered charts and graphs: the Gauge or Speedometer. Using nifty graphical tricks and the SQL*Plus 3D Pack, I managed to create a query that can render visually compelling, not very interactive charts, such as the one below, showing the Salary Sum for the three departments in the EMP table:

Gauge charts in SQL or How to produce the world's ugliest speedometers sqlgauge1

 

Other valuable queries-turned-charts include the number of objects per object type in our database and of course the salary per employee in the EMP table. The possibilities are endless with this new innovative high end graphical solution.

 

See the stunning and grossly unfair distribution of wealth in Department 20:....

Gauge charts in SQL or How to produce the world's ugliest speedometers sqlgauge2 

And be illuminated with the wisdom of the number of objects per object type in my SCOTT schema – terribly exciting of course.

Gauge charts in SQL or How to produce the world's ugliest speedometers sqlgauge3

The only differences between the three queries used to produce these three pictures, are in the top inline view, called chart_query (the Model in terms of MVC for this query):

with chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category
( select ename category
, 'Employee '||empno||' '||ename category_label
, sal value
from emp e
where deptno = 20
)

with chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category
( select deptno category
, 'Department '||deptno||' '||(select dname from dept d where d.deptno = e.deptno) category_label
, sum(sal) value
from emp e
group
by deptno
)


with chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category
( select object_type category
, initcap(object_type) category_label
, count(*) value
from user_objects
group
by object_type
)
 

Well, that is not entirely true: there is a second query that is used to set the layout properties for the gauge, called gauge_dimensions: 

, gauge_dimensions as
( select 30 radius -- the radius of the gauge - can be changed
, 6 marker_count -- the number of markers for the gauge
, 0 low_limit
, 60 high_limit
from dual
)
 

We can set the radius of gauge, the upper and lower limit (or derive those from the actual values in the model-query) and specify the number of markers on the gauge.

The remainder of the SQL query is generic across all gauge-queries.

Is it pure SQL? Well… almost. To merge together the dials, I make use of a small PL/SQL function:

create or replace 
function merge_string_table_to_string
( p_tbl in string_table_collection
) return varchar2
is
l_str varchar2(4000):='';
l_pos number(4);
begin
-- the string merge is for the * character
-- that means that starting with p_tbl(1), all * from the other strings are applied to it
l_str:= p_tbl(1);
for i in least(2,p_tbl.count)..p_tbl.count loop
-- find all * in p_tbl(i) and transfer them to l_str
for j in 1..length(p_tbl(i)) loop
if substr(p_tbl(i),j,1) <> ' '
then
l_str:= substr(l_str, 1, j -1 )||substr(p_tbl(i),j,1)||substr(l_str, j +1 );
end if;
end loop;
end loop;
return l_str;
end;
 

This function is based on a Nested Table type, that also needs to be setup:

create or replace type string_table_collection
as table of varchar2(4000)
/
 

For the rest, it is only SQL. And not even very spectacular SQL at that, apart perhaps from the use of a single Analytical Function to determine the position for the legend-marker in the chart (somewhere in the dial we need an indication of the category that particular dial represents).

Resources 

Download the script behind this article: Gauge.txt.

Note: using the Oracle ADF Faces Rich Components, we can render the first chart of this article (for Department 30) as:

Gauge charts in SQL or How to produce the world's ugliest speedometers richgauge dept30 

8 Comments

  1. Lucas Jellema May 14, 2007
  2. Peter K May 12, 2007
  3. Niall Litchfield May 11, 2007
  4. SwitchBL8 May 11, 2007
  5. Laurent Schneider May 11, 2007
  6. Luc Bors May 11, 2007
  7. opensourcereader May 11, 2007
  8. Jan May 10, 2007