Why does the AddGeometryColumn function not exist in RSQLite?

Question

I am trying to add the geometry to a new table with the following statement:

library(RSQLite)
# db.sqlite was created with spatialite-gui
con <- dbConnect(RSQLite::SQLite(), dbname  = "C:/GIS/db.sqlite") 

rs <- dbSendQuery(con, 
  "CREATE TABLE test_geom (
    id INTEGER NOT NULL
      PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    measured_value DOUBLE NOT NULL);")
dbClearResult(rs)

rs <- dbSendQuery(con,
"SELECT AddGeometryColumn('test_geom', 'Geometry', 4326, 'POINT', 'XY');")

But it generates the following error:

Error: no such function: AddGeometryColumn

If I execute the same statement in spatialite-gui if it works.

Answer 1

R is using the SQLite driver to access the file, so its equivalent to reading the database using sqlite3 instead of spatialite:

$ sqlite3 spatial.sqlite 'select AddGeometryColumn()'
Error: no such function: AddGeometryColumn


$ spatialite spatial.sqlite -cmd 'select AddGeometryColumn()'
spatialite: Error: too many options: "select AddGeometryColumn()"

A database opened with sqlite3 will have all the tables for a spatial database but won't have the functions because those are only available when opened using a spatialite driver. RSQLite only uses the base sqlite3 code.

What you need to do is load the spatialite module into sqlite.

Starting with a non-spatial connection:

> library(RSQLite)
> con = dbConnect(SQLite(),"./spatial.sqlite")
> dbSendQuery(con,"select AddGeometryColumn()")
Error: no such function: AddGeometryColumn

You need to find the path to your spatialite module. On Linux that's a .so file, which I've found here /usr/lib/x86_64-linux-gnu/mod_spatialite.so - then I can load that using load_extension:

> dbSendQuery(con,"select load_extension('/usr/lib/x86_64-linux-gnu/mod_spatialite.so');")
<SQLiteResult>
  SQL  select load_extension('/usr/lib/x86_64-linux-gnu/mod_spatialite.so');
  ROWS Fetched: 0 [incomplete]
       Changed: 0

Now when I see if this connection has AddGeometryColumns:

> dbSendQuery(con,"select AddGeometryColumn()")
Error: wrong number of arguments to function AddGeometryColumn()
In addition: Warning message:
Closing open result set, pending rows 

It jolly well does.

I think under Windows you need to find a .dll file.

Figured most of this out from https://github.com/pschmied/RSQLite.spatialite which is very old and crashed my R. But the principles are outlined there.