Geometry column is changed into unusable format if i use st_write(append=TRUE)

Question

If i save a sf in r with st_write(con, "filename", append=TRUE) my geometry column is converted into a weird format (see picture), where i cannot use it anymore.

If i use the option append=FALSE everything is fine. Did anyone face a similar issue and knows a solution. I tried the option dbWriteTable() but it also did not work.

con is the Database i am writing my data to.

library(sf)
library(tidyverse)

quakes=quakes[1:10,]
quakes = st_as_sf(quakes, coords=c("long", "lat"), crs="EPSG:4326")

con <- DBI::dbConnect(RPostgres::Postgres())
st_write(obj=quakes, dsn=con, "quakes", append=TRUE)
st_read(con, query="SELECT * from quakes")

Answer 1

I can't reproduce your issue. What I did is writing the info to a .gpkg file (in your example it is not shown which type of file are you creating, since con variable was not provided), and everything seems to work as expected. See:

library(sf)
library(tidyverse)

# You didn't provide this parameter, I write here to a .gpkg file
con <- tempfile(fileext = ".gpkg")

data("quakes")

# A. Create the initial file
quakes_1 <- quakes[1:10, ]
quakes_1 <- st_as_sf(quakes_1, coords = c("long", "lat"), crs = "EPSG:4326")

# Write, read and plot
st_write(quakes_1, con, "quakes", append = TRUE)
#> Updating layer `quakes' to data source `/tmp/Rtmp6mAlny/file3fff11ac8a.gpkg' using driver `GPKG'
#> Writing 10 features with 3 fields and geometry type Point.

# File size
file.size(con)
#> [1] 98304

a <- st_read(con, query = "SELECT * from quakes")
#> Reading query `SELECT * from quakes'
#> from data source `/tmp/Rtmp6mAlny/file3fff11ac8a.gpkg' using driver `GPKG'
#> Simple feature collection with 10 features and 3 fields
#> Geometry type: POINT
#> Dimension:     XY
#> Bounding box:  xmin: 166.1 ymin: -28.74 xmax: 184.31 ymax: -11.7
#> Geodetic CRS:  WGS 84
glimpse(a)
#> Rows: 10
#> Columns: 4
#> $ depth    <int> 562, 650, 42, 626, 649, 195, 82, 194, 211, 622
#> $ mag      <dbl> 4.8, 4.2, 5.4, 4.1, 4.0, 4.0, 4.8, 4.4, 4.7, 4.3
#> $ stations <int> 41, 15, 43, 19, 11, 12, 43, 15, 35, 19
#> $ geom     <POINT [°]> POINT (181.62 -20.42), POINT (181.03 -20.62), POINT (184.1 -2…


ggplot(a) +
  geom_sf()

# B. Add more points

quakes_2 <- quakes[11:500, ]
quakes_2 <- st_as_sf(quakes_2, coords = c("long", "lat"), crs = "EPSG:4326")
st_write(quakes_2, con, "quakes", append = TRUE)
#> Updating layer `quakes' to data source `/tmp/Rtmp6mAlny/file3fff11ac8a.gpkg' using driver `GPKG'
#> Updating existing layer quakes
#> Writing 490 features with 3 fields and geometry type Point.

# New file size
file.size(con)
#> [1] 155648

# Read again the same file
b <- st_read(con, query = "SELECT * from quakes")
#> Reading query `SELECT * from quakes'
#> from data source `/tmp/Rtmp6mAlny/file3fff11ac8a.gpkg' using driver `GPKG'
#> Simple feature collection with 500 features and 3 fields
#> Geometry type: POINT
#> Dimension:     XY
#> Bounding box:  xmin: 165.77 ymin: -37.93 xmax: 188.13 ymax: -10.72
#> Geodetic CRS:  WGS 84
glimpse(b)
#> Rows: 500
#> Columns: 4
#> $ depth    <int> 562, 650, 42, 626, 649, 195, 82, 194, 211, 622, 583, 249, 554…
#> $ mag      <dbl> 4.8, 4.2, 5.4, 4.1, 4.0, 4.0, 4.8, 4.4, 4.7, 4.3, 4.4, 4.6, 4…
#> $ stations <int> 41, 15, 43, 19, 11, 12, 43, 15, 35, 19, 13, 16, 19, 10, 94, 1…
#> $ geom     <POINT [°]> POINT (181.62 -20.42), POINT (181.03 -20.62), POINT (18…


ggplot(b) +
  geom_sf()

^{Created on 2023-09-01 with reprex v2.0.2}

Session info

sessionInfo()
#> R version 4.3.1 (2023-06-16)
#> Platform: x86_64-pc-linux-gnu (64-bit)
#> Running under: Ubuntu 20.04.6 LTS
#> 
#> Matrix products: default
#> BLAS:   /usr/lib/x86_64-linux-gnu/atlas/libblas.so.3.10.3 
#> LAPACK: /usr/lib/x86_64-linux-gnu/atlas/liblapack.so.3.10.3;  LAPACK version 3.9.0
#> 
#> locale:
#>  [1] LC_CTYPE=C.UTF-8       LC_NUMERIC=C           LC_TIME=C.UTF-8       
#>  [4] LC_COLLATE=C.UTF-8     LC_MONETARY=C.UTF-8    LC_MESSAGES=C.UTF-8   
#>  [7] LC_PAPER=C.UTF-8       LC_NAME=C              LC_ADDRESS=C          
#> [10] LC_TELEPHONE=C         LC_MEASUREMENT=C.UTF-8 LC_IDENTIFICATION=C   
#> 
#> time zone: UTC
#> tzcode source: system (glibc)
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> other attached packages:
#>  [1] lubridate_1.9.2 forcats_1.0.0   stringr_1.5.0   dplyr_1.1.2    
#>  [5] purrr_1.0.2     readr_2.1.4     tidyr_1.3.0     tibble_3.2.1   
#>  [9] ggplot2_3.4.3   tidyverse_2.0.0 sf_1.0-14      
#> 
#> loaded via a namespace (and not attached):
#>  [1] styler_1.10.2      utf8_1.2.3         generics_0.1.3     xml2_1.3.5        
#>  [5] class_7.3-22       KernSmooth_2.23-21 stringi_1.7.12     hms_1.1.3         
#>  [9] digest_0.6.33      magrittr_2.0.3     timechange_0.2.0   evaluate_0.21     
#> [13] grid_4.3.1         fastmap_1.1.1      R.oo_1.25.0        R.cache_0.16.0    
#> [17] R.utils_2.12.2     e1071_1.7-13       DBI_1.1.3          fansi_1.0.4       
#> [21] scales_1.2.1       cli_3.6.1          rlang_1.1.1        units_0.8-3       
#> [25] R.methodsS3_1.8.2  munsell_0.5.0      reprex_2.0.2       withr_2.5.0       
#> [29] yaml_2.3.7         tools_4.3.1        tzdb_0.4.0         colorspace_2.1-0  
#> [33] curl_5.0.2         vctrs_0.6.3        R6_2.5.1           proxy_0.4-27      
#> [37] lifecycle_1.0.3    classInt_0.4-9     fs_1.6.3           pkgconfig_2.0.3   
#> [41] pillar_1.9.0       gtable_0.3.4       glue_1.6.2         Rcpp_1.0.11       
#> [45] highr_0.10         xfun_0.40          tidyselect_1.2.0   rstudioapi_0.15.0 
#> [49] knitr_1.43         farver_2.1.1       htmltools_0.5.6    rmarkdown_2.24    
#> [53] compiler_4.3.1