Serving MBTiles with datasette-media#

The MBTiles format uses SQLite to bundle map tiles for use with libraries such as Leaflet.

I figured out how to use the datasette-media to serve tiles from this MBTiles file containing two zoom levels of tiles for San Francisco: https://static.simonwillison.net/static/2021/San_Francisco.mbtiles

This TIL is now entirely obsolete: I used this prototype to build the new datasette-tiles plugin.

1
plugins:
2
  datasette-cluster-map:
3
    tile_layer: "/-/media/tiles/{z},{x},{y}"
4
    tile_layer_options:
5
      attribution: "© OpenStreetMap contributors"
6
      tms: 1
7
      bounds: [[37.61746256103807, -122.57290320721465],[37.85395101481279, -122.27695899334748]]
8
      minZoom: 15
9
      maxZoom: 16
10
  datasette-media:
11
    tiles:
12
      database: San_Francisco
13
      sql:
14
        with comma_locations as (
15
          select instr(:key, ',') as first_comma,
16
          instr(:key, ',') + instr(substr(:key, instr(:key, ',') + 1), ',') as second_comma
17
        ),
18
        variables as (
19
          select
20
            substr(:key, 0, first_comma) as z,
21
            substr(:key, first_comma + 1, second_comma - first_comma - 1) as x,
22
            substr(:key, second_comma + 1) as y
23
          from comma_locations
24
        )
25
        select
26
          tile_data as content,
27
          'image/png' as content_type
28
        from
29
          tiles, variables
30
        where
31
          zoom_level = variables.z
32
          and tile_column = variables.x
33
          and tile_row = variables.y