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.
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
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
.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.
In the introduction to web maps we saw how tiles are referenced by their
coordinates. On disk, they are often stored literally in
subdirectories such that they have a filesystem path like
offers a functional equivalent to this – the
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
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);
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.
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.