Skip to content

Instantly share code, notes, and snippets.

@andrewharvey
Last active January 13, 2023 16:39
Show Gist options
  • Save andrewharvey/4516352 to your computer and use it in GitHub Desktop.
Save andrewharvey/4516352 to your computer and use it in GitHub Desktop.
In-place conversion of an mbtiles SQLite3 database into an osmand offline tiles SQLite3 database.
#!/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
-- 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;
@wrtlprnft
Copy link

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 :-)

@jerome077
Copy link

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;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment