Conversion Error -- Spatial extension

[rruiz-s]

Hi,

First of all thank you very much for sharing and working on the duckdb-r library. I wonder if someone could give me a hand.

When loading/transfering a spatial object (sf) by using duckdb::dbWriteTable() , I got the following error

Error in `duckdb_result()`:
! Invalid Error: Conversion Error: Expected geometry type at position '0' near: '0'|<---
ℹ Context: rapi_execute
ℹ Error type: INVALID
ℹ Raw message: Conversion Error: Expected geometry type at position '0' near: '0'|<---
Run `rlang::last_trace()` to see where the error occurred.

These were the steps:

1. Load the sf object, for example using GiscoR

zurich <- giscoR::gisco_get_nuts(country="CH", nuts_level=2) |>
  dplyr::filter(NAME_LATN == "Zürich")

2. Create the database

con <- duckdb::dbConnect(duckdb())

3. Install and load the spatial extension

dbExecute(con, "install spatial; load spatial;")

4. Transfer the sf object to duckdb

duckdb::dbWriteTable(con, "zurich", zurich)

The error appeared in the last step (4). To be fair, the official documentation mentions "data frame", not a sf object. Since it is trying to read the geometry, I would say it is also possible to transfer a sf object into the duckdb database. I tried other sf objects (in case the geometry was invalid) with the same result. The CRS is 4326.

The duckdb-r version used was:

> dbGetQuery(con, "PRAGMA version;")
  library_version  source_id codename
1          v1.4.1 b390a7c376   Andium

Thank you for your time

rlang::last_trace(drop = FALSE)
<error/rlang_error>
Error in duckdb_result():
! Invalid Error: Conversion Error: Expected geometry type at position '0' near: '0'|<---
ℹ Context: rapi_execute
ℹ Error type: INVALID
ℹ Raw message: Conversion Error: Expected geometry type at position '0' near: '0'|<---

---

Backtrace:
▆

1. ├─duckdb::dbWriteTable(con, "zurich", zurich)
2. ├─duckdb::dbWriteTable(con, "zurich", zurich)
3. │ └─sf (local) .local(conn, name, value, ...)
4. │ ├─DBI::dbWriteTable(...)
5. │ └─duckdb::dbWriteTable(...)
6. │ └─duckdb (local) .local(conn, name, value, ...)
7. │ ├─DBI::dbExecute(...)
8. │ └─DBI::dbExecute(...)
9. │ ├─DBI::dbSendStatement(conn, statement, ...)
10. │ └─DBI::dbSendStatement(conn, statement, ...)
11. │ ├─DBI::dbSendQuery(conn, statement, ...)
12. │ └─duckdb::dbSendQuery(conn, statement, ...)
13. │ └─duckdb (local) .local(conn, statement, ...)
14. │ └─duckdb:::duckdb_result(connection = conn, stmt_lst = stmt_lst, arrow = arrow)
15. │ └─duckdb:::duckdb_execute(res)
16. │ └─duckdb:::rethrow_rapi_execute(...)
17. │ ├─rlang::try_fetch(...)
18. │ │ ├─base::tryCatch(...)
19. │ │ │ └─base (local) tryCatchList(expr, classes, parentenv, handlers)
20. │ │ │ └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
21. │ │ │ └─base (local) doTryCatch(return(expr), name, parentenv, handler)
22. │ │ └─base::withCallingHandlers(...)
23. │ └─duckdb:::rapi_execute(stmt, convert_opts)
24. ├─duckdb (local) <fn>(...)
25. │ └─rlang::abort(error_parts, class = "duckdb_error", !!!fields)
26. │ └─rlang:::signal_abort(cnd, .file)
27. │ └─base::signalCondition(cnd)
28. └─rlang (local) <fn>(<dckdb_rr>)
29. └─handlers[1L]

30. └─duckdb:::rethrow_error_from_rapi(e, call)
    

31.   └─rlang::abort(msg, call = call)