Storing tiles

Makers of web maps with millions of tiles are faced with a harsh reality: most filesystems and transfer protocols aren’t designed to handle millions of images efficiently. For files in a single directory FAT32 maxes out at 65,536, HFS cannot list files after 32,767, and EXT3 begins to slow down around 20,000. And whether transferring to a USB device or over the network, copying millions of individual tiles can be inefficient and painfully slow. The MBTiles spec provides a way of storing millions of tiles in a single SQLite database making it possible to store and transfer web maps in a single file. And because SQLite is available on so many platforms, MBTiles is an ideal format for reading tiles directly for serving on the web or displaying on mobile devices.

View the spec mbtiles-spec is an open specification on GitHub. Fork the repository and create issues or pull requests to improve the next version.

A short introduction to SQLite

If you’ve worked with SQL databases like MySQL or PostgreSQL before, using a SQLite database will feel very familiar. You can run familiar SQL SELECT, INSERT, UPDATE statements, and create tables, indexes, and views. The difference between SQLite and other databases is that each SQLite database is contained in a single file on disk. With no external permission systems, database daemons, or configuration, each .sqlite file is a self-contained database. You can copy a .sqlite file from desktop to mobile phone and have all its rows, tables, and indexes ready to be used.

SQLite is small and ubiquitous – it is used by iTunes to store metadata, by Firefox for caches, and many more products (a dated yet impressive list can be found here).

In short, SQLite is a great fit as a portable, single-file solution for storing and serving web maps.

Using tile coordinates in SQL

In the introduction to web maps we saw how tiles are referenced by their z/x/y coordinates. On disk, they are often stored literally in z and x subdirectories such that they have a filesystem path like 0/0/0.png. MBTiles offers a functional equivalent to this – the tiles table:

sqlite> SELECT * FROM tiles;

zoom_level | tile_column | tile_row | tile_data
5          | 13          | 23       | [PNG data]
5          | 13          | 24       | [PNG data]
5          | 14          | 23       | [PNG data]
5          | 14          | 24       | [PNG data]
5          | 15          | 25       | [PNG data]

This table makes it easy to retrieve the image for a particular tile or answer questions like “How many tiles does this map have on zoom level 8?”

sqlite> SELECT tile_data FROM tiles WHERE zoom_level = 8 AND tile_column = 116 AND tile_row = 192;

[PNG data]

sqlite> SELECT COUNT(*) FROM tiles WHERE zoom_level = 8;

130

Using views to reference redundant images

Maps that cover large areas of solid color like ocean or empty land can contain thousands of duplicate, redundant tiles. For example, the tile 4/2/8 in the middle of the pacific ocean might look like this empty patch of blue:

While it may be a few tiles at z4, the same area covered at z16 might be millions of solid blue tiles, all exactly the same.

MBTiles can reduce the amount of space used by these redundant tiles drastically by implementing the tiles table as a view. Instead of a single, literal table, MBTiles implementers often split the tiles table into two: one to store the raw images and one to store the tile coordinates for those images:

CREATE TABLE images (tile_data BLOB, tile_id TEXT);
CREATE TABLE map (zoom_level INTEGER, tile_column INTEGER, tile_row INTEGER, tile_id TEXT);

The tiles table is defined as a view that joins the two together, allowing thousands of tile coordinates to reference the same image blob.

CREATE VIEW tiles AS SELECT
    map.zoom_level AS zoom_level,
    map.tile_column AS tile_column,
    map.tile_row AS tile_row,
    images.tile_data AS tile_data
FROM map JOIN images ON images.tile_id = map.tile_id;

Using this technique MBTiles can store tiles with lower disk usage than filesystem equivalents – sometimes 60% or more depending on the map.

MBTiles in action

MBTiles is the storage format used to export and upload custom maps from TileMill to your Mapbox account. You can also use MBTiles files offline on mobile devices with the Mapbox iOS SDK.

Mission complete! Next up:

  • We’ve looked at how MBTiles gets around filesystem limits and improves tile storage efficiency by leveraging SQLite. In the next section we’ll look at another open format – UTFGrid – and how it can be used to scale interaction for thousands of features on a map.