[R-sig-Geo] Adding spatial tables to existing SpatiaLite DBs

[Barry Rowlingson]

I can create a SpatiaLite DB file and put a layer in it, but if I try
and add another layer, rgdal fails. Example:

Versions etc:

 > require(rgdal)
Loading required package: rgdal
Loading required package: sp
prgdal: version: 1.0-7, (SVN revision 559)
 Geospatial Data Abstraction Library extensions to R successfully loaded
 Loaded GDAL runtime: GDAL 1.11.2, released 2015/02/10
 Path to GDAL shared files: /usr/share/gdal/1.11
 Loaded PROJ.4 runtime: Rel. 4.8.0, 6 March 2012, [PJ_VERSION: 480]
 Path to PROJ.4 shared files: (autodetected)
 Linking to sp version: 1.2-0

Create a simple points data set, write it:

 > pts = data.frame(x=runif(10),y=runif(10),z=1:10)
 > coordinates(pts)=~x+y
 > writeOGR(pts, "tmpfile.db", "pts", driver="SQLite",
dataset_option="SPATIALITE=YES")

Note the use of the dataset_option to make this a proper SpatiaLite,
and not just an SQLite table. The output file is about 4Mb and has a
lot of metadata tables in it. The file loads into QGIS which
recognises it as a SpatiaLite table and I can plot the points.

Now try and create another spatial table (this time, "pts2")  in the
same database file:

 > writeOGR(pts, "tmpfile.db", "pts2", driver="SQLite",
dataset_option="SPATIALITE=YES")
Error in writeOGR(pts, "tmpfile.db", "pts2", driver = "SQLite",
dataset_option = "SPATIALITE=YES") :
  Creation of output file failed

 I've tried various other options with no success. Is this possible?
Or do I ditch writeOGR for this and create the table by hand... ick...

Barry