spatialite from R

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