Delete layer from GeoPackage

Question

I am trying to delete a vector layer from a GeoPackage file using the sf package. By "delete" I mean permanently remove NOT overwrite or update. I am aware of the delete_layer option, but as I understand this only functions to delete a layer before replacing it with a layer of the same name.

Unfortunately I have written a layer with a name using non-standard encoding to a GeoPackage, which effectively makes the entire gpkg-file unreadble in QGIS. Hence, I am trying to find a solution to remove it via R.

Answer 1

A geopackage is also an SQLite database, so you can use RSQLite database functions to remove tables.

Set up a test:

> d1 = st_as_sf(data.frame(x=runif(10),y=runif(10),z=1:10), coords=c("x","y"))
> d2 = st_as_sf(data.frame(x=runif(10),y=runif(10),z=1:10), coords=c("x","y"))
> d3 = st_as_sf(data.frame(x=runif(10),y=runif(10),z=1:10), coords=c("x","y"))

Write those to a GPKG:

> st_write(d1,"deletes.gpkg","d1")
Writing layer `d1' to data source `deletes.gpkg' using driver `GPKG'
features:       10
fields:         1
geometry type:  Point
> st_write(d2,"deletes.gpkg","d2",quiet=TRUE)
> st_write(d3,"deletes.gpkg","d3",quiet=TRUE)

Now to delete, use the RSQLite package (from CRAN), create a database connection:

library(RSQLite)
db = SQLite()
con = dbConnect(db,"./deletes.gpkg")

and remove the table:

dbRemoveTable(con, "d2")

There's one tiny problem - this removes the table but does not remove the metadata that GPKG uses to note this package is a spatial table. Hence you get warnings like this with GDAL tools:

$ ogrinfo -so -al deletes.gpkg 
ERROR 1: Table or view 'd2' does not exist
Warning 1: unable to read table definition for 'd2'

QGIS happily read the remaining two layers in correctly though. I think this can be worked round in R by loading Spatialite module extensions alongside the SQLite modules, or manually removing the rows in the metadata tables gpkg_geometry_columns and maybe gpkg_ogr_contents but nothing seems to break hard with those things not updated.