How to load spatial features into R from MariaDB using sf-package

[Daikoro]

I am looking for a way to read spatial data from MariaDB into R.

Connecting to the database was straightforward using DB::dbConnect and RmariaDB::MariaDB. I can load the data-layer to using dplyr::collect, but it doesn't recognise the geometry column. This is a raw / blob column and I am not sure how to convert that into something that can be read into R with the sf-package.

I've seen people using sf::st_read directly to read in the data. When I try that, I get the error: "object 'sfc' not found".

Can anyone point me into the right direction to read in spatial data?

[technocrat]

Only MSSQLSpatial and PostgreSQL are supported. MySql, MariaDB requires the conversion of the geometry to a WKT representation.

[Daikoro]

That explains why st_read isn't working. How would you go about converting to WKT? Is it better to use MariaDB or R?

[technocrat]

I've been avoiding the issue because I have vague intentions of setting up Postgres, but I'd definitely do it in R, which has a toolset to convert the geometry objects to WKT. So far as I know MariaDB can use that as a type, but I don't know how the return trip works—whether a query returns a geometry or a WKT that has to be again converted.

[Daikoro]

I just wanted to share my final workaround, in case someone else might be interested. I haven't fully tested it and there are probably better ways to do it, but it worked for me using a relatively small dataset.
Note that the column containing the geometries is named 'shape' in the example below.

library(dbplyr)
library(sf)
library(DBI)
library(RMariaDB)

con_geo <- DBI::dbConnect(RMariaDB::MariaDB(),
                      host =  "my-host-name",
                      user = "my-user-name",
                      password = rstudioapi::askForPassword(),
                       dbname = "name-of-db"
)
tbl(con_geo, "name-of-tbl") |>
  mutate(shape = sql("ST_AsText(shape)")) |>
  collect() |> 
  mutate(
    shape = st_as_sfc(shape)
  ) |>
  st_as_sf()