Quantcast

Re: spatialite from R

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: spatialite from R

Alex Mandel-2
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

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: spatialite from R

hi_ono2001
Hello.

 SQLiteMap(Wrapper to spatialite functions) is still available from http://cran.cermin.lipi.go.id/web/packages/SQLiteMap/index.html.


 Regards.

--- On Sat, 2013/5/18, Alex Mandel <[hidden email]> wrote:

> 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
>
> _______________________________________________
> R-sig-Geo mailing list
> [hidden email]
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Loading...