append = T doesn't write geom column as geoemetry

[jonathananolan]

If I'm making a new table from an sf object in R and I type

           dsn = con, 
           append = T,
           Id(schema="public", 
              table = 'object_name'))

con is the connection:

> con 
<PqConnection> postgres@localhost:5433

The geometry column is stored as text. As a result you can't then import the sf object into R again.

But if I comment out "append = T" the geometry column is stored as geometry.

          GEOS           GDAL         proj.4 GDAL_with_GEOS     USE_PROJ_H           PROJ 
      "3.11.0"        "3.5.3"        "9.1.0"         "true"         "true"        "9.1.0" 

               _                           
platform       aarch64-apple-darwin20      
arch           aarch64                     
os             darwin20                    
system         aarch64, darwin20           
status                                     
major          4                           
minor          3.1                         
year           2023                        
month          06                          
day            16                          
svn rev        84548                       
language       R                           
version.string R version 4.3.1 (2023-06-16)
nickname       Beagle Scouts      

POSTGIS="3.3.1 3786b21" [EXTENSION] PGSQL="150" GEOS="3.9.1-CAPI-1.14.2" PROJ="8.0.0" LIBXML="2.9.14" LIBJSON="0.16"

PostgreSQL 15.5 on x86_64-apple-darwin20.6.0, compiled by Apple clang version 12.0.5 (clang-1205.0.22.9), 64-bit

Is this expected behaviour? Thanks so much!

[edzer]

@etiennebr is this something you could look into? BR,

[etiennebr]

Thanks for reporting @jonathananolan. I can reproduce the issue. I'll have a look

library(sf)
#> Linking to GEOS 3.12.1, GDAL 3.8.1, PROJ 9.3.1; sf_use_s2() is TRUE
con <- DBI::dbConnect(RPostgres::Postgres())

x <- st_sf(geometry = st_sfc(st_point(1:2)))
st_write(x, con, "x", append = TRUE, temporary = TRUE)
st_write(x, con, "y", temporary = TRUE)
st_read(con, "x")
#> Warning in st_read.DBIObject(con, "x"): Could not find a simple features
#> geometry column. Will return a `data.frame`.
#>                                     geometry
#> 1 0101000000000000000000f03f0000000000000040
st_read(con, "y")
#> Simple feature collection with 1 feature and 0 fields
#> Geometry type: POINT
#> Dimension:     XY
#> Bounding box:  xmin: 1 ymin: 2 xmax: 1 ymax: 2
#> CRS:           NA
#>      geometry
#> 1 POINT (1 2)

^{Created on 2023-12-05 with reprex v2.0.2}

[etiennebr]

It seems like loading the package from source with devtools::load_all() makes the problem disappear. This will make debugging more challenging. @edzer, any idea why that could happen?

devtools::load_all("~/workspace/sf")
#> ...
#> Linking to GEOS 3.11.1, GDAL 3.6.4, PROJ 9.1.1; sf_use_s2() is TRUE
con <- DBI::dbConnect(RPostgres::Postgres(), host = "localhost")

x <- st_sf(geometry = st_sfc(st_point(1:2)))
st_write(x, con, "x", append = TRUE, temporary = TRUE)
st_read(con, "x")
#> Simple feature collection with 1 feature and 0 fields
#> Geometry type: POINT
#> Dimension:     XY
#> Bounding box:  xmin: 1 ymin: 2 xmax: 1 ymax: 2
#> CRS:           NA
#>      geometry
#> 1 POINT (1 2)

^{Created on 2023-12-06 with reprex v2.0.2}

[edzer]

@etiennebr this works for me without problems, appending simply appends. The first time in an R session I write to a PostGIS database I see

Note: method with signature ‘DBIObject#sf’ chosen for function ‘dbDataType’,
 target signature ‘PqConnection#sf’.
 "PqConnection#ANY" would also be valid

[jonathananolan]

@etiennebr this works for me without problems, appending simply appends. The first time in an R session I write to a PostGIS database I see

Note: method with signature ‘DBIObject#sf’ chosen for function ‘dbDataType’,
 target signature ‘PqConnection#sf’.
 "PqConnection#ANY" would also be valid

One thing I forgot to mention was that the table has to be new. If there is an existing table with geometry behaviour is as you expect. It can be useful to use 'append = t' for a new table when you aren't sure if the table exists yet when writing. My current workaround is to write a function that checks, and if there is no table use append = F.

Best,

Jonathan.

[etiennebr]

Thanks for sharing your workaround Jonathan since this can be useful to other users. It seems like having your version of sf could help me pinpoint the exact issue, in addition to the information you already provided. If you can, could you try to reproduce the issue (using my reprex above) with the latest sf version built from source (https://github.com/r-spatial/sf#macos).

@edzer, the Note from postgres is apparently normal. I am under the impression that I had opened a ticket about it (that I can't find), because the information is not helpful to most users, and adds confusion in my opinion.