Entrance

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.

Benford’s Law predicts the frequency with which each of the digits 1-9 will appear as the first digit in values taken from a real world data set. Benford’s law holds true often enough that forensic accountants suspect financial fraud when it doesn’t. More about Benford’s Law on Wikipedia. and in “The Drunkard’s Walk” by Leonard Mlodinow.

This will make a table of the Benford’s percentages:

    CREATE TABLE benfordtemp (
        d INT,
        p DOUBLE
    );

    INSERT INTO benfordtemp
        VALUES
        (1, 30.1),
        (2, 17.6),
        (3, 12.5),
        (4, 9.7),
        (5, 7.9),
        (6, 6.7),
        (7, 5.8),
        (8, 5.1),
        (9, 4.6);


This is how to calculate percentages for the first digit counts in a particular data set, in this case country populations:

    CREATE TABLE valtemp (
        k int
    );

    SELECT
        k, (COUNT(*) / @total) * 100 ,
        (SELECT p FROM benfordtemp WHERE d=k)
        FROM valtemp
        GROUP BY k
        HAVING k > 0;


Use PLOT to put the two together in a bar chart like the one above:

    PLOT
        AXISLABELS, VERY LIGHT BLUE BARS, VERY BIG BLUE HORTICK
      WITH
        TITLE
          "Benford's Law Applied to Country Populations"
        FORMAT Y DECIMAL "#'%'"
        TITLE "First Digit of Population Value (1-9)"
        LEGEND AT 550 275
        NO SIDES
        NO TICKS X
    SELECT
        k, (COUNT(*) / @total) * 100 ,
        (SELECT p FROM benfordtemp WHERE d=k)
        FROM valtemp
        GROUP BY k
        HAVING k > 0;


The same scripts you use to make charts in the Entrance Desktop application can be used in the Entrance web application to serve up charts. These charts can be embedded in web pages using HTML IMG tags, just as you would any other image.

The Entrance web application requires Java and a servlet container, like Tomcat or Jetty. To install it, decide on the settings for these parameters:

Use the “Install Servlet” tool, in the Entrance Desktop X Menu to make an Entrance Web Application Archive (WAR) file with the settings you want. (If you don’t see it there, follow the instructions below for adding it):

If you don’t have write permissions for the webapps directory on your server, write the Entrance WAR file somewhere else, eg. your home directory, then copy it as a super user.

After deploying Entrance, start and stop the server, then navigate your web browser to:

    http://(the URL of your server)/entrance/

You should see the Entrance welcome screen, which includes a link to a rowser that will let you see the PLOT scripts you have installed.

Use the normal HTML IMG tag to embed Entrance dynamic charts in a web page as you would any online PNG image. Image URLs take the form:

    http://(your server)/entrance/plot/(path to script)

where the ‘path’ is the page from script-root to the script.

Here’s a tip: If you are running Entrance Desktop on the machine where you are running the Entrance web application, add the script-root directory to the Entrance tree using File | Add folder… It will be easy to find and edit Entrance scripts. We’ve used this with remote desktops, too!

Adding InstallServletX
If you have upgraded Entrance from an older version, the installer servlet will not appear in the X Menu unless you have added it. To add the installer: Select “Edit this menu…” from the X Menu, then fill in the dialog that appears to match this:

Editing web.xml
There can be cases when you want to edit the web.xml file youself, without relying on InstallServelX to write the file for you. For example, this is the way to specify mutliple Entrance servlet contexts using different url/user/password combinations. If you need help with this, contact us at “support at entrance.com” or send email to the Entrance Google Group.

A command line utility is bundled with the Entrance desktop application. This utiility uses the same code to draw charts that the Entrance desktop application uses, but output is sent to the JPG or PNG file you specify.

It can be used to “batch” generation of PNG files from shell scripts.

Usage:

java -jar entrance.jar (options) -c (connection_name)
  -o (output file) (script file)

java -jar entrance.jar (options) -url (jdbc url)
  -u (user) -p (password) -o (output file) (script file)

Options:

-c (Entrance connection name)
-url (jdbc url), eg. jdbc:mysql://(host):(port)/(db)
-u (user name)
-p (password)
-w (integer width of the resulting image)
-h (integer height of the resulting image)
-o (PNG or JPG output file)
-d (JDBC driver class)

Examples:

java -jar entrance.jar
  -url jdbc:mysql://localhost/test -u joe -p bagadonuts
  -o out.png ~/area.sql

java -jar entrance.jar -c test -o out.png ~/area.sql
  -w 800 -h 400


The plot above shows several Levy distributions using formulas provided in the Wikipedia article about them.

Its fairly easy to make function plots like this. Use the Tools | “Generate a reference table…” to generate x values:

then use ALTER, UPDATE and the MySQL Mathematical Functions to add a column and fill it in with function values:

    ALTER TABLE ref1
        ADD COLUMN y double;

    SET @c = 1.0;
    UPDATE ref1
        SET y = SQRT(@c / (PI() * 2.0))
            * (EXP(-@c/ (2.0 * x)))
            / (POW(x, 1.5 )) ;


Finally, do the plot:

    PLOT SCATTER
        X, RED LINE, DARK GREEN LINE, BLUE LINE
      WITH
        TITLE "Levy Distributions"
        SCALE Y 0 1.0 0.25
        FORMAT Y DECIMAL "#.##"

        SCALE X 0 3.0 1.0
        SCALE X2 0 3.0 1.0
        NO LABELS X2
        TICKS X2 BOTTOM
        LIGHT GRAY GRID LINES
        LEGEND BOTTOM
    SELECT
        x,
        y AS "c = 0.5",
        y2 AS "c = 1.0",
        y3 AS "c = 2.0"
        FROM ref1;


This is output from a script I cooked up for comparing today’s visitor counts to yesterday’s. The idea is to see if the counts are in line with normal activity: Hourly counts that went low would mean something wrong with the web site. High counts, which happen occasionally, mean that someone gave Entrance a plug somewhere.

(This chart was made a little after 9 AM this morning, so the red bar for 9 is low representing an incomplete count)

To ensure that any 0 counts show up, I include a LEFT JOIN with a table containing the values 0-23. SKINNY makes the second set of bars 1/3 the width of the first set. Here’s the complete script:

-- hours 0 - 23
create temporary table hours_t
    (h int, today int, yesterday int);

plot
    all axislabels, very light blue bar, light red bar
    with
      no sides
      baroverlap 1
      skinny
      comment left "Number of Visitors"
      title x "Hour of Day"
select hours_t.h, c2, c
    from (hours_t LEFT JOIN today_t
    on hours_t.h = today_t.h) LEFT JOIN
    yesterday_t
    on hours_t.h = yesterday_t.h;

With a similar technique, you could compare this months performance to last months, or days in this weeks to last weeks, etc.

This PLOT command made the simple bar chart above (click the chart for a complete script):

    PLOT
        AXISLABELS, LIGHT GRAY BARS, LIGHT RED BARS
      WITH
        TITLE "Main Title"
        TITLE X "X Axis"
        NO SIDES
        TITLE Y "Y Axis"
        GRIDLINES HORIZONTAL
        LEGEND BOTTOM
    SELECT
        lbl, Series1, Series2
        FROM foof;


Use OUTLINES to add bar outlines:

Use BARWIDTH (0.0-1.0) to control the bar widths (these are “BARWIDTH .5″):

Use BARSEPARATION (number of pixels) to control the space between bars in individual clusters (these are “BARSEPARATION 10″):

And use BAROVERLAP (0 or 1) to determine whether bars overlap or not (these are “BAROVERLAP 1″):