Spatial Operations

Spatial Operations with {terra} Comparisons

# load libs
library(dbSpatial)
library(terra)
#> terra 1.7.78
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:terra':
#> 
#>     intersect, union
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

Create test data

# Create a data.frame with x and y coordinates and attributes
coordinates <- data.frame(x = 1:1e3, y = 1:1e3)
attributes <- data.frame(id = 1:1e3, name = paste("A", 1:1e3, sep = "_"))

# Combine the coordinates and attributes
dummy_data <- cbind(coordinates, attributes)

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

# DuckDB
duckdb_conn = DBI::dbConnect(duckdb::duckdb(), ":memory:")

db_points = dbSpatial(conn = duckdb_conn,
                      name = "spatVector_proxy",
                      value = dummy_spatvector,
                      overwrite = TRUE)

# Preview points table
db_points
#> # Class:    dbSpatial 
#> # Source:   table<spatVector_proxy> [?? x 4]
#> # Database: DuckDB v1.0.0 [unknown@Linux 6.5.0-1025-azure:R 4.4.1/:memory:]
#>       id name  geometry   geom      
#>    <int> <chr> <list>     <list>    
#>  1     1 A_1   <raw [21]> <raw [32]>
#>  2     2 A_2   <raw [21]> <raw [32]>
#>  3     3 A_3   <raw [21]> <raw [32]>
#>  4     4 A_4   <raw [21]> <raw [32]>
#>  5     5 A_5   <raw [21]> <raw [32]>
#>  6     6 A_6   <raw [21]> <raw [32]>
#>  7     7 A_7   <raw [21]> <raw [32]>
#>  8     8 A_8   <raw [21]> <raw [32]>
#>  9     9 A_9   <raw [21]> <raw [32]>
#> 10    10 A_10  <raw [21]> <raw [32]>
#> # ℹ more rows

Extent

# terra
terra::ext(dummy_spatvector)
#> SpatExtent : 1, 1000, 1, 1000 (xmin, xmax, ymin, ymax)

# dbSpatial
dbSpatial::st_extent(dbSpatial = db_points, geom = "geom")
#> Warning: Missing values are always removed in SQL aggregation functions.
#> Use `na.rm = TRUE` to silence this warning
#> This warning is displayed once every 8 hours.
#> xmin xmax ymin ymax 
#>    1 1000    1 1000

Is.Valid

# terra
head(terra::geom(dummy_spatvector))
#>      geom part x y hole
#> [1,]    1    1 1 1    0
#> [2,]    2    1 2 2    0
#> [3,]    3    1 3 3    0
#> [4,]    4    1 4 4    0
#> [5,]    5    1 5 5    0
#> [6,]    6    1 6 6    0

head(terra::is.valid(dummy_spatvector))
#> [1] TRUE TRUE TRUE TRUE TRUE TRUE

dbSpatial::st_isvalid(dbSpatial = db_points, geomName = "geom")
#> # Class:    dbSpatial 
#> # Source:   SQL [?? x 1]
#> # Database: DuckDB v1.0.0 [unknown@Linux 6.5.0-1025-azure:R 4.4.1/:memory:]
#>    geom 
#>    <lgl>
#>  1 TRUE 
#>  2 TRUE 
#>  3 TRUE 
#>  4 TRUE 
#>  5 TRUE 
#>  6 TRUE 
#>  7 TRUE 
#>  8 TRUE 
#>  9 TRUE 
#> 10 TRUE 
#> # ℹ more rows

Y, X Max

# terra
head(terra::geom(dummy_spatvector))
#>      geom part x y hole
#> [1,]    1    1 1 1    0
#> [2,]    2    1 2 2    0
#> [3,]    3    1 3 3    0
#> [4,]    4    1 4 4    0
#> [5,]    5    1 5 5    0
#> [6,]    6    1 6 6    0

terra::ymax(dummy_spatvector)
#> [1] 1000

terra::xmax(dummy_spatvector)
#> [1] 1000

# dbSpatial
dbSpatial::st_ymax(db_points)
#> [1] 1000

dbSpatial::st_xmax(db_points)
#> [1] 1000