How do I read a layer from a .gpkg file whilst selecting on an attribute?

Question

I have a GeoPackage (.gpkg) database with only one layer:

ogrListLayers("data/LM_data/zipped/ay_riks.gpkg")
[1] "ay_riks"
attr(,"driver")
[1] "GPKG"
attr(,"nlayers")
[1] 1

There are too many objects and I'd like to read only a subset of them based on an attribute:

library(sf)
library(rgdal)
library(tidyverse)    
dta<-st_read("data/LM_data/zipped/ay_riks.gpkg",
                 layer = "ay_riks",
                 EWKB = T,
                 geom_column="geom",
                 query= "SELECT * FROM ay_riks WHERE KOMMUNKOD='2480';")

I get an error:

Reading layer `ay_riks' from data source `/Users/chihayakenji/Documents/spatial_r/data/LM_data/zipped/ay_riks.gpkg' using driver `GPKG'
Error in st_sf(x, ..., agr = agr, sf_column_name = sf_column_name) : 
  no simple features geometry column present

What is the correct way of doing this?

Answer 1

Although this does not explicitly resolve your issue, the following example should demonstrate that the st_read function can be used successfully in conjunction with an SQL query. As mentioned above, the first step is to confirm that the layer within the GeoPackage contains simple feature geometry column:

ogrinfo lut_data.gpkg 
...
1: lut_data (Multi Polygon)

We can read in the gpkg into R initially without any SQL query to confirm that all features are read:

library(sf)
library(rgdal)
library(tidyverse)

data <- st_read('lut_data.gpkg', layer='lut_data')

We can confirm that all features have been read in:

dim(data)
[1] 60  3

and plot the output

plot(data)

We can now repeat the above steps, but only import the features where the lut == 'CHF', as follows:

 data <- st_read('lut_data.gpkg', layer='lut_data', \ 
                query='SELECT * FROM lut_data WHERE LUT ="CHF"')

We should now have imported a subset of the features:

dim(data)
[1] 42  3

and we can plot the data to confirm the output (and check for missing features from the above figure).