I am running some queries against a spatialite database, and would like to get the data directly into R. This page suggests I can do something like this:
library(RSQLite)
sqldrv <- dbDriver("SQLite")
con <- dbConnect(sqldrv, dbname = "/path/to/db.sqlite",loadable.extensions = TRUE)
spatialitestatus <- dbGetQuery(con, "SELECT load_extension('libspatialite.dylib')")
But when I do, R segfaults with the following:
*** caught segfault ***
address 0x0, cause 'memory not mapped'
Traceback:
1: .Call("RS_SQLite_fetch", rsId, nrec = n, PACKAGE = .SQLitePkgName)
2: sqliteFetch(rs, n = -1, ...)
3: sqliteQuickSQL(conn, statement, ...)
4: dbGetQuery(con, "SELECT load_extension('/usr/local/lib/libspatialite.dylib')")
5: dbGetQuery(con, "SELECT load_extension('/usr/local/lib/libspatialite.dylib')")
In my case, spatialite was built from source using Homebrew, as is the version of sqlite that I would normally use. In the libspatialite homebrew definition, it says it depends on sqlite > 3.7.3. My homebrewed version of sqlite is 3.7.17
I don't know for certain what version of sqlite RSQLite pulls in by default, but I assume it's the version 3.7.17 version included in the RSQLite source package. I tried building RSQLite from source using install.packages(c("RSQLite"), type="source") in hopes that it would simply use my version of sqlite, but it doesn't look like it did. Or if it did, it's still crashing.
Finally, I should mention that I tried this under two versions of R with same results:
Crash confirmed in Ubuntu 13.04 using the libspatialite5 provided by the Ubuntu FOSS GIS repository.
It appears that recent versions of libspatialite (4.x) are problematic. In addition, loading the homebrewed libspatialite3 extension works, but subsequent queries resulted in the same segfault.
In the end, my (short-term, temporary, hackish) solution was to create an R package that compiles its own spatialite and loads it. RSQLite.spatialite can be found here.