[R-sig-DB] Using SpatiaLite 2.3.1 from RSQLite 0.7-3

[Nick Baumberger]

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