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
layervalue in thest_write()function, no error occurs. Instead, it creates a new table in my DB, but theSRIDof the table is 0. - If I don't use the
append = TRUEargument, the table is successfully overwritten. - If I write the dataframe to a shapefile, use
shp2pgsqlto create a.sqlfile, 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?