Delete GeoPackage layer using SQL

Question

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.

Answer 1

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.