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;