Visualization of DuckDB tables

[Cidree]

I open this issue to implement a plot(), autoplot(), mapview()... method for a DuckDB table.
The idea is to map the data that is stored in DuckDB without intermediate step of reading the data into memory.

Since there are many options, I think we could create two options:

• Static map: maybe using ggplot2 or tmap.
• Interactive map: using MapLibre with mapgl, tmap or mapview.

I think this issue is not a priority, so we can leave it for a different release.

[e-kotov]

@Cidree but how is it possible without ingesting as sf into R first?

[Cidree]

@e-kotov oh, I explained myself wrong.
What I meant is to create a function that internally ingests the data as sf, to eliminate that step from the user, and have a convenient function to have an overview of the data in a map.

[rafapereirabr]

One way to make things faster / out of memory would be to retrieve the table as a geoarrow and pass it to the spatial interctive map. However, afaik, these packages for interactive visualization in R are not compatible with geoarrow yet. See here. It seems @tim-salabim will fix this soon with the forthcoming {geoarrowDeckgl}

[tim-salabim]

Thanks for the ping @rafapereirabr . Indeed, i am currently working on a package to utilise geoarrow as the vehicle to transfer data from R memory to the browser. I should be able to release an alpha version of this soon. This will be the foundation for geoarrowDeckgl to make large data quickly visible in the browser. Currently, i am planning to support geoarrow, sf, wk and hopefully also terra vector classes. So if you can convert your duckdb tables to one of those, things should work just fine. Obviously, going straight to geoarrow is recommended, as it minimises overhead.

I'll update this thread, once i have a testable version of the package on github. Until then, keep up the good work!

[tim-salabim]

I've just pushed a first alpha version of geoarrowWidget. Still lacks substantial parts of documentation etc, but the basic functionality is there now.

All it does at the moment is to provide functions that let you take a geoarrow file (or url) and append it to an existing htmlwidget (e.g. leaflet or mapgl). To test things, the package comes with its own little widget called geoarrowDummyWidget that will create a web page with some text and (if successful) print a console.log of the supplied geoarrow file in the browsers console.

Here's an example:

library(geoarrowWidget)
library(sf)
library(nanoarrow)
library(geoarrow)

### generate some random points sf =============================================
n = 1e4
dat = data.frame(
  id = 1:n
  , x = runif(n, -160, 160)
  , y = runif(n, -40, 40)
)
dat = st_as_sf(
  dat
  , coords = c("x", "y")
  , crs = 4326
)

### setup and write to tempfile as geoarrow ====================================
### this is where you'd want your DuckDB table converted (instead of this sf) ==
fl = tempfile()
dir.create(fl)
path = file.path(
  fl
  , "test.arrow"
)

interleaved = TRUE

data_stream = nanoarrow::as_nanoarrow_array_stream(
  dat
  , geometry_schema = geoarrow::infer_geoarrow_schema(
    dat
    , coord_type = ifelse(interleaved, "INTERLEAVED", "SEPARATE")
  )
)

nanoarrow::write_nanoarrow(data_stream, path)

### create widget and check browser console ====================================
wgt = geoarrowDummyWidget(file = path)

options(viewer = NULL)

wgt

This will create a web page with the data attached as a geoarrow object. In the browser console (press F12) this will look something like this (depending on the browser obviously, Librewolf in my case)

The page source (press <CTRL + u>) will look something like this, where in line 11 you see the attached test.arrow file and above the necessary scripts (arrow and geoarrow JS bindings) to process the data further in JavaScript.

To test integration potential with duckspatial you would likely want to create the nanoarrow::as_nanoarrow_array_stream() directly from your DuckDB table, rather than going through sf...

Let me know if I can help.

[e-kotov]

To test integration potential with duckspatial you would likely want to create the nanoarrow::as_nanoarrow_array_stream() directly from your DuckDB table, rather than going through sf...

awesome, that's very close to what we are working on right now!
https://github.com/e-kotov/duckspatial/blob/lazy/R/ddbs_options.R#L5-L10

We will soon have support for different outputs of our functions and that already uses geoarrow and nanoarrow. It will be easy to adapt the code to produce an arrow stream that we can pass over.

[e-kotov]

r-spatial/geoarrowWidget#1 about to be released! Note to self: time to review how to adjust our code that handles outputs from functions to create compatible geoarrow streams/objects to pass over to the widget.

[e-kotov]

@tim-salabim currently adding new PR #121 to make duckspatial compatible with https://github.com/r-spatial/geoarrowDeckglLayers and https://github.com/r-spatial/geoarrowWidget . Based on your work on those so far, do you think there is any chance we could stream geometry directly from DuckDB table from R process into the html file? E.g. so that it works like https://github.com/developmentseed/lonboard in Python + Jupyter instead of having to dump data into intermediary arrow file on disk?

[tim-salabim]

I don't think that is possible because of the way htmlwidgets works. We essentially create a static html file to which we attach the data which is done via a tempfile() dump. IIUC, lonboard + Jupyter is more similar to a shiny setup, where you get 2 way comms, but I haven't investigated shiny yet. What probably could work (if DuckDB is being served via localhost) is to prepare the table on the R side and then send a query to the browser and execute it there via DuckDB-Wasm once that is available... Not sure about the details, though...

[kylebarron]

from R process into the html file? E.g. so that it works like developmentseed/lonboard in Python + Jupyter instead of having to dump data into intermediary arrow file on disk?

Just noting that because it's using Jupyter, which already has a persistent connection between Python and the browser, we never have to create temporary HTML files for visualization, which is another point of performance.

[tim-salabim]

Ah, yeah, it's similar to how reticulate passes arrow data between R and python processes in quarto. This could be another route, but quarto only supports observable js IINM...