joining together two mbtiles files

Question

I haven't managed to find a way to join two *.mbtiles files together (first one contains zoom level from 0-16 and second one zoom level 17). I was working with different sqlite managers, but no mather how I have exported and imported database2 into database1, I had no success - binary field was always so badly corrupted that it couldn't get image.png back.

Does anyone know a simple procedurte of joining two mbtiles files together?

Succinate

Answer 1

If the two files have the same metadata, and if the tiles tables are actually tables and not views, you can simply append the data of one to the other table:

/* open database1 as main database, then: */
ATTACH 'database2' AS db2;
INSERT INTO tiles SELECT * FROM db2.tiles;

Preterition

Error: cannot modify tiles because it is a view – Biedermeier

@Biedermeier To ask a question, use the "Ask Question" button. (And include the database schema.) – Preterition

@Preterition My question is this one, why asking the same, to create a duplicate ? I'm trying to use that answer and it produce an error, so I'm reporting the error as a comment. – Biedermeier

You have a different problem, and it cannot be solved with the information in this question. – Preterition

@user914425 A comment is the wrong place to ask an unrelated question. – Preterition

Answer 2

In my case I have an error with the @CL. solution:

Error: cannot modify tiles because it is a view

So the schema of my database is different:

> .schema

CREATE TABLE grid_key (
    grid_id TEXT,
    key_name TEXT
);
CREATE TABLE grid_utfgrid (
    grid_id TEXT,
    grid_utfgrid BLOB
);
CREATE TABLE images (
    tile_data blob,
    tile_id text
);
CREATE TABLE keymap (
    key_name TEXT,
    key_json TEXT
);
CREATE TABLE map (
   zoom_level INTEGER,
   tile_column INTEGER,
   tile_row INTEGER,
   tile_id TEXT,
   grid_id TEXT
);
CREATE TABLE metadata (
    name text,
    value text
);
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;
CREATE VIEW grids AS
    SELECT
        map.zoom_level AS zoom_level,
        map.tile_column AS tile_column,
        map.tile_row AS tile_row,
        grid_utfgrid.grid_utfgrid AS grid
    FROM map
    JOIN grid_utfgrid ON grid_utfgrid.grid_id = map.grid_id;
CREATE VIEW grid_data AS
    SELECT
        map.zoom_level AS zoom_level,
        map.tile_column AS tile_column,
        map.tile_row AS tile_row,
        keymap.key_name AS key_name,
        keymap.key_json AS key_json
    FROM map
    JOIN grid_key ON map.grid_id = grid_key.grid_id
    JOIN keymap ON grid_key.key_name = keymap.key_name;
CREATE UNIQUE INDEX grid_key_lookup ON grid_key (grid_id, key_name);
CREATE UNIQUE INDEX grid_utfgrid_lookup ON grid_utfgrid (grid_id);
CREATE UNIQUE INDEX images_id ON images (tile_id);
CREATE UNIQUE INDEX keymap_lookup ON keymap (key_name);
CREATE UNIQUE INDEX map_index ON map (zoom_level, tile_column, tile_row);
CREATE UNIQUE INDEX name ON metadata (name);

So you can adapt the solution this way:

First update the metadata the way you want, for example:

sqlite> UPDATE metadata SET value = '7' WHERE name = 'minzoom';

Then insert the tiles:

sqlite> INSERT OR REPLACE INTO images SELECT * from db2.images;
sqlite> INSERT OR REPLACE INTO map SELECT * from db2.map;

Biedermeier

Answer 3

It's been a long time, but i hope my 2 cents will be useful for somebody.

I did it using SQLiteStudio. I used CL.'s solution, but only partially, because i faced with an issue different from toutpt's.

First of all, i think it makes sense to clarify, that @CL.'s 'database2' means exactly the .mbtiles-file to merge into the opened one and inside the singular qoutes you should provide an absolute path to the file.

Regarding the issue, i faced with this one:

UNIQUE constraint failed: tiles.zoom_level, tiles.tile_column, tiles.tile_row

This happens when both of the .mbtiles have entries with the same zoom_level, tile_column and tile_row, but, for example, different blob-geodata on the same zoom-level, or they intersect each other on some zones on the globe.
This error should not appear if you're merging files with different zoom levels (e.g. 0-14 in first .mbtiles and 15-17 in the second one).

The solution of this is written in the end of @toutpt's answer:

INSERT OR REPLACE INTO tiles SELECT FROM db2.tiles;

This will replace the values in the table whenever the entry already exists.
But you also can use the following:

INSERT OR IGNORE INTO tiles SELECT FROM db2.tiles;

This will ingnore that error and continue adding other records. (A bit more detailed decription is here)

Scurrile