In-place conversion of an mbtiles SQLite3 database into an osmand offline tiles SQLite3 database.

inplace-mbtiles2osmand.sh

#!/bin/sh

# To the extent possible under law, the person who associated CC0
# with this work has waived all copyright and related or neighboring
# rights to this work.
# http://creativecommons.org/publicdomain/zero/1.0/

SCRIPT_DIR=`dirname $0`

if [ -e $1 ] ; then
  input=$1
  output=`basename $input .mbtiles`
  output=${output}.sqlitedb

  mv $input $output

  sqlite3 $output < "$SCRIPT_DIR/inplace-mbtiles2osmand.sql"
  
  echo "$input converted to $output"
else
  echo "Usage: $0 <source.mbtiles>"
fi

inplace-mbtiles2osmand.sql

-- SQL stataments to perform an in-place conversion of an mbtiles SQLite3
-- database into an osmand offline tiles SQLite3 database.

-- To the extent possible under law, the person who associated CC0
-- with this work has waived all copyright and related or neighboring
-- rights to this work.
-- http://creativecommons.org/publicdomain/zero/1.0/


-- SQLite3 doesn't allow renaming of column names, so we must create a
-- new table, copy across the data, remove the old one, then rename the
-- newly created temporary table as tiles.
CREATE TABLE mbtiles_tiles
  (
    x int,
    y int,
    z int,
    s int,
    image blob,
    PRIMARY KEY (x,y,z,s)
  );

INSERT INTO mbtiles_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;

DROP TABLE tiles;
ALTER TABLE mbtiles_tiles RENAME TO tiles;

-- Not sure what the s column is for...
--ALTER TABLE tiles ADD COLUMN s int;
--UPDATE tiles SET s = 0;

-- create the osmand info table
CREATE TABLE info (minzoom, maxzoom, url);

-- get the minzoom and maxzoom from the bounds of the data we have
INSERT INTO info (minzoom, maxzoom) SELECT min(z), max(z) FROM tiles;

-- create an index on the tiles table
CREATE INDEX IND on tiles(x,y,z,s);


-- although the following contains usefull information, the osmand schema
-- doesn't have a place for it
DROP TABLE metadata;

-- very important clean up the database after dropping data
VACUUM;

Thanks for this script, it helped a lot!

The .mbtiles database I used had a different convention for the y and z coordinates, so I needed to make the following modifications:

• set the z coordinate to 18 - zoom_level
• set the y coordinate to 2^z - 1 - tile_row

Since sqlite appears to have no built-in power function, I used a couple of statements like

UPDATE tiles SET y =  511 - y WHERE z =  9;
UPDATE tiles SET y = 1023 - y WHERE z = 10;

to do the latter modification. The first one can be done by changing the INSERT INTO mbtiles_tiles statment.

I hope this can save someone else the frustration I experienced :-)

Thanks for the good idea. I needed to do the same thing in the other direction, so I adapted the idea like that:

    -- SQL stataments to perform an in-place conversion of an osmand/locus/orux tiles
    -- SQLite3 database into an mbtiles SQLite3 database.

    -- To the extent possible under law, the person who associated CC0
    -- with this work has waived all copyright and related or neighboring
    -- rights to this work.
    -- http://creativecommons.org/publicdomain/zero/1.0/

    CREATE TABLE metadata (name text, value text);
    INSERT INTO metadata (name, value) VALUES ('name', 'test');
    INSERT INTO metadata (name, value) VALUES ('type', 'baselayer');
    INSERT INTO metadata (name, value) VALUES ('version', '1');
    INSERT INTO metadata (name, value) VALUES ('description', 'test description');
    INSERT INTO metadata (name, value) VALUES ('format', 'jpg');

    UPDATE tiles SET z = 17 - z;

    UPDATE tiles SET y =     1 - y WHERE z =  1;
    UPDATE tiles SET y =     3 - y WHERE z =  2;
    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;

    CREATE TABLE mbtiles_tiles (zoom_level integer, tile_column integer, tile_row integer, tile_data blob);
    INSERT INTO mbtiles_tiles (zoom_level, tile_column, tile_row, tile_data) SELECT z as zoom_level, x as tile_column, y as tile_row, image as tile_data FROM tiles;

    DROP TABLE tiles;
    ALTER TABLE mbtiles_tiles RENAME TO tiles;
    DROP TABLE android_metadata;
    DROP TABLE info;

    VACUUM;