Load spatialite extension in RSQLite crashes R (OS X 10.8)

Thanks Alex,

I'm in the thick of working with this now, and literally just
discovered that libspatialite.so.3 loads in Ubuntu and OS X (however,
queries exercising spatial functions still cause R to segfault on OS
X, but I'll take this as forward progress).

If you don't mind, I'll forward this thread on to the spatialite users
group. I've seen a few postings focussed on other languages that talk
about extension loading problems with the new libspatialite.

Cheers,
Peter


On Sat, Aug 10, 2013 at 11:57 AM, Alex Mandel
<tech_dev at wildintellect.com> wrote:

That's entirely possible Spatialite 4 had some changes to the db
structure when compared to Spatialite 3. I don't think RSQLite or
Pyspatialite have caught up yet, and SQLiteMap (R package) appears
orphaned. So I would try to roll back to spatialite 3 and see if you can
get that working with SQLiteMap. That also appears to mean R below
version 3.

So in Raring (13.04) that means use it from the main ubuntu repos.
On Mac http://www.kyngchaos.com/software:frameworks  Spatialite Tools v3.0

Possible workaround, create a view and then use rgdal (with gdal 1.10)
to import that view.

I tested on ubuntu 12.04 and those instructions (which I wrote) don't
appear to work anymore, at least not with R 3.0+spatialite3. I
originally figured that out back in 2008-2009 though so a lot has
changed since then. I'm going to go back to OSGeoLive 6.5 and see if
they work (that should have R 2.15 with spatialite3)

Thanks,
Alex

On 08/09/2013 01:57 PM, Peter Schmiedeskamp wrote:

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