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

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?

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

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

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.

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.


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() |> 
    shape = st_as_sfc(shape)
  ) |>
1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.