使用R中sf::st_write()向现有RPostgreSQL表上传空间数据报错

我创建了一个带主键和外键的PostgreSQL空间表：

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
);

该表已存在部分数据，我尝试在R中使用sf::st_write()函数向表中追加新数据：

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)

其中new_data是与us_hurricanes表字段完全匹配的要素集，geom列是CRS为2163的sfc_POLYGON对象：

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,...

执行上述st_write()时出现非描述性错误：

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

相关观察 #

• 使用新表名作为layer值时，无报错，会在数据库中创建新表，但表的SRID为0。
• 不使用append = TRUE参数时，可成功覆盖原表。
• 将数据写入shapefile，用shp2pgsql生成.sql文件并通过命令行执行，可成功追加数据至现有表。

问题 #

1. 如何解决st_write()追加数据时的错误？
2. 在R中向现有Postgres表上传新空间数据是否有更好的替代方法？

针对st_write()错误的修复方案 #

1. 统一几何类型匹配 #

目标表geom字段定义为geometry(multipolygon, 2163)，但new_data的几何类型是POLYGON，类型不匹配是核心问题之一。将new_data的几何列转换为MULTIPOLYGON：

new_data$geom <- sf::st_cast(new_data$geom, "MULTIPOLYGON")

转换后再执行st_write()追加操作。

2. 显式指定SRID并禁用几何类型检查 #

在st_write()中添加参数强制指定SRID，并关闭类型检查（适用于确认数据CRS正确的场景）：

sf::st_write(
    new_data, 
    dsn = db, 
    layer = "us_hurricanes",
    append = TRUE,
    layer_options = "SRID=2163",
    check_type = FALSE)

3. 编码问题排查 #

错误提示涉及多字节字符串，检查new_data中字符型字段（如storm_id）的编码，确保与数据库编码一致（通常为UTF-8）：

# 检查字符字段编码
Encoding(new_data$storm_id)
# 强制转换为UTF-8
new_data$storm_id <- iconv(new_data$storm_id, to = "UTF-8")

R中PostGIS数据写入的替代方法 #

1. 使用DBI+sf底层接口手动插入 #

通过DBI::dbWriteTable()结合sf::st_as_text()构造插入语句，更灵活可控（注意自增主键id无需手动传入）：

# 将几何列转换为WKT格式
new_data_wkt <- sf::st_set_geometry(new_data, NULL)
new_data_wkt$geom <- sf::st_as_text(new_data$geom)

# 构造插入语句
insert_query <- paste0(
    "INSERT INTO us_hurricanes (geo_id, storm_id, timestamp, radii, geom) ",
    "VALUES ($1, $2, $3, $4, ST_SetSRID(ST_GeomFromText($5), 2163))"
)

# 批量插入
DBI::dbExecute(db, insert_query, params = as.list(new_data_wkt))

2. 使用postgisr包简化操作 #

postgisr是专门针对PostGIS的R工具包，可直接实现空间数据追加：

# 安装并加载包
install.packages("postgisr")
library(postgisr)

# 追加数据
pg_insert(db, "us_hurricanes", new_data)

3. 替换为odbc驱动连接数据库 #

odbc驱动对PostgreSQL的兼容性优于RPostgreSQL，配合sf使用更稳定：

# 用odbc连接数据库
db <- DBI::dbConnect(odbc::odbc(),
                     Driver = "PostgreSQL",
                     Database = NAME,
                     Server = HOST,
                     Port = PORT,
                     UID = USER,
                     PWD = PASS)

# 追加数据
sf::st_write(new_data, db, "us_hurricanes", append = TRUE)

内容的提问来源于stack exchange，提问作者Julia Wagenfehr