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

[Roger Bivand]

On Tue, 3 Nov 2015, Barry Rowlingson wrote:

> 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

No reported error in 1.1-1, but the original layer is overwritten. The 
layer in the existing dsn is overwritten. Appending is not something 
writeOGR knows about:

> writeOGR(pts, "tmpfile.db", "pts2", driver="SQLite", 
dataset_option="SPATIALITE=YES", layer_option="FORMAT=SPATIALITE")
> writeOGR(pts, "tmpfile.db", "pts2", driver="SQLite", 
dataset_option="SPATIALITE=YES", layer_option="FORMAT=SPATIALITE")
Error in writeOGR(pts, "tmpfile.db", "pts2", driver = "SQLite", 
dataset_option = "SPATIALITE=YES",  :
   layer exists, use a new layer name
> ogrListLayers("tmpfile.db")
[1] "pts2"
attr(,"driver")
[1] "SQLite"
attr(,"nlayers")
[1] 1

For now, system("ogr2ogr ..."), or something from gdalUtils? Contribution 
to writeOGR() to implement appending as in 
http://www.gdal.org/drv_sqlite.html?

Roger

>
> 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
>
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo at r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>

-- 
Roger Bivand
Department of Economics, Norwegian School of Economics,
Helleveien 30, N-5045 Bergen, Norway.
voice: +47 55 95 93 55; fax +47 55 95 91 00
e-mail: Roger.Bivand at nhh.no