Home page for Tod Landis
The New Entrance Charts
November 30, 2007
Shane and I are just about to publish Entrance Version 1.2. This new version supports additional chart types, new chart features, and an extension framework All of the chart types and the chart extension framework are now in the Entrance Community Version, which is GPLed open source.
The other big news is that Entrance will have a booth in the Dot Org Pavillion at the MySQL Show and Conference next year. More to come on that.
The new chart types are areas, "swaths", high/low/open/close charts, rotated bar charts, and "whiskers" charts. Following are descriptions of each of the types, together with examples. Click on a chart to see the script that made it.
Area Charts
AREA and SWATH are new series types for LineChart joining bars, markers labels, and so on. AREA fills in color under a line of series values, while SWATH fils in color between lines:
Entrance color shading helps you select colors for area charts. Generally the light colors work well for area fill, and adding a slightly darker line makes the chart more effective. For example, here is a VERY LIGHT BLUE AREA with a LIGHT BLUE LINE:
Use the shading modifiers, [VERY] DARK | LIGHT, to vary the shades of a color, and use transparency, [VERY] TRANSPARENT, when you want things under the area to show through.
Color overrides work with area charts:
Getting the override color where you want it can be a little tricky. Color overrides on areas (and lines or swathes) affect the area to the left of the corresponding tick mark. The first color highlight above was the result of this update
:
update CPI
set color = 'light blue' where year > 1956 and year <= 1962;
Swath Charts
To draw a SWATH you mark two SWATH columns in a PLOT command:
PLOT SWATH, SWATH
SELECT val1, val2 FROM data;
Each additional swath builds on the top of the last one. Iand in version 1.3, we will add NEWSWATH as a way of starting a new swath, so that one swath can be drawn in the middle of another) The result can look like this:
Swath charts have a number of interesting applications. You can use them to highlight a region near a line:
You can use them to show deviation from a baseline:
The script for that last plot shows how a self join can be used to compute average values for charts like this. Self joins are really useful for data analysis, but more about them in another piece. (The bad news: Unfortunately, Entrance wonZZ™t let you select data from a self join, yet. You have to INSERT the self join data into another table to do data exploration)
We used a baseline of y=0 for the above example, but you might be interested in variation about some other mean value.
Our fourth SWATH sample shows a way to vary the line width between data points
(Entrance canZZ™t yet make charts like MinarZZ™s "NapoleonZZ™s retreat" chart, the one made famous by Edward Tufte and Jon Bentley, When it has swaths for Scatter charts it will be getting close)
Finally, swath makes it easy to place bands of color behind other chart elements:
Whisker Charts
WHISKER charts show markers drawn on a vertical axis. You will usually want to use horizontal tick marks, which are the default::
The ticks in a whiskers chart can be labeled using the standard datalabel syntax, which has been enhanced for version 1.3. Now you can specify where to put the labels:
DATALABELS [LEFT | RIGHT | ABOVE | BELOW | CENTERED]
There is no filtering on the datalabel series, so you may see something like this:
The trick is to do add a temporary column and do the filtering yourself (click on the chart to see the details):
Whisker charts donZZ™t yet support interaction (eg. selecting points, painting etc) and they donZZ™t provide a y axis that can be changed by plot scripting. They do, however, support color overrides.
Rotated Bar Charts
Use rotated bars when you need many or long labels on the category axis:
This may be a good time to discuss the way labels are drawn on a category axis (the yaxis in a LineChart, or the xaxis on a RotatedBarChart). Years ago, I developed a fairly slick algorithm for avoiding collisions between labels on axes when I wrote the QuattroPro chart making engine. I had to avoid using that algorithm for this system because other people own it (!), but, in the end, I think I came up with something as good ,or maybe better.
Ok, here is how category labels work in Entrance. You can allow Entrance to make a "reasonable" choice for the number of labels to show on a category axis, or you can specify the number of labels you want:
ALL XLABELS means show them all
ONLY (n) XLABELS means show only (n) labels and skip the tohers
AUTO (the default) means make a reasonable choice
You can also draw labels near the data points themselves using DATALABELS. Data labels on a rotated bar chart work best if they are the left or right. Here is how they look on the left:
Both bar charts and rotated bar charts support the OUTLINES keyword which allows you to turn the outlines on bars on or off.
High/Low Charts
High/Low charts show HIGH, LOW, OPEN and CLOSE values in the usual way. Additionally, Entrance color overrides make it easy to show days when the close is greater than open in green, and other days red like this:
This example also shows how to shorten labels on a category axis using the MySQL function substring(str, start, len).
The Chart Extension Framework
(for Java Developers)
Java developers can now add new chart temporary, or customize existing types, using the Entrance chart extension framework. Over time I will be documenting the framework, but, for now, this note will show you how to write simple extensions. If you have questions, please contact us using the support email address: support (at) dbentrance (dot) com.
When Entrance encounters an extended chart type in a PLOT command it uses Java reflection to resolve the reference. For example this script:
PLOT entrancex.charts.MyLineChart
XLABELS, LINE
SELECT year, average FROM CPI;
causes Entrance to use the chart type ZZ˜entrancex.charts.MyLineChartZZ™. MyLineChart must be on the classpath and subclass Chart, or an error will result. An easy way to put things on the Entrance class path is to replace entrancex.jar, in the ZZ˜libZZ™ directory.
As an example, here is the Java source for the stubbed extension chart we provide in entrancex.jar:
package entrancex.charts;
import com.todlandis.charts.LineChart;
public class MyLineChart extends LineChart {
}
The Entrance script and Java class for MyLineChart shown above will generate a LineChart, complele with interaction behaviors, when the script is run as an Entrance SQL script.
To change the way series are drawn, while retaining the way LineChart sets up the axes, draws titles, and so on, you can override drawSeries()
public class MyLineChart extends LineChart {
public void drawSeries(Graphics2D g, int colorSeries, int sizeSeries)
// ... your stuff ...
}
}
You might try changing MyLineChart and rebuilding entrancex.jar. When you do, Wdigets will draw empty LineCharts with axes, titles and so on but no lines.
To make something more interesting, take a look at the drawSeries() method in LineChart.java. In LineChart, drawSeries() steps through the chart types the script has specified for columns in the current data set, which is provided by a Data object. Entrance Data objects work a lot like ResultSets: there are first() and next() methods, and you obtain data from them using getDouble() or getString().
To change the way one of the series types is drawn, you can override drawSeries() to call your draw method instead of the usual drawLineSeries(), drawAreaSeries(), and so on. Method drawAreaSeries() is a good starting point for your efforts, but it is short and fairly simple:
public void drawAreaSeries(Graphics2D g, int yColumn,
int colorColumn, int sizeColumn) {
if(!data.first())
return;
int firstX = xaxis.map(0);
int firstTopY = (int)yaxis.map(data.getDouble(yColumn));
boolean goodFirst = (!Double.isNaN(firstX) && !Double.isNaN(firstTopY));
int i= 1;
while(data.next()) {
int thisX = xaxis.map(i);
int thisTopY = (int)yaxis.map(data.getDouble(yColumn));
boolean goodVal = (!Double.isNaN(thisX) && !Double.isNaN(thisTopY));
setColorWithOverrides(g, colorColumn, yColumn);
Polygon poly = new Polygon();
poly.addPoint(firstX, getFrameBottom());
poly.addPoint(thisX, getFrameBottom());
poly.addPoint(thisX, thisTopY);
poly.addPoint(firstX, firstTopY);
g.drawPolygon(poly);
g.fill(poly);
firstX = thisX;
firstTopY = thisTopY;
i++;
}
}
You can see there are two mapping methods, xaxis.map() and yaxis.map() that map data values to points on the screen. The first tick mark on the x axis is the 0th, then comes 1, 2 and so on until data.next() returns false.
Null values in the database table are mapped to NaN "not a number" values;
The method setColorWithOverrides() finds the color set for the current column and then sets it unless there is a color override, in which case it uses the override value.
If you have extended a base chart type, you may want to turn off "data painting" and the its other interaction behaviors. You can do this by overriding the "supports" methods:
public boolean supportsZooming() {
return false;
}
public boolean supportsPanning() {
return false;
}
public boolean supportsReverseMapping() {
return false;
}
To go further, start by examining Chart, LineChart , RotatedBarChart, and ScatterChart in package com.todlandis.charts. These are the charts you will most likely want to extend. The PLOT syntax document may be helpful: http://dbentrance.com/docs/plotsyntax.html and there is a whitepaper about data analysis using Entrance and MySQL here.
Questions or comments? Email us at support (at) dbentrance (dot) com.
Earlier Posts:
Copyright (c) 2007 Tod Landis, All Rights Reserved
MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries.