Skip to content

Adding spatial tables to existing SpatiaLite DBs

2 messages · Barry Rowlingson, Roger Bivand

Original
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
Roger Bivand
#
On Tue, 3 Nov 2015, Barry Rowlingson wrote:

            

      
      
      
    

        
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:

            

      
      
      
    

        
dataset_option="SPATIALITE=YES", layer_option="FORMAT=SPATIALITE")

            

      
      
      
    

        
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

            

      
      
      
    

        
[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