Skip to contents

https://postgis.net/docs/ST_Distance.html

Usage

st_distance(
  g1,
  g1_geomName = "geom",
  g1_cols_keep = "all",
  g2,
  g2_geomName = "geom",
  g2_cols_keep = "all",
  overwrite = FALSE,
  name = "distance_geom",
  ...
)

# S4 method for class 'dbSpatial,ANY,ANY,dbSpatial'
st_distance(
  g1,
  g1_geomName = "geom",
  g1_cols_keep = "all",
  g2,
  g2_geomName = "geom",
  g2_cols_keep = "all",
  overwrite = FALSE,
  name = "distance_geom",
  ...
)

Arguments

g1

A dbSpatial object.

g1_geomName

character. The geometry column name in g1. Default: "geom".

g1_cols_keep

character vector. The column names in g1 to keep. Default: "all".

g2

A dbSpatial object.

g2_geomName

character. The geometry column name in g2. Default: "geom".

g2_cols_keep

character vector. The column names in g2 to keep. Default: "all".

overwrite

logical. If TRUE, overwrite existing table. Default: FALSE.

name

character. Default: "distance_geom"

...

Additional arguments passed to duckdb::dbSendQuery.

Value

dbSpatial object

Functions

  • st_distance( g1 = dbSpatial, g1_geomName = ANY, g1_cols_keep = ANY, g2 = dbSpatial ): Method for dbSpatial objects

Examples

con = DBI::dbConnect(duckdb::duckdb(), ":memory:")

coordinates <- data.frame(x = c(100, 200, 300), y = c(500, 600, 700))
attributes <- data.frame(id = 1:3, name = c("A1", "B1", "C1"))

# Combine the coordinates and attributes
dummy_data <- cbind(coordinates, attributes)
 
points <- dbSpatial(conn = con,
                    name = "points", 
                    value = dummy_data, 
                    overwrite = TRUE, 
                    x_colName = "x", 
                    y_colName = "y")

# preview
points
#> # Class:    dbSpatial 
#> # Source:   table<points> [3 x 5]
#> # Database: DuckDB v1.0.0 [unknown@Linux 6.5.0-1025-azure:R 4.4.1/:memory:]
#>       x     y    id name  geom      
#>   <dbl> <dbl> <int> <chr> <list>    
#> 1   100   500     1 A1    <raw [32]>
#> 2   200   600     2 B1    <raw [32]>
#> 3   300   700     3 C1    <raw [32]>

# Create a second set of points, with B1 and C1 translated by + 100
dummy_data2 <- dummy_data
dummy_data2[c(2,3),c(1,2)] <- dummy_data2[c(2,3),c(1,2)] + 150
dummy_data2$name <- c('A2', 'B2', 'C2')

points2 <- dbSpatial(conn = con,
                     name = "points2", 
                     value = dummy_data2, 
                     overwrite = TRUE, 
                     x_colName = "x", 
                     y_colName = "y")
# preview
points2
#> # Class:    dbSpatial 
#> # Source:   table<points2> [3 x 5]
#> # Database: DuckDB v1.0.0 [unknown@Linux 6.5.0-1025-azure:R 4.4.1/:memory:]
#>       x     y    id name  geom      
#>   <dbl> <dbl> <int> <chr> <list>    
#> 1   100   500     1 A2    <raw [32]>
#> 2   350   750     2 B2    <raw [32]>
#> 3   450   850     3 C2    <raw [32]>

res <- st_distance(g1 = points, 
                   g1_cols_keep = c("name"), 
                   g2 = points2,
                   overwrite = TRUE)

res
#> # Class:    dbSpatial 
#> # Source:   table<distance_geom> [8 x 6]
#> # Database: DuckDB v1.0.0 [unknown@Linux 6.5.0-1025-azure:R 4.4.1/:memory:]
#>   name      x     y    id name_1 geom      
#>   <chr> <dbl> <dbl> <int> <chr>  <list>    
#> 1 A1      350   750     2 B2     <raw [32]>
#> 2 A1      450   850     3 C2     <raw [32]>
#> 3 B1      100   500     1 A2     <raw [32]>
#> 4 B1      350   750     2 B2     <raw [32]>
#> 5 B1      450   850     3 C2     <raw [32]>
#> 6 C1      100   500     1 A2     <raw [32]>
#> 7 C1      350   750     2 B2     <raw [32]>
#> 8 C1      450   850     3 C2     <raw [32]>