[R-sig-Geo] Write sf object to spatialite database

[Loïc Dutrieux]

On 10/11/2016 17:15, Edzer Pebesma wrote:
> Thanks for trying.
> 
> I guess the sqlite session needs to be loaded with the spatialite
> libraries before we can use the spatial functions, like
> AddGeometryColumn, where it breaks on my machine:
> 

I thought I did, but I'm no expert in databases, when I run

dplyr::src_sqlite('~/sandbox/db.sqlite')

rc:  sqlite 3.8.6 [~/sandbox/db.sqlite]
tbls: ElementaryGeometries, geom_cols_ref_sys, geometry_columns,
geometry_columns_auth,
geometry_columns_field_infos, geometry_columns_statistics,
geometry_columns_time, meuse_tbl,
SpatialIndex, spatialite_history, spatial_ref_sys, spatial_ref_sys_all,
spatial_ref_sys_aux,
sqlite_sequence, sql_statements_log, vector_layers, vector_layers_auth,
vector_layers_field_infos, vector_layers_statistics, views_geometry_columns,
views_geometry_columns_auth, views_geometry_columns_field_infos,
views_geometry_columns_statistics, virts_geometry_columns,
virts_geometry_columns_auth,
virts_geometry_columns_field_infos, virts_geometry_columns_statistics

Doesn't it mean that all the spatial extensions of sqlite are loaded?

>> st_write_db(con, sf, "meuse_tbl", dropTable = FALSE)
> Error in sqliteSendQuery(con, statement, bind.data) :
>   error in statement: no such function: AddGeometryColumn
> 
> For postgis, we of course assume that the database has the PostGIS
> extensions loaded, on a "raw" PostgreSQL database you'll get the same
> error, I'd expect.
> 
> To get back to your first question: does your st_drivers() include
> spatialite?

yes it does.
That way I managed to write to the database (created on the fly if it
doesn't exist). I did not manage to add several tables to the same
database though.


st_write(sf, dsn = '/home/loic/sandbox/db2.sqlite', layer = 'meuse1',
driver = 'SQLite')
st_write(sf, dsn = '/home/loic/sandbox/db2.sqlite', layer = 'meuse2',
driver = 'SQLite')
ogrListLayers('/home/loic/sandbox/db2.sqlite')

[1] "meuse2"
attr(,"driver")
[1] "SQLite"
attr(,"nlayers")
[1] 1

I remember a similar issue/limitation with writeOGR
http://r-sig-geo.2731867.n2.nabble.com/Adding-spatial-tables-to-existing-SpatiaLite-DBs-td7589137.html

Cheers,
Loïc

> 
>> x = st_drivers()
>> x[x$name == "SQLite",]
>       name           long_name write  copy is_raster is_vector
> 151 SQLite SQLite / Spatialite  TRUE FALSE     FALSE      TRUE
> 
> In that case, you may want to use st_write instead.
> 
> On 10/11/16 15:47, Loïc Dutrieux wrote:
>> Hi,
>>
>> Is it possible to write sf objects to spatialite databases? I tried to
>> adapt the postgis example of the st_write_db function but I get the
>> following error:
>>
>> Error in sqliteSendQuery(con, statement, bind.data) :
>> error in statement: no such function: AddGeometryColumn
>>
>> Cheers,
>> Loïc
>>
>> library(sf)
>> library(sp)
>> library(RSQLite)
>>
>> data(meuse)
>> sf = st_as_sf(meuse, coords = c("x", "y"), crs = 28992)
>>
>> # I created the db with QGIS, I don't know how to do otherwise
>> con <- dbConnect(RSQLite::SQLite(), '~/sandbox/db.sqlite')
>>
>> st_write_db(con, sf, "meuse_tbl", dropTable = FALSE)
>>
>> _______________________________________________
>> R-sig-Geo mailing list
>> R-sig-Geo at r-project.org
>> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>>
> 
> 
> 
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo at r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
> 

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 819 bytes
Desc: OpenPGP digital signature
URL: <https://stat.ethz.ch/pipermail/r-sig-geo/attachments/20161110/b90bbd87/attachment.bin>