2

I'd like to update records of a GeoPackage containing vector information in R. I've tried this:

library(sf)
library(RSQLite)

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

# Write Geo Package
gpkg <- "nc.gpkg"
st_write(nc, gpkg)

# Create connection
con <- dbConnect(SQLite(), dbname = gpkg)

# Update record
res <- dbSendQuery(con, "UPDATE nc SET NAME = 'My new name' WHERE CNTY_ID == 1828")

Which gives me the following error:

Error: no such function: ST_IsEmpty

A bit of digging reveals that ST_IsEmpty is one of several SQL functions that are required for updating a GeoPackage. How would I go about including these? Or are there any other methods of performing this operation?

1

You've opened an SQLite database connection, which is the "vanilla" SQLite database without the spatial functions which are implemented in SQLite "loadable extensions". If you know where the spatialite extension module is you can load it in.

On my system, its in /usr/lib/x86_64-linux-gnu/mod_spatialite.so, hence I can do:

> res <- dbSendQuery(con, "select load_extension('/usr/lib/x86_64-linux-gnu/mod_spatialite.so');")

to load the extensions, and then your update completes:

> res <- dbSendQuery(con, "UPDATE nc SET NAME = 'My new name' WHERE CNTY_ID == 1828")
Warning message:
Closing open result set, pending rows 
> 

with a warning which I'm going to ignore at the moment.

It might be simpler to rewrite the whole spatial object to the GeoPackage using st_write instead of updating rows - I don't know if there's much of an efficiency gain in row updates... benchmarks would be interesting...

I'm not sure how to reliably find the location of the spatialite extension module on all operating systems, or how to do this programmatically if you want to write a re-usable script.

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.