[GIS] Writing multiple layers to GeoPackage using writeOGR() in R

gdalgeopackagerrgdalsf

I'm trying to write multiple layers to the same GeoPackage in R, but I get an error, Creation of output file failed. I have tried to search for documentation of reading and writing to .gpkg files with RGDAL, e.g. to figure out whether writeOGR() actually supports multiple layers, with little success. Is this even possible, if so, how to do it? Minimal working example:

library(sp)
library(maptools)
library(rgdal)

data(wrld_simpl)

norway <- wrld_simpl[wrld_simpl$NAME == "Norway", ]
sweden <- wrld_simpl[wrld_simpl$NAME == "Sweden", ]

file <- tempfile("scandinavia", fileext = c(".gpkg"))

writeOGR(norway, dsn = file, layer = "norway", driver = "GPKG")
writeOGR(sweden, dsn = file, layer = "sweden", driver = "GPKG")

ogrListLayers(file)

There is apparently an ogr2ogr shell command that does the trick (hat tip mdsumner), which I can wrap in an R function. However, it would be neat if writeOGR() and/or st_write() in the sf package had this built in. I reckon it depends on GDAL's layer_options, but there doesn't seem to be an append-type option for GPKG in GDAL.

I could write a simple wrapper function for st_write() but native support in sf or rgdal would be better.

Best Answer

You can do this using the append flag on sf::st_write():

library(sf)

nc     <- st_read(system.file("shape/nc.shp", package="sf"))
storms <- st_read(system.file("shape/storms_xyz.shp", package="sf"))

st_write(nc,     "nc.gpkg", "nc")
st_write(storms, "nc.gpkg", "storms", append = TRUE)

st_layers("nc.gpkg")
## Driver: GPKG 
## Available layers:
##   layer_name  geometry_type features fields
## 1         nc  Multi Polygon      100     14
## 2     storms 3D Line String       71      0

Related Solutions

GeoPackage – Writing a Table (No Geometry) to GeoPackage using R

GeoPackages are SQLite databases underneath, so you should be able to use the SQLite database driver in R to write plain data frames:

library(RSQLite) # install if needed

connect to an existing geopackage:

con = dbConnect(SQLite(),dbname="./ExistingGeopackage.gpkg")

see what's in there already:

dbListTables(con)

add our data frame and clean up:

dbWriteTable(con,"t1", t1)
dbDisconnect(con)

I'm writing to an existing GeoPackage because a GeoPackage has to contain some special metadata tables (which you see with dbListTables above). If I tried to write a new one with this method I'd get a plain SQLite file without the spatial metadata, and I'm not sure that will get added if you try and add a spatial table afterwards (most likely the driver will error).

If the first thing you want to do is write a non-spatial table to a new GPKG, then first create one using st_write with a junk spatial table in it (a single point at 0,0 perhaps), then add your non-spatial table. Further spatial and non-spatial tables should then be addable without problems.

the GDAL driver doc (which R and QGIS use under the hood) https://www.gdal.org/drv_geopackage.html does talk about non-spatial data but I don't quite see how to leverage that into st_write. If I crack it I'll edit and update here.

GeoPackage Layers – How to Write Multiple Layers to One GeoPackage Using OGR2OGR

You have different SQL statements, so you can't do one single line.

You can do mutiple statements like this:

ogr2ogr -f "GPKG" your_gpkg.gpkg PG:"host=localhost dbname=etienne user=postgres password=foo" -nln points -sql "SELECT * FROM points"
ogr2ogr -f "GPKG" your_gpkg.gpkg PG:"host=localhost dbname=etienne user=postgres password=foo" -nln lines -update  -sql "SELECT * FROM lines"

This will give you 2 layers in the Geopackage:

ogrinfo your_gpkg.gpkg 
INFO: Open of `your_gpkg.gpkg'
      using driver `GPKG' successful.
1: points
2: lines

The -nln is for specifying the layer name. They need to be different. https://gdal.org/programs/ogr2ogr.html#cmdoption-ogr2ogr-nln

And -update is to open the layer in update mode. https://gdal.org/programs/ogr2ogr.html#cmdoption-ogr2ogr-update

Edited, I first used -append and it worked. But as suggested in comment, I have updated the answer to use -update which is designed for this.

Best Answer

Related Solutions

GeoPackage – Writing a Table (No Geometry) to GeoPackage using R

GeoPackage Layers – How to Write Multiple Layers to One GeoPackage Using OGR2OGR

Related Question