为何sf::read_sf无法读取PostgreSQL中的INT类型主键列？

问题描述 #

使用sf::read_sf从PostgreSQL加载空间数据表时，查询执行无报错，但始终无法获取INT类型的主键列h_id。临时把该列复制为VARCHAR类型的h_id_v后能正常读取，但这种方法并不合理。

加载数据代码 #

#### LOAD DATA ####
library(sf)
dsn <- paste("PG:dbname='[dbname]' host='[host]' port='5432' user='[user]' password='[password]'")

#load spatial data ('type = 0001' sets geometry type = point, '0003' sets polygon)
ausw_input <- read_sf(dsn, 'regr.hvv_ausw2_hst_nonull_nosingle', type = 0001)

表中相关列结构 #

h_id | INT -- primary key, not being fetched
h_id_v | VARCHAR -- copy of h_id, being fetched correctly
...
[~30 more columns, including lon/lat]

解决办法 #

1. 手动指定查询列
   不用默认读取全表，直接写SQL查询明确包含h_id，强制读取该列：

   ausw_input <- read_sf(
     dsn,
     query = "SELECT h_id, *, ST_AsText(你的几何列名) AS wkt FROM regr.hvv_ausw2_hst_nonull_nosingle"
   )
   

   记得把你的几何列名换成表中实际的空间列名称，ST_AsText用于让sf正确解析几何数据。

2. 检查权限和PostGIS扩展

   • 确认当前数据库用户对h_id列有SELECT权限，手动执行SELECT h_id FROM regr.hvv_ausw2_hst_nonull_nosingle LIMIT 1验证是否能获取数据
   • 确保PostgreSQL已安装PostGIS扩展，缺少扩展可能导致sf读取元数据时出现异常

3. 去掉type参数重试
   设置的type = 0001可能限制了sf的读取逻辑，先去掉该参数让系统自动识别几何类型：

   ausw_input <- read_sf(dsn, 'regr.hvv_ausw2_hst_nonull_nosingle')
   

4. 更新sf包版本
   旧版本sf可能存在PostgreSQL INT类型列的兼容bug，运行update.packages("sf")更新到最新版后重试

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