Feature Request: Support for SQLite databases as background layers

[abdullahO2]

Hi @iandees,

First off, thanks for developing and maintaining the josm-mbtiles plugin! It's very useful.

I'm writing to inquire about the possibility of adding support for SQLite databases (.sqlite or .db) as background layers, in addition to the existing MBTiles support. Many datasets are available in this format, and having direct support within JOSM would be very convenient.

I understand that MBTiles is essentially a specialized form of SQLite, so perhaps there is a relatively straightforward way to adapt the plugin to handle general SQLite databases containing tile data. I'm happy to provide more details about the specific SQLite structure I'm working with if that would be helpful.

Would you be open to considering this feature? If so, I'd be happy to help in any way I can, such as testing or providing sample data.

Thanks again for your time and consideration.

[iandees]

Can you provide an example of data in a sqlite database that you would want to use in JOSM?

[abdullahO2]

Certainly! Here are two example files illustrating the type of SQLite databases I'd like to use in JOSM: RMaps_SQLite.zip

[iandees]

Those both appear to be mbtiles data. If you rename the file to end with .mbtiles this JOSM plugin should use them.

[abdullahO2]

Thanks for taking a look! I've tried renaming the files to .mbtiles as you suggested, but JOSM still reports an error: Could not read file '[filename].mbtiles'. Error is: Loading MBTIles failed.

This suggests there might be a minor difference in the internal structure compared to standard MBTiles files. I understand you're busy, and creating specific examples might be time-consuming for both of us. Would it be possible to point me towards the part of the plugin's code that handles the MBTiles loading? I might be able to identify the discrepancy and perhaps even propose a small modification to accommodate these slightly different SQLite structures.

Alternatively, if there's a tool or specification you recommend for validating MBTiles files, I'd be happy to check these files against it and report back the results.

[iandees]

Yep, it looks like these are some proprietary format stored in a SQLite database. The "tiles" table looks similar but the metadata is different.

I think this is out of scope for the MBTiles plugin. I don't want to write custom code to handle the different variations of formats. Whatever system produced the files you shared should be modified to output standard MBTiles format.

[iandees]

Since it was cold out I spent a little time inside looking at this. It seems that the files you posted are from the MOBAC app. There isn't enough information in the database files to be able to load them by themselves. Can you take screenshots of how you're using them? Do you have to specify a zoom range when you load them?

[abdullahO2]

Thanks for looking into this further. Yes, these files were created with MOBAC. They are readily viewable in other applications like Global Mapper (desktop) and OsmAnd (mobile), so the information needed for display is within the files.

The key difference seems to be how zoom ranges are stored. MBTiles uses a metadata table with name and value columns (for minzoom and maxzoom), while these SQLite files use an info table with dedicated minzoom and maxzoom columns. A Python script for converting between MBTiles and a similar SQLite format might offer clues: [link to mbtiles2osmand.py].

[abdullahO2]

Thanks for your help!

To help clarify the differences between MBTiles and the SQLite format I'd like to see supported, here's a summary:

1. File Structure:

   • MBTiles: Standardized format with tiles and metadata tables. tiles contains zoom_level, tile_column, tile_row, and tile_data columns. The metadata table stores information like map bounds, zoom levels, and descriptions.
   • sqlitedb (OsmAnd): Custom format used by OsmAnd with tiles and info tables. tiles includes x, y, z, s, and image columns. info contains maxzoom and minzoom values.

2. Coordinate Handling:

   • MBTiles: Uses standard Web Mercator coordinates, where y increases from top to bottom.
   • sqlitedb: Modifies y values to fit OsmAnd's system: ( y = 2^z - 1 - y ). Zoom levels are reversed as ( z = 17 - z ).
     Modify y values (flip vertically):

y_new = (2 ** z) - 1 - y

Reverse zoom levels:

z_new = 17 - z

3. Tile Format:

   • MBTiles: Typically stores tiles as PNG or JPEG with no transformations.
   • sqlitedb: Supports JPEG compression for tiles to reduce file size.

4. Metadata:

   • MBTiles: Includes descriptive metadata like map name, description, bounds, and attribution.
   • sqlitedb: Lacks a metadata table but uses info to define zoom level ranges.

Given these differences, it may be possible to adapt the plugin to handle sqlitedb by:

• Translating coordinate systems and zoom levels.
• Mapping tiles and info structures to an MBTiles-compatible representation.
• Allowing an option to treat sqlitedb as a specialized MBTiles variant.

I'd be happy to share more examples or test any implementations. Let me know how I can assist further!

---

Note: This comparison was generated using AI-based assistance, and I am not certain of its full accuracy or appropriateness. Please feel free to verify or correct as needed.

---

[abdullahO2]

These are suggestions for modifications to the plugin files using artificial intelligence (I don't expect them to be 100% accurate, but they might help and save some time and effort):

Let's proceed methodically, file by file, implementing changes to support the OsmAnd/MOBAC SQLite format.

1. CacheExportPanel.java: This file handles the export functionality, which we won't be modifying for now since the goal is to import the existing SQLite format. So no changes are needed here yet.

2. MbtilesException.java: This file defines a custom exception. We'll want to make it more generic since we're no longer dealing exclusively with MBTiles.

package org.openstreetmap.josm.plugins.mbtiles;

// Rename the exception class for broader applicability
public class SqliteImportException extends Exception { 

    public SqliteImportException(String string) {
        super(string);
    }

    public SqliteImportException(String string, Throwable e) {
        super(string, e);
    }
}

Change Summary: Renamed MbtilesException to SqliteImportException. This better reflects the broader purpose of the exception. We will update other files to use this new exception name.

3. MbtilesFileImporter.java: This is where we start handling the import process. We'll modify it to accept both .mbtiles and .sqlitedb files.

package org.openstreetmap.josm.plugins.mbtiles;

import static org.openstreetmap.josm.tools.I18n.tr;

import java.io.File;
import java.io.IOException;
import java.util.Arrays; // Import for Arrays.asList

import org.openstreetmap.josm.actions.ExtensionFileFilter;
import org.openstreetmap.josm.gui.MainApplication;
import org.openstreetmap.josm.gui.io.importexport.FileImporter;
import org.openstreetmap.josm.gui.progress.ProgressMonitor;
import org.openstreetmap.josm.tools.Logging;

public class MbtilesFileImporter extends FileImporter {

    // Modify the constructor to accept both .mbtiles and .sqlitedb
    public MbtilesFileImporter() {
        super(new ExtensionFileFilter("mbtiles,sqlitedb", "mbtiles,sqlitedb",
                tr("MBTiles/SQLite tilesets") + " (*.mbtiles, *.sqlitedb)"));
    }

    @Override
    public void importData(File file, ProgressMonitor progressMonitor) throws IOException {
        try {
            // Use a more generic layer name
            SqliteTilesLayer layer = new SqliteTilesLayer(file); // Use updated class name
            MainApplication.getLayerManager().addLayer(layer);
        } catch (SqliteImportException e) { // Use updated exception name
            Logging.logWithStackTrace(Logging.LEVEL_WARN, "Error importing data", e);
            throw new IOException(tr("Loading SQLite tiles failed"), e); // More generic message
        }
    }
}

Change Summary:

• Constructor: Modified the file filter to accept both .mbtiles and .sqlitedb extensions and updated the description.
• importData:
  • Changed the layer type to SqliteTilesLayer (we'll create this class next).
  • Updated the exception handling to use SqliteImportException.
  • Made the error message more generic.

4. MbtilesLayer.java: This is the core of the layer handling. We'll rename it to SqliteTilesLayer.java and make substantial modifications to handle both MBTiles and OsmAnd SQLite formats.

package org.openstreetmap.josm.plugins.mbtiles;

import static org.openstreetmap.josm.tools.I18n.tr;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;

import org.openstreetmap.gui.jmapviewer.tilesources.AbstractTMSTileSource;
import org.openstreetmap.gui.jmapviewer.tilesources.TMSTileSource;
import org.openstreetmap.josm.data.imagery.ImageryInfo;
import org.openstreetmap.josm.data.imagery.ImageryInfo.ImageryBounds;
import org.openstreetmap.josm.data.imagery.ImageryInfo.ImageryType;
import org.openstreetmap.josm.data.imagery.TileLoaderFactory;
import org.openstreetmap.josm.gui.layer.AbstractTileSourceLayer;
import org.openstreetmap.josm.tools.Logging;
import org.sqlite.SQLiteConfig;

// Rename the class
public class SqliteTilesLayer extends AbstractTileSourceLayer {

    private final Connection connection;
    private final boolean isMbtiles; // Flag to track the format

    // Modified constructor
    public SqliteTilesLayer(File dbFile) throws SqliteImportException {
        super(buildImageryInfo(dbFile));
        connection = MbTilesUtils.obtainSqliteDbConnection(dbFile, true); // Use existing utility
        this.isMbtiles = dbFile.getName().toLowerCase().endsWith(".mbtiles"); // Check file extension
        super.tileLoader = new MbtilesTileLoader(this, connection, isMbtiles); // Pass the flag to the tile loader
    }

    private static ImageryInfo buildImageryInfo(File dbFile) throws SqliteImportException {
        // ... (Most of the existing code remains the same)

        try {
            // ... (Existing code for loading SQLite driver and opening connection)

            // Use dynamic queries based on the format
            String zoomQuery, boundsQuery, nameQuery;
            if (dbFile.getName().toLowerCase().endsWith(".mbtiles")) {
                zoomQuery = "SELECT max(zoom_level) AS max, min(zoom_level) AS min FROM tiles";
                boundsQuery = "SELECT value FROM metadata WHERE name = 'bounds'";
                nameQuery = "SELECT value FROM metadata WHERE name = 'name'";
            } else { // OsmAnd/MOBAC format
                zoomQuery = "SELECT maxzoom, minzoom FROM info";
                boundsQuery = null; // No bounds info in this format (handle later)
                nameQuery = dbFile.getName();  // Use filename as name 
            }

            // ... (rest of the try block – use zoomQuery, boundsQuery, nameQuery)

            }
        } catch (SQLException e) {
            throw new SqliteImportException(tr("Could not read file '{0}'.\nError is:\n{1}", dbFile.getName(), e.getMessage()), e); // More informative message
        } finally {
             // ... (Existing finally block)
        }

        ImageryInfo info = new ImageryInfo(tr("SQLite Tiles: {0}", name));
                if (bounds != null) {
                    info.setBounds(bounds);
                } else {
                    // If no bounds info is available (e.g. OsmAnd), set some default bounds or handle accordingly
                    // This might require user input or some estimation based on available tiles
                }

        // ... (rest of the method remains the same)
    }

    // ... (Rest of the class –  destroy(), getTileLoaderFactory(), getTileSource() remain the same)

}

Change Summary:

• Renamed to SqliteTilesLayer: Reflects the broader purpose.
• Added isMbtiles flag: Used to differentiate between MBTiles and OsmAnd formats.
• Modified Constructor: Now checks the file extension to set the isMbtiles flag and passes it to the MbtilesTileLoader.
• Modified buildImageryInfo:
  • Added dynamic SQL queries based on the isMbtiles flag to handle differences in table and column names for metadata and zoom levels.

5. MbtilesPlugin.java: This file registers the importer. We need to update it to reflect the changes we've made.

package org.openstreetmap.josm.plugins.mbtiles;

import org.openstreetmap.josm.actions.ExtensionFileFilter;
import org.openstreetmap.josm.gui.preferences.PreferenceSetting;
import org.openstreetmap.josm.plugins.Plugin;
import org.openstreetmap.josm.plugins.PluginInformation;

public class MbtilesPlugin extends Plugin {

    public MbtilesPlugin(PluginInformation info) {
        super(info);
        ExtensionFileFilter.addImporter(new MbtilesFileImporter()); // Importer remains the same
        ExtensionFileFilter.updateAllFormatsImporter();
    }

    @Override
    public PreferenceSetting getPreferenceSetting() {
        return new CacheExportPanel(); // This can remain for now, or be removed/modified later
    }
}

Change Summary: No changes needed in this file as the MbtilesFileImporter class name remains the same (even though its internal logic was modified). 6. MbtilesTileLoader.java: This is where the tile loading logic resides. We need to adjust it to handle the Y-coordinate and zoom level differences.

package org.openstreetmap.josm.plugins.mbtiles;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Logger;

import org.openstreetmap.gui.jmapviewer.FeatureAdapter;
import org.openstreetmap.gui.jmapviewer.OsmTileLoader;
import org.openstreetmap.gui.jmapviewer.Tile;
import org.openstreetmap.gui.jmapviewer.interfaces.TileJob;
import org.openstreetmap.gui.jmapviewer.interfaces.TileLoaderListener;

public class MbtilesTileLoader extends OsmTileLoader {
    private static final Logger LOG = FeatureAdapter.getLogger(MbtilesTileLoader.class.getCanonicalName());

    private final Connection connection;
    private final boolean isMbtiles; // Add the format flag

    // Modified constructor
    public MbtilesTileLoader(TileLoaderListener listener, Connection conn, boolean isMbtiles) {
        super(listener);
        this.connection = conn;
        this.isMbtiles = isMbtiles;
    }

    @Override
    public TileJob createTileLoaderJob(final Tile tile) {
        return new TileJob() {
            @Override
            public void run() {
                try {
                    tile.initLoading();
                    Statement stmt = connection.createStatement();

                    // Dynamically build the SQL query based on the format
                    String sql;
                    if (isMbtiles) {
                        int invY = (int) Math.pow(2, tile.getZoom()) - 1 - tile.getYtile();
                        sql = "SELECT tile_data FROM tiles WHERE zoom_level=" + tile.getZoom() + " AND tile_column=" + tile.getXtile() + " AND tile_row=" + invY + " LIMIT 1";
                    } else { // OsmAnd/MOBAC
                        sql = "SELECT image FROM tiles WHERE z=" + (17 - tile.getZoom()) + " AND x=" + tile.getXtile() + " AND y=" + tile.getYtile() + " LIMIT 1"; // Adjusted zoom and y
                    }

                    ResultSet rs = stmt.executeQuery(sql);

                    // ... (rest of the try block remains the same)
                 } catch (SQLException e) {

                    //Improved logging and error handling
                    String errorDetails;
                    try (Statement statement = connection.createStatement();
                         ResultSet resultSet = statement.executeQuery("SELECT name, value FROM metadata OR IGNORE SELECT name, value FROM info;")) {
                        errorDetails = "";
                        while (resultSet.next()) {
                            errorDetails = errorDetails + resultSet.getString("name") + ": " + resultSet.getString("value") + ",\n";
                        }
                    } catch (Exception ex) {
                        Logging.warn(ex);
                    }

                    LOG.throwing(this.getClass().getName(), "createTileLoaderJob", e);

                    tile.setError(e.getMessage() + errorDetails);
                    listener.tileLoadingFinished(tile, false);

                } catch (IOException e) {
                    LOG.throwing(this.getClass().getName(), "createTileLoaderJob", e);
                    tile.setError(e.getMessage());
                    listener.tileLoadingFinished(tile, false);
                }
            }

            // ... (Rest of the TileJob class – submit() and submit(boolean) remains the same)

        };
    }
}

Change Summary:

• Added isMbtiles flag: Received from the constructor.
• Modified createTileLoaderJob: Now dynamically constructs the SQL query based on the isMbtiles flag. The OsmAnd/MOBAC query uses the adjusted zoom level (17 - tile.getZoom()) and the original tile.getYtile() without flipping.

7. MbtilesTileLoaderFactory.java: This factory creates instances of the tile loader. We'll update it to pass the isMbtiles flag.

package org.openstreetmap.josm.plugins.mbtiles;

import java.sql.Connection;
import java.util.Map;

import org.openstreetmap.gui.jmapviewer.interfaces.TileLoader;
import org.openstreetmap.gui.jmapviewer.interfaces.TileLoaderListener;
import org.openstreetmap.josm.data.imagery.TileLoaderFactory;

public class MbtilesTileLoaderFactory implements TileLoaderFactory {

    private final Connection connection;
    private final boolean isMbtiles; // Add the format flag

    // Modified constructor
    public MbtilesTileLoaderFactory(Connection connection, boolean isMbtiles) {
        this.connection = connection;
        this.isMbtiles = isMbtiles;
    }

    @Override
    public TileLoader makeTileLoader(TileLoaderListener listener, Map<String, String> headers, long minimumExpiryTime) {
        return new MbtilesTileLoader(listener, this.connection, this.isMbtiles); // Pass the flag
    }
}

Change Summary:

• Added isMbtiles flag to the constructor and as a private member.
• Modified makeTileLoader to pass the isMbtiles flag to the MbtilesTileLoader constructor.

8. MbTilesUtils.java: No changes are needed in this file.

Final Important Steps and Considerations:

• Compile and Test: Thoroughly test the modified plugin with both .mbtiles and .sqlitedb files to ensure everything works correctly.
• Error Handling: The provided code includes improved error handling to give you more detailed error messages during testing. Pay close attention to these messages to pinpoint any remaining issues.
• Bounds Handling for OsmAnd/MOBAC: The code in SqliteTilesLayer.java for handling bounds when loading OsmAnd/MOBAC files currently includes a placeholder comment. You will need to implement logic to either estimate the bounds based on the available tiles or prompt the user to define the bounds. This is essential for correct display in JOSM. You could consider adding a dedicated option in the preferences for users to specify custom bounds for non-MBTiles formats. This might be handled as another preference or through a dialog that appears if bounds are not found in the database.
• Refactoring: Consider renaming classes and variables (e.g., MbtilesTileLoader, MbtilesTileLoaderFactory) to more generic names like SqliteTileLoader, SqliteTileLoaderFactory for better consistency.

This detailed, step-by-step guide should help you modify the JOSM plugin to support the OsmAnd/MOBAC SQLite format. Remember to test thoroughly and refine the bounds handling for a complete solution. If you encounter further issues, providing specific error messages and relevant code snippets will help in troubleshooting.