在R中使用PostgreSQL

在以往的科研中，我们通常是使用文件来进行数据的传递和分析，比如常用的 CSV 、 Shapefile 等。面对这些文件，我们需要在硬盘上以文件和目录的方式进行组织。有一次，在处理上下车点数据时，由于文件数量太大以至于根本无法传给师弟，Windows 资源管理器也变得很卡，甚至用 zip 命令压缩都会遇到爆内存的情况。后来我发现了一种更好地数据管理方式——数据库。

相比于使用文件保存数据，使用数据库保存数据更有利于保持对数据之间关系的认知。尤其在大数据时代，数据量的增长使得以文件为单位的数据分析流程容易遇到性能瓶颈，而数据库有索引等一系列技术保证性能。而且 PostgreSQL 的插件 PostGIS 提供了强大的空间处理支撑。而且随着 Docker 的发展，在 Windows 上安装 PostgreSQL 也已经非常方便了。

本文主要介绍一下如何在 R 中链接 PostgreSQL 数据库进行数据的处理和分析。安装过程就省略了，相关资料比较多。

# 依赖包

既然用上了关系数据库，那我们在 R 中就使用 tidyverse 等一系列包进行数据处理，暂时抛弃 R base 系列的函数。

- `tidyverse`
- `dbplyr`
- `RPostgres`
- `sf`
- `tmap`

后面两个包是为了提供空间数据支持的。

# 数据库操作

## 连接

首先要通过 `DBI::dbConnect()` 和 `RPostgres::Postgres()` 函数创建数据库连接

```R
con <- DBI::dbConnect(RPostgres::Postgres(),
    host = "localhost",
    user = "postgres",
    password = "postgres",
    dbname = "postgres"
)
```

这样，这个 `con` 对象就建立了与数据库的连接，无需使用连接字符串。如果不需要再使用，则需要手动关闭连接，运行语句 `DBI::dbDisconnect(con)` 即可。

## 表数据

### 读取

如果读取非空间数据（表数据），我们需要使用 `dplyr::tbl()` 函数，指定一个表进行读取，例如

```R
lsoa_cid <- tbl(con, "lsoa_centroid")
head(lsoa_cid)
```

```plaintext
# Source:   SQL [6 x 5]
# Database: postgres  [postgres@localhost:5432/postgres]
       x      y objectid lsoa01cd  lsoa01nm   
   <int>  <int>    <int> <chr>     <chr>  
1 532182 181785        1 E01000001 City of London 001A  
2 532434 181810        2 E01000002 City of London 001B  
3 532199 182064        3 E01000003 City of London 001C  
4 532107 181183        4 E01000004 City of London 001D  
5 533678 181182        5 E01000005 City of London 001E  
6 544914 184323        6 E01000006 Barking and Dagenham 016A
```

读取完成后，这个数据就和普通的 `tibble` 表无异。更准确的说，在使用 `collect()` 后完全就是一个 `tibble` 了。如果不使用 `as_tibble()` ，后续的 `select` `filter` `arrange` 等操作都会被构造成相应的 SQL 语句提交给数据库进行执行。这样在处理大数据的时候，就可以利用数据库中的索引进行查询。此外，如果不使用 `collect()` ，`nrow()` 和 `tail()` 函数是无法使用的，因为这两个操作只有在得到查询结果之后才能拿到。所以，在确定了查询过程之后，使用 `collect()` 函数获取数据。

### 写入

将一个 `data.frame` 或者 `tibble` 写入数据库的函数是 `copy_to()`，例如

```R
copy_to(con, lsoa_cid, 
    name = DBI::Id(schema = "public", table = "new_table"),
    temporary = FALSE,
    overwrite = TRUE
)
```

如果目标表的 schema 是 public ，其实 `name` 参数可以直接填表的名字。但是如果不是，就需要用 `DBI::Id()` 函数来指定 schema。

此外，还可以使用 **DBI** 包提供的 `dbWriteTable()` 函数，更方便

```R
DBI::dbWriteTable(con, DBI::Id(schema = "public", table = "new_talbe"), lsoa_cid)
```

可以指定 `overwrite = TRUE` 以覆盖原表，`append = TRUE` 以追加数据，

## 空间数据

### 读取

R 中确实有一个 `rpostgis` 包，但是我们直接使用 `sf` 包中的 `st_read()` 函数更方便。依然是使用上面的 `con` 对象，我们可以直接以下面的方式读取并进行预处理

```R
country <- st_read(con, "country") %>%
    st_simplify(dTolerance = 1000)
tm_shape(country) + tm_polygons()
```

![image.png](/media/5bbb8903-6604-4046-a276-5fc5f1fcab42_image.png)

该语句读取了 `country` 表，并进行了形状简化，然后使用 `tmap` 包制图。

如果要读取某个 schema 下面的空间数据，仍然是使用 `Id()` 函数指定 schema 和 table 名称。

### 写入

既然有 `st_read()` 那肯定有 `st_write()` 用于保存数据。用法和之前的 `copy_to()` 是差不多的。

```R
st_write(country, con, "country_buc")
```

相比于写到 ESRI Shapefile 格式的文件中，这种方法可以避免字段名被截断，避免了字符串编码问题，并且在 ArcGIS 等软件中可以直接通过添加的数据库连接打开。这样就没有问题了。

# 问题

**SCRAM authentication requires libpq version 10 or above**

这个问题一般是 PostgreSQL 版本过高导致的。解决的办法是指定用户登录方式为 md5 登录，需要修改两个文件： `postgresql.conf` 和 `pg_hba.conf` ，将 `scram-sha-256` 改为 `md5` 从而兼容 **RPostgreSQL** 包，然后再重置一下用户的密码即可。

将上述两个文件分别做以下修改

```diff
# postgresql.conf
- password_encryption = scram-sha-256
+ password_encryption = md5

# pg_hba.conf
- local    all    all    scram-sha-256
+ local    all    all    md5
```

然后重启 PostgreSQL 。重启完成后，使用原用户名密码登录数据库

```bash
psql -U postgres
```

然后修改密码，注意不一定真的修改，可以重新设置一遍相同的密码

```sql
ALTER USER postgres WITH password 'postgres';
```

然后就可以使用 **RPostgreSQL** 包连接到数据库了。