Write sf object to spatialite database
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>