Extend mbtiles with osmand's .sqllitedb compatibility

mbtiles-osmand.sql

-- Unfortunately osmand's .sqlitedb and .mbtiles use the same table name, but with different columns
alter table tiles rename to mbtiles;

-- osmand uses inverted y numbering (compared to mbtiles)
-- The formula to calculate inverted y is: y' = (2^z - 1) - y
-- As sqlite does not support pow() function, here's a table of precalculated (2^z-1) for z from 0 to 25.
create table maxy (y integer, z integer);
CREATE UNIQUE INDEX IF NOT EXISTS maxy_index on maxy (z,y);

insert into maxy(y,z) values(0,0);
insert into maxy(y,z) values(1,1);
insert into maxy(y,z) values(3,2);
insert into maxy(y,z) values(7,3);
insert into maxy(y,z) values(15,4);
insert into maxy(y,z) values(31,5);
insert into maxy(y,z) values(63,6);
insert into maxy(y,z) values(127,7);
insert into maxy(y,z) values(255,8);
insert into maxy(y,z) values(511,9);
insert into maxy(y,z) values(1023,10);
insert into maxy(y,z) values(2047,11);
insert into maxy(y,z) values(4095,12);
insert into maxy(y,z) values(8191,13);
insert into maxy(y,z) values(16383,14);
insert into maxy(y,z) values(32767,15);
insert into maxy(y,z) values(65535,16);
insert into maxy(y,z) values(131071,17);
insert into maxy(y,z) values(262143,18);
insert into maxy(y,z) values(524287,19);
insert into maxy(y,z) values(1048575,20);
insert into maxy(y,z) values(2097151,21);
insert into maxy(y,z) values(4194303,22);
insert into maxy(y,z) values(8388607,23);
insert into maxy(y,z) values(16777215,24);

-- Create a view compatible with both mbtiles and osmand
CREATE VIEW if not exists tiles as select
    -- osmand sqlitedb columns: x,y,z,image
    tile_column as x,
    -- (2^z-1)-y:
    (select maxy.y - tile_row from maxy maxy where maxy.z = zoom_level) as y,
    zoom_level as z,
    tile_data as image,

    -- mbtiles columns:
    tile_row,
    tile_column,
    zoom_level,
    tile_data from mbtiles;

-- osmand metainfo:
CREATE TABLE if not exists info(tilenumbering,minzoom,maxzoom,timecolumn,url,rule,referer);
delete from info;
insert into info values('simple',
    6,  -- min zoom level
    15, -- max zoom level
    'no',null,null,null);

You could replace the first
alter table tiles rename to mbtiles;

(doesn't seem to work due to a "cannot alter view" restriction when tested using SQLite Manager add-in) with the following which should have the same effect:
DROP view tiles CREATE VIEW mbtiles 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;

The final min and max zoom levels can also be extracted from the existing metadata table by replacing:

CREATE TABLE if not exists info(tilenumbering,minzoom,maxzoom,timecolumn,url,rule,referer); delete from info; insert into info values('simple', 6, -- min zoom level 15, -- max zoom level 'no',null,null,null);

with

CREATE TABLE if not exists info(tilenumbering,minzoom,maxzoom,timecolumn,url,rule,referer); delete from info; insert into info values('simple', (SELECT value FROM metadata WHERE name='minzoom'), -- min zoom level (SELECT value FROM metadata WHERE name='maxzoom'), -- max zoom level 'no',null,null,null);

I have a note to add:

• z is no longer inverted if the info table declares the `` as anything but BigMap.
• y inversion is no longer needed.

The full specs are now in https://osmand.net/help-online/technical-articles#OsmAnd_SQLite_Spec and you can also read all the SELECTs in https://github.com/osmandapp/Osmand/blob/master/OsmAnd/src/net/osmand/plus/SQLiteTileSource.java .