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

rsf

I'm trying to do something so incredibly simple: write a table (with no geometry) to a GeoPackage. I can store tables in a GeoPackage using QGIS, so it stands to reason I'd be able to write one from R. So far no luck. Here's an example:

library(sf)

t1 <- data.frame(numbers=c(1:5), letters=c(LETTERS[1:5]))

st_write(t1, dsn="NewGeopackage.gpkg", layer="t1")

I've tried converting the table to other formats (using st_as_sf, for example) but I always get the same error: 

Error in st_sf(x, ..., agr = agr, sf_column_name = sf_column_name) : 
  no simple features geometry column present

Any idea how to do this?

Best Answer

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.

Related Question