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@r-project.org >> https://stat.ethz.ch/mailman/listinfo/r-sig-geo >> > > > > _______________________________________________ > R-sig-Geo mailing list > R-sig-Geo@r-project.org > https://stat.ethz.ch/mailman/listinfo/r-sig-geo >
signature.asc
Description: OpenPGP digital signature
_______________________________________________ R-sig-Geo mailing list R-sig-Geo@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-geo