Simon Willison’s Weblog

Serving map tiles from SQLite with MBTiles and datasette-tiles

4th February 2021

Working on datasette-leaflet last week re-kindled my interest in using Datasette as a GIS (Geographic Information System) platform. SQLite already has strong GIS functionality in the form of SpatiaLite and datasette-cluster-map is currently the most downloaded plugin. Most importantly, maps are fun!

MBTiles

I was talking to Tom MacWright on Monday and I mentioned that I’d been thinking about how SQLite might make a good mechanism for distributing tile images for use with libraries like Leaflet. “I might be able to save you some time there” he said... and he showed me MBTiles, a specification he started developing ten years ago at Mapbox which does exactly that—bundles tile images up in SQLite databases.

(My best guess is I read about MBTiles a while ago, then managed to forget about the spec entirely while the idea of using SQLite for tile distribution wedged itself in my head somewhere.)

The new datasette-tiles plugin

I found some example MBTiles files on the internet and started playing around with them. My first prototype used the datasette-media plugin, described here previously in Fun with binary data and SQLite. I used some convoluted SQL to teach it that hits to /-/media/tiles/{z},{x},{y} should serve up content from the tiles table in my MBTiles database—you can see details of that prototype in this TIL: Serving MBTiles with datasette-media.

The obvious next step was to write a dedicated plugin: datasette-tiles. Install it and run Datasette against any MBTiles database file and the plugin will set up a /-/tiles/db-name/z/x/y.png endpoint that serves the specified tiles.

It also adds a tile explorer view with a pre-configured Leaflet map. Here’s a live demo serving up a subset of Stamen’s toner map—just zoom levels 6 and 7 for the country of Japan.

Here’s how to run this on your own computer:

# Install Datasette
brew install datasette
# Install the plugin
datasette install datasette-tiles
# Download the japan-toner.db database
curl -O https://datasette-tiles-demo.datasette.io/japan-toner.db
# Launch Datasette and open a browser
datasette japan-toner.db -o
# Use the cog menu to access the tile explorer
# Or visit http://127.0.0.1:8001/-/tiles/japan-toner

Creating MBTiles files with my download-tiles tool

A sticking point when I started playing with MBTiles was finding example files to work with.

After some digging, I came across the amazing HOT Export Tool. It’s a project by the Humanitarian OpenStreetMap Team that allows anyone to export subsets of data from OpenStreetMap in a wide variety of formats, including MBTiles.

I filed a minor bug report against it, and in doing so took a look at the source code (it’s all open source)... and found the code that assembles MBTiles files. It uses another open source library called Landez, which provides functions for downloading tiles from existing providers and bundling those up as an MBTiles SQLite file.

I prefer command-line tools for this kind of thing over using Python libraries directly, so I fired up my click-app cookiecutter template and built a thin command-line interface over the top of the library.

The new tool is called download-tiles and it does exactly that: downloads tiles from a tile server and creates an MBTiles SQLite database on disk containing those tiles.

Please use this tool responsibly. Downloading large numbers of tiles is bad manners. Be sure to familiarize yourself with the OpenStreetMap Tile Usage Policy, and use the tool politely when pointing it at other tile servers.

Basic usage is as follows:

download-tiles world.mbtiles

By default the tool pulls tiles from OpenStreetMap. The above command will fetch zoom levels 0-3 of the entire world—85 tiles total, well within acceptable usage limits.

Various options (described in the README) can be used to customize the tiles that are downloaded. Here’s how I created the japan-toner.db demo database, linked to above:

download-tiles japan-toner.mbtiles \ 
    --zoom-levels 6-7 \
    --country Japan \
    --tiles-url "http://{s}.tile.stamen.com/toner/{z}/{x}/{y}.png" \
    --tiles-subdomains "a,b,c,d" \
    --attribution 'Map tiles by Stamen Design, under CC BY 3.0. Data by OpenStreetMap, under CC BY SA.'

The --country Japan option here looks up the bounding box for Japan using Nominatim. --zoom-levels 6-7 fetches zoom levels 6 and 7 (in this case that makes for 193 tiles total). --tiles-url and --tiles-subdomain configure the tile server to fetch them from. The --attribution option bakes that string into the metadata table for the database—which is then used to display it correctly in the tile explorer (and eventually in other Datasette plugins).

datasette-basemap

Out of the box, Datasette’s current Leaflet plugins (datasette-cluster-map, datasette-leaflet-geojson and so on) serve tiles directly from the OpenStreetMap tile server.

I’ve never felt particularly comfortable about this. Users can configure the plugins to run against other tile servers, but pointing to OpenStreetMap as a default was the easiest way to ensure these plugins would work for people who just wanted to try them out.

Now that I have the tooling for bundling map subsets, maybe I can do better.

datasette-basemap offers an alternative: it’s a plugin that bundles a 22.7MB SQLite file containing zoom levels 0-6 of OpenStreetMap—5,461 tiles total.

Running pip install datasette-basemap (or datasette install datasette-basemap) will install the plugin, complete with that database—and register it with Datasette.

Start Datasette with the plugin installed and /basemap will expose the bundled database. Install datasette-tiles and you’ll be able to browse it as a tile server: here’s a demo.

(I recommend also installing datasette-render-images so you can see the tile images themselves in the regular table view, like this.)

Zoom level 6 is close enough that major cities and the roads between them are visible, for all of the countries in the world. Not bad for 22.7MB!

This is the first time I’ve built a Datasette plugin that bundles a full SQLite database as part of the Python package. The pattern seems to work well—I’m excited to explore it further with other projects.

Bonus feature: tile stacks

I added one last feature to datasette-tiles before writing everything up for my blog. I’m calling this feature tile stacks—it lets you serve tiles from multiple MBTiles files, falling back to other files if a tile is missing.

Imagine you had a low-zoom-level world map (similar to datasette-basemap) and a number of other databases providing packages of tiles for specific countries or cities. You could run Datasette like this:

datasette basemap.mbtiles japan.mbtiles london.mbtiles tokyo.mbtiles

Hitting /-/tiles-stack/1/1/1.png would seek out the specified tile in the tokyo.mbtiles file, then fall back to london.mbtiles and then japan.mbtiles and finally basemap.mbtiles if it couldn’t find it.

For a demo, visit https://datasette-tiles-demo.datasette.io/-/tiles-stack and zoom in on Japan. It should start to display the Stamen toner map once you get to zoom levels 6 and 7.

Next steps

I’ve been having a lot of fun exploring MBTiles—it’s such a natural fit for Datasette, and it’s exciting to be able to build new things on top of nearly a decade of innovation by other geo-hackers.

There are plenty of features missing from datasette-tiles.

It currently only handles .png image data, but the MBTiles 1.3 specification also defines .jpg and .webp tiles, plus vector tiles using Mapbox’s .pbf gzip-compressed protocol buffers.

UTFGrid is a related specification for including “rasterized interaction data” in MBTiles databases—it helps efficiently provide maps with millions of embedded objects.

As a newcomer to the MBTiles world I’d love to hear suggestions for new features and feedback on how I can improve what I’ve got so far in the datasette-tiles issues.

Being able to serve your own map tiles like this feels very much in the spirit of the OpenStreetMap project. I’m looking forward to using my own tile subsets for any future projects that fit within a sensible tile subset.