Entrance

This chart tells a story, doesn’t it?

The approximate value for Lear Jet CO2 emissions is thanks to a calculation on http://benzzo.co.tv/learjet-fuel-consumption and is based on values from the DOE site http://www.eia.doe.gov/ Here is the Entrance script for producing the chart:


    plot rotatedbarchart
      skyblue bar,
      all axislabels,
      datalabels right
    with
      no collar
      no outlines
      baroverlap 0
      barwidth .85
      barseparation 5
      no sides
      no xaxis
      no labels x
      no ticks x
      scale x 0 1800 600
      frame insets 175 40 85 0
    select
      `grams_per_km`,name, concat(grams_per_km, ' g/km')
      from co2_emissions
    union
    select
      1766, 'Lear Jet', concat(1766, ' g/km')
      order by grams_per_km
;


Finally, here’s sample data for the various cars:
“grams_per_km”,”name”
“383″,”Escalade”
“346″,”Hummer”
“324″,”Corvette”
“268″,”Escalade Hybrid”
“198″,”Mercedes Benz clc class”
“140″,”BMW Series 1″
“167″,”Subaru Forester”
“148″,”Accord”
“119″,”Volvo v70″
“119″,”Ford Fusion”
“116″,”Ford Fiesta”
“104″,”Toyota Prius”
“101″,”Honda Insight”

The CDC site, http://www.cdc.gov/flu/weekly/, has a chart I thought could use a make-over, and this is the result. The Entrance version is easier to read, and titles on the two vertical axes don’t collide with the axis labels.

Reducing chart clutter makes the shape of the H1N1 counts clearer:

The script for the top chart is here and the script for the bottom one is here

I started the scripts with Charts | Make a stacked bar chart… and then tuned up a few things. As you can see, the Tango Desktop colors LIGHT SKYBLUE and LIGHT TANGOORANGE work well together.

Finally, here is the CSV data (which can be imported using Entrance Tools | Import file or URL..)

Here is a sampler of line styles supported by Entrance version 1.7.10:

plot
  black dashed line,   -- default
  black dotted line,
  black dashdotted line,
  black solid line,
  axislabels
  with
    page 0 0 500 400
    gridlines
    comment " "
    comment left "Default Line Thickness"
select Jan, Jan + 30, Jan + 60, jan+90, year  from cpi_orig
  where year < 2007;

plot
  black thin dashed line,
  black thin dotted line,
  black thin dashdotted line,
  black thin solid line,
  axislabels
  with
    page 0 0 500 400
    gridlines
    comment " "
    comment left "THIN Lines"
select Jan, Jan + 30, Jan + 60, jan+90, year  from cpi_orig
  where year < 2007;

plot
  black thick dashed line,
  black thick dotted line,
  black thick dashdotted line,
  black thick solid line,
  axislabels
  with
    page 0 0 500 400
    gridlines
    comment " "
    comment left "THICK Lines"
select Jan, Jan + 30, Jan + 60, jan+90, year  from cpi_orig
  where year < 2007;

plot
  black very thick dashed line,
  black very thick dotted line,
  black very thick dashdotted line,
  black very thick solid line,
  axislabels
  with
    page 0 0 500 400
    gridlines
    comment " "
    comment left "VERY THICK Lines"
select Jan, Jan + 30, Jan + 60, jan+90, year  from cpi_orig
  where year < 2007;


  plot bitmapchart
      lat, lon, datalabels below
  with
      pagebitmap gulf_big.png

      no yaxis
      no xaxis
      no sides
  select
      28.72, -88.36, 'Deepwater Horizon';

This Entrance map plots the location of Deepwater Horizon (latitude 28.72, longitude -88.36). The PNG file I used for the background is here. Its left edge s longitude -100, right -75. The top edge of the map is latitude 15, bottom 32.

To make maps like this, you can use the Wood Hole MapIt server.   Use Entrance PAGEBITMAP to specify the background map and FRAME INSETS, SCALE X, and SCALE Y set it up its scale. Using PAGEBITMAP you can overlay any of the Entrance charts on a background image this way.

Use Entrance FRAME when you know the scale for part of \the background image. Here’s a NOAA map of the disaster site with an Entrance plot layered on top:


  plot bitmapchart
      lat, lon, datalabels
    with
      pagebitmap noaa_gulf.png
      foreground black
      background white
      fontsize 18

To figure out the FRAME coordinates, start by drawing a chart with the map as the PAGEBITMAP. Then drag out a box somewhere where it will be easy to assign (lat, lon) coordnates (in this case, between reference lines). When the Entrance popup appears select “Get Box Info…” and you can determine the pixel coordinates to use for the new FRAME.

Entrance Version 1.7.4 is now available for download from the main site. This version has an exciting new feature that lets you “layer” charts to build composite images like the one above.

Use SEND TO TEMPORARY to save a chart image to a temporary buffer, and use PAGEBITMAP TEMPORARY to use the buffer as a background image. The complete script for the image above is here. In outline form, it looks like this:

  plot earthchart
      lat, lon
      with
        send to temporary
        ... more stuff ...

  plot rotatedbarchart
     axislabels, light red bar, datalabels right
    with
      pagebitmap temporary
      frame 105 275 220 425
      ... more stuff ...

FRAME takes pixel coordinates. (0,0) corresponds to the upper left hand corner of the chart page.

Multiple charts can be chained in the same way. Here is an example where six charts are drawn on a single image as a 2×3 matrix. Image size is determined by the first chart, which in this case is set to be 1000 by 500 pixels using the PAGE keyword:

plot
  red line
  page  0 0 1000 500
  frame 50 50 150 150
  send to temporary
  title "January CPI"
select
  jan
  from cpi;

plot
  red line
  page  0 0 1000 500
  frame 200 50 300 150
  pagebitmap temporary
  send to temporary
  title "February CPI"
select
  feb
  from cpi;

plot
  red line
  page  0 0 1000 500
  frame 350 50 450 150
  pagebitmap temporary
  send to temporary
  title "March CPI"
select
  mar
  from cpi;

plot
  red line
  frame 50 200 150 300
  pagebitmap temporary
  send to temporary
  title "April CPI"
select
  apr
  from cpi;

plot
  red line
  page  0 0 1000 500
  frame 200 200 300 300
  pagebitmap temporary
  send to temporary
  title "May CPI"
select
  may
  from cpi;

plot
  red line
  page  0 0 1000 500
  frame 350 200 450 300
  pagebitmap temporary
  title "June CPI"
select
  june
  from cpi;

In this note I’ll show how to use the color palettes in “slide:ology” by Nancy Duarte (those of us who spoke at the MySQl Conference received it as a nice gift from O’Reilly).

Step 1 is to download Entrance Community edition from our web site: http://dbentrance.com/. You need JRE 1.6 or better and access to a MySQL server to run it. Simply unzip the download and start the program with “java -jar entrance.jar”.

To make a chart, click “New” and enter a PLOT command:

   plot
       axislabels, red bar, blue bar, green bar
   select "label", 1, 2, 3;

The SELECT part of the command can be almost any MySQL SELECT statement. (e.g. unfortunately you can’t change delimiters yet).

Click “Run” and you have the chart. Now to replace those eye-scorching colors with something better from the book. Take a look at the section “Using the Power of Color” on pages 130-131 and you will find a collection of RGB values for color palettes. Any of these can be used in Entrance. For example, here is “Earthy”:

   -- Earthy palette from slide:ology
   plot
      axislabels, rgb(72,107,28) bar, rgb(101,141,43) bar,
            rgb(144,193,62) bar
   select "label", 1, 2, 3;

Once you have entered a palette you can copy and paste it as text to other scripts. Be careful not to put any spaces in the rgb() values or Entrance will complain, and be careful that the chart series you list in the “plot part” match up 1-to-1 with columns in the “select part”.

Now you can do things like adding titles, legends, gridlines and so on using the syntax described here: http://dbentrance.com/newdocs/plotsyntax.html. You can also search this blog for examples, eg. try “bar chart”.

This is an Entrance chart on an iPhone. It was generated by a Groovy script using the Entrance Java API to plot data from a RAWS weather station.

The script takes advantage of certain Apple iPhone CSS extensions and Joe Hewitt’s iUI framework to make the page behave a lot like like a native iPhone application. If you have an iPhone, you can see the result here here

Groovy and the Entrance API
Groovy is a scripting language for the Java platform. To use the Entrance Java API from Groovy scripts drop two jars, ‘entrance.jar’ and ‘cotta.jar’, into the Groovy ‘lib’ directory. If you are running Groovy from the command line, put them in:

    (your home dir)/.groovy/lib

To use the Entrance API with the Groovy servlet, put them in:

    (your webapps)/groovy/WEB-INF/lib


Scripts you write using the Entrance desktop application can also be run from the Entrance API using EntrancePlot. These two methods take the path to an Entrance script file as an argument:

public static String generatePNG( Connection con,
    String pngFile, String scriptFile,
    boolean deleteIfExists)

public static String generateTempPNG(Connection con,
    String scriptFile)

and this method takes the script itself as a String argument:


public static String generatePNG(Connection con,
    String pngFile,
    String script);


Here’s the usual pattern for drawing Entrance charts with Groovy:

import com.dbentrance.entrance.EntrancePlot
import groovy.sql.Sql

... then refer to the PNG file in HTML output ...

Note that this technique will work for any database with a JDBC driver. The complete groovy script for the example above is here.

Sizing the charts
Use PAGE and FRAME in an Entrance script to size charts for mobile devices like the iPhone:

PAGE 0 0 (width) (height)
FRAME (x0) (y0) (x1) (y1)

Both take pixel coordinates. In pixel coordinates the upper left hand corner of the display is (0,0),. Coordinates increase going down and to the right.

Use PAGE to set the width and height of the output bitmap, and use FRAME to locate the chart on the page.
The first two coordinates of FRAME specify the upper left hand corner of the chart. The last two specify its lower right hand corner.

Once you have a PAGE and FRAME combination you like, you can copy and paste it into other scripts. These sizes worked well to for fitting two charts at a time in iUI on an iPhone screen:

    PAGE 0 0 275 150
    FRAME 46 5 245 125

To completely fill the iPhone display in landscape mode without iUI, you could use:

    PAGE 0 0 320 356


Fonts
Generally, the default font sizes should work for both the screen and iPhone output. If you find you need to tune font sizes, use FONT and TITLEFONT::

FONT (font family) (style) (size)
TITLEFONT (font family) (style) (size)

Both take a font family, which can be one of the platform-independent names: Serif, SansSerif, Monospaced, Dialog, and DialogInput, or another font family names supported by your platform. ‘Style’ can be PLAIN, BOLD, or ITALIC and the font ’size’ is set using point sizes. Generally speaking a font size that is legible on the display screen will also be legible on the iPhone.

Black Backgrounds
Use BACKGROUND, FOREGROUND and GRIDLINES to change colors:

    BACKGROUND BLACK
    FOREGROUND GRAY
    GRIDLINES GRAY


A Note About the iPad
You can also use Entrance API to generate charts for the iPad. The iPad display is 1024 x 768 and 132 pixels per inch. I haven’t tested it, but I think you will want to double Entrance font sizes that look good on the desktop display when sending them to iPad. More to come on the iPad.

The latest version of Entrance can be downloaded from the main page.

In an earlier post I showed how to import USGS earthquake data for the previous seven days into Entrance. Do that today, and you will see the Mendocino and Haiti Quakes:

This script adds a datetime column to the dataset::

ALTER TABLE earthquakes
    ADD COLUMN dt DATETIME;

UPDATE earthquakes
  SET dt =
    STR_TO_DATE(datetime, "%W, %M %e, %Y %H:%i:%S UTC");

and a Datetime-Y chart shows the severity of the aftershocks:that followed the main Haiti quake:

Click on the charts to see the scripts for making them.


PLOT RADARCHART
     DIRECTION, RED TRIANGLE,
     DIRECTION, BLUE VECTOR
   WITH
    NO LABELS Y
    FRAMECOLOR RGB(230,230,255)
    GRIDLINES
SELECT peak_wind_dir, peak_wind_speed, wind_dir,
   wind_speed
    FROM raws_bl
    WHERE dt > DATE_SUB(NOW(), INTERVAL 24 hour);


Entrance IDE 1.5 is available for download now, with support for drawing radar charts like the one above. (Radar charts are not supported by the current community version)

To plot data using a radar chart specify a DIRECTION column containing angles in degrees 0-360 and a data value column to use for plotting VECTOR, LINE, AREA or one of the marker types.

Radar charts use the settings for the y axis to determine distances from the center of the chart circle. The y scale can be autoscaled or set manually using
SCALE Y (min) (max) (increment)


Use the SCALE X to control the spacing between ticks and radial gridlines. Use [NO] LABELS Y to determine whether to label the scaling circles and and use [NO] LABELS X to determine whether to draw labels around the outer circle..

This example mixes an AREA type column with a LINE:


PLOT RADARCHART
     DIRECTION, GREEN AREA
     DIRECTION, BLUE LINE
    WITH
     NO LABELS Y
     FRAMECOLOR rgb(230,230,255)
     GRIDLINES
SELECT peak_wind_dir, peak_wind_speed, wind_dir,
      wind_speed
     FROM raws_bl
     WHERE dt > DATE_SUB(NOW(), INTERVAL 24 hour);

COLOR and SIZE OVERRIDES, LEGENDs and TITLES are supported, and RadarCharts support any of the line and marker types, TRIANGLE, BOX, and so on, as well as bubble effects.

Points in a radar chart can labelled using DATALABELs.   (DATALABELs are drawn next to points specified by the previous DIRECTION, VECTOR series)  

Use DATALABEL and VECTOR with radar charts to make gauges like these in a dashboard (click on a gauge to see the script that made it):

Some details:
In a radar chart, DIRECTION 0 is always straight up, and increases clockwise: e.g. 90 degrees is to the right, 270 degrees is to the left, and so on. To make different scales like the meters you see above, you’ll need to cheat: use NO LABELS X to turn off the auotmatic labels and make your own using a DATALABEL column.

See also: Entrance IDE 1.5 also supports DatetimeDirCharts, which plot direction changes on a datetime axis: see “Plotting Wind Direction and Speed” for the details.

Entrance home page: http://dbentrance.com/
Plot syntax: http://dbentrance.com/newdocs/plotsyntax.html

Copyright (c) 2009 dbEntranceSoftware, All Rights Reserved

I’ve been reading Stephen Few’s excellent book, “Now You See It”. Its a big book (300+) pages, packed with practical examples, and one that I think any Entrance user will find useful. The Pareto Chart above is loosely based on one of his examples (p. 196).

In a Pareto Chart, bars are drawn with a line showing accumulated sums. The bars are ordered for decreasing height, so the changes in the sums decrease as well. This makes it possible to read off the largest contributors to the total. In our example, you can see that the first three categories account for 88.9% of total sales.

One way to accumulate sums is to use a MySQL variable, like this:

    SET @sum=0;
    SELECT @sum := @sum+sales FROM t;

To re-express values in a table column as percentages, use a pattern like this:

    SET @total = (SELECT SUM(sales) FROM t);
    SELECT (sales / @total) * 100 FROM t;

Putting the pieces together, adding PLOT, and filling in a few details we have the Pareto Chart shown above:

    -- DROP TABLE IF EXISTS t;
    -- DROP TABLE IF EXISTS t2;

-- make the plot
    PLOT
      AXISLABELS, LIGHT GRAY BAR,
      RED LINE, BLACK FILLED CIRCLE, DATALABELS
    WITH
      SCALE Y 0 100 25
      FORMAT Y DECIMAL "#'%'"
      NO SIDES
      TITLE " "
      TITLE Y "Percentage of Sales"
      TITLE X "Item"
    SELECT
      what, sales_pct,
      accum_pct, accum_pct, CONCAT(FORMAT(accum_pct, 1), '% ')
      FROM t2
      ORDER BY sales DESC
      ;


Note: You have to be careful about updating user defined variables in this way, as discussed in “High Performance SQL” by Schartz et al. and on Roland Bouman’s blog. This script works because we haven’t made assumptions about the evaluation order of the columns.