[SpatiaLite-Users] Re: Load spatialite extension in RSQLite crashes R (OS X 10.8)
Peter,
I wanted to report that I've found another workaround. If you create a
view in spatialite (pick your interface), that view can then be
registered as spatial and loaded via rgdal readOGR:
1. CREATE VIEW with a geometry column
2. INSERT a record into views_geometry_columns for that view
Looks like:
INSERT INTO views_geometry_columns
(view_name, view_geometry, view_rowid, f_table_name, f_geometry_column)
VALUES ('italy', 'geometry', 'ROWID', 'local_councils', 'geometry');
f_table_name is the table referenced in the view from which the geometry
actually derives from.
3. Open R
4. Load rgdal
5. test <- readOGR("somedata.db","italy",verbose=TRUE,disambiguateFIDs=TRUE)
verbose isn't required, disambiguiateFIDs is if you don't have a unique
ID column in your view.
Hints from:
http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/sp-view.html
http://gis.stackexchange.com/a/25044
Note, such views can also be loaded in QGIS > 1.8 (so master version
soon to be 2.0), this should work with spatialite 3+
Not as clean as just executing the sql in R, but I usually prep my
queries first anyways and store them in code, so making views is just as
cheap and easy.
Thanks,
Alex
On 08/10/2013 03:33 PM, Peter Schmiedeskamp wrote:
I never was quite able to figure out how to get even version 3 of the libspatialite package to load in R on OS X with the CRAN version of RSQLite. So, my short-term solution was to create a package, RSQLite.spatialite, that builds and loads its own amalgamated build of spatialite. https://github.com/pschmied/RSQLite.spatialite This is kind of a short-term hacky solution until someone (probably not me) comes up with a more robust way of loading libspatialite4. Cheers, Peter On Saturday, August 10, 2013 12:29:00 PM UTC-7, Peter Schmiedeskamp wrote:
Dear list, I've spent the past few days trying to track down an issue whereby I'm unable to load the latest version of libspatialite (4.1.1) as an extension in various environments (RSQLite, Clojure/Xerial-JDBC on Ubuntu 13.04 and OS X 10.8). Using libspatialite 3.x, it would appear things mostly work. I'm forwarding this email history along in case anyone might know what sort of differences in libspatialite 4 might be causing this, or in case some future intrepid explorer finds their way to the same problem. Cheers, Peter ---------- Forwarded message ---------- From: Alex Mandel <tech_dev at wildintellect.com> Date: Sat, Aug 10, 2013 at 12:06 PM Subject: Re: [R-sig-Geo] Load spatialite extension in RSQLite crashes R (OS X 10.8) To: Peter Schmiedeskamp <peter at thoughtspot.net> Cc: r-sig-geo at r-project.org 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: (
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 (
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:
_______________________________________________ R-sig-Geo mailing list R-sig-Geo at r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-geo