Rename layers in geopackage in R-CodePudding

Is it possible to directly rename layers of a geopackage in R without 1) reading in the layer, 2) copying it back to same database with a new name, and 3) deleting the original layer?

PseudoCode

library(sf)

geopackage = "example.gpkg"

lyrs = st_layers(geopackage)
lyrs
# Driver: GPKG 
# Available layers:
#   layer_name geometry_type features fields
# 1      Layer1_old Multi Polygon        1      1
# 2      Layer2_old Multi Polygon        1      1
# 3      Layer3_old Multi Polygon        1      1


### I'm looking for something like so I can cycle through
st_rename(geopackage, layer = lyrs[1], new.name = "Layer1_new")

CodePudding user response：

This is an interesting problem. As far as I know there is no generic function in {sf} and friends to rename layers.

But - and here it gets interesting! - the Geopackage file is special. It is, at the same time,

a spatial data file and
a regular SQLite database.

Meaning we can hack it with our SQL toolset (ALTER TABLE, I'm looking at you!).

So consider this piece of code, built on the well known & much loved North Carolina shapefile that ships with {sf}.

What it does is that it runs alter table on the nc layer (note that the "nc" in this context is driven by the name of the R object used to create the geopackage; in your use case the names will be already given).

After the alter table has been run (and the name of the layer object altered) there needs to be done a small cleanup on the gpkg_contents and gpkg_geometry_columns objects to reflect the update performed. Again familiar SQL stuff.

library(sf)
library(DBI)
library(RSQLite)

# the one & only NC shapefile...
nc <- st_read(system.file("shape/nc.shp", package="sf")) 

# ...saved as a geopackage
st_write(nc, "nc.gpkg")

# check layers name
st_layers("nc.gpkg")

# Driver: GPKG 
# Available layers:
#   layer_name geometry_type features fields crs_name
# 1         nc Multi Polygon      100     14    NAD27

# connect to nc.gpkg as to a sqlite database
con <- dbConnect(RSQLite::SQLite(), "nc.gpkg")

result <- dbSendQuery(con, "ALTER TABLE nc RENAME to CN;")
dbClearResult(result) # you don't really care about the results, but they need to be "handled"

# align other features / see http://www.geopackage.org/spec120/#features
result <- dbSendQuery(con, "update gpkg_contents set table_name = 'CN' where table_name = 'nc';")
dbClearResult(result)
result <-  dbSendQuery(con, "update gpkg_geometry_columns set table_name = 'CN' where table_name = 'nc';")
dbClearResult(result)

# our work is done... let's not keep the connection hanging
dbDisconnect(con)

# read the newly created CN layer
shape <- st_read("nc.gpkg",
                 layer = "CN")

# check results / does it look as the nc.shp we all know & love?
plot(st_geometry(shape))