3

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?

3

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.

  • This worked for me - I actually didn't need to create a new GeoPackage in the process, but just did so for the sake of example. – Mike Talbot Mar 7 at 23:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.