5

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...

3
  • I tested the commands with Windows build of GDAL from gisinternals.com. There is either some difference in our GDAL versions or then config options must be set in a different way for Linux.
    – user30184
    Aug 22 '17 at 4:11
  • FYI, I gave a try with R (RSQLite package) to remove raster table and manipulate gpkg_content, gpkg_metadata_reference, gpkg_tile_matrix and gpkg_tile_matrix_set. Workflow: (1) dbConnect(); (2) made a temp copy of the tables; (3) dbRemoveTable(); (4) performed changes on temp; (5) dbWriteTable(); (6) dbDisconnect(). But still the geopackages ended corrupted and returned bad result for PRAGMA foreign_key_check stuff when i tried gdalinfo or when trying to open in QGIS. NOTE that I could remove some wrong entries in layer_styles table in the same way with success! Aug 23 '17 at 10:20
  • Read trac.osgeo.org/gdal/wiki/ConfigOptions and try to set config option with "export".
    – user30184
    Aug 23 '17 at 10:36
4

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.

3
  • Thanks for your quick and detailed answer! But I got an issue at third step and now it seems that the geopackage is corrupted. The error message is too long to paste here, so I'll edit the question... Aug 21 '17 at 21:27
  • The config option should prevent the error message. Notice that there are two dashes in --config.
    – user30184
    Aug 21 '17 at 21:31
  • Yep, I used 2 dashes, I actually copied-paste from your answer Aug 21 '17 at 21:34
0

You can use gdalmanage utility. Utility for identify, delete, rename and copy raster data files.

gdalmanage delete 20170831_Expertise_Elia_Gblx-Auvl.gpkg/new_table

The documentations can be found here: http://gdal.org/gdalmanage.html

6
  • Have you tested that it works with GeoPackage?
    – user30184
    Aug 22 '17 at 8:20
  • $ gdalmanage delete 20170831_Expertise_Elia_Gblx-Auvl.gpkg/new_table ERROR 1: No identifiable driver for 20170831_Expertise_Elia_Gblx-Auvl.gpkg/new_table Aug 22 '17 at 8:25
  • The correct syntax is probably gdalmanage delete GPKG:20170831_Expertise_Elia_Gblx-Auvl.gpkg:new_table but program seems to do nothing for the geopackage. It does not throw any errors either. – user30184 6 mins ago edit
    – user30184
    Aug 22 '17 at 9:28
  • "... seems to do nothing for the geopackage" => Exact! Tested here on Ubunutu: no error message but no changes as well... Aug 22 '17 at 9:47
  • Gdalmanage does not work, the ogrinfo way is the only possibility with GDAL tools, see lists.osgeo.org/pipermail/gdal-dev/2017-August/046975.html. You can also use for example spatialite-gui for doing the drop and deletions from graphic user interface.
    – user30184
    Aug 22 '17 at 10:10
0

I have just discovered a useful SQLIte Database utility called DB Browser for SQL Lite. It has some facilities for interogating SQL lite databases, and appears to support these on geopackages. However some initial tests do show that a simple 'delete' of a table from the database would need some embedded SQL code to ensure removal of all reference records that are created in geopackages. ie: I encountered some similar errors about foreign keys above. However the utility may provide some better tools/navigiation methods across the geopackage, because it treats it like a SQLite database. Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.