user interface

[e-kotov]

What I mean with materialization as a vector with new_column = NULL, is to keep the exact same behavior as the sf package.

Yes, I got that. I was just saying that this seems confusing to me that new_column = NULL means 'return a vector'. It would definitely work, but I don't think this is good API design.

Another option I would see is that output = "sf" would return the same type of object as the sf package does, and not inherently an sf object. That is, if st_is_simple() returns a logical vector, then ddbs_is_simple(..., output = "sf") would return a logical vector, and not the sf version of duckspatial_df. Is this what you meant?

Yes, exactly.

Do you mean to remove "raw", "geoarrow" here, and keep the other two (duckspatial_df and sf)?

Not really, as we may need these outputs too.

I think we are doing most things correct now, it's just that we are not packaging them behind a well designed intuitive API, and the main problem is mostly semantics. That is, if we were to just rename output to mode it might immediately become better, as output='sf' indeed suggests that the output is 'sf' object, but mode='sf' is easier to understand as 'oh, I will get an object same as or similar to what I would expect form 'sf'. Though if we rename output to mode, I'm not sure other existing options will work well ("raw", "geoarrow") as we don't really have any support for them in any downstream workflows. So perhaps we need to split mode and output. I'm not sure what exactly to do right now in terms of editing code. I would take some time to experiment with the codebase that we have right now and perhaps split this discussion into a separate issue where we could agree on how the final API should look like.

e.g. in sf:

library(sf)

# 1. st_is_valid returns a raw logical vector (TRUE/FALSE/NA)
valid_vector <- st_is_valid(my_sf_object)

# 2. To use it for filtering, you often attach it back or index directly
my_sf_object$is_valid <- valid_vector
clean_data <- my_sf_object[my_sf_object$is_valid == TRUE, ]

in duckspatial

library(duckspatial)

# 1. ddbs_is_valid returns a 'tbl_lazy' (pointer to DB) with a NEW column added
my_db_object <- ddbs_is_valid(my_db_object, new_column = "is_valid")

# or just this if we assume 'duckspatial_df` mode/output is the default
my_db_object <- ddbs_is_valid(my_db_object)

# 2. Filtering happens inside the database
clean_data <- my_db_object |>
  filter(is_valid == TRUE)

So actually we are already winning in my opinion, as there is no extra step of assigning a vector manually. But, there might be other use cases, so we could take a bit of time pooling our collective experience of working with spatial data thinking what other workflows we might have issues with if we keep things as they are designed now. Or perhaps what user facing helpers we might need to add for those cases.

Originally posted by @e-kotov in #83

[Cidree]

Overview

I will try to divide the functions based on the type of expected output:

• Group 1: functions that do any transformation or calculation with the geometry, and return a different geometry. Here we have most of the functions (e.g. ddbs_centroid(), ddbs_buffer(), ddbs_filter() , ddbs_transform()....). These type of functions will always return a table-like result, and shouldn't be more problematic than resolving the issue with output/mode.
• Group 2: functions that generate a new attribute "geometry-wise" that can be attached to the current data, returning a vector with one result per row. These functions might return a table-like result with the new column added to the input data, or return the vector as sf does. With duckspatial they should always return a new table with a new column added, so the data is kept within DuckDB ready to use. If the user specify sf, the data is materialized to a vector, in the same way the sf package does. But, what should geoarrow and raw options do? Return a materialized dataset?
• Group 3: functions that return other output types. For example, ddbs_predicate() or ddbs_distance() return a list or a matrix.

The current approach deals with several output types:

• duckspatial_df: default mode. It returns a duckspatial_df always.
• sf: it returns an sf object, or a vector
• raw: it returns the geometry as a WKB list (therefore, in group 2 functions, if we decided to retrieve just a vector, the result would be exactly the same as sf).
• geoarrow: it returns the geometry as a geoarrow vector (idem as previous two).

Possible idea

• Action 1: Rename the argument to mode, and deal only with duckspatial_df and sf. This would allow the user to do the workflow completely in DuckDB, or materialize the result just as sf would do (sf, vector, or whatever).
• Action 2: Remove raw and geoarrow from the function-level (i.e. from ddbs_handle_output()), and rely on `ddbs_collect() & colleagues.
• Positble action 3: create new function ddbs_as_geoarrow(). For raw geometry we have ddbs_as_wkb(). However, all the ddbs_as_*() functions are designed to return a vector with the geometry transformed, as sf does.

With this approach we would simplify the current output argument to just two options, and when the user wants to get the object with the geometry as a geoarrow vector, they just need to use ddbs_collect(as = "geoarrow"). In computing time I guess it's exactly the same, and we would maintain only the two main options within the geospatial operation mode.

I think this would avoid to mix both concepts in one argument, and also avoid to generate two arguments for deciding the type of output.

[e-kotov]

• Action 1: Rename the argument to mode, and deal only with duckspatial_df and sf. This would allow the user to do the workflow completely in DuckDB, or materialize the result just as sf would do (sf, vector, or whatever).
• Action 2: Remove raw and geoarrow from the function-level (i.e. from ddbs_handle_output()), and rely on `ddbs_collect() & colleagues.

That seems like a great idea to me!

• Positble action 3: create new function ddbs_as_geoarrow(). For raw geometry we have ddbs_as_wkb(). However, all the ddbs_as_*() functions are designed to return a vector with the geometry transformed, as sf does.

For now the collect should do the job just fine. Also I looked into the vis package in #38 and it would need an s3 method from us, rather than an actual 'output'/'mode'. So removing raw and geoarrow from 'output'/'mode' should work fine.

---

Potential API Friction Points: sf vs. duckspatial

If we enforce that all geometry functions must return a duckspatial_df (essentially tbl_lazy) with a new column (side-effect) rather than a vector (value), we introduce friction in several common R workflows.

Here are the specific scenarios where the "Add Column" approach falls short compared to standard sf usage:

1. "Throwaway" calculation

Users often calculate a metric just to aggregate it immediately. They don't want to modify the table schema.

• sf (Clean):

# Calculate, sum, and forget
total_area <- sum(st_area(my_sf))

• duckspatial (Verbose):
  We force the user to name a temporary column they don't actually want.

# Must add column -> summarize -> collect
total_area <- my_lazy_tbl |>
  ddbs_area(new_column = "temp_area") |>
  summarise(total = sum(temp_area)) |>
  collect()

2. Functional composition/nesting

R users are used to nesting functions, passing the output of one geometry operation directly into another.

• sf (Fluid):

# Buffer then immediately get the BBox
box <- st_bbox(st_buffer(my_sf, 10))

• duckspatial (Broken Flow):
  Since functions return a table, they cannot be nested. Users must unroll the logic into a pipe and explicitly reference column names at every step.

# Cannot nest. Must pipe and track column names.
result <- my_lazy_tbl |>
  ddbs_buffer(dist = 10, new_column = "buf_geom") |>
  ddbs_bbox(geom_column = "buf_geom")

3. Control flow & validation checks

Users often run a check to conditionally stop a script or branch logic.

• sf (Direct):

if (any(!st_is_valid(my_sf))) {
  stop("Invalid geometries detected!")
}

• duckspatial (Requires Materialization):
  You cannot put a lazy table into an if() statement.

# Must explicitly compute and pull to R
check_df <- my_lazy_tbl |>
  ddbs_is_valid(new_column = "is_val") |>
  filter(is_val == FALSE) |>
  collect()

if (nrow(check_df) > 0) stop(...)

4. "Column pollution" in exploratory work

In interactive sessions, forcing column creation can clutter the lazy query.

• Scenario: A user trying to find the right calculation.

# Attempt 1
x <- x |> ddbs_area(new_column = "area")
# Attempt 2 (Different units) - can't overwrite easily in lazy queries
x <- x |> ddbs_area(new_column = "area_m2")
# Attempt 3
x <- x |> ddbs_area(new_column = "area_final")

The underlying SQL query becomes massive, tracking multiple columns the user no longer cares about.

---

Possible solution: a polymorphic API with "smart" defaults

To resolve these friction points without breaking existing workflows or implementing sf methods, we can make ddbs_* functions polymorphic.

Using the existing output (renaming to 'mode') argument, functions will adapt their return type based on context:

1. Table Input: Returns a duckspatial_df (adds a column) - so works like it does right now.
2. Symbol/Expression Input: Returns an SQL snippet (dplyr::sql), which enables nesting and mutate() usage.

So to self-respond to aforementioned friction points:

1. The "throwaway" calculation

Users can now use ddbs_* functions inside summarise() or mutate() without creating permanent columns first.

# 'geom' is a symbol, so ddbs_area returns an SQL snippet (ST_Area(geom))
# It is injected directly into the summary aggregation.
total_area <- my_lazy_tbl |>
  summarise(total = sum(ddbs_area(geom)))

2. The functional composition/nesting

Since the functions can now exchange SQL strings, we can nest them naturally. The "Pipe Style" is no longer mandatory for simple math.

my_lazy_tbl |>
  mutate(
    # Nesting works naturally!
    # ddbs_buffer returns SQL -> passed to ddbs_bbox -> returns SQL
    bbox_wkt = ddbs_bbox(ddbs_buffer(geom, 10))
  )

3. Control flow & validation

Users can filter directly using geometry predicates, exactly like in sf.

# ddbs_is_valid returns a SQL boolean expression used by filter()
clean_data <- my_lazy_tbl |>
  filter(ddbs_is_valid(geom))

4. Solving "column pollution"

Exploratory work no longer requires appending columns to the table schema.

# This creates a temporary calculation in the query, not a column in the schema
my_lazy_tbl |>
  mutate(area = ddbs_area(geom))

If the user runs this again with different parameters, they are just rewriting the SELECT clause, not widening the source table.

[Cidree]

Great to hear! I will try to go over the points you mention, and try to find any alternative.

NOTE: I was answering point by point before reading your possible solutions, so in the next comment I will refer to it.

1. "Throwaway" calculation

Here the user could have also the quicker option (slightly more verbose than sf), that would return the vector as sf.

total_area <- my_lazy_tbl  |> 
  ddbs_area(mode = "sf") |> 
  sum()

Maybe not better than sf in verbosity, but pretty close. Posible option to improve:

• Add an argument for summarising: same as we have with other functions, we could add the by_feature (or alternative names such as total_area = TRUE) argument with TRUE by default. When the user says by_feature = FALSE, the total area/length would be returned. Advantage over what I wrote: the sum() would be done inside duckdb, and the function would ignore the mode, and just return a number with the total area:

total_area <- ddbs_area(my_lazy_tbl, by_feature = FALSE)

The main issue: we would need to make clear that by_feature = FALSE would ignore other arguments such as name and mode.

2. Functional composition/nesting

I'm not sure if I get what you're trying to explain. But in this case, ddbs_buffer() would fall in the Group 1 of functions, so the argument new_column wouldn't exist there, as it modifies the geometry, it does not add a new attribute to the table. In the case of ddbs_bbox(), it's a group 2-3 function, as it just returns always a defined 4-column and 1-row data.frame. Therefore, the workflow I see there is simply:

my_lazy_tbl |> 
  ddbs_buffer(dist = 10) |> 
  ddbs_bbox()

3. Control flow & validation checks

Here I'd see the quickest option similar as the current approach I mentioned in 1, and probably a similar solution.

## current
if (any(!ddbs_is_valid(my_sf, mode = "sf"))) {
  stop("Invalid geometries detected!")
}

## alternative with changes mentioned in 1
if (!ddbs_is_valid(my_sf, by_feature = FALSE)) {
  stop("Invalid geometries detected!")
}

With the by_feature = FALSE, we would return TRUE only if all geometries are TRUE, and FALSE if at least 1 is FALSE. Again, this would just return a length 1 logical vector, so mode would be just ignored with by_feature = FALSE.

4. "Column pollution" in exploratory work

That's a great catch! Solutions that would come to my mind:

• Throw an error if the user creates a column with a name that already exists. Too dramatic.
• Throw an error if the user creates a column with a name that already exists, unless overwrite = TRUE. I think this can be deceiving.
• Modify internally the function with new_column argument to use dplyr::select(-new_column) when the column exists. Could be an alternative.

[Cidree]

Alright, after reading what you suggest, I think it sounds much better.
However, I am unsure about how to implement it. We can try to close current MR, and from there move our focus to this issue.

[Cidree]

Implementation

I think there could be a couple of ways to implement this, according to what Egor suggested:

1. a new "dispatcher" helper and a new output/mode arg,
2. for the code that you are trying right now, we might also need macros inside duckdb conneciton that will map ddbs_is_simple and others to sql functions.

Possible Option 1

One way to implement this could be to create a helper that registers all the necessary macros.:

ddbs_register_macros <- function(conn = NULL, geom_name) {
  target_conn <- conn %||% ddbs_default_conn(create = FALSE)
  
  DBI::dbExecute(target_conn, 
   glue::glue("CREATE OR REPLACE MACRO ddbs_is_simple({geom_name}) AS ST_IsSimple({geom_name})"))
  DBI::dbExecute(target_conn, 
    glue::glue("CREATE OR REPLACE MACRO ddbs_is_valid({geom_name}) AS ST_IsValid({geom_name})"))
  # Add more macros here...
  
  invisible(TRUE)
}

And register all the macros when the temporary view is created (e.g. when as_duckspatial_df() or ddbs_write_vector() is invoked):

ddbs_register_macros()

Then, at the function level we would differenciate how to evaluate the function:

ddbs_is_simple <- function(
  x,
  conn = NULL,
  name = NULL,
  new_column = NULL,
  crs = NULL,
  crs_column = "crs_duckspatial",
  output = NULL,
  overwrite = FALSE,
  quiet = FALSE
) {

  is_table <- tryCatch({
    inherits(x, c("sf", "duckspatial_df", "tbl_lazy", 
                  "tbl_duckdb_connection", "data.frame")) || 
      is.character(x)
  }, error = function(e) FALSE)
  
  if (is_table) {
    return(
      template_new_column(
      x = x, 
      conn = conn, 
      name = name, 
      new_column = new_column,
      crs = crs, 
      crs_column = crs_column, 
      output = output,
      overwrite = overwrite, 
      quiet = quiet, 
      fun = "ST_IsSimple"
    )
  )
  }
  
  # Expression mode: macro should already exist
  # Just return SQL
  x_expr <- substitute(x)
  dplyr::sql(paste0("ddbs_is_simple(", as.character(x_expr), ")"))
}

For a specific duckspatial_df object, it will register all the methods in the default connection of the view.

Possible Option 2

Instead of registering the Macro when the view is created, we create it when the function is called:

ddbs_is_simple <- function(
  x,
  conn = NULL,
  name = NULL,
  new_column = NULL,
  crs = NULL,
  crs_column = "crs_duckspatial",
  output = NULL,
  overwrite = FALSE,
  quiet = FALSE
) {

  # Check if x is a table/dataset (table mode)
  is_table <- tryCatch({
    inherits(x, c("sf", "duckspatial_df", "tbl_lazy", 
                  "tbl_duckdb_connection", "data.frame")) || 
      is.character(x)
  }, error = function(e) FALSE)
  
  if (is_table) {
    # Table mode: use template
    return(template_new_column(
      x = x,
      conn = conn,
      name = name,
      new_column = new_column,
      crs = crs,
      crs_column = crs_column,
      output = output,
      overwrite = overwrite,
      quiet = quiet,
      fun = "ST_IsSimple"
    ))
  }

 # Get the geometry column name for the macro
  x_expr <- substitute(x)
  col_name <- as.character(x_expr)
  
  # Expression mode: ensure macro exists, then return SQL
  target_conn <- conn %||% ddbs_default_conn(create = FALSE)
  
  # Create macro if it doesn't exist (using CREATE OR REPLACE to be safe)
  DBI::dbExecute(
    target_conn,
    glue::glue("CREATE OR REPLACE MACRO ddbs_is_simple({col_name}) AS ST_IsSimple({col_name})")
  )
  
  # Return SQL that references the macro
  #  (to be honest, this is not even necessary, as the macro is already registered, and the function will just work)
  return(dplyr::sql(paste0("ddbs_is_simple(", col_name, ")")))
}

Conclusions

Both methods would work fine. However, I am not sure which is the cleanest of them, or if there's another better way.
But this might be a good start point to discuss a possible implementation.

A possible bottleneck: this would only work with lazy tables, not with sf objects.

[Cidree]

Correction: the second method won't work in a fresh session.
That's because the method should be registered as a macro before running the ddbs_is_simple() function. Therefore, I see another alternative that looks to work just fine:

1. Load macros with .onLoad() in the default connection. Actually, the col_name argument is not necessary at all in ddbs_register_macros().
2. Edit internal logic of necessary functions to use the right method based on the input of the function.

So, we could just execute this onLoad():

ddbs_register_macros <- function() {
  target_conn <- ddbs_default_conn(create = TRUE)
  
  DBI::dbExecute(target_conn, 
   glue::glue("CREATE OR REPLACE MACRO ddbs_is_simple(geom) AS ST_IsSimple(geom)"))
  DBI::dbExecute(target_conn, 
    glue::glue("CREATE OR REPLACE MACRO ddbs_is_valid(geom) AS ST_IsValid(geom)"))
  # Add more macros here...
  
  invisible(TRUE)
}
ddbs_register_macros()

And the function would just work fine:

ddbs_is_simple <- function(
  x,
  conn = NULL,
  name = NULL,
  new_column = NULL,
  crs = NULL,
  crs_column = "crs_duckspatial",
  output = NULL,
  overwrite = FALSE,
  quiet = FALSE
) {

  is_table <- tryCatch({
    inherits(x, c("sf", "duckspatial_df", "tbl_lazy", 
                  "tbl_duckdb_connection", "data.frame")) || 
      is.character(x)
  }, error = function(e) FALSE)
  
  if (is_table) {
    return(
      template_new_column(
      x = x, 
      conn = conn, 
      name = name, 
      new_column = new_column,
      crs = crs, 
      crs_column = crs_column, 
      output = output,
      overwrite = overwrite, 
      quiet = quiet, 
      fun = "ST_IsSimple"
    )
  )
  }
  
  # Expression mode: macro should already exist
  # Just return SQL
  x_expr <- substitute(x)
  dplyr::sql(paste0("ddbs_is_simple(", as.character(x_expr), ")"))
}

This will work:

countries_ddbs |> 
  filter(!ddbs_is_simple(geometry))

countries_ddbs |> 
  ddbs_is_simple()

countries_ddbs |> 
  ddbs_is_simple(new_column = "is_simple")

countries_ddbs |> 
  ddbs_is_simple(new_column = "is_simple") |> 
  filter(!is_simple)

Let me know if you find some caveats or improvements @e-kotov .