Can't write spatial data to table

[latot]

Hi, I'm trying to write spatial data, sadly don't works D:

This is a local test:

Sys.setenv("DOLR_DIR" = "doltdb")

doltr::dolt_init(dir = Sys.getenv("DOLT_DIR", "doltdb"))

nc <- sf::st_read(system.file("shape/nc.shp", package="sf"))

sf::st_write(
 nc,
 doltr::dolt(),
 layer="nc",
 factorsAsCharacter=FALSE,
 driver="MySQL"
)

Result and traceback:

Note: method with signature ‘DBIObject#sf’ chosen for function ‘dbDataType’,
 target signature ‘DoltLocalConnection#sf’.
 "DoltConnection#ANY" would also be valid
Error in .local(conn, name, value, ...) : 
  unused argument (factorsAsCharacter = FALSE)
> traceback()
7: dbWriteTable(conn, name, to_postgis(conn, value, binary), ..., 
       row.names = row.names, overwrite = overwrite, append = append, 
       field.types = field.types)
6: dbWriteTable(conn, name, to_postgis(conn, value, binary), ..., 
       row.names = row.names, overwrite = overwrite, append = append, 
       field.types = field.types)
5: .local(conn, name, value, ...)
4: dbWriteTable(dsn, name = layer, value = obj, append = append, 
       overwrite = delete_layer, factorsAsCharacter = factorsAsCharacter, 
       ...)
3: dbWriteTable(dsn, name = layer, value = obj, append = append, 
       overwrite = delete_layer, factorsAsCharacter = factorsAsCharacter, 
       ...)
2: st_write.sf(nc, doltr::dolt(), layer = "nc", factorsAsCharacter = FALSE, 
       driver = "MySQL")
1: sf::st_write(nc, doltr::dolt(), layer = "nc", factorsAsCharacter = FALSE, 
       driver = "MySQL")

Thx!

[noamross]

Thanks for the report, @latot. Spatial support is on our list (#38), though it may be some time before we support it. Question: have you tested this with MySQL? I don't think sf actually currently supports the spatial types that MySQL or Dolt uses, so part of the work would be PR'ing those wrappers into sf (there are only postgres wrappers in https://github.com/r-spatial/sf/blob/5dde39c8261dc6b202e6cde9d41ad3bf0f46aa3a/R/db.R)

[latot]

Mmmm, no, I haven't tested it, I can try it. I want this!

[latot]

Sooo, didn't works, but with a different problem (?

Error: Cannot get geometry object from data you send to the GEOMETRY field [1416]
> traceback()
22: stop(structure(list(message = "Cannot get geometry object from data you send to the GEOMETRY field [1416]", 
        call = NULL, cppstack = NULL), class = c("Rcpp::exception", 
    "C++Error", "error", "condition")))
21: result_bind(res@ptr, params)
20: dbBind(rs, params)
19: dbBind(rs, params)
18: dbSend(conn, statement, params, is_statement = TRUE)
17: .local(conn, statement, ...)
16: dbSendStatement(conn, statement, ...)
15: dbSendStatement(conn, statement, ...)
14: dbExecute(conn, query, params = unname(as.list(value)))
13: dbExecute(conn, query, params = unname(as.list(value)))
12: .nextMethod(conn = conn, name = name, value = value)
11: callNextMethod()
10: dbAppendTable(conn = conn, name = name, value = value)
9: dbAppendTable(conn = conn, name = name, value = value)
8: .local(conn, name, value, ...)
7: dbWriteTable(conn, name, to_postgis(conn, value, binary), ..., 
       row.names = row.names, overwrite = overwrite, append = append, 
       field.types = field.types)
6: dbWriteTable(conn, name, to_postgis(conn, value, binary), ..., 
       row.names = row.names, overwrite = overwrite, append = append, 
       field.types = field.types)
5: .local(conn, name, value, ...)
4: dbWriteTable(dsn, name = layer, value = obj, append = append, 
       overwrite = delete_layer, factorsAsCharacter = factorsAsCharacter, 
       ...)
3: dbWriteTable(dsn, name = layer, value = obj, append = append, 
       overwrite = delete_layer, factorsAsCharacter = factorsAsCharacter, 
       ...)
2: st_write.sf(nc, con, layer = "nc", factorsAsCharacter = FALSE, 
       driver = "MySQL")
1: sf::st_write(nc, con, layer = "nc", factorsAsCharacter = FALSE, 
       driver = "MySQL")

[noamross]

Yes, so this error is because sf doesn't yet have MySQL-specific methods yet, which is what we would wrap. I believe that the MySQL geometry types are slight tweaks on EWKB (well-known binary's unofficial extension that includes the projection info in the type). Ideally we'll contribute methods to sf for MySQL/MariaDB, and then add the methods to doltr to use those for DoltConnections.

[n8layman]

Issue's #38 and #60

[n8layman]

The goals is to add a WEKB reader to doltr that can be pulled into sf eventually. Write an st_read method to handle this case.
We will need to read in a table that has a sf geometry class column. It should work for things like dolt diff even without unpacking the sf columns.

[n8layman]

Relevant resources:

r-spatial/sf#757
https://dev.mysql.com/doc/refman/8.0/en/gis-data-formats.html

[n8layman]

@latot would you be willing to test a potential fix to this issue? If so, install doltr from the following branch and try out st_read() and st_write(). No need to set driver="MySQL"

remotes::install_github("ecohealthalliance/doltr@start-write-sf")

[latot]

Yes, I don't know if I can now, but I'll try in the week.

[latot]

Results:

> sf::st_write(
 nc,
 doltr::dolt(),
 layer="nc",
 factorsAsCharacter=FALSE
)
Note: method with signature ‘DBIObject#sf’ chosen for function ‘dbDataType’,
 target signature ‘DoltLocalConnection#sf’.
 "DoltConnection#ANY" would also be valid
Error in str_split(sf::st_crs(col)$input, ":") : 
  Can't found the function "str_split"

sf::st_write(
 nc,
 doltr::dolt(),
 layer="nc" 
)

Same error as above.

> traceback()
14: unlist(str_split(sf::st_crs(col)$input, ":"))
13: quoteRecords(conn, records)
12: valuesClause(conn, records)
11: insertClause(conn, table, batch)
10: f(records)
9: dbx:::inBatches(records, batch_size, function(batch) {
       sql <- insertClause(conn, table, batch)
       if (show_sql) 
           message(sql)
       dbx:::selectOrExecute(conn, sql, batch, returning = returning)
   })
8: dbxInsert(conn = conn, table = name, records = value, batch_size = batch_size)
7: .local(conn, name, value, ...)
6: FUN(conn, name, value, field.types = field.types, row.names = row.names, 
       overwrite = overwrite, append = append, temporary = temporary, 
       batch_size = batch_size, ...)
5: .local(conn, name, value, ...)
4: dbWriteTable(dsn, name = layer, value = obj, append = append, 
       overwrite = delete_layer, factorsAsCharacter = factorsAsCharacter, 
       ...)
3: dbWriteTable(dsn, name = layer, value = obj, append = append, 
       overwrite = delete_layer, factorsAsCharacter = factorsAsCharacter, 
       ...)
2: st_write.sf(nc, doltr::dolt(), layer = "nc")
1: sf::st_write(nc, doltr::dolt(), layer = "nc")