R/utils_not_exported.R

In duckspatial: R Interface to 'DuckDB' Database with Spatial Extension

## dbConnCheck

#' Check if a supported DuckDB connection
#'
#' @template conn
#'
#' @keywords internal
#' @returns TRUE (invisibly) for successful import
dbConnCheck <- function(conn) {  # nocov start
    if (inherits(conn, "duckdb_connection")) {
        return(invisible(TRUE))

    } else if (is.null(conn)) { return(invisible(FALSE))

    } else {
        cli::cli_abort("'conn' must be connection object: <duckdb_connection> from `duckdb`")
    }
}  # nocov end

#' Normalize spatial input for processing
#'
#' Standardizes all input types before spatial operations:
#' - sf objects: passed through unchanged
#' - duckspatial_df: passed through unchanged
#' - tbl_duckdb_connection: coerced to duckspatial_df (with CRS/source_table attributes)
#' - character: verified to exist in connection
#'
#' This normalization enables downstream functions to work with a consistent set of types
#' (sf, duckspatial_df, or character) rather than handling all possible input variations.
#'
#' @param x Spatial input (sf, duckspatial_df, tbl_duckdb_connection, or character)
#' @param conn DuckDB connection (required for character table names)
#' @return Normalized input ready for get_query_list()
#' @keywords internal
#' @noRd
normalize_spatial_input <- function(x, conn = NULL, geom_col = NULL) {
  # 1. sf: pass through
  if (inherits(x, "sf")) return(x)
  
  # 2. duckspatial_df: already normalized
  if (inherits(x, "duckspatial_df")) return(x)
  
  # 3. tbl_duckdb_connection: coerce to duckspatial_df
  if (inherits(x, "tbl_duckdb_connection")) {
    return(as_duckspatial_df(x, geom_col = geom_col))
  }
  
  # 4. character: verify table/view exists
  if (is.character(x)) {
    if (is.null(conn)) {
      cli::cli_abort("{.arg conn} required when using character table names.")
    }
    if (!table_exists(conn, x) & !arrow_view_exists(conn, x)) {
      cli::cli_abort("Table or view {.val {x}} does not exist in connection.")
    }
    return(x)
  }
  
  # Unsupported type - let downstream handle/error
  x
}


table_exists <- function(conn, name, schema = "main") {
  DBI::dbGetQuery(
    conn,
    sprintf(
      "SELECT EXISTS (
         SELECT 1 FROM information_schema.tables
         WHERE table_schema='%s' AND table_name='%s'
       )",
      schema, name
    )
  )[1,1]
}


arrow_view_exists <- function(conn, name) {
  name %in% duckdb::duckdb_list_arrow(conn)
}

#' Get DuckDB connection from an object
#'
#' Extracts the connection from duckspatial_df, tbl_lazy, or validates a direct connection.
#'
#' @param x A duckspatial_df, tbl_lazy, duckdb_connection, or NULL
#' @return A duckdb_connection or NULL
#' @keywords internal
get_conn_from_input <- function(x) {  # nocov start
  if (is.null(x)) return(NULL)
  
  if (inherits(x, "duckdb_connection")) return(x)
  
  if (inherits(x, c("duckspatial_df", "tbl_lazy"))) {
    return(dbplyr::remote_con(x))
  }
  
  NULL
}  # nocov end

#' Compare two CRS objects for equality
#'
#' Properly compares CRS objects, handling different representations of the same CRS.
#'
#' @param crs1 First CRS object
#' @param crs2 Second CRS object
#' @return Logical indicating if CRS are equal
#' @keywords internal
crs_equal <- function(crs1, crs2) {  # nocov start
  if (is.null(crs1) || is.null(crs2)) return(FALSE)
  isTRUE(sf::st_crs(crs1) == sf::st_crs(crs2))
}  # nocov end

#' Import a view/table from one connection to another
#'
#' Enables cross-connection operations by importing views using one of three strategies.
#'
#' @param target_conn Target DuckDB connection
#' @param source_conn Source DuckDB connection  
#' @param source_object duckspatial_df, tbl_lazy, or tbl_duckdb_connection from source_conn
#' @param target_name Name for view in target connection (auto-generated if NULL)
#' @template quiet
#' @return List with imported view name and cleanup function
#' @keywords internal
import_view_to_connection <- function(target_conn, source_conn, source_object, target_name = NULL, quiet = FALSE) {
  
  if (is.null(target_name)) {
    target_name <- paste0("imported_", gsub("-", "_", uuid::UUIDgenerate()))
  }
  
  # Track cleanup actions
  cleanup_actions <- list()
  
  # STRATEGY 1: SQL recreation (same DB, direct view reference)
  if (inherits(source_object, c("duckspatial_df", "tbl_duckdb_connection", "tbl_lazy"))) {
    source_table <- dbplyr::remote_name(source_object)
    
    if (!is.null(source_table) && !inherits(source_table, "sql")) {
      source_table_clean <- gsub('^"|"$', "", as.character(source_table))
      
      view_sql <- tryCatch({
        q_sql <- glue::glue(
          "SELECT sql FROM duckdb_views() WHERE view_name = {DBI::dbQuoteString(source_conn, source_table_clean)}"
        )
        result <- DBI::dbGetQuery(source_conn, q_sql)
        if (nrow(result) > 0) result$sql else NULL
      }, error = function(e) NULL)
      
      if (!is.null(view_sql) && length(view_sql) > 0) {
        source_pat_clean <- paste0("VIEW\\s+\"", source_table_clean, "\"")
        source_pat <- paste0("VIEW\\s+", source_table)
        
        if (grepl(source_pat_clean, view_sql, ignore.case = TRUE)) {
          new_sql <- sub(source_pat_clean, paste0("VIEW ", target_name), view_sql, ignore.case = TRUE)
        } else {
          new_sql <- sub(source_pat, paste0("VIEW ", target_name), view_sql, ignore.case = TRUE)
        }
        new_sql <- sub("CREATE VIEW", "CREATE OR REPLACE TEMPORARY VIEW", new_sql, ignore.case = TRUE)
        
        tryCatch({
          DBI::dbExecute(target_conn, new_sql)
          if (!quiet) cli::cli_inform("Imported view using SQL recreation (zero overhead)")
          return(list(name = target_name, method = "sql_recreation", cleanup = function() NULL))
        }, error = function(e) {
          if (isTRUE(getOption("duckspatial.debug", FALSE))) {
            cli::cli_alert_info("Strategy 1 (SQL recreation) failed: {conditionMessage(e)}")
          }
        })
      }
    }
  }
  
  # STRATEGY 2: SQL render (same DB, lazy query)
  if (inherits(source_object, c("tbl_lazy", "tbl_duckdb_connection"))) {
    query_sql <- tryCatch({
      as.character(dbplyr::sql_render(source_object, con = source_conn))
    }, error = function(e) NULL)
    
    if (!is.null(query_sql)) {
      view_query <- glue::glue("CREATE OR REPLACE TEMPORARY VIEW {target_name} AS {query_sql}")
      
      tryCatch({
        DBI::dbExecute(target_conn, view_query)
        if (!quiet) cli::cli_inform("Imported view using SQL query (zero overhead)")
        return(list(name = target_name, method = "sql_render", cleanup = function() NULL))
      }, error = function(e) {
        if (isTRUE(getOption("duckspatial.debug", FALSE))) {
          cli::cli_alert_info("Strategy 2 (SQL render) failed: {conditionMessage(e)}")
        }
      })
    }
  }
  
  # STRATEGY 3: ATTACH file-based source DB (READ_ONLY)
  # Try multiple ways to get dbdir since dbGetInfo may return NULL
  source_dbdir <- tryCatch({
    info <- DBI::dbGetInfo(source_conn)
    dbdir <- info$dbdir
    if (is.null(dbdir) || length(dbdir) == 0) {
       dbdir <- info$dbname
    }
    
    if (is.null(dbdir) || length(dbdir) == 0) {
      # Fallback: try to query from DuckDB itself
      res <- DBI::dbGetQuery(source_conn, "SELECT current_database()")
      NULL # Still can't get file path from this
    }
    dbdir
  }, error = function(e) NULL)
  
  if (!is.null(source_dbdir) && source_dbdir != ":memory:" && file.exists(source_dbdir)) {
    source_table <- tryCatch(dbplyr::remote_name(source_object), error = function(e) NULL)
    
    if (!is.null(source_table) && !inherits(source_table, "sql")) {
      attach_alias <- paste0("src_", gsub("-", "_", uuid::UUIDgenerate()))
      source_table_clean <- gsub('^"|"$', "", as.character(source_table))
      
      tryCatch({
        DBI::dbExecute(target_conn, glue::glue("ATTACH '{source_dbdir}' AS {attach_alias} (READ_ONLY)"))
        
        view_query <- glue::glue("
          CREATE OR REPLACE TEMPORARY VIEW {target_name} AS
          SELECT * FROM {attach_alias}.{source_table_clean}
        ")
        DBI::dbExecute(target_conn, view_query)
        
        if (!quiet) cli::cli_inform("Imported view using ATTACH (zero-copy, READ_ONLY)")
        return(list(
          name = target_name, 
          method = "attach",
          cleanup = function() {
            tryCatch(DBI::dbExecute(target_conn, glue::glue("DETACH {attach_alias}")), error = function(e) NULL)
          }
        ))
      }, error = function(e) {
        if (isTRUE(getOption("duckspatial.debug", FALSE))) {
          cli::cli_alert_info("Strategy 3 (ATTACH) failed: {conditionMessage(e)}")
        }
        tryCatch(DBI::dbExecute(target_conn, glue::glue("DETACH IF EXISTS {attach_alias}")), error = function(e) NULL)
      })
    }
  }
  
  # STRATEGY 4: Nanoarrow streaming (cross-DB)
  # Materialize into target to avoid Arrow lifecycle issues
  if (inherits(source_object, c("tbl_lazy", "tbl_duckdb_connection", "duckspatial_df"))) {
    query_sql <- tryCatch({
      as.character(dbplyr::sql_render(source_object, con = source_conn))
    }, error = function(e) NULL)
    
    if (!is.null(query_sql)) {
      tryCatch({
        res <- DBI::dbSendQuery(source_conn, query_sql, arrow = TRUE)
        
        reader <- duckdb::duckdb_fetch_arrow(res)
        stream <- nanoarrow::as_nanoarrow_array_stream(reader)
        
        # Register temporarily, then materialize into a view
        temp_arrow_name <- paste0("temp_arrow_", gsub("-", "_", uuid::UUIDgenerate()))
        duckdb::duckdb_register_arrow(target_conn, temp_arrow_name, stream)
        
        # Create permanent view from Arrow data (materializes)
        DBI::dbExecute(target_conn, glue::glue(
          "CREATE OR REPLACE TEMPORARY VIEW {target_name} AS SELECT * FROM {temp_arrow_name}"
        ))
        
        # Cleanup Arrow registration
        DBI::dbClearResult(res)
        tryCatch(duckdb::duckdb_unregister_arrow(target_conn, temp_arrow_name), error = function(e) NULL)
        
        if (!quiet) cli::cli_inform("Imported via nanoarrow streaming (zero R materialization)")
        return(list(name = target_name, method = "nanoarrow", cleanup = function() NULL))
      }, error = function(e) {
        if (isTRUE(getOption("duckspatial.debug", FALSE))) {
          cli::cli_alert_info("Strategy 4 (nanoarrow) failed: {conditionMessage(e)}")
        }
      })
    }
  }
  
  # STRATEGY 5: Collect + register (last resort)
  df <- dplyr::collect(source_object)
  
  if (inherits(df, "sf")) {
    duckspatial::ddbs_write_table(target_conn, df, target_name, temp_view = TRUE, quiet = quiet)
    cli::cli_warn("Imported via collection (materialized to R, then uploaded)")
    return(list(name = target_name, method = "collect_and_write", data = df, cleanup = function() NULL))
  } else if (is.data.frame(df)) {
    # For non-spatial data, use duckdb_register (zero-copy from R)
    duckdb::duckdb_register(target_conn, target_name, df)
    cli::cli_warn("Imported via duckdb_register (collected to R, zero-copy to target)")
    return(list(name = target_name, method = "duckdb_register", data = df, cleanup = function() NULL))
  } else {
    cli::cli_abort("Import failed: Cannot import object of class {.cls {class(df)}}.")
  }
}



#' Get column names in a DuckDB database
#'
#' @template conn
#' @param x name of the table
#' @param rest whether to return geometry column name, of the rest of the columns
#'
#' @keywords internal
#' @returns name of the geometry column of a table
get_geom_name <- function(conn, x, rest = FALSE, collapse = FALSE, table_id = NULL) {  # nocov start

    # check if the table exists (via DESCRIBE which works for temp views too)
    info_tbl <- try(DBI::dbGetQuery(conn, glue::glue("DESCRIBE {x};")), silent = TRUE)
    
    if (inherits(info_tbl, "try-error")) {
        cli::cli_abort("The table <{x}> does not exist.")
    }
    other_cols <- if (rest) {
        info_tbl[!grepl("GEOMETRY", info_tbl$column_type, ignore.case = TRUE), "column_name"]
    } else {
        info_tbl[grepl("GEOMETRY", info_tbl$column_type, ignore.case = TRUE), "column_name"]
    }

    # collapse columns with quoted names
    if (isTRUE(collapse)) {
      if (length(other_cols) > 0) {
        prefix <- if (is.null(table_id)) "" else paste0(table_id, ".")
        other_cols <- paste0(prefix, '"', other_cols, '"', collapse = ', ')
        other_cols <- paste0(other_cols, ", ") # trailing comma for SELECT {rest} something
      } else {
        other_cols <- ""
      }
    }

    return(other_cols)
}  # nocov end


#' Get names for the query
#'
#' @param name table name
#'
#' @keywords internal
#' @returns list with fixed names
get_query_name <- function(name) {  # nocov start
    if (length(name) == 2) {
        table_name <- name[2]
        schema_name <- name[1]
        query_name <- paste0(name, collapse = ".")
    } else {
        table_name   <- name
        schema_name <- "main"
        query_name <- name
    }
    list(
        table_name = table_name,
        schema_name = schema_name,
        query_name = query_name
    )
} # nocov end



#' Get names for the query
#'
#' @param x sf, duckspatial_df, tbl_lazy, or character
#' @template conn_null
#'
#' @keywords internal
#' @noRd
#' @returns list with fixed names
# IMPORTANT: This function returns a cleanup function instead of using on.exit() internally.
# 
# Why? R's on.exit() runs when the function containing it exits, NOT when the caller exits.
# If we used on.exit() here, the temporary views would be dropped as soon as get_query_list()
# returns, BEFORE the caller can execute their SQL query that references the view.
#
# The caller MUST register the cleanup function with on.exit() in their own scope:
#   result <- get_query_list(x, conn)
#   on.exit(result$cleanup(), add = TRUE)
#   # ... use result$query_name ...
#   # ... use result$query_name ...
get_query_list <- function(x, conn) {

  if (inherits(x, "sf")) {
    temp_view_name <- ddbs_temp_view_name()
    duckspatial::ddbs_write_table(conn = conn, data = x, name = temp_view_name,
                                    quiet = TRUE, temp_view = TRUE)
    x_list <- get_query_name(temp_view_name)
    x_list$cleanup <- function() {
      tryCatch(DBI::dbExecute(conn, glue::glue("DROP VIEW IF EXISTS {temp_view_name};")), error = function(e) NULL)
      tryCatch(duckdb::duckdb_unregister_arrow(conn, temp_view_name), error = function(e) NULL)
    }
    x_list$owned <- FALSE   # created here, caller should not clean up
    return(x_list)

  } else if (inherits(x, "duckspatial_df")) {
    source_table <- attr(x, "source_table")
    if (!is.null(source_table)) {
      remote_name_result <- tryCatch(dbplyr::remote_name(x), error = function(e) NULL)
      if (!is.null(remote_name_result) &&
          !inherits(remote_name_result, "sql") &&
          as.character(remote_name_result) == source_table) {
        result <- get_query_name(source_table)
        result$cleanup <- function() NULL
        result$owned <- TRUE
        return(result)
      }
    }
    ## Test duckdb 1.5
    x_list <- get_query_name(source_table)
    if (!is.null(x_list$table_name)) {
      x_list$cleanup <- function() NULL
      x_list$owned <- TRUE
      return(x_list)
    }
    ## Modified by dplyr verbs: render to a new temp view
    temp_view_name <- ddbs_temp_view_name()
    query_sql <- dbplyr::sql_render(x, con = conn)
    DBI::dbExecute(conn, glue::glue(
      "CREATE OR REPLACE TEMPORARY VIEW {temp_view_name} AS {query_sql}"
    ))
    x_list <- get_query_name(temp_view_name)
    x_list$cleanup <- function() {
      tryCatch(DBI::dbExecute(conn, glue::glue("DROP VIEW IF EXISTS {temp_view_name};")), error = function(e) NULL)
    }
    x_list$owned <- TRUE

    return(x_list)

  } else if (inherits(x, "tbl_lazy")) {
    temp_view_name <- ddbs_temp_view_name()
    query_sql <- dbplyr::sql_render(x)
    DBI::dbExecute(conn, glue::glue(
      "CREATE OR REPLACE TEMPORARY VIEW {temp_view_name} AS {query_sql}"
    ))
    x_list <- get_query_name(temp_view_name)
    x_list$cleanup <- function() {
      tryCatch(DBI::dbExecute(conn, glue::glue("DROP VIEW IF EXISTS {temp_view_name};")), error = function(e) NULL)
    }
    x_list$owned <- TRUE
    return(x_list)

  } else if (inherits(x, "data.frame")) {
    temp_view_name <- ddbs_temp_view_name()
    duckdb::duckdb_register(conn, temp_view_name, x)
    x_list <- get_query_name(temp_view_name)
    x_list$cleanup <- function() {
      tryCatch(DBI::dbExecute(conn, glue::glue("DROP VIEW IF EXISTS {temp_view_name};")), error = function(e) NULL)
      tryCatch(duckdb::duckdb_unregister_arrow(conn, temp_view_name), error = function(e) NULL)
    }
    x_list$owned <- TRUE
    return(x_list)

  } else {
    ## Character table name: pre-existing, never clean up
    x_list <- get_query_name(x)
    x_list$cleanup <- function() NULL
    x_list$owned <- TRUE
    return(x_list)
  }
}




#' Gets predicate name
#'
#' Gets a full predicate name from the shorter version
#'
#' @template predicate
#'
#' @keywords internal
#' @returns character
get_st_predicate <- function(predicate) { # nocov start
    switch(predicate,
      "intersects"            = "ST_Intersects",
      "intersects_extent"     = "ST_Intersects_Extent",
      "covers"                = "ST_Covers",
      "touches"               = "ST_Touches",
      "contains"              = "ST_Contains",
      "contains_properly"     = "ST_ContainsProperly",
      "within"                = "ST_Within",
      "within_properly"       = "ST_WithinProperly",
      "disjoint"              = "ST_Disjoint",
      "equals"                = "ST_Equals",
      "overlaps"              = "ST_Overlaps",
      "crosses"               = "ST_Crosses",
      "covered_by"            = "ST_CoveredBy",
      "dwithin"               = "ST_DWithin",
      cli::cli_abort(c(
          "Invalid spatial predicate: {.val {predicate}}",
          "i" = "Valid options: {.val {c('intersects', 'intersects_extent', 'covers', 'touches', 'contains', 'contains_properly', 'within', 'within_properly', 'dwithin', 'disjoint', 'equals', 'overlaps', 'crosses', 'covered_by')}}"
        ))
      )
} # nocov end


#' Converts from data frame to sf using WKB conversion
#'
#' Converts a table that has been read from DuckDB into an sf object.
#'
#' @param data a tibble or data frame
#' @template crs
#' @param x_geom name of geometry column
#'
#' @keywords internal
#' @returns sf
convert_to_sf_wkb <- function(data, crs, x_geom) { # nocov start

  # 1. Resolve CRS
  target_crs <- crs

  # Add warning if still no CRS found
  if (is.null(target_crs)) {
    cli::cli_alert_warning("No CRS found for the imported table.")
  }

  # 2. Check Geometry Type and Convert
  geom_data <- data[[x_geom]]

  if (inherits(geom_data, "blob") || is.list(geom_data)) {
    # --- FAST PATH: Binary Data ---

    # Attempt to use wk directly.
    # We use tryCatch because:
    # 1. It handles lists where the first element is NULL (which is.raw() misses)
    # 2. It safely falls back if the list contains non-WKB data
    
    wk_success <- tryCatch({
      # Strip attributes (like 'blob') to ensure it's a clean list for wk
      attributes(geom_data) <- NULL
      
      # OPTIMIZATION: Zero-copy wrap and convert
      wkb_obj <- wk::new_wk_wkb(geom_data)
      data[[x_geom]] <- sf::st_as_sfc(wkb_obj)
      TRUE
    }, error = function(e) {
      FALSE
    })

    if (!wk_success) {
      # --- FALLBACK PATH ---
      # Used if wk failed (e.g., data is native arrow structure or complex list)
      tryCatch({
        ga_vctr <- geoarrow::as_geoarrow_vctr(geom_data)
        data[[x_geom]] <- sf::st_as_sfc(ga_vctr)
      }, error = function(e) {
        # Final fallback: standard sf blob reading
        data[[x_geom]] <- sf::st_as_sfc(structure(geom_data, class = "WKB"))
      })
    }

  } else if (is.character(geom_data)) {
    # --- SLOW PATH: WKT Strings ---
    # Used if the query explicitly used ST_AsText() or older DuckDB versions
    data[[x_geom]] <- sf::st_as_sfc(geom_data)
  }

  # 3. Construct SF Object
  # Use st_as_sf with the pre-converted geometry column
  # We explicitly set the geometry column name to handle cases where x_geom isn't "geometry"
  sf_obj <- sf::st_as_sf(data, sf_column_name = x_geom)

  # 4. Assign CRS if found
  if (!is.null(target_crs)) {
    sf::st_crs(sf_obj) <- sf::st_crs(target_crs)
  }

  return(sf_obj)
} # nocov end






#' Feedback for overwrite argument
#'
#' @param x table name
#' @template conn
#' @template quiet
#' @template overwrite
#'
#' @keywords internal
#' @returns cli message
overwrite_table <- function(x, conn, quiet, overwrite) { # nocov start
  if (overwrite) {
    DBI::dbExecute(conn, glue::glue("DROP TABLE IF EXISTS {x};"))
    if (isFALSE(quiet)) cli::cli_alert_info("Table <{x}> dropped")
  }
} # nocov end





#' Feedback for query success
#'
#' @template quiet
#'
#' @keywords internal
#' @returns cli message
feedback_query <- function(quiet) { # nocov start
  if (isFALSE(quiet)) cli::cli_alert_success("Query successful")
} # nocov end



#' Get the number of rows in a table
#'
#' @template conn
#' @param table name of the table
#'
#' @keywords internal
#' @returns number of rows in the table
get_nrow <- function(conn, table) { # nocov start
  DBI::dbGetQuery(conn, glue::glue("SELECT COUNT(*) as n FROM {table}"))$n
} # nocov end





reframe_predicate_data <- function(
  conn, 
  data, 
  x_list, 
  y_list, 
  id_x, 
  id_y, 
  sparse) { # nocov start

  ## get number of rows
  nrowx <- get_nrow(conn, x_list$query_name)
  nrowy <- get_nrow(conn, y_list$query_name)

  ## convert results to matrix -> to list
  ## return matrix if sparse = FALSE
  pred_mat  <- matrix(data$predicate, nrow = nrowx, ncol = nrowy, byrow = TRUE)
  if (isFALSE(sparse)) return(pred_mat)

  pred_list <- apply(pred_mat, 1, function(row) which(row), simplify = FALSE)

  ## return if no matches have been found
  if (length(pred_list) == 0) return(NULL)

  ## rename list if id is provided
  if (!is.null(id_x)) {
    idx_names <- DBI::dbGetQuery(conn, glue::glue("SELECT {id_x} as id FROM {x_list$query_name}"))$id
    names(pred_list) <- idx_names
  }

  ## rename list if id is provided
  if (!is.null(id_y)) {
    idy_names <- DBI::dbGetQuery(conn, glue::glue("SELECT {id_y} as id FROM {y_list$query_name}"))$id
    pred_list <- lapply(pred_list, function(ind) {
      if (length(ind) == 0) return(ind)
      idy_names[ind]
    })
  }

  return(pred_list)

} # nocov end

#' Convert CRS input to DuckDB SQL literal
#'
#' Helper to format numeric EPSG codes, WKT strings, or `sf::st_crs` objects
#' into a SQL literal string compatible with `ST_Transform`.
#'
#' @param x numeric (EPSG), character (WKT/Proj), or `sf` crs object
#'
#' @keywords internal
#' @noRd
#' @returns character string (e.g. "'EPSG:4326'") or "NULL"
crs_to_sql <- function(x) {  # nocov start
  if (is.null(x)) return("NULL")
  if (inherits(x, "crs") && is.na(x)) return("NULL")
  if (is.atomic(x) && all(is.na(x))) return("NULL")

  if (inherits(x, "crs")) {
    if (!is.na(x$epsg)) return(paste0("'EPSG:", x$epsg, "'"))
    if (!is.null(x$wkt)) {
      # Escape single quotes for SQL
      val_clean <- gsub("'", "''", x$wkt)
      return(paste0("'", val_clean, "'"))
    }
    return("NULL")
  }

  if (is.numeric(x)) {
    return(paste0("'EPSG:", as.integer(x), "'"))
  }

  if (is.character(x)) {
    val_clean <- gsub("'", "''", x)
    return(paste0("'", val_clean, "'"))
  }

  return("NULL")
} # nocov end






#' Handle output type for duckspatial functions
#'
#' Returns a `sf` or a `duckspatial_df` from a query, depending
#' on the `mode` parameter or global options.
#'
#' @param query A query
#' @param conn DuckDB connection
#' @template mode
#' @template crs
#' @param x_geom Name of the geometry column
#'
#' @keywords internal
#' @noRd
#' @returns Object of the specified mode type
ddbs_handle_query <- function(
  query, 
  conn, 
  mode = NULL, 
  crs = NULL,
  x_geom = "geometry",
  fun_group = 1,
  units = NULL
) { # nocov start

  # First, handle simple data frames
  crs_is_na <- is.null(crs) || (inherits(crs, "crs") && is.na(crs)) || (is.atomic(crs) && all(is.na(crs)))
  if (crs_is_na && length(x_geom) == 0) {

    ## Create the table
    view_name <- ddbs_temp_table_name()
    DBI::dbExecute(
      conn, 
      glue::glue("CREATE TEMP TABLE {view_name} AS {query};")
    )

    ## Return a lazy table
    return(dplyr::tbl(conn, view_name))

  }
  
  # Resolve mode type: parameter > global option > default
  if (is.null(mode)) {
    mode <- getOption("duckspatial.mode", "duckspatial")
  }
  
  # Validate mode type
  valid_modes <- c("duckspatial", "sf")
  if (!mode %in% valid_modes) {
    cli::cli_abort(
      "{.arg mode} must be one of {.val {valid_modes}}, not {.val {mode}}."
    )
  }
  
  # Handle based on mode type
  if (mode == "sf") {

    ## Get the query as a data frame
    data_tbl <- DBI::dbGetQuery(conn, query)

    ## Manage sf output depending on the function group
    ## - Group 1: functions that return a normal {sf} object (most of the funs)
    ## - Group 2: functions that return a vector (units or not units)
    if (fun_group == 1) {

      ## Convert to sf object
      data_sf <- convert_to_sf_wkb(
        data       = data_tbl,
        crs        = crs,
        x_geom     = x_geom
      )
      return(data_sf)

    } else if (fun_group == 2) {
      
      ## Return units/non-units vector
      if (is.null(units)) {
        return(data_tbl[, 1])
      } else {
        return(units::as_units(data_tbl[, 1], units))
      }
      
    }
    
  } else {
    # mode == "duckspatial"
    # Create a view name and the query
    view_name <- ddbs_temp_table_name()
    query <- glue::glue("
      CREATE TEMP TABLE {view_name} AS
      {query};
    ")
    # on.exit(DBI::dbExecute(conn, glue::glue("DROP TABLE IF EXISTS {view_name}")))

    # Create the view
    DBI::dbExecute(conn, query)

    # Open lazily as duckspatial_df
    lazy_tbl <- duckdb::tbl_function(conn, view_name)

    result <- new_duckspatial_df(
      lazy_tbl, 
      crs = crs, 
      geom_col = x_geom, 
      source_table = view_name,
      source_conn = conn
    )
    
    return(result)
  }
  # nocov end
}



#' Get CRS from a spatial file
#'
#' @param path Path to the file
#' @param conn DuckDB connection
#' @return crs object or NULL
#' @keywords internal
#' @noRd
get_file_crs <- function(path, conn) {
    tryCatch({
      meta_query <- glue::glue("
        SELECT 
          layers[1].geometry_fields[1].crs.auth_name as auth_name,
          layers[1].geometry_fields[1].crs.auth_code as auth_code
        FROM st_read_meta('{path}')
      ")
      meta <- DBI::dbGetQuery(conn, meta_query)
      
      if (!is.na(meta$auth_code) && !is.na(meta$auth_name)) {
        crs_string <- paste0(meta$auth_name, ":", meta$auth_code)
        sf::st_crs(crs_string)
      } else {
        NULL
      }
    }, error = function(e) {
      cli::cli_warn("Could not auto-detect CRS from file: {e$message}")
      NULL
    })
}



#' Get or create default DuckDB connection with spatial extension installed and loaded
#'
#'
#' @param create Logical. If TRUE and no connection exists, create one.
#'   Default is TRUE.
#' @param ... Additional parameters to pass to `ddbs_create_conn()`
#'
#' @returns A `duckdb_connection` or NULL if no connection exists and
#'   create = FALSE
#'
#' @keywords internal
ddbs_default_conn <- function(create = TRUE, ...) {
  conn <- getOption("duckspatial_conn", NULL)

  # Check if existing connection is still valid

  if (!is.null(conn)) {
    if (!DBI::dbIsValid(conn)) {
      options(duckspatial_conn = NULL)
      conn <- NULL
    }
  }

  # Create new connection if needed
  if (is.null(conn) && create) {
    conn <- ddbs_create_conn(dbdir = "memory", ...)
    options(duckspatial_conn = conn)
  }

  # Activate macros
  if (!is.null(conn)) {
    create_duckspatial_macros(conn)
  }

  conn
}

#' Generate unique temporary view name
#'
#' Creates a unique name for temporary views to avoid collisions.
#'
#' @returns Character string with unique view name
#' @keywords internal
ddbs_temp_view_name <- function() { # nocov start
  paste0("temp_view_", gsub("-", "_", uuid::UUIDgenerate()))
} # nocov end

#' Generate unique temporary table name
#'
#' Creates a unique name for temporary tables to avoid collisions.
#'
#' @returns Character string with unique table name
#' @keywords internal
ddbs_temp_table_name <- function() { # nocov start
  paste0("temp_table_", gsub("-", "_", uuid::UUIDgenerate()))
} # nocov end

ddbs_checkpoint_if_possible <- function(conn) {
  if (!DBI::dbIsValid(conn)) {
    return(invisible(FALSE))
  }

  ok <- tryCatch({
    DBI::dbExecute(conn, "FORCE CHECKPOINT")
    TRUE
  }, error = function(e) {
    FALSE
  })

  invisible(ok)
}


#' Create an ephemeral DuckDB connection
#'
#' Creates a DuckDB connection that is automatically closed when the calling
#' function exits (either normally or due to an error). For file-based 
#' connections, the database file can also be automatically deleted on cleanup.
#'
#' @param file If TRUE, creates a file-based temporary database instead of 
#'   in-memory. If a character string, uses that as the database file path.
#'   Default is FALSE (in-memory).
#' @param read_only If TRUE and file is provided, opens the connection as 
#'   read-only. Has no effect on in-memory connections. Default is FALSE.
#' @param cleanup If TRUE (default), the connection will be closed (with 
#'   shutdown = TRUE for file-based) and for file-based connections, the 
#'   database file will be deleted.
#' @param envir The environment in which to schedule cleanup. Default is the
#'   parent frame (the caller's environment).
#' @template threads
#' @template memory_limit_gb
#' @param duckdb_storage_version Storage compatibility for newly created persistent
#'   native DuckDB files (\code{.duckdb}, \code{.db}, \code{.ddb}).
#'   \itemize{
#'     \item \code{"v1.5.0"} (\strong{Native Spatial Storage}, Default): Preserves
#'           CRS metadata in native DuckDB \code{GEOMETRY} columns. Requires
#'           DuckDB >= 1.5.0 to open the file.
#'     \item \code{"v1.0.0"} (\strong{Legacy Compatibility}): Creates
#'           files readable by older DuckDB versions (>= 1.0.0). Persists CRS
#'           metadata in duckspatial-managed column comments (a convention not
#'           recognized by other spatial software).
#'   }
#'
#' @returns A `duckdb_connection` that will be automatically closed on exit.
#'   For file-based connections, also returns the file path as an attribute 
#'   `db_file`.
#' @noRd
#' @keywords internal
ddbs_temp_conn <- function(file = FALSE, read_only = FALSE, cleanup = TRUE, 
                            envir = parent.frame(), threads = NULL, memory_limit_gb = NULL,
                            duckdb_storage_version = duckspatial_storage_default()) {

  assert_threads(threads)
  assert_memory_limit_gb(memory_limit_gb)
  duckdb_storage_version <- match_duckdb_storage_version(duckdb_storage_version)

  if (isTRUE(file) || is.character(file)) {
    # File-based connection
    if (is.character(file)) {
      db_file <- file
    } else {
      db_file <- tempfile(fileext = ".duckdb")
    }
    
    # IMPORTANT: DuckDB cannot open non-existent files in read-only mode
    # If creating a new tempfile with read_only=TRUE, we must create it first
    if (isTRUE(read_only) && !file.exists(db_file)) {
      # Create the database file first in writable mode
      conn_init <- ddbs_open_persistent(
        db_file,
        duckdb_storage_version = duckdb_storage_version,
        read_only = FALSE
      )
      ddbs_checkpoint_if_possible(conn_init)
      drv_init <- conn_init@driver
      DBI::dbDisconnect(conn_init)
      if (inherits(drv_init, "duckdb_driver")) {
        duckdb::duckdb_shutdown(drv_init)
      }
    }
    
    conn <- ddbs_open_persistent(
      db_file,
      duckdb_storage_version = duckdb_storage_version,
      read_only = read_only
    )
    
    # Checks and installs the Spatial extension
    # NOTE: These operations work fine on read-only connections:
    # - INSTALL writes to global extension dir (~/.duckdb/extensions), not the database
    # - LOAD just loads extension into session memory
    # - SET operations are session-level settings
    ddbs_install(conn, upgrade = FALSE, quiet = TRUE)
    ddbs_load(conn, quiet = TRUE, create_macros = !read_only)

    # Configure resources
    ddbs_set_resources(conn, threads = threads, memory_limit_gb = memory_limit_gb)

    # Cleanup: disconnect and optionally delete file
    withr::defer({
      if (DBI::dbIsValid(conn)) {
        if (!isTRUE(read_only)) {
          ddbs_checkpoint_if_possible(conn)
        }
        drv <- conn@driver
        tryCatch(suppressWarnings(DBI::dbDisconnect(conn)), error = function(e) NULL)
        if (inherits(drv, "duckdb_driver")) {
          tryCatch(duckdb::duckdb_shutdown(drv), error = function(e) NULL)
        }
      }
      if (isTRUE(cleanup) && file.exists(db_file)) unlink(db_file)
    }, envir = envir)
  } else {
    # In-memory connection
    conn <- ddbs_create_conn(
      dbdir = "memory",
      threads = threads,
      memory_limit_gb = memory_limit_gb,
      duckdb_storage_version = duckdb_storage_version
    )
    withr::defer({
      if (DBI::dbIsValid(conn)) {
        drv <- conn@driver
        tryCatch(suppressWarnings(DBI::dbDisconnect(conn)), error = function(e) NULL)
        if (inherits(drv, "duckdb_driver")) {
          tryCatch(duckdb::duckdb_shutdown(drv), error = function(e) NULL)
        }
      }
    }, envir = envir)
  }
  
  conn
}

#' Resolve connections and handle cross-connection imports
#'
#' @param x Input x (sf, duckspatial_df, tbl, character, etc.)
#' @param y Input y
#' @param conn Explicit target connection (optional)
#' @param conn_x Connection for x (optional, resolved if NULL)
#' @param conn_y Connection for y (optional, resolved if NULL)
#' @template quiet
#' 
#' @return List containing:
#'   - conn: The target connection to use
#'   - x: Updated x (may be new view name if imported)
#'   - y: Updated y (may be new view name if imported)
#'   - cleanup: A function to call on exit to drop temporary views
#' @keywords internal
#' @noRd
resolve_spatial_connections <- function(
  x, 
  y, 
  conn = NULL, 
  conn_x = NULL, 
  conn_y = NULL, 
  quiet = FALSE) {
    
    cleanup_funs <- list()
    add_cleanup <- function(fn) {
        cleanup_funs <<- c(cleanup_funs, list(fn))
    }
    
    # 1. Resolve source connections
    # If not provided, try to extract from objects
    # Note: Character inputs will return NULL from get_conn_from_input
    source_conn_x <- conn_x %||% get_conn_from_input(x)
    source_conn_y <- conn_y %||% get_conn_from_input(y)
    source_conn_ds <- attr(x, "source_conn")
    
    # 2. Determine target connection
    # Priority: explicit conn > conn_x > conn_y > default
    target_conn <- if (!is.null(conn)) {
        conn
    } else if (!is.null(source_conn_x)) {
        source_conn_x
    } else if (!is.null(source_conn_y)) {
        source_conn_y
    } else if (!is.null(source_conn_ds)) {
      source_conn_ds
    } else {
      ddbs_default_conn()
    }
  
    
    # 2.1 Validate target connection
    if (!DBI::dbIsValid(target_conn)) {
        cli::cli_abort("Target connection is not valid. Please provide a valid DuckDB connection.")
    }
    
    # 2.2 Warn if conn_x and conn_y differ but no explicit conn was provided
    if (is.null(conn) && 
        !is.null(source_conn_x) && 
        !is.null(source_conn_y) && 
        !identical(source_conn_x, source_conn_y)) {
        cli::cli_warn(c(
            "{.arg x} and {.arg y} come from different DuckDB connections.",
            "i" = "Using {.arg x}'s connection as the target. Provide {.arg conn} to override."
        ))
    }
    
    # 3. Handle imports if source connections differ from target
    
    # Check x
    # We only import x if it HAS a source connection that is different from target
    if (!is.null(source_conn_x) && !identical(target_conn, source_conn_x)) {
         # Need to import x
         cli::cli_warn(c(
            "{.arg x} and the target connection are different.",
            "i" = "Importing {.arg x} to the target connection.",
            "i" = "This may require materializing data."
         ))
         
         x_to_import <- x
         if (is.character(x)) {
             x_to_import <- tryCatch({
                 tbl_obj <- dplyr::tbl(source_conn_x, x)
                 suppressWarnings(as_duckspatial_df(tbl_obj))
             }, error = function(e) {
                 tryCatch(dplyr::tbl(source_conn_x, x), error = function(ex) x)
             })
         }
         
         res <- import_view_to_connection(target_conn, source_conn_x, x_to_import, quiet = quiet)
         x <- res$name
         
         add_cleanup(function() {
             tryCatch(DBI::dbExecute(target_conn, glue::glue("DROP VIEW IF EXISTS {res$name}")), error = function(e) NULL)
             if (is.function(res$cleanup)) tryCatch(res$cleanup(), error = function(e) NULL)
         })
    }
    
    # Check y
    # We only import y if it HAS a source connection that is different from target
    if (!is.null(source_conn_y) && !identical(target_conn, source_conn_y)) {
         # Need to import y
         cli::cli_warn(c(
            "{.arg y} and the target connection are different.",
            "i" = "Importing {.arg y} to the target connection.",
            "i" = "This may require materializing data."
         ))
         
         y_to_import <- y
         if (is.character(y)) {
             y_to_import <- tryCatch({
                 tbl_obj <- dplyr::tbl(source_conn_y, y)
                 suppressWarnings(as_duckspatial_df(tbl_obj))
             }, error = function(e) {
                 tryCatch(dplyr::tbl(source_conn_y, y), error = function(ex) y)
             })
         }
         
         res <- import_view_to_connection(target_conn, source_conn_y, y_to_import, quiet = quiet)
         y <- res$name
         
         add_cleanup(function() {
             tryCatch(DBI::dbExecute(target_conn, glue::glue("DROP VIEW IF EXISTS {res$name}")), error = function(e) NULL)
             if (is.function(res$cleanup)) tryCatch(res$cleanup(), error = function(e) NULL)
         })
    }
    
    list(
        conn = target_conn,
        x = x,
        y = y,
        cleanup = function() {
            for (fn in cleanup_funs) fn()
        }
    )
}



#' Builds query in geometries
#' 
#' @param fun Duckdb spatial function
#' @template mode
#'
#' @keywords internal
#' @noRd
build_geom_query <- function(fun, name, crs, mode) { # nocov start
  ## Get mode
  if (is.null(name) && mode == "sf") {
    ## If not creating a table, fallback to BLOB
    glue::glue("ST_AsWKB({fun})")
  } else {
    ## When creating a table in a connection, we preserve the CRS
    ## in the geometry column
    geom_field <- duckdb_geometry_type(conn = NULL, crs)
    glue::glue("{fun}::{geom_field}")
  }
} # nocov end


#' Gets the current mode
#' 
#' @param mode duckspatial, sf or NULL
#'
#' @keywords internal
#' @noRd
get_mode <- function(mode, name) { # nocov start

  ## If name is not NULL, the geospatial operations will create a new table,
  ## so we are interested in the duckspatial version (i.e. not add ST_AsWKB())
  ## This is the same as ignoring the option
  if (!is.null(name)) {
    return("duckspatial")
  } 

  ## Get the current option
  if (is.null(mode)) {
    mode <- getOption("duckspatial.mode", "duckspatial")
  }

  return(mode)

} # nocov end



#' Builds verbose query of ddbs_union
#'
#' @keywords internal
#' @noRd
build_union_sql <- function(
  by_feature, 
  x_geom, 
  y_geom = NULL,
  x_query, 
  y_query = NULL) { # nocov start
  if (!is.null(y_query)) {
    if (by_feature) {
      list(
        geom_call = glue::glue("ST_Union(v1.{x_geom}, v2.{y_geom})"),
        geom_alias = x_geom,
        from      = glue::glue(
          "(SELECT ROW_NUMBER() OVER () as rn, * FROM {x_query}) v1
           JOIN (SELECT ROW_NUMBER() OVER () as rn, * FROM {y_query}) v2
           ON v1.rn = v2.rn"
        )
      )
    } else {
      list(
        geom_call  = glue::glue("ST_Union_Agg(geom)"),
        geom_alias = x_geom,
        from       = glue::glue(
          "(SELECT {x_geom} as geom FROM {x_query}
            UNION ALL
            SELECT {y_geom} as geom FROM {y_query}) v1"
        )
      )
    }
  } else {
    list(
      geom_call  = glue::glue("ST_Union_Agg({x_geom})"),
      geom_alias = x_geom,
      from       = x_query
    )
  }
} # nocov end





#' Builds verbose query of ddbs_union
#'
#' @keywords internal
#' @noRd
build_union_query <- function(
  by_feature, 
  name,
  crs,
  mode, 
  name_query,
  x_geom, 
  y_geom = NULL, 
  x_query, 
  y_query = NULL) { # nocov start

  parts     <- build_union_sql(by_feature, x_geom, y_geom, x_query, y_query)
  geom_expr <- glue::glue("{build_geom_query(parts$geom_call, name, crs, mode)} as {parts$geom_alias}")

  if (!is.null(y_query)) {
    row_id  <- if (by_feature) "ROW_NUMBER() OVER () as row_id," else "1 as row_id,"
  } else {
    row_id  <- ""
  }

  prefix <- if (!is.null(name_query)) {
    glue::glue("CREATE TABLE {name_query} AS")
  } else {
    ""
  }

  glue::glue("
    {prefix}
    SELECT {row_id} {geom_expr}
    FROM {parts$from};
  ")
} # nocov end


#' Gets the CRS of a table's geometry column
#' 
#' @param conn DuckDB connection
#' @param geom_name Name of the geometry column
#' @param table_name Name of the table
#' 
#' @return CRS object or NULL if not found
#'
#' @keywords internal
#' @noRd
get_table_crs <- function(conn, geom_name, table_name) { # nocov start

  DBI::dbGetQuery(
    conn,
    glue::glue("
        SELECT 
            ST_CRS({geom_name}) AS crs 
        FROM 
            {table_name}
        LIMIT 1;")
    )$crs

} # nocov end


#' Formats the geometry type for DuckDB queries based on the CRS of the data
#' 
#' @param x An sf object or CRS object to extract the CRS from
#' 
#' @return A character string representing the geometry type for DuckDB
#'
#' @keywords internal
#' @noRd
get_geometry_type_duckdb <- function(x, conn = NULL) { # nocov start
  duckdb_geometry_type(conn, x)
} # nocov end




#' Helper to create a table during a geospatial operation
#' 
#' @returns TRUE invisibly after creating the table
#'
#' @keywords internal
#' @noRd
create_duckdb_table <- function(
  conn,
  name,
  query,
  overwrite,
  quiet
) { # nocov start
  ## Convenient names of table and/or schema.table
  name_list <- get_query_name(name)

  ## Overwrite handling
  overwrite_table(name_list$query_name, conn, quiet, overwrite)

  ## Create and execute the query
  tmp.query <- glue::glue("
      CREATE TABLE {name_list$query_name} AS
      {query}
  ")
  DBI::dbExecute(conn, tmp.query)
  feedback_query(quiet)
  return(invisible(TRUE))
} # nocov end




#' Helper to create a the predicate clause for a spatial join/filter
#' 
#' @returns A character string with the predicate clause
#'
#' @keywords internal
#' @noRd
generate_predicate_clause <- function(
  predicate,
  conn,
  x_list,
  y_list,
  x_geom,
  y_geom,
  distance,
  crs_x
) { # nocov start

  ## For ST_Dwithin we have an extra distance argument, and the duckdb functions
  ## differ based on the CRS units (ST_DWithin vs ST_DWithin_Spheroid)
  if (predicate == "ST_DWithin") {

      ## if distance is not specified, it will fall back to ST_Within
      if (is.null(distance)) {
          cli::cli_warn("{.val distance} wasn't specified. Using ST_Within.")
          distance <- 0
      }

      ## check the CRS units to use the right function
      crs_units <- crs_x$units_gdal
      if (crs_units != "metre") {

          ## When using Spheroid version, only point geometry is allowed
          geom_type_x <- ddbs_geometry_type(x_list$query_name, FALSE, conn)
          geom_type_y <- ddbs_geometry_type(y_list$query_name, FALSE, conn)
          if (!geom_type_x %in% c("POINT", "MULTIPOINT") || !geom_type_y %in% c("POINT", "MULTIPOINT")) {
              cli::cli_abort(c(
                  "ST_DWithin with non-meter units only supports POINT or MULTIPOINT geometries.",
                  "i" = "Detected types: {.val {geom_type_x}} and {.val {geom_type_y}}."
              ))
          }

          # st_predicate <- glue::glue("ST_DWithin_Spheroid(v1.{x_geom}, v2.{y_geom}, {distance})")
          st_predicate <- glue::glue("
              ST_DWithin_Spheroid(
                  ST_Point(ST_Y(v1.{x_geom}), ST_X(v1.{x_geom})), 
                  ST_Point(ST_Y(v2.{y_geom}), ST_X(v2.{y_geom})), 
                  {distance})
              ")
          if (crs_x$input != "EPSG:4326") {
              cli::cli_warn(c(
                "Inputs are in {.val {crs_x$input}}, not {.val EPSG:4326}.",
                "i" = "Distance calculations may be less accurate.",
                "i" = "Consider transforming to {.val EPSG:4326} or a projected CRS."
              ))
          }
      } else {
          st_predicate <- glue::glue("ST_DWithin(v1.{x_geom}, v2.{y_geom}, {distance})")
      }

  } else {
      ## In every other case, it's a simple binary predicate with no extra arguments
      st_predicate <- glue::glue("{predicate}(v1.{x_geom}, v2.{y_geom})")
  }

  return(st_predicate)
} # nocov end



#' Helper to validate that the CRS of two tables match before a spatial operation
#'
#' @keywords internal
#' @noRd
validate_xy_crs <- function(
  crs_x,
  crs_y,
  conn,
  x_list,
  y_list
) { # nocov start
  if (!is.null(crs_x) && !is.null(crs_y)) {
       if (!crs_equal(crs_x, crs_y)) {
         cli::cli_abort("The Coordinates Reference System of {.arg x} and {.arg y} is different.")
       }
    } else {
       assert_crs(conn, x_list$query_name, y_list$query_name)
    }
} # nocov end




#' Helper to create macros for duckspatial functions
#'
#' @keywords internal
#' @noRd
create_duckspatial_macros <- function(conn) { # nocov start

  macros <- list(
    
    # --- geometry validation
    "CREATE OR REPLACE MACRO ddbs_is_simple(geom) AS ST_IsSimple(geom);",
    "CREATE OR REPLACE MACRO ddbs_is_valid(geom) AS ST_IsValid(geom);",
    "CREATE OR REPLACE MACRO ddbs_is_closed(geom) AS ST_IsClosed(geom);",
    "CREATE OR REPLACE MACRO ddbs_is_ring(geom) AS ST_IsRing(geom);",
    "CREATE OR REPLACE MACRO ddbs_is_empty(geom) AS ST_IsEmpty(geom);",
    "CREATE OR REPLACE MACRO ddbs_geometry_type(geom) AS ST_GeometryType(geom);",

    # --- measure functions
    "CREATE OR REPLACE MACRO ddbs_area(geom) AS (
      CASE 
        WHEN ST_CRS(geom) = 'EPSG:4326' THEN ST_Area_Spheroid(ST_FlipCoordinates(geom))
        ELSE ST_Area(geom)
      END
    );",

    "CREATE OR REPLACE MACRO ddbs_length(geom) AS (
      CASE 
        WHEN ST_CRS(geom) = 'EPSG:4326' THEN ST_Length_Spheroid(ST_FlipCoordinates(geom))
        ELSE ST_Length(geom)
      END
    );",

    "CREATE OR REPLACE MACRO ddbs_perimeter(geom) AS (
      CASE 
        WHEN ST_CRS(geom) = 'EPSG:4326' THEN ST_Perimeter_Spheroid(ST_FlipCoordinates(geom))
        ELSE ST_Perimeter(geom)
      END
    );",

    # --- aggregation functions
    "CREATE OR REPLACE MACRO ddbs_union_agg(geom) AS ST_Union_Agg(geom);",
    "CREATE OR REPLACE MACRO ddbs_union(geom) AS ST_Union_Agg(geom);",

    # --- coordinate operations
    "CREATE OR REPLACE MACRO ddbs_x(geom) AS ST_X(geom);",
    "CREATE OR REPLACE MACRO ddbs_y(geom) AS ST_Y(geom);",
    "CREATE OR REPLACE MACRO ddbs_m(geom) AS ST_M(geom);",
    "CREATE OR REPLACE MACRO ddbs_z(geom) AS ST_Z(geom);",

    # --- dimension operations
    "CREATE OR REPLACE MACRO ddbs_has_z(geom) AS ST_HasZ(geom);",
    "CREATE OR REPLACE MACRO ddbs_has_m(geom) AS ST_HasM(geom);",

    # --- format conversion
    "CREATE OR REPLACE MACRO ddbs_as_text(geom) AS ST_AsText(geom);",
    "CREATE OR REPLACE MACRO ddbs_as_wkb(geom) AS ST_AsWKB(geom);",
    "CREATE OR REPLACE MACRO ddbs_as_hexwkb(geom) AS ST_AsHexWKB(geom);",
    "CREATE OR REPLACE MACRO ddbs_as_geojson(geom) AS ST_AsGeoJSON(geom);",

    # --- extent functions
    "CREATE OR REPLACE MACRO ddbs_bbox(geom) AS (
      SELECT {
        xmin: ST_XMin(ST_Extent(geom)),
        ymin: ST_YMin(ST_Extent(geom)),
        xmax: ST_XMax(ST_Extent(geom)),
        ymax: ST_YMax(ST_Extent(geom))
      }
    );",

    # --- geometry processing general
    "CREATE OR REPLACE MACRO ddbs_boundary(geom) AS ST_Boundary(geom);",
    "CREATE OR REPLACE MACRO 
      ddbs_buffer(geometry, distance, num_triangles := 8, cap_style := 'CAP_ROUND', join_style := 'JOIN_ROUND', mitre_limit := 1) AS 
      ST_Buffer(geometry, distance::DOUBLE, num_triangles::INTEGER, cap_style::VARCHAR, join_style::VARCHAR, mitre_limit::DOUBLE);
    ",
    "CREATE OR REPLACE MACRO ddbs_centroid(geom) AS ST_Centroid(geom);",
    "CREATE OR REPLACE MACRO 
      ddbs_concave_hull(geom, ratio := 0.5, allow_holes := TRUE) AS 
      ST_ConcaveHull(geom, ratio, allow_holes);
    ",
    "CREATE OR REPLACE MACRO ddbs_convex_hull(geom) AS ST_ConvexHull(geom);",
    "CREATE OR REPLACE MACRO ddbs_exterior_ring(geom) AS ST_ExteriorRing(geom);",
    "CREATE OR REPLACE MACRO ddbs_voronoi(geom) AS ST_VoronoiDiagram(geom);",
    "CREATE OR REPLACE MACRO ddbs_build_area(geom) AS ST_BuildArea(geom);"


  )

  invisible(lapply(macros, DBI::dbExecute, conn = conn))

} # nocov end