Write layer style .qml as predefined within a gpkg using R

Question

I have this code to export a layer (Pbm) to a gpkg called "Atlas":

writeOGR(Pbm, paste0(normalizePath(OutputFolder),'\\', "Atlas.gpkg"),"Atlas",
             driver="GPKG", encoding = "UTF-8", overwrite_layer = TRUE) 

In other hand I have a .qml file with the QGIS style I like and which I want to save within the .gpkg. Does it exist the possibility to write this style within the .gpkg directly from R ?

PD: I already know that copying the .qml file with the same name as the .gpkg layer will add that style directly when loading into QGIS. The issue here is that I don't want that extra file out of the .gpkg.

Answer 1

Yes it is possible.

A GeoPackage is a special kind of SQLite database, and when QGIS saves QML data with a geopackage layer it saves the text and metadata of the QML in a table called layer_styles.

The table has the following schema:

CREATE TABLE IF NOT EXISTS "layer_styles" 
   ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     "f_table_catalog" TEXT(256),
     "f_table_schema" TEXT(256),
     "f_table_name" TEXT(256),
     "f_geometry_column" TEXT(256), 
     "styleName" TEXT(30),
     "styleQML" TEXT,
     "styleSLD" TEXT,
     "useAsDefault" BOOLEAN,
     "description" TEXT,
     "owner" TEXT(30), 
     "ui" TEXT(30),
     "update_time" DATETIME DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')));

You can create a new row in the table using R's database functions - you need to figure out the value of each of those fields and then add or update the row corresponding to the geopackage spatial layer it refers to.

The styleQML and styleSLD fields contain the XML text of the QML and SLD.

So yes it looks possible and given all the field data would only take a line or five of R database code to open the DB, write the row, and close the connection. There may be some pitfalls I'm missing though.

Answer 2

Thanks to @Spacedman I manage to do so by :

-in Qgis(3.2) save your style in database (Geopackage) and export as .gpkg

in R :

#I save my style layer as an object
    mystyle <- sf::st_read("file.gpkg", layer = "layer_styles")



#In this object rename the $f_table_name
#Give it the same name as your data layer mine is "pts"
    mystyle$f_table_name <- as.facort("pts")  



sf::st_write(obj = sf::st_as_sf(myData, coords = c("X","Y")), dsn = "myData.gpkg", layer = "pts",  append = TRUE, delete_layer = TRUE)
sf::st_write(mystyle, "myData.gpkg", "layer_styles", append = TRUE)

your .gpkg should display your style (at least it worked for me on Qfield)