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