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_...@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 <pe...@thoughtspot.net> >> Cc: r-sig-geo@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_...@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 >>>>> <pe...@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@r-project.org >>>>> https://stat.ethz.ch/mailman/listinfo/r-sig-geo >>>>> >>>> >> > _______________________________________________ R-sig-Geo mailing list R-sig-Geo@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-geo