2

I would like to read one variable (column) from a very large gpkg file (20GB) into R. It has only one layer. Is it any way to get one column (I assume that it is a character type but it could be a numeric one, I am not sure how this data is stored). I tried this way:

df_pi <- st_read("All_DB2_Shapes.gpkg", query = "SELECT POLY_ID")

But I received this message:

Error in CPL_read_ogr(dsn, layer, query, as.character(options), quiet, : SQL execution failed, cannot open layer. In addition: Warning message: In CPL_read_ogr(dsn, layer, query, as.character(options), quiet, : GDAL Error 1: In ExecuteSQL(): sqlite3_prepare_v2(SELECT POLY_ID): no such column: POLY_ID

If I just try to open it with st_read command then it cannot manage it because of the lack of memory.

  • 1
    Why are you using system.file with a package? That looks for files installed with a package. If I do: system.file("foo.gpkg",package="sf") it returns "" which will lead to your error. Just give the path to the geopackage. – Spacedman Oct 14 at 15:18
  • Thanks, however, it still does not work. I do not know how to open it without all the other attributes and run this query, so it would only read that particular variable that I need. I cannot open the whole thing because it is too big to handle for R. – Malna Oct 16 at 8:13
  • I found out that the variable I need (POLY_ID) is a numeric one. – Malna Oct 16 at 9:22
  • Edit your question and show what you tried and give the new error message. – Spacedman Oct 16 at 9:22
  • I set up wd where the package is stored. – Malna Oct 16 at 9:27
1

I can now duplicate your error with a test package I've made:

> d = st_read("/tmp/test.gpkg",query="select resolution")
Error in CPL_read_ogr(dsn, layer, query, as.character(options), quiet,  : 
  Query execution failed, cannot open layer.
In addition: Warning message:
In CPL_read_ogr(dsn, layer, query, as.character(options), quiet,  :
  GDAL Error 1: In ExecuteSQL(): sqlite3_prepare_v2(select resolution):
  no such column: resolution

That error is because the query doesn't define which table it is trying to select resolution from. Let's add that:

> d = st_read("/tmp/test.gpkg",query="select resolution from test")
Reading layer `test' from data source `/tmp/test.gpkg' using driver `GPKG'
Warning message:
no simple feature geometries present: returning a data.frame or tbl_df 

Now because I'm only selecting one column there's no geometry. d is a simple data frame with no spatial data.

> class(d)
[1] "data.frame"
> head(d)
  resolution
1         10
2         10
3         10

If that's what you want - just the column data and not the locations - then that's answered. If you also want the location info, add it to the query:

> d = st_read("/tmp/test.gpkg",query="select resolution,geom from test")
Reading layer `test' from data source `/tmp/test.gpkg' using driver `GPKG'
Simple feature collection with 8621 features and 1 field
geometry type:  POLYGON
dimension:      XY
bbox:           xmin: -11.11163 ymin: 49.76681 xmax: 2.513016 ymax: 61.58096
epsg (SRID):    4326
proj4string:    +proj=longlat +datum=WGS84 +no_defs

Now I have a spatial object with polygons.

I can refine this query with a where clause if I want to select fewer elements:

> d = st_read("/tmp/test.gpkg",query="select resolution,geom from test where resolution > 10")
Reading layer `test' from data source `/tmp/test.gpkg' using driver `GPKG'
Simple feature collection with 1583 features and 1 field

So the short answer is "add the layer name so the query knows which layer to select from".

  • It worked, thanks. However, it is still using too much memory, so I cannot get the data into R. – Malna Oct 16 at 14:03
  • how many rows are you expecting? You can get the total number of rows of GPKG layer called grid from file bto.gpkg with st_read("bto.gpkg",query="select count(*) from grid") BUT again, edit your question and show the error message. – Spacedman Oct 16 at 14:19
  • The error message is that there is not enough memory. Although I know the number of rows: 29351312. – Malna Oct 16 at 14:24
  • Are you also trying to read the geometry? What are you running? Can you read in a small number of rows by doing something like query="select X from Y LIMIT 100" - which should only read 100 rows? Please edit your question and show your command and paste your error message in. – Spacedman Oct 16 at 14:40
  • I run this code db2.df1 <- read_sf("All_DB2_Shapes.gpkg", query = "SELECT POLY_ID, geom from DB2_Shapes LIMIT 10000000" ) and it could have been executed. Is there any way to define which rows I want to be read, so I could read the data in three steps? – Malna Oct 17 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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