[R-sig-Geo] Using duckdb spatial module from R (with sf)?

[Carl Boettiger]

Hi folks,


I'm curious if anyone has explored the relatively new spatial
extension in duckdb (https://duckdb.org/docs/extensions/spatial.html)
or has any pointers/tutorials regarding its use from R?

Consider the following minimal example that seeks to use the sf
library to speak to duckdb:

  library(duckdb)
  library(sf)
  conn <- DBI::dbConnect(duckdb::duckdb())
  status <- DBI::dbExecute(conn, "INSTALL 'spatial';")
  status <- DBI::dbExecute(conn, "LOAD 'spatial';")

  test <- data.frame(site = letters[1:10], latitude = 1:10, longitude = 1:10)
  DBI::dbWriteTable(conn, "test", test)

# Ok, let's try and make a geometry column
  query <- paste(
    'SELECT *, ST_Point(longitude, latitude) AS geom',
    'FROM "test"'
  )

  ex <- st_read(con, query=query, geometry_column = "geom")
  ## error: reading wkb type 0 is not supported


  ex <- st_read(con, query=query, geometry_column = "geom", EWKB = FALSE)
  ## prints: wkbType: 1572864
  ## Error in CPL_read_wkb(x, EWKB, spatialite) : unsupported wkbType
dim in switch

 We seem to get closer than I might have hoped (sf doesn't just insist
that conn isn't postgresgis), but is getting stuck on the encoding of
the wkb.  Is this something we can work around?  The queries seem to
run successfully, I just seem to be getting some unsupported ecoding
of the WKB geometry column....

---
Carl Boettiger
http://carlboettiger.info/