[gdal-dev] Appending to an existing GeoPackage raster table

[Brad Hards]

I'm trying to find a way to append data to an existing geopackage raster
layer using GDAL.

The specific case is to use different sources (or different configurations
of a source like WMS) at different zoom levels, but extending an existing
table at the same zoom might also be useful.

As an example, consider a rendered map (like a OSM bright or humanitarian
style), where the level of detail you want at level 12 is different to level
8 or 15.
I can easily create a table at level 12, and add overviews, but the
overviews are just the same data made smaller. Instead, I want to render it
differently at each zoom level.

I tried using APPEND_DATASET, which does work for a new table, but not for
adding data to an existing table:
bradh at marginata:~/related_tables$ gdal_translate -projwin -68.1109 18.6664
-65.0914 17.7307 -projwin_srs EPSG:4326 -co APPEND_SUBDATASET=yes -co
TILING_SCHEME=GoogleMapsCompatible  wmssrc.xml -co RASTER_TABLE=out -of GPKG
out.gpkg
Input file size is 524288, 524288
ERROR 1: sqlite3_exec(CREATE TABLE "out" (id INTEGER PRIMARY KEY
AUTOINCREMENT,zoom_level INTEGER NOT NULL,tile_column INTEGER NOT
NULL,tile_row INTEGER NOT NULL,tile_data BLOB NOT NULL,UNIQUE (zoom_level,
tile_column, tile_row));CREATE TRIGGER "out_zoom_insert" BEFORE INSERT ON
"out" FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on table ''out''
violates constraint: zoom_level not specified for table in
gpkg_tile_matrix') WHERE NOT (NEW.zoom_level IN (SELECT zoom_level FROM
gpkg_tile_matrix WHERE lower(table_name) = lower('out'))) ; END; CREATE
TRIGGER "out_zoom_update" BEFORE UPDATE OF zoom_level ON "out" FOR EACH ROW
BEGIN SELECT RAISE(ABORT, 'update on table ''out'' violates constraint:
zoom_level not specified for table in gpkg_tile_matrix') WHERE NOT
(NEW.zoom_level IN (SELECT zoom_level FROM gpkg_tile_matrix WHERE
lower(table_name) = lower('out'))) ; END; CREATE TRIGGER
"out_tile_column_insert" BEFORE INSERT ON "out" FOR EACH ROW BEGIN SELECT
RAISE(ABORT, 'insert on table ''out'' violates constraint: tile_column
cannot be < 0') WHERE (NEW.tile_column < 0) ; SELECT RAISE(ABORT, 'insert on
table ''out'' violates constraint: tile_column must by < matrix_width
specified for table and zoom level in gpkg_tile_matrix') WHERE NOT
(NEW.tile_column < (SELECT matrix_width FROM gpkg_tile_matrix WHERE
lower(table_name) = lower('out') AND zoom_level = NEW.zoom_level)); END;
CREATE TRIGGER "out_tile_column_update" BEFORE UPDATE OF tile_column ON
"out" FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'update on table ''out''
violates constraint: tile_column cannot be < 0') WHERE (NEW.tile_column < 0)
; SELECT RAISE(ABORT, 'update on table ''out'' violates constraint:
tile_column must by < matrix_width specified for table and zoom level in
gpkg_tile_matrix') WHERE NOT (NEW.tile_column < (SELECT matrix_width FROM
gpkg_tile_matrix WHERE lower(table_name) = lower('out') AND zoom_level =
NEW.zoom_level)); END; CREATE TRIGGER "out_tile_row_insert" BEFORE INSERT ON
"out" FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on table ''out''
violates constraint: tile_row cannot be < 0') WHERE (NEW.tile_row < 0) ;
SELECT RAISE(ABORT, 'insert on table ''out'' violates constraint: tile_row
must by < matrix_height specified for table and zoom level in
gpkg_tile_matrix') WHERE NOT (NEW.tile_row < (SELECT matrix_height FROM
gpkg_tile_matrix WHERE lower(table_name) = lower('out') AND zoom_level =
NEW.zoom_level)); END; CREATE TRIGGER "out_tile_row_update" BEFORE UPDATE OF
tile_row ON "out" FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'update on table
''out'' violates constraint: tile_row cannot be < 0') WHERE (NEW.tile_row <
0) ; SELECT RAISE(ABORT, 'update on table ''out'' violates constraint:
tile_row must by < matrix_height specified for table and zoom level in
gpkg_tile_matrix') WHERE NOT (NEW.tile_row < (SELECT matrix_height FROM
gpkg_tile_matrix WHERE lower(table_name) = lower('out') AND zoom_level =
NEW.zoom_level)); END; ) failed: table "out" already exists
ERROR 1: Raster table out not correctly initialized due to missing call to
SetGeoTransform()

At this stage, the only thing I can think of is to create a bunch of tables
and merge them in some manual SQL. Any other suggestions?