updateImage / deleteImage SQL queries

[wilhelmberg]

hey @ibesora very nice blog post and great tool to gain insights into what's using all the space.

I'm trying out the optimization but hitting a blocker when it stops at ✖ Writing output file to ....

I was able to track the problem down to these two queries: https://github.com/ibesora/vt-optimizer/blob/7d17c7a838155cb5613f01113339290ce1c96e64/src/core/VTWriter.js#L118-L132

I don't have the tables map or images in my mbtiles so I changed them to:

UPDATE tiles SET tile_data=(?) WHERE zoom_level=${zoom_level} AND tile_row=${tile_row} AND tile_column=${tile_column}

DELETE FROM tiles WHERE zoom_level=${zoom_level} AND tile_row=${tile_row} AND tile_column=${tile_column}

and then it ran through looking very promising:

  ✔ Parsing VT file contents
  ✔ Parsing the style file
  ✔ Processing tiles
  ✔ Writing output file to style-opti.mbtiles
Process results
• Removed 1493 features in level 2
....
• Removed layer .... from zoom levels 8, 9, 10, 11, 12, 13, 14, 15, 16
....

but unfortunately the resulting mbtiles was emtpy. Looks like everything has been removed.

Looking at the mbtiles spec https://github.com/mapbox/mbtiles-spec/blob/master/1.3/spec.md I don't see any references to map or images.

Any ideas what I'm missing?

[ibesora]

Hey @BergWerkGIS thanks for your feedback. I have never seen an mbtiles without the map and images tables but reading the spec, you are right, those are not guaranteed to exist. If you can share the mbtiles file you are using I can implement a workaround and see what's hapenning. Seeing the spec, tiles can be a view instead of a table so I can see why your modification doesn't work.

Can you share the mbtiles you are using to have a look at it? And can you tell me how it was created?

Thanks!

[ibesora]

Having a look at it, I thought I remembered seeing the map and images tables on some version of the spec. The mapbox@node-mbtiles which is a much more updated project references a now-defunct website mbtiles.org. Looking at it via archiv.org mbtiles.org website I can see that it talks about the map and images tables although it's just a recommendation. In addition to that, the same mapbox@node-mbtiles package includes a schema.sql file used to create the mbtiles that includes those two tables. And in fact, their own mbtiles example file (found here) has them.

I might have just assumed that it was an undocumented feature. If you can send me an mbtiles without those tables to see how it's implemented, I can update the project to support them.

Again, thanks for trying it!

[wilhelmberg]

Thanks for your hints @ibesora 🙏 I had used outdated software to create the mbtiles file, which I updated and don't get the error from the OP anymore.