Error uploading spatial data to existing RPostgreSql table using sf::st_write() in R

Viewed 31

I created the following RPostgreSql table with a primary key and foreign key:

create table
us_hurricanes (
    id         serial                        primary key
  , geo_id     int                           references geo_id_master_list(geo_id)
  , storm_id   text                          not null
  , timestamp  timestamp with time zone      not null
  , radii      int                           not null
  , geom       geometry(multipolygon, 2163)  not null
);

It currently contains some observations. In R, I am trying to use the sf::st_write() function to upload additional entries to the table:

db <- RPostgreSQL::dbConnect(
    dbDriver("PostgreSQL"),
    dbname = NAME,
    host = HOST,
    port = PORT,
    user = USER,
    password = PASS)

sf::st_write(
    new_data, 
    dsn = db, 
    layer = "us_hurricanes",
    append = TRUE)

where new_data is a dataframe that contains all of the same fields that are in the us_hurricanes table, and the geom column is an sfc_POLYGON object with CRS of 2163.

Simple feature collection with 1 feature and 5 fields
Geometry type: POLYGON
Dimension:     XY
Bounding box:  xmin: 2628189 ymin: -2026100 xmax: 3092784 ymax: -1531227
Projected CRS: US National Atlas Equal Area
    id geo_id storm_id           timestamp radii                           geom
1 3210   3210 al072022 2022-09-21 12:00:00    34 POLYGON ((2783123 -1544385,...

I get the following non-descriptive error when I run the st_write() function above:

Error in nchar(sm[1L], type = "w") : invalid multibyte string, element 1

A few observations:

  • If I use a new table name as the layer value in the st_write() function, no error occurs. Instead, it creates a new table in my DB, but the SRID of the table is 0.
  • If I don't use the append = TRUE argument, the table is successfully overwritten.
  • If I write the dataframe to a shapefile, use shp2pgsql to create a .sql file, and run that file via the command line, the new entries are successfully uploaded to the existing table.

How can I navigate this issue? Is there a better alternative in R to upload new spatial entries to an existing Postgres table?

0 Answers
Related