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

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

rs <- dbSendQuery(con, 
  "CREATE TABLE test_geom (
    name TEXT NOT NULL,
    measured_value DOUBLE NOT NULL);")

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.

  • Does the RSQLite library claim to implement every statement in spatiallite-gui?
    – PolyGeo
    Sep 14, 2019 at 5:27

1 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');")
  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.

  • I downloaded the dll from gaia-gis.it/gaia-sins/windows-bin-amd64 but he doesn't recognize it. > file.exists("C:/GIS/sqlite/mod_spatialite.dll") [1] TRUE > dbSendQuery(con,"select load_extension('C:/GIS/sqlite/mod_spatialite.dll');") Error: No se puede encontrar el módulo especificado.
    – Novvier
    Sep 14, 2019 at 16:42
  • Hmmm see if you can get it working from an sqlite3 command line. Try it without the ".dll" part... Really not a windows expert so not sure what I can do...
    – Spacedman
    Sep 14, 2019 at 16:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.