Add SQL index in .mbtiles generated

[BenOnTrack]

Currently, I'm using .mbtiles as my tiles source (for display purpose) but also as a database to be queried by the User to easily identify features

I'm using basic SQL query such as

SELECT tile_data FROM tiles
WHERE zoom_level = ? AND tile_column = ? AND tile_row = ?;

but then I need to decode the tile_data, extracts the feature, and check if the feature properties (for instance name contains my query) - It's working great, but can be very slow.

I'm a beginner in SQL but I've read online that if a specific data was "indexed" (for instance name), the query would be much faster (I assume there would be no need anymore to decompress every tile one by one).

The question is : does Tilemaker (or any 3rd party libary) allows for post-processing to perform such tasks ? And, is it the best way to fill my requirement ?

Thanks in advance for your feedback

Ben

[systemed]

Unfortunately this won't work. An .mbtiles is just a collection of binary vector tiles in an SQLite container. SQLite doesn't understand the vector tiles themselves - they're just arbitrary binary blobs as far as it knows - so it can't index their contents.

Traditionally if you wanted to do this, you would take the opposite approach to tilemaker: feed all your data into a Postgres database using osm2pgsql, then create vector tiles on the fly with Martin, Tilekiln or similar. You then have a full queryable SQL database in Postgres. But the vector tile pipeline then becomes very different to the tilemaker way of doing things.

One approach you could potentially look at is outputting text files from Lua, one per thread, containing the objects you want to "index". Open a text file in init_function (with a randomly suffixed name, because you'll have 16 of them or however many threads you have running). Whenever your Lua script in way_function or node_function encounters something you want to index, write this out to the file, together with its lat/lon. Then close the file in exit_function. Your app can then read all these text files in and find the location of features.

[BenOnTrack]

I see, thank you for your feedback.
I'll try your lua workaround see if that could fit my need.

I'm just curious how app such as Organic Maps https://github.com/organicmaps/organicmaps (based on Maps.Me) works though.
Do they have files for tiles + files for "data set" (hence duplicating data information) ?

The current decoding of .mbtiles on-the-go I've implemented is working, but a bit slow (as it needs to look through each tiles, at zoom 14) - today I'm doing something similar to OrganicMaps, where the query only focus on "close" tiles (so if you're in Australia and query a cafe name you know exists in Paris, it won't be returned) - nevertheless OrganicMaps returns the query in a second where my implementation does it in 4 to 6 seconds. So I assume they store "data set / metadata" somewhere to fasten the query lookup.

[systemed]

Historically the maps.me family used its own vector format rather than .mvt. I'm not sure whether it still does.

I do something similar for the cycle.travel app (using tilemaker-produced tiles) and it's generally in the region of a second, so maybe there's some room for optimisation there.

[BenOnTrack]

My code is the following - PWA built with svelte 5 and maplibre-gl-js

db is my mbtiles as a SQLite (using Wasm)
I fix the zoom at 14 since most of my POI have been generated with tilemaker for that zoom.
I go through all layers (i have multiple layers that contains POI data)
I check if the name or name:XXX (where XXX is "en" or "fr" for instance, based on user selection) contains my query
I save all features that match this requiremnt

const stmt = db.prepare(`
  SELECT tile_data, tile_column, tile_row
  FROM tiles
  WHERE zoom_level = 14
  `);
  
  while (stmt.step()) {
  const tileData = stmt.get(0) as Uint8Array;
  const x = stmt.get(1) as number;
  const y = stmt.get(2) as number;
  const isGzipped = tileData[0] === 0x1f && tileData[1] === 0x8b;
  const buffer = isGzipped ? await gunzip(tileData) : tileData;
  
  const vectorTile = new VectorTile(new Protobuf(buffer));
  
  for (const sourceLayer of layers) {
    const layer = vectorTile.layers[sourceLayer];
    if (!layer) continue;
  
    for (let i = 0; i < layer.length; i++) {
      const vectorTileFeature = layer.feature(i);
      const props = vectorTileFeature.properties as Record<string, any>;
  
      const nameMatch = props["name"]?.toLowerCase().includes(lowerQuery);
      const langMatch = props[language]
        ?.toLowerCase()
        .includes(lowerQuery);
  
      if (nameMatch || langMatch) {
        const geojsonFeature = vectorTileFeature.toGeoJSON(
          x,
          (1 << z) - 1 - y,
          z,
        );
  
        // Store raw feature data instead of converting immediately
          matchingFeatures.push({
            geojsonFeature,
            source,
            sourceLayer,
          });
    }
  }
  }
  stmt.finalize();

[systemed]

Mine's in Swift or Kotlin so not directly comparable, but areas for optimisation might be (a) just using one layer, (b) making sure everything in that layer is a single point (so you don't have to reconstruct complex geometries), (c) writing your own simple vector tile geometry reader so you avoid the GeoJSON step.