[R-sig-Geo] [SpatiaLite-Users] Re: Load spatialite extension in RSQLite crashes R (OS X 10.8)
Alex Mandel
tech_dev at wildintellect.com
Mon Aug 12 08:04:28 CEST 2013
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: (
>> 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
>>>>>
>>>>
>>
>
More information about the R-sig-Geo
mailing list