Re: [R-sig-Geo] Using duckdb spatial module from R (with sf)?

[Carl Boettiger]

thanks Dewey, good call on st_aswkb.  yup I was puzzled as to what that
internal format is too! from the docs (
https://github.com/duckdblabs/duckdb_spatial#multi-tiered-geometry-type-system
)

> The internal binary format is very similar to the one used by PostGIS -
basically double aligned WKB, and we may eventually look into enforcing the
format to be properly compatible with PostGIS (which may be useful for the
PostGIS scanner extension)

so maybe one day this coercion won't be necessary.

The docs detail which operations are currently 'native' threaded duckdb and
which aren't
https://github.com/duckdblabs/duckdb_spatial#supported-functions
Looks like their roadmap might be of interest to users on this sig as well
(looks like spatial indexing is still on there
, but that's way
over my head).

So far this works pretty well for me! Using this trivial example of a
spatial polygon filter, I'm able to run a query against a GBIF parquet
snapshot (~2000+ parquet partitions, about 175 GB compressed) on an S3
bucket from duckdb without downloading in about 17 minutes.  (doing
approximately the same filtering using only the bounding box of the
polygon, ie. in pure duckdb, w/o spatial extension, takes about 8 minutes,
so the additional overhead of casting lat/long columns to geometry and
filtering with the polygon really isn't that bad!). Note that RAM use is
minimal, as expected.

Cheers,

Carl
---
Carl Boettiger
http://carlboettiger.info/

---
Carl Boettiger
http://carlboettiger.info/


On Tue, Aug 15, 2023 at 5:58 PM Dewey Dunnington 
wrote:

> I think you probably want ST_AsWKB() (almost certainly faster than hex).
> The raw result looks like the internal geometry representation which is
> probably something more like lwgeom's serialized form than WKB.
> As far as I know, they have not yet implemented spatial index or
> prepared
> geometry support; however, plug-and-chug computations like length, area,
> and distance are likely to be much faster (if you turn on threading).
> I haven't played with it much yet but am looking forward to it!
>
> -dewey
>
> library(duckdb)
> #> Loading required package: DBI
>
> 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_AsWKB(ST_Point(longitude, latitude)) AS geom',
>'FROM "test"'
> )
>
> sf::st_read(conn, query = query, geometry_column = "geom", EWKB = FALSE)
> #> Simple feature collection with 10 features and 3 fields
> #> Geometry type: POINT
> #> Dimension: XY
> #> Bounding box:  xmin: 1 ymin: 1 xmax: 10 ymax: 10
> #> CRS:   NA
> #>site latitude longitude  geom
> #> 1 a1 1   POINT (1 1)
> #> 2 b2 2   POINT (2 2)
> #> 3 c3 3   POINT (3 3)
> #> 4 d4 4   POINT (4 4)
> #> 5 e5 5   POINT (5 5)
> #> 6 f6 6   POINT (6 6)
> #> 7 g7 7   POINT (7 7)
> #> 8 h8 8   POINT (8 8)
> #> 9 i9 9   POINT (9 9)
> #> 10j   1010 POINT (10 10)
>
> On 2023-08-15 20:02, Carl Boettiger 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/

Re: [R-sig-Geo] Using duckdb spatial module from R (with sf)?

[Dewey Dunnington]

I think you probably want ST_AsWKB() (almost certainly faster than hex).
The raw result looks like the internal geometry representation which is
probably something more like lwgeom's serialized form than WKB.
As far as I know, they have not yet implemented spatial index or 
prepared

geometry support; however, plug-and-chug computations like length, area,
and distance are likely to be much faster (if you turn on threading).
I haven't played with it much yet but am looking forward to it!

-dewey

library(duckdb)
#> Loading required package: DBI

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_AsWKB(ST_Point(longitude, latitude)) AS geom',
  'FROM "test"'
)

sf::st_read(conn, query = query, geometry_column = "geom", EWKB = FALSE)
#> Simple feature collection with 10 features and 3 fields
#> Geometry type: POINT
#> Dimension: XY
#> Bounding box:  xmin: 1 ymin: 1 xmax: 10 ymax: 10
#> CRS:   NA
#>site latitude longitude  geom
#> 1 a1 1   POINT (1 1)
#> 2 b2 2   POINT (2 2)
#> 3 c3 3   POINT (3 3)
#> 4 d4 4   POINT (4 4)
#> 5 e5 5   POINT (5 5)
#> 6 f6 6   POINT (6 6)
#> 7 g7 7   POINT (7 7)
#> 8 h8 8   POINT (8 8)
#> 9 i9 9   POINT (9 9)
#> 10j   1010 POINT (10 10)

On 2023-08-15 20:02, Carl Boettiger 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


___
R-sig-Geo mailing list
R-sig-Geo@r-project.org
https://stat.ethz.ch/mailman/listinfo/r-sig-geo

Re: [R-sig-Geo] Using duckdb spatial module from R (with sf)?

[Carl Boettiger]

thanks!  of course the polygon can be a triangle, but I think the
difference here is that (a) I wanted st_within() if I'm giving my
points first and then my filter, and I think st_contains / st_within
have slightly different opinions about being on the exact boundary
than st_filter().

Anyway, fixing my filter does indeed work!  Thanks Josiah for the help
here.  This is very appealing to me thanks to the ability of duckdb to
open very large remote csv / parquet files without downloading them,
which often contain lat / long columns in my work.


I'll stop bugging the rest of you :-)




here's the working filter for comparison in case anyone is following along.

## a trivial search polygon to filter:
p2 <- rbind(c(0,0), c(0,3), c(3,3), c(3,0), c(0,0))
pol <-st_polygon(list(p2))
txt <- st_as_text(pol)

## Can we use this to filter duckdb?
sql <- tbl(conn, "test") |>
  mutate(geometry = ST_Point(longitude, latitude),
 geom = ST_AsHEXWKB(geometry)) |>
  filter(ST_Within(geometry, ST_GeomFromText({txt}))) |>
  dbplyr::sql_render()
sql
ex <- st_read(conn, query=sql, geometry_column = "geom", EWKB=FALSE)
ex

(note this still differs with sf::st_filter() regarding the 3,3 point,
which I believe is well documented I just overlooked it).

---
Carl Boettiger
http://carlboettiger.info/

On Tue, Aug 15, 2023 at 5:39 PM Josiah Parry  wrote:
>
> Quick note on the contains: a polygon has 5 points! The first has to be the 
> same as the last. And they shuold be going in Counter clock wise order if 
> memory serves ! :)
>
> On Tue, Aug 15, 2023 at 8:34 PM Carl Boettiger  wrote:
>>
>> Hi list,
>>
>> One more go at this and I'll stop for other ideas.  Below is a
>> slightly modified example using dbplyr::sql_render() to construct the
>> query, which works and to me feels a little cleaner, though maybe is
>> ill-advised.  Then I try to construct a spatial filter (for points in
>> a polygon) this way with ST_Contains, but my effort comes back empty.
>> Not sure where I went wrong.  Any ideas?
>>
>>
>> ## boilerplate setup
>> library(duckdb)
>> 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)
>>
>> ## Here we go, works!
>> sql <- tbl(con, "test") |>
>>   mutate(geom = ST_AsHEXWKB(ST_Point(longitude, latitude))) |>
>>   dbplyr::sql_render()
>> ex <- st_read(conn, query=sql, geometry_column = "geom", EWKB=FALSE)
>> ex
>>
>> ## a trivial search polygon to filter:
>> p2 <- rbind(c(1,1), c(1,2), c(2,2), c(1,1))
>> pol <-st_polygon(list(p2))
>> txt <- st_as_text(pol)
>>
>> ## Can we use this to filter duckdb?
>> sql <- tbl(conn, "test") |>
>>   mutate(geometry = ST_Point(longitude, latitude),
>>  geom = ST_AsHEXWKB(geometry)) |>
>>   filter(ST_Contains(geometry, ST_GeomFromText({txt}))) |>
>>   dbplyr::sql_render()
>> sql
>> ex <- st_read(conn, query=sql, geometry_column = "geom", EWKB=FALSE)
>> ex
>> ## oh no! our result comes back empty?  did I need CRS on this?  Or do
>> I missunderstand "ST_Contains()"?
>>
>> ## Here's what the desired result would be from pure sf:
>> sf <- st_as_sf(test, coords = c(3,2))
>> filter <- st_as_sf(tibble(sites = "A", geometry = list(pol)))
>> sf |> st_filter(filter)
>>
>>
>>
>>
>> ---
>> Carl Boettiger
>> http://carlboettiger.info/
>>
>> On Tue, Aug 15, 2023 at 4:42 PM Carl Boettiger  wrote:
>> >
>> > Ah ha.  if we ask duckdb to coerce it's geometry format to hex, it
>> > appears sf can understand it just fine!
>> >
>> > replacing the above query with the following we are good to go.
>> >
>> >
>> >
>> > query <- paste(
>> >   'SELECT *, ST_AsHEXWKB(ST_Point(longitude, latitude)) AS geom',
>> >   'FROM "test"'
>> > )
>> > ex <- st_read(con, query=query, geometry_column = "geom", EWKB=FALSE)
>> > ex
>> >
>> >
>> > I'll experiment further.  I'm terrible at SQL, and to my eyes it looks
>> > needlessly verbose.  I'm keen to understand how I can better leverage
>> > sf's notation to compose the sql queries to duckdb  but it seems
>> > to work!   I'm also still trying to determine if duckdb is using EWKB
>> > or vanilla WKB
>> >
>> > ---
>> > Carl Boettiger
>> > http://carlboettiger.info/
>> >
>> > On Tue, Aug 15, 2023 at 4:02 PM Carl Boettiger  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 = l

Re: [R-sig-Geo] Using duckdb spatial module from R (with sf)?

[Josiah Parry]

Quick note on the contains: a polygon has 5 points! The first has to be the
same as the last. And they shuold be going in Counter clock wise order if
memory serves ! :)

On Tue, Aug 15, 2023 at 8:34 PM Carl Boettiger  wrote:

> Hi list,
>
> One more go at this and I'll stop for other ideas.  Below is a
> slightly modified example using dbplyr::sql_render() to construct the
> query, which works and to me feels a little cleaner, though maybe is
> ill-advised.  Then I try to construct a spatial filter (for points in
> a polygon) this way with ST_Contains, but my effort comes back empty.
> Not sure where I went wrong.  Any ideas?
>
>
> ## boilerplate setup
> library(duckdb)
> 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)
>
> ## Here we go, works!
> sql <- tbl(con, "test") |>
>   mutate(geom = ST_AsHEXWKB(ST_Point(longitude, latitude))) |>
>   dbplyr::sql_render()
> ex <- st_read(conn, query=sql, geometry_column = "geom", EWKB=FALSE)
> ex
>
> ## a trivial search polygon to filter:
> p2 <- rbind(c(1,1), c(1,2), c(2,2), c(1,1))
> pol <-st_polygon(list(p2))
> txt <- st_as_text(pol)
>
> ## Can we use this to filter duckdb?
> sql <- tbl(conn, "test") |>
>   mutate(geometry = ST_Point(longitude, latitude),
>  geom = ST_AsHEXWKB(geometry)) |>
>   filter(ST_Contains(geometry, ST_GeomFromText({txt}))) |>
>   dbplyr::sql_render()
> sql
> ex <- st_read(conn, query=sql, geometry_column = "geom", EWKB=FALSE)
> ex
> ## oh no! our result comes back empty?  did I need CRS on this?  Or do
> I missunderstand "ST_Contains()"?
>
> ## Here's what the desired result would be from pure sf:
> sf <- st_as_sf(test, coords = c(3,2))
> filter <- st_as_sf(tibble(sites = "A", geometry = list(pol)))
> sf |> st_filter(filter)
>
>
>
>
> ---
> Carl Boettiger
> http://carlboettiger.info/
>
> On Tue, Aug 15, 2023 at 4:42 PM Carl Boettiger  wrote:
> >
> > Ah ha.  if we ask duckdb to coerce it's geometry format to hex, it
> > appears sf can understand it just fine!
> >
> > replacing the above query with the following we are good to go.
> >
> >
> >
> > query <- paste(
> >   'SELECT *, ST_AsHEXWKB(ST_Point(longitude, latitude)) AS geom',
> >   'FROM "test"'
> > )
> > ex <- st_read(con, query=query, geometry_column = "geom", EWKB=FALSE)
> > ex
> >
> >
> > I'll experiment further.  I'm terrible at SQL, and to my eyes it looks
> > needlessly verbose.  I'm keen to understand how I can better leverage
> > sf's notation to compose the sql queries to duckdb  but it seems
> > to work!   I'm also still trying to determine if duckdb is using EWKB
> > or vanilla WKB
> >
> > ---
> > Carl Boettiger
> > http://carlboettiger.info/
> >
> > On Tue, Aug 15, 2023 at 4:02 PM Carl Boettiger 
> 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

Re: [R-sig-Geo] Using duckdb spatial module from R (with sf)?

[Carl Boettiger]

Hi list,

One more go at this and I'll stop for other ideas.  Below is a
slightly modified example using dbplyr::sql_render() to construct the
query, which works and to me feels a little cleaner, though maybe is
ill-advised.  Then I try to construct a spatial filter (for points in
a polygon) this way with ST_Contains, but my effort comes back empty.
Not sure where I went wrong.  Any ideas?


## boilerplate setup
library(duckdb)
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)

## Here we go, works!
sql <- tbl(con, "test") |>
  mutate(geom = ST_AsHEXWKB(ST_Point(longitude, latitude))) |>
  dbplyr::sql_render()
ex <- st_read(conn, query=sql, geometry_column = "geom", EWKB=FALSE)
ex

## a trivial search polygon to filter:
p2 <- rbind(c(1,1), c(1,2), c(2,2), c(1,1))
pol <-st_polygon(list(p2))
txt <- st_as_text(pol)

## Can we use this to filter duckdb?
sql <- tbl(conn, "test") |>
  mutate(geometry = ST_Point(longitude, latitude),
 geom = ST_AsHEXWKB(geometry)) |>
  filter(ST_Contains(geometry, ST_GeomFromText({txt}))) |>
  dbplyr::sql_render()
sql
ex <- st_read(conn, query=sql, geometry_column = "geom", EWKB=FALSE)
ex
## oh no! our result comes back empty?  did I need CRS on this?  Or do
I missunderstand "ST_Contains()"?

## Here's what the desired result would be from pure sf:
sf <- st_as_sf(test, coords = c(3,2))
filter <- st_as_sf(tibble(sites = "A", geometry = list(pol)))
sf |> st_filter(filter)




---
Carl Boettiger
http://carlboettiger.info/

On Tue, Aug 15, 2023 at 4:42 PM Carl Boettiger  wrote:
>
> Ah ha.  if we ask duckdb to coerce it's geometry format to hex, it
> appears sf can understand it just fine!
>
> replacing the above query with the following we are good to go.
>
>
>
> query <- paste(
>   'SELECT *, ST_AsHEXWKB(ST_Point(longitude, latitude)) AS geom',
>   'FROM "test"'
> )
> ex <- st_read(con, query=query, geometry_column = "geom", EWKB=FALSE)
> ex
>
>
> I'll experiment further.  I'm terrible at SQL, and to my eyes it looks
> needlessly verbose.  I'm keen to understand how I can better leverage
> sf's notation to compose the sql queries to duckdb  but it seems
> to work!   I'm also still trying to determine if duckdb is using EWKB
> or vanilla WKB
>
> ---
> Carl Boettiger
> http://carlboettiger.info/
>
> On Tue, Aug 15, 2023 at 4:02 PM Carl Boettiger  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

Re: [R-sig-Geo] Using duckdb spatial module from R (with sf)?

[Carl Boettiger]

Ah ha.  if we ask duckdb to coerce it's geometry format to hex, it
appears sf can understand it just fine!

replacing the above query with the following we are good to go.



query <- paste(
  'SELECT *, ST_AsHEXWKB(ST_Point(longitude, latitude)) AS geom',
  'FROM "test"'
)
ex <- st_read(con, query=query, geometry_column = "geom", EWKB=FALSE)
ex


I'll experiment further.  I'm terrible at SQL, and to my eyes it looks
needlessly verbose.  I'm keen to understand how I can better leverage
sf's notation to compose the sql queries to duckdb  but it seems
to work!   I'm also still trying to determine if duckdb is using EWKB
or vanilla WKB

---
Carl Boettiger
http://carlboettiger.info/

On Tue, Aug 15, 2023 at 4:02 PM Carl Boettiger  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

Re: [R-sig-Geo] Using duckdb spatial module from R (with sf)?

[Josiah Parry]

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  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