UserMap: How to convert MBtile into RMapSQLite for FIF

[farfly]

done

Many thanks to RogerF for this solution.

[RogerF]

Based on ideas found on the github forum (thanks!) and an analysis of map files in both formats, I came to the sql script below.
As requested be Petr, I post here my tutorial for the conversion from .mbtiles maps to .sqlitedb maps for FIF.
The "upload attachment" did not work so you should copy/paste the sql script below onto a file "Mbtiles_to_sqlitedb_RogerF.sql"

For the conversion of maps, all you need is to make a copy of the .mbtiles file and rename the copy as .sqlitedb
Then you open it with an sqlite application; the script has been developed and tested with the free application "DB browser for sqlite" v3.10.1 found on http://sqlitebrowser.org.
Then you paste my sql script below in the "Execute SQL" window and execute it.

Feel free to use this script.

Good flights - fly safe!
RogerF
EBTX, MUAC, Belga Radar

-------------------------------------------------------------------- script --------------------------------------------------------------------
-- Conversion of "mbtiles" map into "sqlitedb" map
-- Roger Fraikin - V1 - June 2018

--- Use is simple:
--- 1. Copy the xxxx.mbtiles file to a new xxxx.sqlitedb file
--- 2. Open this file with an sqlite application (e.g. "DB Browser for sqlite")
--- 3. Load this "Mbtiles_to_sqlitedb_RogerF.sql" script as sql query and let it run
--- 4. Confirm the final cleanup (the VACUUM command triggers a warning)
--- 5. Close the sqlite application, confirming the "save the modified database" message if any.
---
--- Development notes
---
--- N1. The sql command "Rename columns" is not supported by sqlite (despite many requests on the dedicated forum, but probably for good reasons).
--- The workaround is to copy all elements onto a new table with the adequate structure for sqlitedb.
---
--- N2. The formula to translate the mbtiles "tile_row" data into sqlitedb "y" is: y = 2^z - & - tile_row
--- Unfortunately, sqlite only supports basic mathematics like +,-,*,/%, not the "^" exponential operator.
--- The workaround is to perform separate queries for each zoom levels
---
--- N3. Especially with low zoom levels, there is a chance the "y" conversion leads to a "duplicate indexes" error
--- That is why I only create the index after the conversion has been made
---
--- N4. The query can take some time for big maps; that is why I added a series of SELECT messages to monitor the progress
--- Be patient for the final cleanup (VACUUM) as the DB is completely copied/rebuilt.
---
--- Feel free to use this script to convert your maps

CREATE TABLE sqlitedb_tiles
(
x int,
y int,
z int,
s int,
image blob,
PRIMARY KEY (x,y,z,s)
);
SELECT "New table created";

INSERT INTO sqlitedb_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;
SELECT "Data copied";

--- suppress old table "tiles"
DROP TABLE tiles;
SELECT "Old table dropped";

--- suppress old table "tiles" and rename the new one to "tiles"
ALTER TABLE sqlitedb_tiles RENAME TO tiles;
SELECT "New Table renamed";

--- no need to keep the old "metadata" table
DROP TABLE metadata;
SELECT "Metadata dropped";

--- convert mbtiles data into sqlitedb data assuming that minimum zoom level is 3 and maximum is 16
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 = 65533 - y where z = 16;
SELECT "y converted";

UPDATE tiles SET z = 17 - z;
SELECT "z converted";

--- create the INFO table (required by sqlitedb) with the min and max zoom level (strangely, these values are as coded in mbtiles, not the sqlitedb zoom levels)
CREATE TABLE info (minzoom, maxzoom, url);
INSERT INTO info (minzoom, maxzoom) SELECT min(z), max(z) FROM tiles;
SELECT "INFO table created";

--- craete the required index "IND"
CREATE INDEX IND on tiles(x,y,z,s);
SELECT "IND Index created";

--- cleanup possible garbage and reorganise the indexes - this can take a while and you need to confirm a warning message
SELECT "Cleanup running";
VACUUM;
SELECT "Conversion completed";
-------------------------------------------------------------------- script --------------------------------------------------------------------

[RogerF]

A new post has been created with my tutorial for the conversion mbtiles => sqlitedb.
Here is the link: http://funair.cz/domains/funair.cz/forum/viewtopic.php?f=4&t=6045&p=8766#p8766

[kitercuda]

Version 22.50 supports MBTiles map.
Simple copy your xxx.mbtiles to .../Maps/MBTiles folder at your Android device.

[RogerF]

As from today, FlyIsFun supports mbtiles as well !
Many thanks to Petr for this implementation.
I will save a lot of conversion time and reduce my maps archive size !

[Tonio]

Hi Roger, Hi Petr

Nice

Roger
Could you provide us with some good source of MBtiles maps
- for Europe
- for Africa
- for Asia
...

Thanks

Tonio

[RogerF]

An interesting source of tiles is https://www.openflightmaps.org/ proposing monthly (AIRAC cycle) updates and covering many countries (excl. France).
There you get tiles, that can be easily assembled to build a .mbtiles file using mbutils https://pypi.org/project/mbutil/0.0.1/

Another way is to use MOBAC; it can build both mbtiles and sqlitedb databases.

There are also a number of commercial maps available in this format (Cartabossy, France IGN).

A number of other gps applications also propose and support mbtiles (e.g. locusmap).
Using the mbtiles format allow for keeping only one version of the maps and thereby saving SD memory .

Sorry, I do not fly in Asia and Africa

[kitercuda]

Version 22.50 supports MBTiles maps.
Copy your xxx.mbtiles to .../Maps/MBTiles folder at your Android device.

A lot of aviation *.mbtiles were created with zoom level 9 and more. So if you use the default FIF settings you do not see the map with FIF zoom 40 and 60nm.
If you want to see map (zoom 40, 60nm] long press compass rose for screen customize, roll down and and press button "Load default high map resolution" . But this button - set all screen settings to factory deafult. The second possibility is set zoom level manually - for zoom level 40nm and 60nm set MOBAC zoom level 9.
Warning - if the graphical card at your device has not enough memory, it can cause application crash - out of memory.

[rotaxquax]

hi ,
as I am only a small operator, I don't understand the function of that MBtiles feature.
Are these special kind of maps or special overlays?
Do I have to "switch " it on by any button?
I organised some directory, / maps/MBtiles/ did some files in it (ED_256.tiles) from the
openflight maps homepage and....
I see no effect..
Thank you for any explanation.
Cheers
harry

[RogerF]

Mbtiles is a tiles database format.
Previously, FIF only supported the. Sqlitedb format.
Now it can also display tiles provided in mbtiles format.

If you do not have .Mbtiles maps, just forget about this new feature.

Similar to sqlitedb, an mbtiles file contains all the tiles in a single file.

Another way is to copy the whole folders/tiles structure to a folder in the FlyIsFun Maps folder.
In this case you should NOT copy your map in the sqlitedb or mbtiles folders, but create a folder with the name of the map in the Maps folder. Then FIF will be able to find the map and select it in this maps menu.

Maybe a good time to read the manual if this is not clear enough...