Skip to main content
Add a more fluent sequence of commands.
Source Link
user30184
  • 69.1k
  • 4
  • 71
  • 125

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.

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.

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.

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.

Source Link
user30184
  • 69.1k
  • 4
  • 71
  • 125

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.

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.