write and read simple features from sqlite

readme.md

2018-03-07 RM Hogervorst MIT licence

Writing and reading between sqlite and sf in R

I wrote this short gist down because this feature is not well documented.

when will I need this?

If you have some potentially expensive computation and want to use the results of that selectively. If you precalculate something, but don't want to load the entire thing in memory.

What?

Example: I calculated great circles (lines over curvature of earth) between all countries in the world, this calculation took minutes.

I want to use this in many ways, but the resulting file is rather large (156 Mb). Per session I might only need a subset of that.

write_sf() actually takes care of many things: sets all sorts of information, makes tables, and writes the sqlite file.

This line mutate(geometry = st_as_sfc(GEOMETRY)) sets the type of that column and gives it the name of geometry. The sf dataframe uses geometry as default column, so this takes care of that.

read_from_sqlite_to_sf.R

library(RSQLite) 
library(sf)
library(dplyr)

## retrieve part of the dataset

connection_object <- DBI::dbConnect(RSQLite::SQLite(), "circles.sqlite") 
## make a databaseconnection filter, collect data, mutate column and plot result.
tbl(connection_object, "circles") %>%
    filter() %>%
    collect() %>% # otherwise the data will remain in the database
    mutate(geometry = st_as_sfc(GEOMETRY)) %>%  # 
    ggplot() +
    geom_sf()

write_to_sqlite.R

library(RSQLite) # is it neccesary?
library(sf)
library(dplyr)

## Then you do your big operation that results in a huge sf -dataframe 

circles <- potentially_huge_operation()

# write the result to a new sqlite database 
write_sf(circles, "circles.sqlite")