Hfrhyu

I want to build an mbtiles with different map detail at different zoom levels (e.g. contours at 1m at levels >16 and 5m <=16). I've exported raster images at the right resolution using QGIS and now want to combine them with GDAL.

gdal_translate -of mbtiles -co "TILE_FORMAT=PNG8" Level19.png basemap_hires.mbtiles
gdaladdo -r nearest basemap_hires.mbtiles 2 4
gdal_translate -of mbtiles -co "TILE_FORMAT=PNG8" Level16.png basemap_lores.mbtiles
gdaladdo -r nearest basemap_lores.mbtiles 2 4 8 16

generates levels 19->17 from the hi-res map and levels 16->12 from the lo-res map, in separate files. But I'd like to combine them into one file.

Any ideas? Of course, I could use just the Level19.png file and do 2 4 8 16 32 64 with gdaladdo, but then the level 16->12 tiles will have too much detail (and puny labels). In a way, this is a replacement for QTiles3 on the cheap, since it would generate the "right" map at all scale levels, but it's not robust for me in QGIS and I really only need 2 levels of detail.

This is basis SQL to combine the various mbtiles. You can do it from command line with Sqlite3 or in your favorite sqlite editor I like Sqlite Expert Professional open first database and then attach second and do insert into tiles. The other way I do this is extract mbtiles to folder of tiles for each database and then run python mbutil or other scripts or tiles2gpkg_parallel.py to build a geopackage

Expanding on GeospatialInformationTech's answer, here's what worked for me:

gdal_translate -of mbtiles -co "TILE_FORMAT=PNG8" -co "ZLEVEL=9" "Level 19.tif" "mapbase.mbtiles"
gdaladdo -r nearest -oo "TILE_FORMAT=PNG8" -oo "ZLEVEL=9" "mapbase.mbtiles" 2 4
gdal_translate -of mbtiles -co "TILE_FORMAT=PNG8" -co "ZLEVEL=9" "Level 16.tif" "mapbase low.mbtiles"
gdaladdo -r nearest -oo "TILE_FORMAT=PNG8" -oo "ZLEVEL=9" "mapbase low.mbtiles" 2 4 8 16
echo ATTACH "mapbase low.mbtiles" AS low; INSERT INTO main.tiles SELECT * FROM low.tiles; | sqlite3 "mapbase.mbtiles"

The -co and -oo compress the map, otherwise the mbtiles grows huge (this is for rendered patterned maps, jpeg would probably be better if there were aerial photos involved). The powers of 2 in the gdaladdo lines buildup exactly the right overlays to avoid overlap, in this case levels (19 18 17)(16 15 14 13 12). The last line, written Windows batch format here, uses sqlite3 (already installed with QGIS/osg4w) to munge it together.

I was expecting to have to also do a echo UPDATE metadata SET value=12 WHERE name='minzoom' | sqlite3 "mapbase.mbtiles" to fix the minimum zoom level after the SQL merge, but it was somehow automagically correct.

Someone asked why I don't use vector tiles (though that comment is now gone). 1) Better control of rendering with QGIS, 2) My users use the mbtiles output as offline maps in Guru/Galileo maps on iOS, and there's no mention in its docs that vector tiles in mbtiles would be supported (it's possible they are, since they have their own offline vector maps).