Creating a choropleth map with TileMill, QGIS and SQLite

1. Download shape file

For instance "Presidential General Election, State Results, 2008" from

http://www.nationalatlas.gov/atlasftp.html?openChapters=chphist%2Cchpref%2Cchpgeol#chpgeol

2. Convert shape file to sqlite

• Use QGIS: https://skitch.com/alexbarth/gjb6d/skype
• The result is a plain sqlite database, there's a geometry table (geometry_columns), a projection table (spatial_ref_sys) and for us most interestingly, a table containing the data with the same name as the database (here: election2008) https://skitch.com/alexbarth/gjnyc/terminal-sqlite3-100x50

3. Create a new Tile Mill project, add the sqlite data layer

• Pick sqlite file we just exported https://skitch.com/alexbarth/gjb91/tilemill
• Add the table name (or subquery) pulling out data we want to show https://skitch.com/alexbarth/gjnru/tilemill

Note: Here I found out that database names with numbers are best avoided in TileMill < 0.7. On my TileMill 0.6 installation I could add the table name 'election2008' but I could not use it in a subquery.

4. Theme it

This is a coloring of the map by voting results favoring democrats (somewhat naive because an absence of a democrat vote assumes a republican vote).

https://skitch.com/alexbarth/gjnju/tilemill

#results {
  polygon-fill: #f5f5f5;
}

#results::fill[zoom>=0] {
  [percent_de > '70.0']{ polygon-fill:#0a66c2; }
  [percent_de > '60.0'][percent_de <= '70.0']{ polygon-fill:#2f53a5; }
  [percent_de > '50.0'][percent_de <= '60.0']{ polygon-fill:#544189; }
  [percent_de > '40.0'][percent_de <= '50.0']{ polygon-fill:#782f6c; }
  [percent_de > '30.0'][percent_de <= '40.0']{ polygon-fill:#9d1c4f; }
  [percent_de <= '30.0']{ polygon-fill:#c20a33; }
  [percent_de = null]{ polygon-fill:#f5f5f5; }
}

Further reading

Will White explains how to use SQLite's attach feature in TileMill. This is useful for joining SQLite databases containing the data to be displayed to SQLite databases containing the geometry to use.

http://developmentseed.org/blog/2011/aug/30/tilemill-05-adds-sqlite-datasource-support/