til: datasette_serving-mbtiles.md
This data as json
| path | topic | title | url | body | html | shot | created | created_utc | updated | updated_utc | shot_hash | slug |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| datasette_serving-mbtiles.md | datasette | Serving MBTiles with datasette-media | https://github.com/simonw/til/blob/main/datasette/serving-mbtiles.md | The [MBTiles](https://github.com/mapbox/mbtiles-spec) format uses SQLite to bundle map tiles for use with libraries such as Leaflet. I figured out how to use the [datasette-media](https://datasette.io/plugins/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](https://datasette.io/plugins/datasette-tiles) plugin. ```yaml plugins: datasette-cluster-map: tile_layer: "/-/media/tiles/{z},{x},{y}" tile_layer_options: attribution: "© OpenStreetMap contributors" tms: 1 bounds: [[37.61746256103807, -122.57290320721465],[37.85395101481279, -122.27695899334748]] minZoom: 15 maxZoom: 16 datasette-media: tiles: database: San_Francisco sql: with comma_locations as ( select instr(:key, ',') as first_comma, instr(:key, ',') + instr(substr(:key, instr(:key, ',') + 1), ',') as second_comma ), variables as ( select substr(:key, 0, first_comma) as z, substr(:key, first_comma + 1, second_comma - first_comma - 1) as x, substr(:key, second_comma + 1) as y from comma_locations ) select tile_data as content, 'image/png' as content_type from tiles, variables where zoom_level = variables.z and tile_column = variables.x and tile_row = variables.y ``` | <p>The <a href="https://github.com/mapbox/mbtiles-spec">MBTiles</a> format uses SQLite to bundle map tiles for use with libraries such as Leaflet.</p> <p>I figured out how to use the <a href="https://datasette.io/plugins/datasette-media" rel="nofollow">datasette-media</a> to serve tiles from this MBTiles file containing two zoom levels of tiles for San Francisco: <a href="https://static.simonwillison.net/static/2021/San_Francisco.mbtiles" rel="nofollow">https://static.simonwillison.net/static/2021/San_Francisco.mbtiles</a></p> <p>This TIL is now entirely obsolete: I used this prototype to build the new <a href="https://datasette.io/plugins/datasette-tiles" rel="nofollow">datasette-tiles</a> plugin.</p> <div class="highlight highlight-source-yaml"><pre><span class="pl-ent">plugins</span>: <span class="pl-ent">datasette-cluster-map</span>: <span class="pl-ent">tile_layer</span>: <span class="pl-s"><span class="pl-pds">"</span>/-/media/tiles/{z},{x},{y}<span class="pl-pds">"</span></span> <span class="pl-ent">tile_layer_options</span>: <span class="pl-ent">attribution</span>: <span class="pl-s"><span class="pl-pds">"</span>© OpenStreetMap contributors<span class="pl-pds">"</span></span> <span class="pl-ent">tms</span>: <span class="pl-c1">1</span> <span class="pl-ent">bounds</span>: <span class="pl-s">[[37.61746256103807, -122.57290320721465],[37.85395101481279, -122.27695899334748]]</span> <span class="pl-ent">minZoom</span>: <span class="pl-c1">15</span> <span class="pl-ent">maxZoom</span>: <span class="pl-c1">16</span> <span class="pl-ent">datasette-media</span>: <span class="pl-ent">tiles</span>: <span class="pl-ent">database</span>: <span class="pl-s">San_Francisco</span> <span class="pl-ent">sql</span>: <span class="pl-s">with comma_locations as (</span> <span class="pl-s">select instr(:key, ',') as first_comma,</span> <span class="pl-s">instr(:key, ',') + instr(substr(:key, instr(:key, ',') + 1), ',') as second_comma</span> <span class="pl-s">),</span> <span class="pl-s">variables as (</span> <span class="pl-s">select</span> <span class="pl-s">substr(:key, 0, first_comma) as z,</span> <span class="pl-s">substr(:key, first_comma + 1, second_comma - first_comma - 1) as x,</span> <span class="pl-s">substr(:key, second_comma + 1) as y</span> <span class="pl-s">from comma_locations</span> <span class="pl-s">)</span> <span class="pl-s">select</span> <span class="pl-s">tile_data as content,</span> <span class="pl-s"><span class="pl-pds">'</span>image/png<span class="pl-pds">'</span></span> <span class="pl-s">as content_type</span> <span class="pl-s">from</span> <span class="pl-s">tiles, variables</span> <span class="pl-s">where</span> <span class="pl-s">zoom_level = variables.z</span> <span class="pl-s">and tile_column = variables.x</span> <span class="pl-s">and tile_row = variables.y</span></pre></div> | <Binary: 71,538 bytes> | 2021-02-03T15:12:05-08:00 | 2021-02-03T23:12:05+00:00 | 2021-02-03T15:12:05-08:00 | 2021-02-03T23:12:05+00:00 | cc9a5cbba9f61f58837d25d2b323bcbc | serving-mbtiles |