Feature request - Support for `geometry` column type

## Context This issue follows the conversation [from this issue](https://github.com/Cidree/duckspatial/issues/109). With the release of DuckDB v1.5.0, the `GEOMETRY` type was moved from the spatial extension into core DuckDB [(source)](https://duckdb.org/2026/03/09/announcing-duckdb-150#geometry-rework). In previous versions of `duckdb-r`, the `GEOMETRY` type from the spatial extension was recognized when the data was queried allowing functions such as `DBI::dbGetQuery()` and `dplyr::tbl()` to translate the geoemtry type to R. However, with the v1.5.0 the geometry type is not recognized anymore, and functions that depend on requesting data with a `GEOMETRY` column type will fail. ## Example The next example shows that: * The `GEOMETRY` type of DuckDB v1.5.0 is not recognized by `dbGetQuery()`, so every function that tries to print that column type will fail. * Transforming the geometry with `ST_AsWKB()` to `BLOB` allows to overcome this issue. This is how I am working in the `dev` version of [duckspatial](https://github.com/Cidree/duckspatial/tree/dev). ```r # 1. Set up -------------------------------------------------------------- ## Load packages library(duckdb) library(dplyr) library(duckspatial) library(glue) ## Create a connection with spatial extension enabled conn <- duckspatial::ddbs_create_conn() # 2. The error ----------------------------------------------------------- ## Write some data ## When writting a local file, it uses directly `ST_Read( )`, so the geometry ## type is automatically generated as GEOMETRY('EPSG:4326') path_to_file <- system.file("spatial/argentina.geojson", package = "duckspatial") dbExecute( conn, glue("CREATE TABLE argentina AS SELECT * FROM ST_Read('{path_to_file}')") ) ## Check the data type ## We confirm that the column_type is GEOMETRY('EPSG:4326') dbGetQuery(conn, "DESCRIBE argentina;") ## Now, if we try to query query the data lazily with tbl() it does ## not work ## ! Unknown column type for prepare: GEOMETRY('EPSG:4326') tbl(conn, "argentina") ## This neither - root of the error appears to be the same ## ! Unknown column type for prepare: GEOMETRY('EPSG:4326') dbGetQuery(conn, "SELECT * FROM argentina;") ## This works, as the geometry type is converted into dbGetQuery(conn, "SELECT * REPLACE (ST_AsWKB(geom) AS geom) FROM argentina;") ## We can check that the data type is converted to BLOB with ST_AsWKB() dbGetQuery( conn, "DESCRIBE SELECT * REPLACE (ST_AsWKB(geom) AS geom) FROM argentina" ) # 3. Blob ---------------------------------------------------------------- ## We can also store the geometry as a blob dbExecute( conn, " CREATE TABLE argentina_blob AS SELECT * REPLACE (ST_AsWKB(geom) AS geom) FROM argentina; " ) ## And this works directly dbGetQuery(conn, "DESCRIBE argentina_blob") tbl(conn, "argentina_blob") ## But then, we cannot check the CRS directly dbGetQuery(conn, "SELECT ST_CRS(geom) FROM argentina_blob;") dbGetQuery(conn, "SELECT ST_CRS(geom) FROM argentina;") ## In summary: ## - GEOMETRY: needed for geospatial operations in DuckDB, not supported in R ## - BLOB: needed to show results lazily in R, not supported in geospatial operations # 4. Geospatial workflow ------------------------------------------------- ## A geospatial workflow that uses duckspatial, performs geospatial operations ## within duckdb, and returns a lazy tbl. Therefore, we need to cast, and cast ## back after each operation ## Step 1 - we have the data "argentina" living in duckdb. To display it in ## R we need to cast it to BLOB dbExecute( conn, "CREATE TEMP TABLE argentina_r AS SELECT * REPLACE (ST_AsWKB(geom) AS geom) FROM argentina; " ) ## The user gets this tbl(conn, "argentina_r") ## Step 2 - Continue with geospatial ops. We use the original table, and again ## return the casted table to the user dbExecute( conn, "CREATE TEMP TABLE argentina_centroid AS SELECT * REPLACE (ST_Centroid(geom) AS geom) FROM argentina; " ) dbExecute( conn, "CREATE TEMP TABLE argentina_centroid_r AS SELECT * REPLACE (ST_AsWKB(geom) AS geom) FROM argentina_centroid; " ) tbl(conn, "argentina_centroid_r") ``` ## Feature Request The previous workflow introduces some complexity in the current design of `duckspatial`, as working with the `GEOMETRY` type seamlessly without the need of casting to BLOB would be ideal. However, the changes to `duckspatial` are not so dramatic as I thought in the begining. In any case, I don't know about the complexity of doing this, as the ' GEOMETRY` type is not just `GEOMETRY`. There's one `GEOMETRY('AUTH:CODE')` for each different coordinates reference system. Please, let me know if you think this will be possible, and if we can align for `duckdb` v1.5.1, and `duckspatial` v1.1.0 for this feature.