unknown wkbType dim in switch

[gdrolet]

Why is st_read_db dependent on the PostgreSQL driver used for the connection:

# using <PostgreSQLDriver> from package RPostgreSQL
conn <- DBI::dbConnect(RPostgreSQL::PostgreSQL(), 
    dbname = "postgis")
st_read_db(conn, query = "select geom from plots limit 10;")

Simple feature collection with 10 features and 0 fields
geometry type:  POINT
dimension:      XY
bbox:           xmin: 1802914 ymin: 1076334 xmax: 1907419 ymax: 1334777
epsg (SRID):    3175
proj4string:    +proj=aea +lat_1=42.122774 +lat_2=49.01518 +lat_0=45.568977 +lon_0=-83.248627 +x_0=1000000 +y_0=1000000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs
precision:      double (default; no precision model)
                             geom
1  POINT(1849836.61661095 1076...
2  POINT(1849806.67270222 1076...
3  POINT(1875028.97521343 1116...
4  POINT(1874767.55838702 1116...
5  POINT(1803250.35520253 1334...
6  POINT(1802913.8109178 13347...
7  POINT(1907418.52662745 1223...
8  POINT(1840845.46786735 1220...
9  POINT(1836423.52704828 1226...
10 POINT(1836041.74915185 1226...

# using <PqDriver> from package RPostgres
conn <- DBI::dbConnect(RPostgres::Postgres(), 
    dbname = "postgis")
st_read_db(conn, query = "select geom from plots limit 10;")

Error in eval(substitute(expr), envir, enclos) : 
  unknown wkbType dim in switch

However, using this driver works if I use the results from a previous query:

library(data.table)
conn <- DBI::dbConnect(RPostgres::Postgres(), 
    dbname = "postgis")
plots <- data.table(DBI::dbGetQuery(conn,
    dplyr::build_sql("
        SELECT   id_pe, geom
        FROM     plots
        LIMIT    10;")))
plots[, sfc := st_as_sfc(structure(geom, class = "WKB"), EWKB = TRUE, 
    crs = "+init:epsg=3175")]
plots[, str(sfc)]

Is something missing in the code for st_read_db (or absent from the doc) or does something differ in the way each driver return geometries (or I miss extra options when calling st_read_db with driver PqDriver)?

[edzer]

where in the source code of st_read_db do you see that dependency?

[gdrolet]

It isn't there.. that's what I mean. I think a check should be added to the code and this should be mentioned in the doc for that function: what do you think?

Personally I always use ``RPostgres::Postgres()` to connect to my databases so it took me a bit before I found that the driver was the problem.

[edzer]

Should be solved now, please check -- I added an EWKB option that now also defaults to TRUE if the driver is from RPostgres.

[gdrolet]

Awesome! Works like a charm, thanks!

[edzer]

Cool! Let me know when you get stuck.

[jsta]

Is this driver detection switch activated on st_write_db as well? Here is my comparison code:

library(sp)
library(sf)
data(meuse)
sf <- st_as_sf(meuse, coords = c("x", "y"), crs = 28992)'

Testing st_write_db with RPostgres
con <- dbConnect(RPostgres::Postgres())
st_write_db(con, sf, "meuse_tbl", dropTable = FALSE)

Error in result_create(conn@ptr, statement) : expecting a single value
In addition: Warning message:
In if (DBI::dbExistsTable(conn, table)) { :
the condition has length > 1 and only the first element will be used

Testing st_write_db with RPostgreSQL
library(RPostgreSQL)
conn <- dbConnect(PostgreSQL())
st_write_db(conn, sf, "meuse_tbl", drop_table = FALSE)
st_read_db(conn, "meuse_tbl")

Simple feature collection with 155 features and 12 fields
geometry type: POINT
dimension: XY
...

[edzer]

Withouth having tried but according to the docs, DBI::dbExistsTable should return a scalar logical (DBI_0.5-17) so this looks like an RPostgres issue rather than an sf issue.

[etiennebr]

There is no switch per say, but the tests are currently run using RPostgreSQL, so that might explain why these issues never got caught. I'll add an issue to test RPostgres as it seems more up-to-date than RPostgreSQL and given the author I would expect it to catch up very quickly with latest DBI developments.

Also note that st_read_db and st_write_db could well be called st_read_postgis and st_write_postgis because they are very specific and tested using postgis, for now. DBI package is preparing an implementation for custom database types, and also for listing database objects; this will facilitate support for multiple drivers.

[edzer]

Should we go there (st_read_mydatabase), or can we factor out / make conditional the code specific to PostGIS?

[etiennebr]

Indeed, that would allow driver specific solutions. I think we should go with this solution. The thing I realized is that it's not just the DBI layer, but there's the spatial-db layer that we have to manage (where are geometries and crs listed, how they handle crs, what function they use to create geometries). st_read_db and st_write_db could just be a switch to all the driver specific functions.

Future development in DBI will make it easier with things like complete table listing.

[edzer]

Good idea. The link in your comment does not work, could you give me the right one?

[etiennebr]

I've fixed the link r-dbi/DBI#24