TileMill 0.5.0 is now available, with the biggest addition being support for using SQLite databases as geographic datasources. This gives TileMill cartographers an alternative geographic data source to shapefiles and the ability to join multiple databases from different sources together into one source. TileMill 0.5.0 has several smaller enhancements that you can see in the changelog, and Konstantin will blog about the new Mac OS X features later this week.

SQLite files are an ideal alternative to shapefiles because they consist of just one file, making them very easy to share, and just like shapefiles, SQLite databases can store geographic features along with non-geographic attributes about those features. They are easy to edit with free tools like Quantum GIS, and feature attributes can be edited with any SQLite client. Performance wise, our test show that SQLite layer performance is about equal to that of shapefiles, and TileMill will even automatically index your SQLite database if an index is missing.

Joining multiple SQLite files

The real power of having SQLite in TileMill is the ability to join two or more SQLite databases together. This feature allows you supplement your geographic data with data from other sources and use it in your stylesheets and tooltips.

For example, say you have a database that contains polygons for each park in your city. You set up a web application that gathers ratings from citizens about each park and shows the results on a map that is updated once a week. Since the parks’ polygons don’t change very often, that database can remain static from week to week. The ratings however are quite dynamic and will be different each week. You could configure your web application to export the ratings data as an SQLite database, allowing you to join in the new data for your map each week, without bothering with the polygon data.

When joining multiple SQLite files, you will have one database that contains feature geometries and additional database can add more attributes about those features. For example, take two SQLite databases:

• World Country Borders (contains the geometry)

• World Country GDP figures

I imported the GDP data from the the World Bank’s open data site. To complete the join, the databases need to share a common key or ID, an din this case both have a column containing each country’s ISO3 code, which we will use to join the databases together.

I start by adding a new layer in TileMill, using the World Country Borders as primary datasource. Then I use the “Attach DB” field to import my GDP database, which I’ve copied to my project folder. I enter gdp_db@country_gdp.sqlite into this field, which will use SQLite ATTACH DATABASE to make the GDP database available for use the in the query field by the name gdp_db. Then I write my join subquery and put that into the "Table or subquery" field:

`(SELECT * FROM '10m_admin_0_countries' INNER JOIN gdp_db.gdp ON gdp_db.gdp.code = iso_a3)`

After saving the layer, the GDP data will be available in the layer inspector and I can use those attributes for styling my map. Later if I find updated GDP numbers I can update my map by replacing the GDP database and leave the world borders database untouched.

I’ve turned this example into a sample project, which you can download and use for practice.

A big thanks to Stella Laurenzo for her work on the Attach DB functionality in Mapnik.

You can download the latest release of TileMill at tilemill.com. For documentation and support, check out support.mapbox.com, and stay tuned for more on the Mac OS X feature additions on our blog later this week.

UPDATE: I adjusted the example project to make it more clear how the file paths work.