[GIS] Remove a raster from Geopackage

gdal-translategeopackageraster

I'm a beginner in the world of geopackages.
I learnd from here and here how to use gdal_translate (and gdaladdo) to import rasters in an existing geopackage or to create a new one. Works fine!
But how can I remove one raster that I don't want anymore in the dataset?

Edit after applying method described in user30184's answer: (I quote with copy-paste)
Step 1 

$ ogrinfo -sql "drop table new_table" 20170831_Expertise_Elia_Gblx-Auvl.gpkg

INFO: Open of '20170831_Expertise_Elia_Gblx-Auvl.gpkg'
using driver `GPKG' successful.

Step 2 

$ ogrinfo -sql "delete from gpkg_contents where table_name='new_table'" 20170831_Expertise_Elia_Gblx-Auvl.gpkg

INFO: Open of '20170831_Expertise_Elia_Gblx-Auvl.gpkg'
using driver `GPKG' successful.

Step 3 

$ ogrinfo -sql "delete from gpkg_metadata_reference where table_name='new_table'" 20170831_Expertise_Elia_Gblx-Auvl.gpkg --config OGR_GPKG_FOREIGN_KEY_CHECK OFF

ERROR 1: bad result for PRAGMA foreign_key_check, got 11 rows, expected 0
ERROR 1: pragma foreign_key_check on '20170831_Expertise_Elia_Gblx-Auvl.gpkg' failed
ERROR 1: bad result for PRAGMA foreign_key_check, got 11 rows, expected 0
ERROR 1: pragma foreign_key_check on '20170831_Expertise_Elia_Gblx-Auvl.gpkg' failed
FAILURE:
Unable to open datasource `20170831_Expertise_Elia_Gblx-Auvl.gpkg' with the following drivers.
-> PCIDSK
-> netCDF
-> etc…

=> At 3rd step it went wrong…

Best Answer

You can remove raster from GeoPackage with ogrinfo but it is a bit tricky because each raster table has also references in several metadata tables.

EDIT

GeoPackage has foreign key constraints which can prevent user from deleting some data. There are two alternatives to make deletes to success:

  1. Delete stuff in right order, or
  2. Disable foreign key check from SQLite database before deletes.

Here are ogrinfo commands ordered so that foreign keys don't make any trouble.

ogrinfo -sql "delete from gpkg_tile_matrix_set where table_name='raster2'" droptest.gpkg
ogrinfo -sql "delete from gpkg_tile_matrix where table_name='raster2'" droptest.gpkg
ogrinfo -sql "delete from gpkg_metadata_reference where table_name='raster2'" droptest.gpkg
ogrinfo -sql "delete from gpkg_contents where table_name='raster2'" droptest.gpkg
ogrinfo -sql "drop table raster2" droptest.gpkg

In my original answer I deleted things from the database in alphabetic order and I had to disable the foreign key check with a specific GDAL config option.

Drop the tiles

ogrinfo -sql "drop table raster2" droptest.gpkg

Remove line from gpkg_contents

ogrinfo -sql "delete from gpkg_contents where table_name='raster2'" droptest.gpkg

Remove references from gpkg_metadata_reference table. Ogrinfo does not accept the command with the same syntax as above because the SQLite database is now in invalid state because of orphaned foreign key constraints and you must use a special GDAL configuration option to override the validity check.

ogrinfo -sql "delete from gpkg_metadata_reference where table_name='raster2'" droptest.gpkg --config OGR_GPKG_FOREIGN_KEY_CHECK OFF

Remove references from gpkg_tile_matrix table.

ogrinfo -sql "delete from gpkg_tile_matrix where table_name='raster2'" droptest.gpkg --config  OGR_GPKG_FOREIGN_KEY_CHECK OFF

Remove references from gpkg_tile_matrix_set table

ogrinfo -sql "delete from gpkg_tile_matrix_set where table_name='raster2'" droptest.gpkg --config OGR_GPKG_FOREIGN_KEY_CHECK OFF

Now the raster table and all the references have been deleted. As a final step it might be good to vacuum the database.

ogrinfo -sql "VACUUM" droptest.gpkg

However, if the database is very big VACUUM will be slow. It could actually be faster to copy all the raster layers except the one that you want to drop into a new GeoPackage with gdal_translate. If you have also lots of vector layers in your GeoPackage that option may not be so fascinating. For simplifying the deletion you can save all the ogrinfo commands into a batch file and give the name of the raster file that you want to drop as a variable.

Related Question