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).

1 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.

  • This worked perfectly, thank you!
    – sligocki
    Mar 27, 2021 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.