Converting MBTiles sqlite into physical tile images for LeafletJS

I have used a program called Tilemill to create a map and export it into MBTiles format. This is essentially SQLite flat-file database that contains the tiles.

If you were to open a .mbtiles file in SQLite Browser, the data I need can be found here:

There are other tables on this sqlite db, like map but it contains similar data/rows as tiles table. Most of the other tables are empty.

Based on this; I have written the following PHP script to connect to a given .mbtiles file and export the tiles out of the SQLite databse into physical tile images in this directory structure: OutputDir/ZoomLevel/ColumnIndex/RowIndex.jpg - which is required format for Leaflet JS library.

This is my PHP Script that convert .mbtiles to individual jpeg tile images in the LeafletJS format:

<?php

// (C) 2016 By latheesan Kanesamoorthy

// Script Config
$fileName = 'NMM2.mbtiles';

/**************************
 * DO NOT EDIT BELOW HERE *
 **************************/

// Internal Config
error_reporting(E_ALL);
set_time_limit(0);
ini_set('memory_limit', '1024M');
ini_set('display_errors', '1');

// Helper function to recursively remove directory
// Source: http://php.net/rmdir
function rrmdir($dir) {
    if (is_dir($dir)) {
        $objects = scandir($dir);
        foreach ($objects as $object) {
            if ($object != "." && $object != "..") {
                if (filetype($dir."/".$object) == "dir") 
                    rrmdir($dir."/".$object); 
                else 
                    unlink($dir."/".$object);
            }
        }
        reset($objects);
        rmdir($dir);
    }
}

// Remove old output dir
$outputDir = 'MBTiles';
if (is_dir($outputDir))
    rrmdir($outputDir);

// Helper function to show error message
function ShowError() {
    ShowMessage(true, func_get_args());
}

// Helper function to show info message
function ShowInfo() {
    ShowMessage(false, func_get_args());
}

// Generic method to show message
function ShowMessage() {
    $args = func_get_args();
    $isError = array_shift($args);
    $messageParams = array_shift($args);
    $messageText = array_shift($messageParams);
    echo Colorize($isError, sprintf("[ %s ] %s :: %s",
        date('H:i:s A'),
        $isError ? 'ERROR' : ' INFO',
        vsprintf($messageText,
            $messageParams))) . "\n";
}

// Method to prettify output message
function Colorize($isError, $messageText) {
    $out = $isError ? '[41m' : '[44m';
    return chr(27) . $out . ' '. $messageText . ' ' . chr(27) . '[0m';
}

// Init
echo "\n";
$start = microtime(true);
ShowInfo('Processing file: %s', $fileName);

// Anticipate errors
try
{
    // Create a pdo connection to sqlite file
    $conn = new PDO("sqlite:$fileName");

    // Debug
    ShowInfo('Connected to SQLite database file; querying available tiles');

    // Calculate total number of tiles available
    $sql = "SELECT count(*) FROM `tiles`"; 
    $result = $conn->prepare($sql);
    $result->execute(); 
    $tileCount = $result->fetchColumn();

    // Check tile count
    if (!$tileCount)
        throw new Exception('SQLite file does not contain any tiles');

    // Debug
    ShowInfo('Found %d tiles in the SQLite file', $tileCount);

    // Query database
    $sql = "SELECT * FROM `tiles`";
    $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);

    // Iterate through results
    $jobCount = 1;
    while ($row = $q->fetch(PDO::FETCH_BOUND))
    {
        // Debug
        ShowInfo('Converting tile ( %d of %d ) : %d-%d-%d',
            $jobCount,
            $tileCount,
            $zoom_level,
            $tile_column,
            $tile_row);

        // Create tile directory recursively
        $tileDir = "$outputDir/$zoom_level/$tile_column";
        if (!is_dir($tileDir))
            mkdir($tileDir, 0777, true);

        // Convert tile blob data to image
        file_put_contents("$tileDir/$tile_row.jpg", $tile_data);

        // Increment job counter
        $jobCount++;
    }

    // Clean-up
    $conn = null;
}
catch (Exception $ex)
{
    // Error
    ShowError('%s @ %s:%d',
        $ex->getMessage(),
        basename($ex->getFile()),
        $ex->getLine());
}

// Finished
$executionTime  = ((microtime(true) - $start) / 60);
ShowInfo('Process completed in %.6f seconds', $executionTime);
echo "\n";

I ran this script with PHP-CLI in windows and it works like this:

The output dir is MBTiles. If I go into it, I'll see the Zoom Level dirs like this:

If I go into the first zoom level 1, I will see the column index dirs like this:

If I go into the first column 0, I will see the row tiles like this:

So far everything has worked (based on what I saw in the SQLite database and what was exported).

So, I wrote the following html script to use the exported tiles using Leadlet JS library like this:

<!doctype html>
<html lang="en">
    <head>
        <title>Map Test</title>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
        <link rel="stylesheet" href="http://ift.tt/1KzQLRC" />
        <style type="text/css">
            html { height: 100% }
            body { height: 100%; margin: 0; padding: 0; }
            #map { height: 100%; }
        </style>
        <script src="http://ift.tt/20iI1kL"></script>
    </head>
    <body>
        <div id="map"></div>
        <script type="text/javascript">

            (function () {

                // Config
                var isOnline = false;

                // Init
                var map = 
                    L.map('map')
                    .setView([51.56341, 14.67773], 6);

                // Set tile layer & add to map
                L.tileLayer('MBTiles/{z}/{x}/{y}.jpg', {
                    minZoom : 6,
                    maxZoom : (isOnline ? 18 : 8),
                    attribution: '&copy; <a target="_blank" href="http://ift.tt/VGR6Z6">OpenStreetMap</a> contributors'
                })
                .addTo(map);

                // Test marker
                L.marker([51.5, -0.09]).addTo(map)
                    .bindPopup("<b>Hello world!</b><br />I am a <u>html</u> popup.");

            })();

        </script>
    </body>
</html>

When I execute the above html in browser, the map is rendered all scrambled like this:

---

The problem looks to be related to the column index dirs & row index file names.

I previously used a program called Mobile Atlas Creator 2.0.0 beta 1 to export a selected region of a map (MapQuest as Source) and the exported tiles looked like this:

Note* MapQuest is the export dir name

Here we see the zoom levels I have exported (6, 7 & 8).

So, if I go into the first zoom level 6, I will see the column index dirs:

As you can see, unlike my export, the MOBAC exported tiles column index dirs do not start at 0, they start at a specific range.

If I go into the first column index dir 30 for example, I will see the row index tile images:

As you can see again, the index do not start at 0 like my export, they also start at a specific range.

This MOBAC exported tiles do however render correctly:

---

So, my questions:

1. Am I doing something wrong when I export the .mbtiles to image tiles?
2. Is it not possible to achieve what I am trying? i.e. export mbtiles to image tiles
3. Are the correct column / row indexes per tiles stored somewhere else in the sqlite db?
4. Was the map incorrectly exported into .mbtiles format out of TileMill?
5. Is there a way to fix this? i.e. tell Leaflet JS that the column/row index actually start at 0 for my offline map tiles?