Unable to open existing output MBTiles for update / append with ogr2ogr

[kueda]

Expected behavior and actual behavior.

When I try to update an existing MBTiles vector layer with new data at a zoom level that has not yet been written to the file, I get an Unable to open existing output datasource error even though the output file exists and is writable.

Steps to reproduce the problem.

1. Download and unzip the test input GeoJSON: test.geojson.zip
2. ogr2ogr test.mbtiles test.geojson -dsco MINZOOM=3 -dsco MAXZOOM=13 -lco MINZOOM=3 -lco MAXZOOM=12 should successfully create an MBTiles file
3. ogr2ogr -update -append test.mbtiles test.geojson -lco MINZOOM=13 -lco MAXZOOM=13 yields this error: ERROR 1: Unable to open existing output datasource 'test.mbtiles'.
4. ogrinfo test.mbtiles yields the following:

INFO: Open of `test.mbtiles'
      using driver `MBTiles' successful.
Metadata:
  ZOOM_LEVEL=5
  name=test
  description=
  version=2
  minzoom=0
  maxzoom=5
  center=-122.1058549,37.7416428,0
  bounds=-123.1461176,37.2155962,-121.0655921,38.2676893
  type=overlay
  format=pbf
  scheme=tms
1: test (Multi Polygon)

Operating system

Ubuntu 20.04.1 LTS

GDAL version and provenance

$ apt-cache showpkg gdal-bin
Package: gdal-bin
Versions: 
3.0.4+dfsg-1build3 (/var/lib/apt/lists/archive.ubuntu.com_ubuntu_dists_focal_universe_binary-amd64_Packages) (/var/lib/dpkg/status)

[jratike80]

Confirmed with GDAL 3.2. Output from -- debug on

GDAL: GDALOpen(test.geojson, this=0000018F7237CD90) succeeds as GeoJSON.
MBTILES: SELECT value FROM metadata WHERE name = 'minzoom' UNION ALL SELECT value FROM metadata WHERE name = 'maxzoom'
SQLite: 2 features read on layer 'SELECT'.
MBTILES: SELECT value FROM metadata WHERE name = 'bounds'
SQLite: 1 features read on layer 'SELECT'.
MBTILES: SELECT tile_data FROM tiles WHERE tile_column = 1316 AND tile_row = 5023 AND zoom_level = 13
MBTILES: SELECT tile_data FROM tiles WHERE zoom_level = 13 LIMIT 1
SQLite: 10 features read on layer 'SELECT'.
MBTILES: SELECT value FROM metadata WHERE name = 'json'
SQLite: 1 features read on layer 'SELECT'.
GDAL: GDALOpen(test.mbtiles, this=0000018F74153850) succeeds as MBTiles.
OGR: Unloading VirtualOGR module
GDAL: GDALClose(test.mbtiles, this=0000018F74153850)
ERROR 1: Unable to open existing output datasource `test.mbtiles'.
GDAL: GDALClose(test.geojson, this=0000018F7237CD90)
GDAL: In GDALDestroy - unloading GDAL shared library.

[rouault]

The driver has no update capabilities. This is write-once support only. If you want to add several datasets in a MBTiles file, you should use an intermediate format like GeoPackage, and convert it to MBTiles

[jratike80]

How users can know that (exept by reading the documentation, that does not mention this limitation yet)? If I look it right both gpkg and MBTiles report the same capabilities with ogrinfo/gdalinfo

ogrinfo --format MBTiles
Format Details:
  Short Name: MBTiles
  Long Name: MBTiles
  Supports: Raster
  Supports: Vector
  Extension: mbtiles
  Help Topic: drivers/raster/mbtiles.html
  Supports: Open() - Open existing dataset.
  Supports: Create() - Create writable dataset.
  Supports: CreateCopy() - Create dataset by copying another.

[rouault]

How users can know that

They can't. It is a bit tricky here since update mode is possible for the raster part of the driver, but not the vector one. And actually we don't have a metadata item to indicate if update is supported ( well, in raster mode, Create() availabiliity more or less implies update mode ). The doc should at least be updated to make that limitation clearer.

[kueda]

Thanks all, and thanks for updating the docs. A few additional questions:

1. Are there plans to support updating vector MBTiles? If that's possible, should I open another issue for that?
2. Should all intermediate formats that support multiple layers work the same? I was able to follow your advice and make it work with a GeoPackage intermediary, but using a PostGIS intermediary seems to take... forever, or at least long enough that it seems like it will never end. Here's what my command looks like when I try to write two PostGIS tables to the same MBTiles layer at different zoom ranges:

ogr2ogr -f MBTILES \
  test-pgsql.mbtiles \
  PG:dbname=osm_ways \
  -dsco MINZOOM=7 \
  -dsco MAXZOOM=13 \
  -dsco CONF='
    {
      "osm_ways_highways": {
        "target_name": "osm_ways",
        "minzoom": 7,
        "maxzoom": 12
      },
      "osm_ways": {
        "target_name": "osm_ways",
        "minzoom": 13,
        "maxzoom": 13
      }
    }'

Doing exactly the same thing but writing from a GeoPackage to MBTiles works fine. Am I missing something?

[rouault]

Closing. It doesn't seem realistic to implement that capability / too particular use case to fit in the scope of GDAL