[R-sig-Geo] Load spatialite extension in RSQLite crashes R (OS X 10.8)

[Peter Schmiedeskamp]

I fired up an Ubuntu VM and found that loading the spatialite
extension also seems to crash there. My test Ubuntu setup is v13.04
(x86_64). I installed libspatialite5 from the Ubuntu FOSS GIS
packages: (http://hub.qgis.org/projects/quantum-gis/wiki/Download#262-With-updated-dependencies).
R was installed from the CRAN Ubuntu package site.

I am starting to wonder if this isn't an incompatibility between
RSQLite and the latest versions of libspatialite. If anyone has any
experience using the new libspatialite with RSQLite, I'd love to hear
how you got it working.

Cheers,
Peter

On Thu, Aug 8, 2013 at 7:08 PM, Peter Schmiedeskamp
<peter at thoughtspot.net> wrote:
> I am running some queries against a spatialite database, and would
> like to get the data directly into R.This thread
> (http://r-sig-geo.2731867.n2.nabble.com/Re-spatialite-from-R-td7583603.html)
> 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. My libspatialite is
> libspatialite-4.1.1.
>
> 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
> usinginstall.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:
>
> R 3.0.0 downloaded in binary form from CRAN
> R 3.0.1 downloaded in source form and installed via homebrew
>
> Cheers,
> Peter
>
> P.S. I also posted this to stackoverflow a couple days ago, if anyone
> is hankering for some karma:
> http://stackoverflow.com/questions/18107336/load-spatialite-extension-in-rsqlite-crashes-r-os-x