[R-sig-Geo] spatialite from R

[Alex Mandel]

On 05/17/2013 04:04 AM, Gilles San Martin wrote:
> Hello
>
> Sorry for contacting you directly but I've been searching the web for a
> while now about connections between R and spatialite without success and I
> have seen you name comming back regularly (r-help, spatialite,... mailing
> lists). So I was wondering if you have some new ways to deal with that
> problem as the last messages I saw were from 1-2 years ago ?
>
> I just would like to be able to execute a SQL query on a spatialite
> database from R.
> I have noted that rgdal now supports importing tables from spatialite but I
> need an approach that would work also for huge tables (too big probably to
> be imported in R directly, before the query).
>
> The idea would be probably to create a view (from R, not from a spatialite
> interface) and then access to it via rgdal (readOGR).
> If there is no way to do it in R, I was wondering if it would be possible
> to use the system() function to execute a command as if you were at the
> command line (I don't see how however).
>
> I would like something without configuration, in a way that would allow me
> to provide script and data to colleagues that they could run it without
> additional configuration needs.
>
> NB : I don't know spatialite. I'm just exploring the possibilities to
> decide which tool (spatialite, psotgis, something else ?) will match my
> needs the best to decide which one to learn.
> I'm attracted by spatialite but I'm a little bit afraid by its lack of
> support/indifference in R and the fact that it seems to be developped
> outside OSgeo or big structure like that (what about durability?) .
>
> Best regards
> Gilles
>

These questions are fine for either R-sig-geo or Spatialite mailing 
lists (I've copied them on this email). Since you're most interested in 
R, maybe start with R-sig-geo

I haven't done this in a while but here are my notes:
library(RSQLite)
m <- dbDriver("SQLite")
con <- dbConnect(m, dbname = "test.db",loadable.extensions = TRUE)
sql <- "SELECT load_extension('libspatialite.so')"
rs <- dbGetQuery(con, sql)

Once you've done that you can run any query you want, the only tricky 
part is that you can't retrieve spatial blobs in any sensible way via 
this method. However if you have made a view, I believe that readOGR can 
then pull that whole view over as an sp object.

The code above is linux specific since an .so is a .dll on Windows. Also 
you'll need the RSQLite, RGDAL and Spatialite installed on the system 
thats going to use it. If you can't rely on your users to be able to 
install those things you might want to find a different way.

Thanks,
Alex