Fixing the OpenLayers MBTiles Example

Question

I've been trying to implement the OpenLayers Example for reading MBTiles: http://projects.bryanmcbride.com/ol_mbtiles/

Right off, the link to the necessary mbtiles.php script was broken, but I found what I believe to be a copy of it, here: https://gist.github.com/1818011

After exporting my MBTiles file from TileMill, I saw in the SQLite that the mbtiles.php file's SQL query was using the wrong columns and tables for pulling the appropriate tile images.

The original query:

$sql = "SELECT * FROM tiles WHERE zoom_level = $zoom AND tile_column = $column AND tile_row = $row";

What it should be based off what TileMill Exported:

$sql = "SELECT images.tile_data FROM map, images WHERE map.zoom_level = $zoom AND map.tile_column = $column AND map.tile_row = $row AND map.tile_id = images.tile_id";

Once I did that and stopped doing bindColumns on SQLite columns I wasn't using, I could pull the tile images from my MBTiles file using the php script.

That's where I get stuck. I followed the Javascript code from the OpenLayers example linked to above, but the TMS layer keeps calling for tile rows and columns that don't exist.

Here's the relevant bits of my JS code. What I'm not showing is my creation of an OSM base layer. I think the problem lies in the math used to calculate the x and y values in mbtilesURL():

function load_mbtiles(){
            var mbtilesLayer = new OpenLayers.Layer.TMS("MBTiles Overlay", "mbtiles.php", {
                getURL: mbtilesURL,
                isBaseLayer: false
            });

            map.addLayer(mbtilesLayer);
        }

        function mbtilesURL (bounds) {
            var db = "filename.mbtiles";
            var res = this.map.getResolution();
            var x = Math.round ((bounds.left - this.maxExtent.left) / (res * this.tileSize.w));
            var y = Math.round ((this.maxExtent.top - bounds.top) / (res * this.tileSize.h));
            var z = this.map.getZoom();

            return this.url+"?db="+db+"&z="+z+"&x="+x+"&y="+((1 << z) - y - 1);

        }

Can anyone think of why this function would be calling the completely wrong tiles?

Answer 1

It ends up that it was all a projection problem. When I created the map object, I just needed to specify the projections, and then my MBTiles layer was positioned correctly.

var options = {
  projection: "EPSG:900913",
  displayProjection: new OpenLayers.Projection("EPSG:4326")
};

map = new OpenLayers.Map('map', options);

var layer = new OpenLayers.Layer.WMS(
  "OpenLayers WMS",
  "http://vmap0.tiles.osgeo.org/wms/vmap0",
  {layers: 'basic'}
);

map.addLayer(layer);

Answer 2

I have updated and GitHubbed my PHP-MBTiles-Server script, with updated examples showing how to use it in both OpenLayers and Leaflet.

I have tested again and you absolutely can query the "tiles" view from a TileMill export.