Adding spatial tables to existing SpatiaLite DBs

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

=2.

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

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

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.

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

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

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

 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

After some digging...

The problem appears to be that OGR_write.cpp always tries to create a
new data source. This would appear to be the wrong thing to do when
you have an existing data source that can have multiple layers. Code
should probably only try and create the data source if its not there.
The PostGIS driver in OGR can't create data sources, so I'd expect
writing PostGIS with writeOGR to fail, which I'm not sure is the case
because surely people would scream and I don't have a PostGIS handy so
that's a bit contradictory... There seems to be an OGR "Can You Create
a Data Source?" capability test for drivers...

I think ogr2ogr works because it has a special case for a few drivers
(inlcuding SQLite) where it uses the same driver handle for source and
destination - so it doesn't even try creating the destination data
source.

I'm not sure how much more I want to dig into this, especially since
I'm on a Gdal 1.11 system but everything is going all 2.0 now. Would
there be any point making it work for 1.11 systems? How long until
we're all on 2.0?

Barry