pVqHPZNi

1-- Unfortunately osmand's .sqlitedb and .mbtiles use the same table name, but with different columns
2alter table tiles rename to mbtiles;
3
4-- osmand uses inverted y numbering (compared to mbtiles)
5-- The formula to calculate inverted y is: y' = (2^z - 1) - y
6-- As sqlite does not support pow() function, here's a table of precalculated (2^z-1) for z from 0 to 25.
7create table maxy (y integer, z integer);
8CREATE UNIQUE INDEX IF NOT EXISTS maxy_index on maxy (z,y);
9
10insert into maxy(y,z) values(0,0);
11insert into maxy(y,z) values(1,1);
12insert into maxy(y,z) values(3,2);
13insert into maxy(y,z) values(7,3);
14insert into maxy(y,z) values(15,4);
15insert into maxy(y,z) values(31,5);
16insert into maxy(y,z) values(63,6);
17insert into maxy(y,z) values(127,7);
18insert into maxy(y,z) values(255,8);
19insert into maxy(y,z) values(511,9);
20insert into maxy(y,z) values(1023,10);
21insert into maxy(y,z) values(2047,11);
22insert into maxy(y,z) values(4095,12);
23insert into maxy(y,z) values(8191,13);
24insert into maxy(y,z) values(16383,14);
25insert into maxy(y,z) values(32767,15);
26insert into maxy(y,z) values(65535,16);
27insert into maxy(y,z) values(131071,17);
28insert into maxy(y,z) values(262143,18);
29insert into maxy(y,z) values(524287,19);
30insert into maxy(y,z) values(1048575,20);
31insert into maxy(y,z) values(2097151,21);
32insert into maxy(y,z) values(4194303,22);
33insert into maxy(y,z) values(8388607,23);
34insert into maxy(y,z) values(16777215,24);
35
36-- Create a view compatible with both mbtiles and osmand
37CREATE VIEW if not exists tiles as select
38    -- osmand sqlitedb columns: x,y,z,image
39    tile_column as x,
40    -- (2^z-1)-y:
41    (select maxy.y - tile_row from maxy maxy where maxy.z = zoom_level) as y,
42    zoom_level as z,
43    tile_data as image,
44
45    -- mbtiles columns:
46    tile_row,
47    tile_column,
48    zoom_level,
49    tile_data from mbtiles;
50
51-- osmand metainfo:
52CREATE TABLE if not exists info(tilenumbering,minzoom,maxzoom,timecolumn,url,rule,referer);
53delete from info;
54insert into info values('simple',
55    6,  -- min zoom level
56    15, -- max zoom level
57    'no',null,null,null);