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

[Barry Rowlingson]

I don't think what I'm trying to do is "appending". I'm trying to
write two spatial data tables with different names in the single
spatial database file. The database file is the DSN and the tables are
layers - and rgdal is quite happy to add Shapefile "layers" to a
folder "DSN"...

Have just upgraded to rgdal 1.1-1 and still the same problem. Here's a
reprex without prompts:

require(rgdal)
pts=data.frame(x=runif(10),y=runif(10),z=1:10)
coordinates(pts)=~x+y
file.remove("tmpfile.db")

# write layer pts, this works
writeOGR(pts, "tmpfile.db", "pts", driver="SQLite",
dataset_option="SPATIALITE=YES", layer_option="FORMAT=SPATIALITE")

# write layer pts2
writeOGR(pts, "tmpfile.db", "pts2", driver="SQLite",
dataset_option="SPATIALITE=YES", layer_option="FORMAT=SPATIALITE")

Interestingly when I try and write with the *same* table name, rgdal
helpfully suggests "layer exists, use a new layer name", but when I
obey, I get the error behaviour I've described....

Barry



On Mon, Nov 2, 2015 at 11:21 PM, Roger Bivand <Roger.Bivand at nhh.no> wrote:
> 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
>