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

[Peter Schmiedeskamp]

Thank you, Alex.

That indeed is a work-around. And for all practical purposes, I like
to keep my intermediate results around as views anyway. The package I
built lets me create the views by executing the SQL directly in R,
however, I'll confess that there are nicer ways to explore building
those views. Still, there are some instances where executing a spatial
query directly from R is handy, and I guess I now have my package!

It looks like support for "amalgamation" builds was dropped in v4.x of
libspatialite, so my approach is old and getting older. This is a
shame, considering the speed benefits in 4.1
(https://www.gaia-gis.it/fossil/libspatialite/wiki?name=speed-optimization).
In theory, it would be possible to build libspatialite 4.1 inside an R
package, but I have no idea how to go about packaging software whose
build process is more elaborate / uses the autotools.

If I were a smarter and had more time, I'd consider resurrecting the
SQLiteMap package, which wraps all the libspatialite functions in R...

Cheers,
Peter


On Sun, Aug 11, 2013 at 11:04 PM, Alex Mandel
<tech_dev at wildintellect.com> wrote:
> 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
>