[updated => v2 of the script]
OK Petr; here is the tutorial:
All you need is to make a copy of the .mbtiles file and rename the copy as .sqlitedb
Then you open it with an sqlite application, for example the free application "DB browser for sqlite".
Then you run my sql script here below (thanks to the github forum for providing ideas and info; I also needed to analyse map files in both formats to get to this sql script).
Uploading attachments here does not seem to work: I always get a message "The extension xxx is not allowed", whatever extension I use, so I have simply pasted the contents of my
my "Mbtiles_to_sqlitedb_RogerF.sql" file here. Feel free to use it.
-- Conversion of "mbtiles" map into "sqlitedb" map
-- Roger Fraikin - V2 - June 2018
--- Use is simple:
--- 1. Copy the xxxx.mbtiles file to a new xxxx.sqlitedb file
--- 2. Open this file with an sqlite application (e.g. "DB Browser for sqlite")
--- 3. Load this "Mbtiles_to_sqlitedb_RogerF.sql" script as sql query and let it run
--- 4. Confirm the final cleanup (the VACUUM command triggers a warning)
--- 5. Close the sqlite application, confirming the "save the modified database" message.
---
--- Development notes
---
--- N1. The sql command "Rename columns" is not supported by sqlite (despite many requests on the dedicated forum, but probably for good reasons).
--- The workaround is to copy all elements onto a new table with the adequate structure for sqlitedb.
---
--- N2. The formula to translate the mbtiles "tile_row" data into sqlitedb "y" is: y = 2^z - & - tile_row
--- Unfortunately, sqlite only supports basic mathematics like +,-,*,/%, not the "^" exponential operator.
--- The workaround is to perform separate queries for each zoom levels
---
--- N3. Especially with low zoom levels, there is a chance the "y" conversion leads to a "duplicate indexes" error
--- That is why I only create the index after the conversion has been made
---
--- N4. The query can take some time for big maps; that is why I added a series of SELECT messages to monitor the progress
--- Be patient for the final cleanup (VACUUM) as the DB is completely copied/rebuilt.
---
--- Feel free to use this script to convert your maps
CREATE TABLE sqlitedb_tiles
(
x int,
y int,
z int,
s int,
image blob,
PRIMARY KEY (x,y,z,s)
);
SELECT "New table created";
INSERT INTO sqlitedb_tiles (x, y, z, s, image) SELECT tile_column as x, tile_row as y, zoom_level as z, '0' as s, tile_data as image FROM tiles;
SELECT "Data copied";
--- suppress old table "tiles"
DROP TABLE tiles;
SELECT "Old table dropped";
--- suppress old table "tiles" and rename the new one to "tiles"
ALTER TABLE sqlitedb_tiles RENAME TO tiles;
SELECT "New Table renamed";
--- no need to keep the old "metadata" table
DROP TABLE metadata;
SELECT "Metadata dropped";
--- convert mbtiles data into sqlitedb data assuming that minimum zoom level is 3 and maximum is 16
UPDATE tiles SET y = 7 - y where z = 3;
UPDATE tiles SET y = 15 - y where z = 4;
UPDATE tiles SET y = 31 - y where z = 5;
UPDATE tiles SET y = 63 - y where z = 6;
UPDATE tiles SET y = 127 - y where z = 7;
UPDATE tiles SET y = 255 - y where z = 8;
UPDATE tiles SET y = 511 - y where z = 9;
UPDATE tiles SET y = 1023 - y where z = 10;
UPDATE tiles SET y = 2047 - y where z = 11;
UPDATE tiles SET y = 4095 - y where z = 12;
UPDATE tiles SET y = 8191 - y where z = 13;
UPDATE tiles SET y = 16383 - y where z = 14;
UPDATE tiles SET y = 32767 - y where z = 15;
UPDATE tiles SET y = 65535 - y where z = 16;
UPDATE tiles SET y = 131071 - y where z = 17;
SELECT "y converted";
UPDATE tiles SET z = 17 - z;
SELECT "z converted";
--- create the INFO table (required by sqlitedb) with the min and max zoom level
CREATE TABLE info (minzoom, maxzoom, url);
INSERT INTO info (minzoom, maxzoom) SELECT min(z), max(z) FROM tiles;
SELECT "INFO table created";
--- craete the required index "IND"
CREATE INDEX IND on tiles(x,y,z,s);
SELECT "IND Index created";
--- cleanup possible garbage and reorganise the indexes - this can take a while and you need to confirm a warning message
SELECT "Cleanup running";
VACUUM;
SELECT "Conversion completed";
Posted: 20 May 2018, 12:03