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

[Alex Mandel]

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
> 
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo at r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>