Tiled Map Storage in SQLite

Locus App allows offline sqlite format maps. I was always curious about the self-contained maps specially because I love sqlite.

So last evening I selected a small area on Locus and asked it to download in SQLite format. This GIF should show you how to download the maps on locus for offline usage. Remember to choose the 'Separate Map (Sqlite)' as the format to save for this experiment.

This slideshow requires JavaScript.

Once the download was complete, I copied the same to PC to explore. I opened it in std sqlite browser. The database is a normal database (and not spatialite). So you can manipulate it using standard SQL queries. With a bit of search I figured that this format is called RMaps SQLite format. RMaps is one of the earliest, open source offline android map application. It's the format they used for their maps. I tried to find the documentation, but couldn't. I derived some of the details by exploring the schema and code of RMaps.

CREATE TABLE tiles (x int, y int, z int, s int,
 image blob, PRIMARY KEY (x,y,z,s))
-- x =
-- y = 
-- z = 17-Zoom 
-- s = timestamp 
-- image = blob of jpeg image of size 256x256 px

CREATE TABLE android_metadata (locale TEXT);

CREATE TABLE info(minzoom,maxzoom)
-- minzoom - min of column z in tiles table
-- maxzoom - max of column z in tiles table

But then exploring further I found MBTiles. It has detailed specification on GitHub. MBTiles is also a simple SQLite format. Similar to RMaps. So I downloaded MBtiles using Mobac and opened it using SQLite browser.

CREATE TABLE metadata (name text, value text)
-- metadata table is used as a key/value store for settings. 
-- Five of them are important. Five keys are required
-- including image format. 

CREATE TABLE tiles (zoom_level integer, tile_column integer,
 tile_row integer, tile_data blob)
-- Quite straight forward column names.

As a next step to exploring I wrote a simple tile server to expose to MBTiles to the outside world and accessed it using leaflet on the front end.
On the server side, just query and serve

$zoom = $_GET['z'];
$tile_column = $_GET['x'];
$tile_row = $_GET['y'];
$conn = new PDO("sqlite:mb.mbtiles");
$sql = "SELECT * FROM tiles WHERE zoom_level = $zoom AND tile_column = $tile_column AND tile_row = $tile_row";
$q = $conn->prepare($sql);
$q->execute();
$q->bindColumn(1, $zoom_level);
$q->bindColumn(2, $tile_column);
$q->bindColumn(3, $tile_row);
$q->bindColumn(4, $tile_data, PDO::PARAM_LOB);

while($q->fetch())
{
	header("Content-Type: image/jpg");
	echo $tile_data;
}

On client side make sure to use your own tile server

var mbTiles = new L.tileLayer('server_mb.php?z={z}&x={x}&y={y}', {
   tms: true,
   attribution: 'OpenStreetMap, Mobac - MB Tiles',
   opacity: 0.7
});
map = new L.Map("map",{
		zoom: 16,
		center: <sup class='footnote'><a href='#fn-4524-1' id='fnref-4524-1' onclick='return fdfootnote_show(4524)'>1</a></sup>,
		layers: [mbTiles]
	});

It works perfectly fine. So now I have my simple tile server. Locus also supports MBtiles format. What better do I want? I have an offline MBTiles sqlite for my area now. Now I dont need to be online to explore the maps.

Of course I tried running tile server rm maps. But tiles keep shifting towards top right. Not sure why, I didn't invest time to explore it. All the code is on GitHub if you want to explore.

But for now MBTiles will be the format of choice for offline maps. Both for mobile and web.