st_write: unable to append to a GeoPackage table

[GreatEmerald]

I'm trying to figure out how to append rows to a GeoPackage (I am treating it as a database in this case). Following #624 and taking inspiration from one of the tests included in sf:

library(sf)
#> Linking to GEOS 3.4.2, GDAL 2.3.2, PROJ 4.8.0
geom = st_sfc(st_point(0:1), st_multipoint(matrix(1:4,2,2)))
st_write(geom, "geom1.gpkg", layer = "foo")
#> Writing layer `foo' to data source `geom1.gpkg' using driver `GPKG'
#> features:       2
#> fields:         0
#> geometry type:  Unknown (any)
st_write(geom, "geom1.gpkg", layer = "foo", update=TRUE)
#> Updating layer `foo' to data source `geom1.gpkg' using driver `GPKG'
#> Warning in CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), : GDAL Error 1: Layer foo already exists, CreateLayer failed.
#> Use the layer creation option OVERWRITE=YES to replace it.
#> Creating layer foo failed.
#> Error in CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), : Layer creation failed.
st_write(geom, "geom1.gpkg", layer = "foo", append=TRUE)
#> Error in st_write.sf(st_sf(geom = obj), dsn, layer, ...): unrecognized argument(s) TRUE

^{Created on 2019-08-12 by the reprex package (v0.3.0)}

For GPKG, update is already TRUE, so setting it doesn't really change anything. I don't want to overwrite the whole table, I want to append, so I don't think I should set OVERWRITE=YES. And then using append=TRUE, as it was mentioned in #624, results in a rather confusing error unrecognized argument(s) TRUE; this is actually another bug in

sf/R/read.R

Line 374 in 52a8351

stop(paste("unrecognized argument(s)", unlist(list(...)), "\n"))

because it should be names(list(...)), not unlist(list(...)), since the parameter name is what is relevant, not the value.

So what should I do if I indeed just want to append to a table in a GPKG? I can't read the whole table, do an rbind, and write it, because it's a pretty huge GPKG (points throughout the globe) and the whole table doesn't easily fit into memory.

[edzer]

What makes you think the GDAL GPKG driver has this capability?

[GreatEmerald]

It works just fine with ogr2ogr. Taking the file created with sf:

ogr2ogr duplicated.gpkg geom1.gpkg -append -update
ogr2ogr duplicated.gpkg geom1.gpkg -append -update
ogr2ogr duplicated.gpkg geom1.gpkg -append -update

And back in sf:

print(st_read("duplicated.gpkg"))
#> Reading layer `foo' from data source `/home/greatemerald-local/shared/duplicated.gpkg' using #> driver `GPKG'
#> Simple feature collection with 6 features and 0 fields
#> geometry type:  MULTIPOINT
#> dimension:      XY
#> bbox:           xmin: 0 ymin: 1 xmax: 2 ymax: 4
#> epsg (SRID):    NA
#> proj4string:    NA
#> Simple feature collection with 6 features and 0 fields
#> geometry type:  MULTIPOINT
#> dimension:      XY
#> bbox:           xmin: 0 ymin: 1 xmax: 2 ymax: 4
#> epsg (SRID):    NA
#> proj4string:    NA
#>                    geom
#> 1      MULTIPOINT (0 1)
#> 2 MULTIPOINT (1 3, 2 4)
#> 3      MULTIPOINT (0 1)
#> 4 MULTIPOINT (1 3, 2 4)
#> 5      MULTIPOINT (0 1)
#> 6 MULTIPOINT (1 3, 2 4)

[edzer]

Thanks - should work now!

[GreatEmerald]

Yeap, seems to work well, thanks a lot!

The printed output for st_write may be a bit confusing to some in that case, since it says Updating existing layer <name>, features: <number of features to be appended>, whereas with that wording people may expect features: to mean the total. Maybe printing Appending to existing layer <name> would be a bit more clear.

Similarly, people may not expect that writing to a GPKG that exists would append data rather than overwrite or error, but in this case the printed output is pretty clear about what is happening, so that may be fine.

[GreatEmerald]

I just ran into an edge-case bug in error reporting: if the file is read-only (e.g. chmod u-w), trying to append results in the error message:

Dataset geom1.gpkg already exists: remove first, use update=TRUE to append,
delete_layer=TRUE to delete layer, or delete_dsn=TRUE to remove the entire data source before writing.
Error in CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options),  :
  Dataset already exists.

This is now a very confusing message as update=TRUE is in fact set. Maybe there's a way to determine whether the file is writable and if not, print "permission denied"?

[andrew-plowright]

Thanks for including this very useful feature. Updating GPKG layers seems to work with st_write, but not with write_sf. Not entirely sure what the difference between those two functions is meant to be, but just thought I'd mention it.

Reading in test data

> nc = st_read(system.file("shape/nc.shp", package="sf"), quiet = TRUE)
> gpkg1 <- "test1.gpkg"
> gpkg2 <- "test2.gpkg"

Using st_write:

> sf::st_write(nc[1,], gpkg1)
> sf::st_write(nc[2:3,], gpkg1, update = TRUE)
> sf::st_layers(gpkg1)
Driver: GPKG 
Available layers:
  layer_name geometry_type features fields
1      test1 Multi Polygon        3     14

Total of 3 features. Success!

Now with write_sf:

> sf::write_sf(nc[1,], gpkg2)
> sf::write_sf(nc[2:3,], gpkg2, update = TRUE)
> sf::st_layers(gpkg2)
Driver: GPKG 
Available layers:
  layer_name geometry_type features fields
1      test2 Multi Polygon        2     14

Only 2 features. The layer was overwritten instead of appended.

[edzer]

Currently you have to specify delete_layer = FALSE in addition to update = TRUE for write_sf. The thinking behind this is that tidyverse write methods overwrite by default, unlike st_write.