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

Tue Nov 3 08:15:34 CET 2015

On Tue, 3 Nov 2015, Roger Bivand wrote:

> On Tue, 3 Nov 2015, Barry Rowlingson wrote:
>
>>  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....
>
> There is nothing (much) in rgdal::writeOGR() that knows much about the 
> drivers and (see separate recent thread) the implementation is really based 
> on the simplest formats that existed 10 years ago. Spatialite is 
> fast-changing, and not very stable in my experience. You're asking for OGR to 
> handle this, but writeOGR() isn't aware that it needs to ask OGR for things 
> that were not there when it was written (it was based on GRASS v.out.ogr from 
> even earlier). It would be nice if it worked by chance, unspecified, but more 
> needs to be done to check, and it looks as though it doesn't work (the single 
> added layer overwrites the existing layer, problably because the dsn= isn't 
> opened to append).
>
> Does the same thing happen without specifying Spatialite? Does the same thing 
> happen with PostGIS? Other DBs? We know that when dsn= is a directory and the 
> driver is ESRI Shapefile, it does what we expect, but should we expect it to 
> do that when dsn= is a file?
>
> Please look around line 60 in rgdal/R/ogr_write.R to see how writeOGR() 
> handles dsn and layer checking. This may need conditioning on the driver - 
> there is already a kludgy "fix" for dsn= deletion for shapefiles for GDAL 
>> =2.
>
> When you're checking improvements to writeOGR(), please set a baseline using 
> gdalUtils::ogr2ogr() so that we know where we are. We'll need to support GDAL 
> < 2 and GDAL >= 2, which use drivers differently.

tf1 <- tempfile()
tf2 <- tempfile()
require(rgdal)
pts=data.frame(x=runif(10),y=runif(10),z=1:10)
coordinates(pts)=~x+y
writeOGR(pts, tf1, "pts", driver="SQLite",
dataset_option="SPATIALITE=YES", layer_option="FORMAT=SPATIALITE")
writeOGR(pts, tf2, "pts2", driver="SQLite",
dataset_option="SPATIALITE=YES", layer_option="FORMAT=SPATIALITE")
ogrListLayers(tf1)
ogrListLayers(tf2)
library(gdalUtils)
ogr2ogr(tf2, tf1, append=TRUE, nln="pts2a")
ogrListLayers(tf1)
ogrInfo(tf1, "pts")
ogrInfo(tf1, "pts2a")

Roger

>
> Best wishes,
>
> Roger
>
>>
>>  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
>> > 
>> 
>
>

-- 
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