Load data from GPKG incrementally in R

Question

I need to process a GPKG file in R, but it's too large (50GB) to load the entire thing into memory at once. Is there any way to incrementally load the results? Right now we are loading it with a command like this:

data <- sf::st_read(filename,
                    query = glue("SELECT * FROM layer ",
                                 "LIMIT {num_rows_per_batch} OFFSET {batch_offset};"))

in order to load only num_rows_per_batch at a time. However, when running this with a large batch_offset, it takes a long time (I'm guessing because behind the scenes SQLite is iterating through all the previous results. Rather than loading each batch with an entirely separate query, is there any way to use a single query, but load the results incrementally, so that we could process them one batch at a time? I'm thinking something like the way RSQLite::dbFetch() allows loading one batch at a time (see, ex: the "Batched queries" section in the RSQLite vignettes).

Answer 1

Have you tried creating a numeric INDEX on the rows and then selecting on a range of that variable? Once the index is created (which might be slow) selecting on a range of it should be quick.

You can create an index on a column in a geopackage because it is also an SQLIte database file. For a table called bigdata run something like:

CREATE INDEX bigdata_fid ON bigdata(fid);

using either the SQLite command line or the DBI package (which I think has a generic "execute this SQL" function) using a connection from the RSQLite package. Then range selections on fid should be fast. You'll have to craft the query string yourself.

I think all geopackage layers should have a unique numeric fid suitable for this, otherwise you'll need to create a new column or use an existing one.