Writing a table (no geometry) to GeoPackage using R

Question

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?

Answer 1

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.

Answer 2

Since sf version 0.9-4 (2020-06-12) (see changelog) you can use GDAL's capabability to write non-spatial tables into a GeoPackage (which respects the GPKG standard of how to store such tables).

library(sf)
#> Linking to GEOS 3.9.0, GDAL 3.1.3, PROJ 7.2.1
library(tibble)

mydf <- tibble(a = rep(letters, 100),
               b = seq_along(a) %>% rev,
               c = b / 100,
               d = lubridate::dmy("1/1/2020") - b,
               e = lubridate::now() - b)

st_write(mydf,
         "layers.gpkg",
         layer = "mydf")
#> Writing layer `mydf' to data source `layers.gpkg' using driver `GPKG'
#> Writing 2600 features with 5 fields without geometries.

(read_sf("layers.gpkg", layer = "mydf"))
#> # A tibble: 2,600 x 5
#>    a         b     c d          e                  
#>    <chr> <int> <dbl> <date>     <dttm>             
#>  1 a      2600  26   2012-11-18 2021-03-02 09:21:37
#>  2 b      2599  26.0 2012-11-19 2021-03-02 09:21:38
#>  3 c      2598  26.0 2012-11-20 2021-03-02 09:21:39
#>  4 d      2597  26.0 2012-11-21 2021-03-02 09:21:40
#>  5 e      2596  26.0 2012-11-22 2021-03-02 09:21:41
#>  6 f      2595  26.0 2012-11-23 2021-03-02 09:21:42
#>  7 g      2594  25.9 2012-11-24 2021-03-02 09:21:43
#>  8 h      2593  25.9 2012-11-25 2021-03-02 09:21:44
#>  9 i      2592  25.9 2012-11-26 2021-03-02 09:21:45
#> 10 j      2591  25.9 2012-11-27 2021-03-02 09:21:46
#> # … with 2,590 more rows

all.equal(mydf, read_sf("layers.gpkg", layer = "mydf"))
#> [1] TRUE

^{Created on 2021-03-02 by the reprex package (v1.0.0)}