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