USMap

The US States Coloring Book

Entrance “coloring books” make it possible to fill in regions with colors based on the values returned by a MySQL queriy. You assign colors to regions in a color book using their names. In the case of the US coloring book, these names are the state names with any spaces removed: California, NewYork, Florida, and so on.

Quick review: Entrance charts are described using the PLOT command, which has “plot part” and a “select part”. Any MySQL SELECT statement can be used for the select part. The important thing to remember is that the series listed in the plot part of a PLOT command must match up one-to-one with columns generated by the select part.

In the case of a US coloring book, you assign one column to be the “layer” column, which should contain state names, and another column to be a “color override”, which should contain colors like “red”, “blue”, and so on. For example, this script:

    plot USColoringBook

      layer, color override

    select 

      'California', 'Blue';

paints California in blue on a background image of the United States:

To color in all of the US states, you can do something like this:

plot USColoringBook

      layer, color override

    select 

      state, if(winner like ‘%Obama%’, ‘skyblue’, ‘scarletred’)

      from election_results;

Using data from the US Presidential Election, November 4th, 2008, will make this chart:

Some refinements

We can also add text and a legend:

A script for making the chart above is here, complete with the election results data that was available as I wrote this..

Making Other ColoringBooks

The Entrance US coloring book was drawn by artist Nancy White of lizmar.com. Shown below are are the background image and layer for California she provided:

You can make other coloring books by by supplying images on bitmap layers that “match up” with each other in a similar way. To draw a coloring book using your images, use the keywords BITMAP to specify the background image and LAYERS to specify the layer bitmaps to go with it as in this example:

  plot ColoringBook

    layer, color override

  with

    bitmap  "/Users/todlandis/proj/entrance/samples/ca_counties/ca_counties.png"

    layers  "/Users/todlandis/proj/entrance/samples/ca_counties/ca_counties_layers"

    translucence 100 

  select county, color from CountiesTable;

TRANSLUCENCE (0-255) determines how transparent the layers are when they are drawn on top of each other.

Pushing the envelope further

The USColoringBook, like all ColoringBooks, is also a ScatterChart. Anything you can do with a scatter chart, such as locating the chart frame, plotting markers, drawing data labels and so on, can also do with a ColoringBook. Use FRAME to reposition the background images and X and Y axes.

You can also assign the background layer, “states_map”, a color, producing some interesting effects:

plot USColoringBook

  layer, color override

 with

  background gray

  foreground white 

select

  'states_map', 'very light blue'

union

select

  'pennsylvania', 'light red';

MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries.

Java is a trademark or registered trademarks of Sun Microsystems, Inc. in the U.S. and other countries.