[R-sig-DB] Using SpatiaLite 2.3.1 from RSQLite 0.7-3
Nick Baumberger
nb@umberger @end|ng |rom y@hoo@com
Thu Nov 26 15:49:54 CET 2009
Dear all,
I was wondering whether I can use the spatialite extension in combination with RSQLite.
1) In theory libspatialite-2.dll and the other dll's should load dynamically, but in practice I am not able to correctly setup this thing or get any reasonable error message when doing "loadable.extensions = TRUE"
2) The other question is how SpatiaLite can work without having defined the my table called "geometry_columns"
setup
*****
Im am using R 2.9.1, RSQLite 0.7-3 on a Windows XP and have installed the following dll's on an accessible directory (to which I point a user defined env path variable)
RSQLite.dll
libspatialite-2.dll
libgeos-3-0-0.dll
libgeos_c-1.dll
libproj-0.dll
preparation: get the spatial dataset
************************
install.packages("SQLiteMap")
library(RSQLite)
sqli.db <- system.file("sqlimaps/sids.db3", package="SQLiteMap")
drv <- dbDriver("SQLite")
con.1 <- dbConnect(drv, dbname = sqli.db)
sql <- 'select sidsmap.* from sidsmap'
rs <- dbSendQuery(con.1, sql)
my.spatial.data <- fetch(rs, n = -1)
-> has one column geom
geom : chr "POLYGON ((-79.2461929321289 35.8681526184082, -79.2379913330078 ......
prepare SpatiaLite 2.3.1 evironment
***********************************
see: http://www.gaia-gis.it/spatialite/
IMPORTANT CONFIGURATION STEP ! -> setting loadable.extensions = TRUE in order to dynamically load below dll's
m <- dbDriver("SQLite", max.con = 25)
con <- dbConnect(m, dbname="sqlite.db", loadable.extensions = TRUE)
sql <- 'SELECT load_extension("libspatialite-2.dll")'
rs <- dbSendQuery(con, sql)
dbGetException(con)
dbClearResult(rs)
sql <- 'SELECT load_extension("libgeos-3-0-0.dll")'
rs <- dbSendQuery(con, sql)
dbGetException(con)
dbClearResult(rs)
sql <- 'SELECT load_extension("libgeos_c-1.dll")'
rs <- dbSendQuery(con, sql)
dbGetException(con)
dbClearResult(rs)
sql <- 'SELECT load_extension("libproj-0.dll")'
rs <- dbSendQuery(con, sql)
dbGetException(con)
dbClearResult(rs)
-> don't get any error message
load my.spatial.data into sqlite database
****************************************
dbWriteTable(con, "mygeom", my.spatial.data)
operate on table
****************
dbGetQuery(con, "select * from mygeom")[1:2,] # this works
[[elided Yahoo spam]]
-> see
dbGetQuery(con, "select ST_Centroid(geom) from mygeom")
msg
Fehler in sqliteExecStatement(con, statement, bind.data) :
RS-DBI driver: (error in statement: no such function: ST_Centroid)
obviously I have a problem binding the spatial functions / loading the dlls. Is there anybody out there who can help ?
best regards
Nick
More information about the R-sig-DB
mailing list