Adding spatial tables to existing SpatiaLite DBs
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