Adding spatial tables to existing SpatiaLite DBs

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

Ogr2ogr can add layers to any driver that supports multiple layers using 
the -update switch:

     ogr2ogr -update destination source [layers]

If layers are not specified for a multi-layer source, *all* of them will 
be transferred.

Although writeOGR does expose the -update switch, it does successfully 
add layers to existing PostGIS database.

As Roger pointed out, gdalUtils exposes full functionality of ogr2ogr. 
So the following takes a couple of extra lines of code but works:

```
library(rgdal)
library(gdalUtils)

pts = data.frame(x=runif(10),y=runif(10),z=1:10)
coordinates(pts)=~x+y
writeOGR(pts, "final.sqlite", "pts", driver="SQLite", dataset_options = 
"SPATIALITE=YES")
writeOGR(pts, "tmp.sqlite", "pts2", driver="SQLite", dataset_options = 
"SPATIALITE=YES")

ogr2ogr("tmp.sqlite", "final.sqlite", "pts2", update=TRUE)
file.remove("tmp.sqlite")

```

Some other notes:

Without the -update switch, ogr2ogr will silently overwrite the 
destination.

Note that what writeOGR calls dataset_options are dataset *creation* 
options, so "SPATIALITE=YES" is not needed when adding layers to 
existing SpatiaLite DB. (For that matter, neither is the layer creation 
option "FORMAT=SPATIALITE".)

Best,
--Lee

Lee Hachadoorian
Assistant Professor of Instruction, Geography & Urban Studies
Assistant Director, Professional Science Master's in GIS
Temple University
http://geospatial.commons.gc.cuny.edu
http://freecity.commons.gc.cuny.edu