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
Load spatialite extension in RSQLite crashes R (OS X 10.8)
5 messages · Peter Schmiedeskamp, Alex Mandel
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
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
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
_______________________________________________ R-sig-Geo mailing list R-sig-Geo at r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Yes, thats a good place to ask, though you may once again be talking to me. I did find this old thread I had forgot about, which has some more ideas. https://stat.ethz.ch/pipermail/r-sig-db/2010q1/000784.html Thanks, Alex
On 08/10/2013 12:02 PM, Peter Schmiedeskamp wrote:
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
_______________________________________________ R-sig-Geo mailing list R-sig-Geo at r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-geo