Comments (2)
cboettig
commented on June 21, 2024
2
@dcaseykc You are really close, just missing two things:
First, you must remember that duckdb spatial has it's own native encoding of a geometry column that is not WKB. So it is up to you to manually call st_AsWKB to turn the geom column into WKB so other tools like sf can read it. Second, you should tell sf where to find the geometry column. Try this:
library(duckdb)
library(sf)
library(glue)
library(dplyr)
con <- dbConnect(duckdb::duckdb())
dbExecute(con, "install spatial; load spatial;")
dbExecute(con, glue("create table spat as select *
from ST_Read('{system.file('shape/nc.shp', package='sf')}')"))
a1 = dbGetQuery(con, 'select * from spat')
q <- tbl(con, "spat") |> mutate(geometry = st_aswkb(geom)) |> dbplyr::sql_render()
a2 = st_read(con, query = q, geometry_column = "geometry") However, I find all that really verbose and hard to read. duckdbfs is a small wrapper around this syntax with helper functions. The above is the same as:
library(duckdbfs)
nc <- open_dataset("https://github.com/r-spatial/sf/raw/main/inst/shape/nc.shp")
nc |> to_sf()HTH
from duckdb-r.
dcaseykc
commented on June 21, 2024
Assuming my problems are related to this issue (and are not distinct):
The examples below show some attempts at reading/writing from a spatial enabled duckdb. I can get data in through sql but not dbWriteTable or sf::st_write. Nor have I found a way to translate what duckdb returns for the geom column into something R (sf or wk can understand.
library(duckdb)
#> Warning: package 'duckdb' was built under R version 4.3.3
#> Loading required package: DBI
library('sf')
#> Linking to GEOS 3.11.2, GDAL 3.7.2, PROJ 9.3.0; sf_use_s2() is TRUE
library('glue')
# Init DB
con <- dbConnect(duckdb::duckdb())
dbExecute(con, "install spatial; load spatial;")
#> [1] 0
# USE ST read
dbExecute(con, glue("create table spat as select *
from ST_Read('{system.file('shape/nc.shp', package='sf')}')"))
#> [1] 99
# Try to get the stuff
a1 = dbGetQuery(con, 'select * from spat')
# I've not found a way to convert format of geom into something usable
a2 = st_read(con, 'spat') #breaks on the binary
#> Warning in st_read.DBIObject(con, "spat"): Could not find a simple features
#> geometry column. Will return a `data.frame`.
# Try to write it
# Gets errors
nc = sf::read_sf(system.file("shape/nc.shp", package="sf"))
dbWriteTable(con, name = 'nc', nc)
#> Note: method with signature 'DBIObject#sf' chosen for function 'dbDataType',
#> target signature 'duckdb_connection#sf'.
#> "duckdb_connection#ANY" would also be valid
#> Error: rapi_execute: Failed to run query
#> Error: Conversion Error: ParseException: Unknown type: '0106000020AB100000010000000103000000010000001B000000000000A0415E54C000000060FF1D4240000000209D6254C000000080E122424000000080F76354C0000000200523424000000020846854C0000000A09B2B4240000000C06D6F54C00000000026324240000000A0B06C54C000000040633C4240000000A0FA6C54C0000000C07942424000000040E16A54C0000000A0794B424000000060195654C0000000A053494240000000203E5654C000000060DA44424000000020C95454C000000040C0414240000000800D5454C000000080873D4240000000000A5154C000000060F637424000000060D25054C000000060D833424000000080674F54C0000000C090304240000000605A4F54C000000040C42E424000000060E95054C0000000E01B2D4240000000400E5554C000000040872E4240000000C0205754C000000060342D424000000080675754C000000000662B424000000020AA5654C0000000205D26424000000060845754C000000060AC23424000000040025A54C0000000A07C244240000000A0635A54C0000000A03622424000000020965B54C0000000405F21424000000020FC5C54C0000000C0AA1E4240000000A0415E54C000000060FF1D4240'
sf::st_write(nc, dsn = con, layer = 'pts')
#> Error: rapi_execute: Failed to run query
#> Error: Conversion Error: ParseException: Unknown type: '0106000020AB100000010000000103000000010000001B000000000000A0415E54C000000060FF1D4240000000209D6254C000000080E122424000000080F76354C0000000200523424000000020846854C0000000A09B2B4240000000C06D6F54C00000000026324240000000A0B06C54C000000040633C4240000000A0FA6C54C0000000C07942424000000040E16A54C0000000A0794B424000000060195654C0000000A053494240000000203E5654C000000060DA44424000000020C95454C000000040C0414240000000800D5454C000000080873D4240000000000A5154C000000060F637424000000060D25054C000000060D833424000000080674F54C0000000C090304240000000605A4F54C000000040C42E424000000060E95054C0000000E01B2D4240000000400E5554C000000040872E4240000000C0205754C000000060342D424000000080675754C000000000662B424000000020AA5654C0000000205D26424000000060845754C000000060AC23424000000040025A54C0000000A07C244240000000A0635A54C0000000A03622424000000020965B54C0000000405F21424000000020FC5C54C0000000C0AA1E4240000000A0415E54C000000060FF1D4240'
dbWriteTable(con, 'nc', nc, append = T, field.types = c('id' = 'INTEGER', 'geometry' = 'GEOMETRY'))
#> Error: rapi_execute: Failed to run query
#> Error: Conversion Error: ParseException: Unknown type: '0106000020AB100000010000000103000000010000001B000000000000A0415E54C000000060FF1D4240000000209D6254C000000080E122424000000080F76354C0000000200523424000000020846854C0000000A09B2B4240000000C06D6F54C00000000026324240000000A0B06C54C000000040633C4240000000A0FA6C54C0000000C07942424000000040E16A54C0000000A0794B424000000060195654C0000000A053494240000000203E5654C000000060DA44424000000020C95454C000000040C0414240000000800D5454C000000080873D4240000000000A5154C000000060F637424000000060D25054C000000060D833424000000080674F54C0000000C090304240000000605A4F54C000000040C42E424000000060E95054C0000000E01B2D4240000000400E5554C000000040872E4240000000C0205754C000000060342D424000000080675754C000000000662B424000000020AA5654C0000000205D26424000000060845754C000000060AC23424000000040025A54C0000000A07C244240000000A0635A54C0000000A03622424000000020965B54C0000000405F21424000000020FC5C54C0000000C0AA1E4240000000A0415E54C000000060FF1D4240'Created on 2024-03-28 with reprex v2.1.0
from duckdb-r.
Related Issues (20)
- Undefined behavior with list columns HOT 12
- add `temporary` parameter to `duckdb_read_csv()` HOT 2
- `round()` semantics of R-version differs from its duckdb-translation HOT 1
- Use proper std::isnan in C++ code, not C99 versions which may not compile HOT 4
- Error: String contains null byte
- `NULL` mapped to `NA_integer_` HOT 4
- Example does not use any of the functions in the man page
- Support arrow stream in duckdb
- Feature request: Support for clock::date_count_between()
- Appender used in `duckdb_read_csv()` and `dbAppendTable()`?
- Simplify `dbdir` entrypoint
- Expose code to determine whether statement is fetchable HOT 2
- `n_distinct()` does not work with non-trivial windows HOT 2
- Issues exporting large spatial table to Geopackage format HOT 1
- Potential `rchk` issue HOT 2
- Prepared statement with semicolon separated SQL statements HOT 2
- User Defined Functions with R HOT 1
- dbWriteTable(con, "CO2", CO2) fails with error
- connect to existing sqlite and postgres database HOT 1
- Timezone problem with POSIXct HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from duckdb-r.