1

How do you delete a GeoPackage layer using plain SQL? I tried

drop table my_layer

and it deletes the table, but leaves behind the spatial index and other metadata.

1
  • 2
    It would be easiest to do with GDAL that has a special DROP TABLE for that purpose gdal.org/drivers/vector/gpkg.html. But you can do it with plain SQL as well. Just drop one by one the table, the spatial index table, and triggers, and update gpkg_contents, and gpkg_geometry_columns.
    – user30184
    Dec 27, 2022 at 22:42

1 Answer 1

2

GDAL has a special DROP TABLE function in the GeoPackage driver for that purpose https://gdal.org/drivers/vector/gpkg.html that makes dropping a table with all dependent elements easy.

The source code can be used as a model for doing the same with plain SQL. The places to look at are

https://github.com/OSGeo/gdal/blob/master/ogr/ogrsf_frmts/gpkg/ogrgeopackagedatasource.cpp

and

https://github.com/OSGeo/gdal/blob/master/ogr/ogrsf_frmts/gpkg/ogrgeopackagetablelayer.cpp

There will be more to delete if the table has foreign key constraints or if the table is used by some views but at least these items must be dropped always when a table has a spatial index.

  • DROP TABLE
  • DELETE FROM gpkg_contents WHERE...
  • DELETE FROM gpkg_ogr_contents WHERE...
  • DELETE FROM gpkg_geometry_columns WHERE...
  • DROP TRIGGER "trigger_insert_feature_count_%w
  • DROP TRIGGER "trigger_delete_feature_count_%w\
  • DROP TABLE "%w"", m_osRTreeName
  • DROP TRIGGER "%w_insert";
  • DROP TRIGGER "%w_update1";
  • DROP TRIGGER "%w_update2";
  • DROP TRIGGER "%w_update3";
  • DROP TRIGGER "%w_update4";
  • DROP TRIGGER "%w_delete";
  • DELETE FROM gpkg_metadata WHERE id IN...
  • DELETE FROM gpkg_metadata_reference WHERE...

The list is long and most probably not complete. If the plain SQL solution is needed I recommend running hand written SQL and GDAL DROP TABLE first side by side, comparing the results, and iterating as long as the results match.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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