[R-sig-Geo] Slow writing of point features to SpatialLite-DB or Geopackage
Vijay Lulla
vijaylulla at gmail.com
Fri Aug 25 01:57:23 CEST 2017
According to http://gdal.org/drv_sqlite.html you can unset
OGR_SQLITE_SYNCHRONOUS and it should improve performance. So, use
Sys.setenv(OGR_SQLITE_SYNCHRONOUS="OFF")
in your script/session before you wish to call writes. The SQLite and
GPKG writes are still 11x and 13x respectively slower than shapefile writes
but not terribly bad. Below is what resulted in these numbers. Output
from my .Rout file follows:
> library(sf)
Linking to GEOS 3.5.1, GDAL 2.2.0, proj.4 4.9.2
> library(rbenchmark)
>
> n <- 1000
> d <- data.frame(a=seq_len(n),x=rnorm(n,1,1),y=rnorm(n,1,1))
> mp1 <- st_as_sf(d,coords=c('x','y'))
>
> td <- tempdir()
> file.remove(list.files(td,full.names=TRUE))
logical(0)
> stwrite <- function(dat, dsn, ...) {
+ if (file.exists(dsn)) unlink(dsn)
+ st_write(dat,dsn=dsn, ...)
+ }
>
> Sys.setenv(OGR_SQLITE_SYNCHRONOUS="OFF")
>
> benchmark(
+ stwrite(mp1, dsn=file.path(td,'data1.shp'), driver='ESRI Shapefile'),
+ stwrite(mp1, dsn=file.path(td,'data2.sqlite'), layer="layer1",
driver='SQLite'),
+ stwrite(mp1, dsn=file.path(td,'data2.sqlite'), layer="layer2",
driver='SQLite'),
+ stwrite(mp1, dsn=file.path(td,'data3.gpkg'), layer="layer1"),
+ stwrite(mp1, dsn=file.path(td,'data3.gpkg'), layer="layer2"),
+ replications=5
+ )
test
1 stwrite(mp1, dsn = file.path(td, "data1.shp"), driver =
"ESRI Shapefile")
2 stwrite(mp1, dsn = file.path(td, "data2.sqlite"), layer = "layer1",
driver = "SQLite")
3 stwrite(mp1, dsn = file.path(td, "data2.sqlite"), layer = "layer2",
driver = "SQLite")
4 stwrite(mp1, dsn = file.path(td, "data3.gpkg"),
layer = "layer1")
5 stwrite(mp1, dsn = file.path(td, "data3.gpkg"),
layer = "layer2")
replications elapsed relative user.self sys.self user.child sys.child
1 5 0.034 1.000 0.028 0.008 0 0
2 5 0.374 11.000 0.060 0.312 0 0
3 5 0.359 10.559 0.084 0.272 0 0
4 5 0.454 13.353 0.192 0.260 0 0
5 5 0.451 13.265 0.172 0.280 0 0
> sessionInfo()
R version 3.4.1 (2017-06-30)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 16.04.3 LTS
Matrix products: default
BLAS: /usr/lib/libblas/libblas.so.3.6.0
LAPACK: /usr/lib/lapack/liblapack.so.3.6.0
locale:
[1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C
[3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8
[5] LC_MONETARY=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8
[7] LC_PAPER=en_US.UTF-8 LC_NAME=C
[9] LC_ADDRESS=C LC_TELEPHONE=C
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C
attached base packages:
[1] tools stats graphics grDevices utils datasets methods
[8] base
other attached packages:
[1] rbenchmark_1.0.0 sf_0.5-4
loaded via a namespace (and not attached):
[1] compiler_3.4.1 magrittr_1.5 DBI_0.7 units_0.4-5
Rcpp_0.12.12
[6] udunits2_0.13 grid_3.4.1
>
>
> proc.time()
user system elapsed
1.436 1.340 2.791
On Thu, Aug 24, 2017 at 5:44 PM, Loïc Dutrieux <loic.dutrieux at conabio.gob.mx
> wrote:
>
>
> On 24/08/17 10:23, Roger Bivand wrote:
>
>> On Thu, 24 Aug 2017, manuel.schneider at agroscope.admin.ch wrote:
>>
>> Dear list
>>>
>>> I am searching alternatives to ESRI shapefiles for the storage of GPS
>>> data, i.e. tagged point features, and came across SpatialLite or
>>> Geopackage. Unfortunately writing to both formats is very slow compared to
>>> shapefiles making practical use impossible.
>>>
>>> library(sf)
>>> library(rgdal)
>>> library(RSQLite)
>>>
>>> n<- 1000
>>> d <-data.frame(a=1:n, X=rnorm(n,1,1), Y=rnorm(n,1,1))
>>> mp1 <- st_as_sf(d, coords=c("X","Y"))
>>>
>>> t1 <- system.time(st_write(mp1, dsn = 'C:/Temp/data1.shp', driver =
>>> 'ESRI Shapefile'))
>>> t2 <- system.time(st_write(mp1, dsn = 'C:/Temp/test.sqlite', layer =
>>> 'data1', driver = 'SQLite'))
>>> t3 <- system.time(st_write(mp1, "C:/Temp/data1.gpkg"))
>>>
>>> rbind(t1,t2,t3)[,1:3]
>>>
>>> user.self sys.self elapsed
>>> t1 0.03 0.03 0.09
>>> t2 0.53 5.04 29.33
>>> t3 0.48 4.29 32.19
>>>
>>> As n increases, processing time explodes for SpatialLite and Geopackage,
>>> and I usually have a couple of 10000 points to store. Any experiences of
>>> others would be highly appreciated.
>>>
>>
>> Fedora 26 64-bit:
>>
>> n 1000
>>
>> rbind(t1,t2,t3)[,1:3]
>>>
>> user.self sys.self elapsed
>> t1 0.007 0.001 0.010
>> t2 0.067 0.035 0.103
>> t3 0.029 0.042 0.073
>>
>> n 25000
>>
>> rbind(t1,t2,t3)[,1:3]
>>>
>> user.self sys.self elapsed
>> t1 0.120 0.032 0.153
>> t2 0.412 0.829 1.247
>> t3 0.645 0.834 1.487
>>
>> R version 3.4.1 (2017-06-30)
>> Platform: x86_64-pc-linux-gnu (64-bit)
>> Running under: Fedora 26 (Workstation Edition)
>> other attached packages:
>> [1] sf_0.5-3
>>
>> loaded via a namespace (and not attached):
>> [1] compiler_3.4.1 magrittr_1.5 tools_3.4.1 DBI_0.7
>> units_0.4-5
>> [6] Rcpp_0.12.12 udunits2_0.13 grid_3.4.1
>>
>>
> I also get large differences on ubuntu 16.04 64-bits with ssd;
> particularly when writing a second layer to an existing geopackage
>
> library(sf)
>
> n <- 1000
> d <- data.frame(a=1:n, X=rnorm(n,1,1), Y=rnorm(n,1,1))
> mp1 <- st_as_sf(d, coords=c("X","Y"))
>
> td <- tempdir()
> file.remove(list.files(td, full.names = TRUE))
>
> t1 <- system.time(st_write(mp1, dsn = file.path(td, 'data1.shp'), driver =
> 'ESRI Shapefile'))
> t2 <- system.time(st_write(mp1, dsn = file.path(td, 'data2.sqlite'), layer
> = 'layer1', driver = 'SQLite'))
> t3 <- system.time(st_write(mp1, dsn = file.path(td, 'data2.sqlite'), layer
> = 'layer2', driver = 'SQLite'))
> t4 <- system.time(st_write(mp1, dsn = file.path(td, 'data3.gpkg'), layer =
> 'layer1'))
> t5 <- system.time(st_write(mp1, dsn = file.path(td, 'data3.gpkg'), layer =
> 'layer2'))
>
> rbind(t1,t2,t3,t4,t5)[,1:3]
>
> user.self sys.self elapsed
> t1 0.012 0.000 0.010
> t2 0.180 0.456 8.993
> t3 0.220 0.460 10.637
> t4 0.016 0.064 0.082
> t5 0.200 0.472 9.199
>
> R version 3.4.0 (2017-04-21)
> Platform: x86_64-pc-linux-gnu (64-bit)
> Running under: Ubuntu 16.04.2 LTS
>
> other attached packages:
> [1] sf_0.5-3 raster_2.5-8 sp_1.2-4
>
> loaded via a namespace (and not attached):
> [1] compiler_3.4.0 magrittr_1.5 DBI_0.6-1 tools_3.4.0
> units_0.4-5 yaml_2.1.14 Rcpp_0.12.10 udunits2_0.13 grid_3.4.0
> lattice_0.20-35
>
> Cheers,
> Loïc
>
>
> There is no need to load rgdal or RSQLite, neither are needed or used. For
>> portability use tempdir():
>>
>> t1 <- system.time(st_write(mp1, dsn = paste0(td, 'data1.shp')))
>> t2 <- system.time(st_write(mp1, dsn = paste0(td, 'test.sqlite'), layer =
>> 'data1', driver = 'SQLite'))
>> t3 <- system.time(st_write(mp1, paste0(td, 'data1.gpkg')))
>>
>> Maybe an order of magnitude difference because the databases need
>> initialising, but nothing like your scale; does 32/64 bit make a difference?
>>
>> I'm assuming that you installed sf as a Windows binary from CRAN?
>>
>> Consider using a github issue when others have tried tis out on other
>> platforms.
>>
>> Roger
>>
>> Many thanks
>>> Manuel
>>>
>>>
>>> ------
>>> R version 3.4.1 (2017-06-30)
>>> Platform: i386-w64-mingw32/i386 (32-bit)
>>> Running under: Windows 7 (build 7601) Service Pack 1
>>>
>>> Matrix products: default
>>>
>>> locale:
>>> [1] LC_COLLATE=German_Switzerland.1252 LC_CTYPE=German_Switzerland.1252
>>> [3] LC_MONETARY=German_Switzerland.1252 LC_NUMERIC=C
>>> [5] LC_TIME=German_Switzerland.1252
>>>
>>> attached base packages:
>>> [1] stats graphics grDevices utils datasets methods base
>>>
>>> other attached packages:
>>> [1] sf_0.5-3 RSQLite_2.0 rgdal_1.2-8 sp_1.2-5
>>>
>>> loaded via a namespace (and not attached):
>>> [1] Rcpp_0.12.12 lattice_0.20-35 digest_0.6.12 grid_3.4.1
>>> DBI_0.7
>>> [6] magrittr_1.5 units_0.4-5 rlang_0.1.2 blob_1.1.0
>>> tools_3.4.1
>>> [11] udunits2_0.13 bit64_0.9-7 bit_1.1-12 compiler_3.4.1
>>> memoise_1.1.0
>>> [16] tibble_1.3.4
>>>
>>> _______________________________________________
>>> 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
>
[[alternative HTML version deleted]]
More information about the R-sig-Geo
mailing list