Hey Carl, this is super cool! Is there a way to get the query result as wkb and read the wkb using {wk}? That’s where I might start—validating the wkb output :)
On Tue, Aug 15, 2023 at 19:02 Carl Boettiger <cboet...@gmail.com> wrote: > Hi folks, > > > I'm curious if anyone has explored the relatively new spatial > extension in duckdb (https://duckdb.org/docs/extensions/spatial.html) > or has any pointers/tutorials regarding its use from R? > > Consider the following minimal example that seeks to use the sf > library to speak to duckdb: > > library(duckdb) > library(sf) > conn <- DBI::dbConnect(duckdb::duckdb()) > status <- DBI::dbExecute(conn, "INSTALL 'spatial';") > status <- DBI::dbExecute(conn, "LOAD 'spatial';") > > test <- data.frame(site = letters[1:10], latitude = 1:10, longitude = > 1:10) > DBI::dbWriteTable(conn, "test", test) > > # Ok, let's try and make a geometry column > query <- paste( > 'SELECT *, ST_Point(longitude, latitude) AS geom', > 'FROM "test"' > ) > > ex <- st_read(con, query=query, geometry_column = "geom") > ## error: reading wkb type 0 is not supported > > > ex <- st_read(con, query=query, geometry_column = "geom", EWKB = FALSE) > ## prints: wkbType: 1572864 > ## Error in CPL_read_wkb(x, EWKB, spatialite) : unsupported wkbType > dim in switch > > We seem to get closer than I might have hoped (sf doesn't just insist > that conn isn't postgresgis), but is getting stuck on the encoding of > the wkb. Is this something we can work around? The queries seem to > run successfully, I just seem to be getting some unsupported ecoding > of the WKB geometry column.... > > --- > Carl Boettiger > http://carlboettiger.info/ > > _______________________________________________ > R-sig-Geo mailing list > R-sig-Geo@r-project.org > https://stat.ethz.ch/mailman/listinfo/r-sig-geo > [[alternative HTML version deleted]] _______________________________________________ R-sig-Geo mailing list R-sig-Geo@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-geo