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

[Lee Hachadoorian]

On 11/03/2015 04:24 AM, Barry Rowlingson wrote:
> 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