Skip to contents

Create a dbSpatial object with geometry data type

dbSpatial.Rd

Constructor function to ingest diverse spatial data sources and create a dbSpatial object containing a geometry data type based on the Simple Features standard.

If x_colName and y_colName are both provided, a POINT geometry will be constructed based on these columns.

Usage

dbSpatial(
  value,
  name,
  conn,
  x_colName = NULL,
  y_colName = NULL,
  geomName = "geom",
  overwrite = FALSE,
  ...
)

Arguments

value

data.frame, tbl_duckdb_connection, character (valid file path), sf object, or terra object. Data to construct dbSpatial object with geometry data type. See details for more information.

name

The table name, passed on to dbQuoteIdentifier(). Options are:

  • a character string with the unquoted DBMS table name, e.g. "table_name",

  • a call to Id() with components to the fully qualified table name, e.g. Id(schema = "my_schema", table = "table_name")

  • a call to SQL() with the quoted and fully qualified table name given verbatim, e.g. SQL('"my_schema"."table_name"')

conn

A DBIConnection object, as returned by dbConnect().

x_colName

character. Name of column containing numerical X coordinates. default = NULL.

y_colName

character. Name of column containing numerical Y coordinates. default = NULL.

geomName

character string. The geometry column name in the dbSpatial object. Default: "geom".

overwrite

logical. Overwrite existing table. default = FALSE.

...

Additional arguments to be passed

Value

dbSpatial object.

Details

For list of files supported see link below. https://DuckDB.org/docs/extensions/spatial.html#st_read---read-spatial-value-from-files

See also

Other dbSpatial: as_dbSpatial(), show()

Examples

# create in-memory DuckDB db
duckdb_conn = DBI::dbConnect(duckdb::duckdb(), ":memory:")

# test value
test_data = data.frame(x = 1:10, y = 1:10, id = 1:10)

write.csv(test_data, "test_data.csv", row.names = FALSE)

# read data.frame and create point geometry
dbSpatial(conn = duckdb_conn,
          name = "test_points",
          value = test_data,
          x_colName = "x",
          y_colName = "y",
          overwrite = TRUE)
#> # Class:    dbSpatial 
#> # Source:   table<test_points> [10 x 4]
#> # Database: DuckDB v1.0.0 [unknown@Linux 6.5.0-1025-azure:R 4.4.1/:memory:]
#>        x     y    id geom      
#>    <int> <int> <int> <list>    
#>  1     1     1     1 <raw [32]>
#>  2     2     2     2 <raw [32]>
#>  3     3     3     3 <raw [32]>
#>  4     4     4     4 <raw [32]>
#>  5     5     5     5 <raw [32]>
#>  6     6     6     6 <raw [32]>
#>  7     7     7     7 <raw [32]>
#>  8     8     8     8 <raw [32]>
#>  9     9     9     9 <raw [32]>
#> 10    10    10    10 <raw [32]>

# read csv
dbSpatial(conn = duckdb_conn,
          name = "test_points",
          value = 'test_data.csv',
          x_colName = "x",
          y_colName = "y",
          overwrite = TRUE)
#> # Class:    dbSpatial 
#> # Source:   table<test_points> [10 x 4]
#> # Database: DuckDB v1.0.0 [unknown@Linux 6.5.0-1025-azure:R 4.4.1/:memory:]
#>    x     y     id    geom      
#>    <chr> <chr> <chr> <list>    
#>  1 1     1     1     <raw [32]>
#>  2 2     2     2     <raw [32]>
#>  3 3     3     3     <raw [32]>
#>  4 4     4     4     <raw [32]>
#>  5 5     5     5     <raw [32]>
#>  6 6     6     6     <raw [32]>
#>  7 7     7     7     <raw [32]>
#>  8 8     8     8     <raw [32]>
#>  9 9     9     9     <raw [32]>
#> 10 10    10    10    <raw [32]>