Skip to contents

Getting Started

getting_started.Rmd

Introduction

This vignette demonstrates how to use the dbSpatial package to create a DuckDB database with spatial points and polygons starting from various data sources.

Creating a DuckDB connection 

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

Reading in spatial data from various sources

From data.frames 

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

# df, tbl
# specify x and y column names to cast to a point geometry
a <- dbSpatial(conn = duckdb_conn,
               name = "test_points",
               value = test_data,
               x_colName = "x",
               y_colName = "y",
               overwrite = TRUE)
a
#> # 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]>

From .csv file 

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

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

# load file in db
a <- dbSpatial(conn = duckdb_conn,
               name = "test_points",
               value = 'test_data.csv',
               x_colName = "x",
               y_colName = "y",
               overwrite = TRUE)
a
#> # 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]>

From {terra} objects: SpatVector 

# load terra package
library(terra)
#> terra 1.7.78

# Create a SpatVector from the data.frame
dummy_spatvector <- terra::vect(test_data, geom = c("x", "y"))

# Load SpatVector in db
dbSpatial(conn = duckdb_conn,
          name = "spatVector_proxy",
          value = dummy_spatvector,
          overwrite = TRUE)
#> # Class:    dbSpatial 
#> # Source:   table<spatVector_proxy> [10 x 3]
#> # Database: DuckDB v1.0.0 [unknown@Linux 6.5.0-1025-azure:R 4.4.1/:memory:]
#>       id geometry   geom      
#>    <int> <list>     <list>    
#>  1     1 <raw [21]> <raw [32]>
#>  2     2 <raw [21]> <raw [32]>
#>  3     3 <raw [21]> <raw [32]>
#>  4     4 <raw [21]> <raw [32]>
#>  5     5 <raw [21]> <raw [32]>
#>  6     6 <raw [21]> <raw [32]>
#>  7     7 <raw [21]> <raw [32]>
#>  8     8 <raw [21]> <raw [32]>
#>  9     9 <raw [21]> <raw [32]>
#> 10    10 <raw [21]> <raw [32]>