# 使用 PostGIS 扩展管理空间数据
<a name="Appendix.PostgreSQL.CommonDBATasks.PostGIS"></a>

PostGIS 是一个 PostgreSQL 扩展，用于存储和管理空间信息。要了解有关 PostGIS 的更多信息，请参阅 [PostGIS.net](https://postgis.net/)。

从版本 10.5 开始，PostgreSQL 支持 PostGIS 用于处理地图框矢量平铺数据的 libprotobuf 1.3.0 库。

设置 PostGIS 扩展需要 `rds_superuser` 权限。我们建议您创建一个用户（角色），以管理 PostGIS 和您的空间数据。PostGIS 扩展及其相关组件为 PostgreSQL 添加了数千个函数。如果这对您的使用案例有意义，请考虑在自己的架构中创建 PostGIS 扩展。以下示例说明了如何在其各自的数据库中安装扩展，但这并不是必需的。

**Topics**
+ [步骤 1：创建用户（角色）来管理 PostGIS 扩展](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Connect)
+ [步骤 2：加载 PostGIS 扩展](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.LoadExtensions)
+ [步骤 3：移交扩展架构的所有权](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferOwnership)
+ [步骤 4：移交 PostGIS 表的所有权](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferObjects)
+ [步骤 5：测试扩展](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Test)
+ [步骤 6：升级 PostGIS 扩展](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Update)
+ [PostGIS 扩展版本](#CHAP_PostgreSQL.Extensions.PostGIS)
+ [将 PostGIS 2 升级到 PostGIS 3](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3)

## 步骤 1：创建用户（角色）来管理 PostGIS 扩展
<a name="Appendix.PostgreSQL.CommonDBATasks.PostGIS.Connect"></a>

首先，以具有 `rds_superuser` 权限的用户身份连接到 RDS for PostgreSQL 数据库实例。如果您在设置实例时保留原定设置名称，则以 `postgres` 进行连接。

```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```

创建单独的角色（用户）来管理 PostGIS 扩展。

```
postgres=>  CREATE ROLE gis_admin LOGIN PASSWORD 'change_me';
CREATE ROLE
```

向此角色授予 `rds_superuser` 权限，以允许角色安装扩展。

```
postgres=> GRANT rds_superuser TO gis_admin;
GRANT
```

创建一个要用于 PostGIS 构件的数据库。此为可选步骤。或者，您可以在用户数据库中为 PostGIS 扩展创建架构，但这也不是必需的。

```
postgres=> CREATE DATABASE lab_gis;
CREATE DATABASE
```

为 `gis_admin` 提供 `lab_gis` 数据库的所有权限。

```
postgres=> GRANT ALL PRIVILEGES ON DATABASE lab_gis TO gis_admin;
GRANT
```

退出会话并以 `gis_admin` 身份重新连接 RDS for PostgreSQL 数据库实例。

```
postgres=> psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=gis_admin --password --dbname=lab_gis
Password for user gis_admin:...
lab_gis=>
```

按照后续步骤中的详细说明，继续设置扩展。

## 步骤 2：加载 PostGIS 扩展
<a name="Appendix.PostgreSQL.CommonDBATasks.PostGIS.LoadExtensions"></a>

PostGIS 扩展模块包括多个相关的扩展，它们协同工作以提供地理空间功能。您可能不需要在此步骤中创建的所有扩展，具体取决于您的使用案例。

使用 `CREATE EXTENSION` 语句加载 PostGIS 扩展。

```
CREATE EXTENSION postgis;
CREATE EXTENSION
CREATE EXTENSION postgis_raster;
CREATE EXTENSION
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
CREATE EXTENSION postgis_topology;
CREATE EXTENSION
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION
```

您可以通过运行以下示例中显示的 SQL 查询来验证结果，该查询列出了扩展及其所有者。

```
SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
  FROM pg_catalog.pg_namespace n
  WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
  ORDER BY 1;
List of schemas
     Name     |   Owner
--------------+-----------
 public       | postgres
 tiger        | rdsadmin
 tiger_data   | rdsadmin
 topology     | rdsadmin
(4 rows)
```

## 步骤 3：移交扩展架构的所有权
<a name="Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferOwnership"></a>

使用 ALTER SCHEMA 语句将架构的所有权移交给 `gis_admin` 角色。

```
ALTER SCHEMA tiger OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA tiger_data OWNER TO gis_admin; 
ALTER SCHEMA
ALTER SCHEMA topology OWNER TO gis_admin;
ALTER SCHEMA
```

您可以通过运行以下 SQL 查询来确认所有权变更。或者，您也可以从 psql 命令行使用 `\dn` 元命令。

```
SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
  FROM pg_catalog.pg_namespace n
  WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
  ORDER BY 1;

       List of schemas
     Name     |     Owner
--------------+---------------
 public       | postgres
 tiger        | gis_admin
 tiger_data   | gis_admin
 topology     | gis_admin
(4 rows)
```

## 步骤 4：移交 PostGIS 表的所有权
<a name="Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferObjects"></a>

**注意**  
请勿更改 PostGIS 函数的所有权。PostGIS 的正常运行和未来升级要求这些函数保留原始所有权。有关 PostGIS 权限的更多信息，请参阅 [PostgreSQL Security](https://postgis.net/workshops/postgis-intro/security.html)。

使用以下函数将 PostGIS 表的所有权移交给 `gis_admin` 角色。从 psql 提示符处运行以下语句以创建此函数。

```
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
CREATE FUNCTION
```

接下来，运行以下查询以运行 `exec` 函数，该函数进而将运行语句并更改权限。

```
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO gis_admin;')
  FROM (
    SELECT nspname, relname
    FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
    WHERE nspname in ('tiger','topology') AND
    relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;
```

## 步骤 5：测试扩展
<a name="Appendix.PostgreSQL.CommonDBATasks.PostGIS.Test"></a>

为避免需要指定架构名称，请使用以下命令将 `tiger` 架构添加到搜索路径中。

```
SET search_path=public,tiger;
SET
```

使用以下 SELECT 语句测试 `tiger` 架构。

```
SELECT address, streetname, streettypeabbrev, zip
 FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
address | streetname | streettypeabbrev |  zip
---------+------------+------------------+-------
       1 | Devonshire | Pl               | 02109
(1 row)
```

要了解有关此扩展的更多信息，请参阅 PostGIS 文档中的 [Tiger 地理编码器](https://postgis.net/docs/Extras.html#Tiger_Geocoder)。

使用以下 `SELECT` 语句测试对 `topology` 架构的访问。这将调用 `createtopology` 函数，以使用指定的空间参考标识符（26986）和原定设置容差（0.5）注册新的拓扑对象（my\$1new\$1topo）。要了解更多信息，请参阅 PostGIS 文档中的 [CreateTopology](https://postgis.net/docs/CreateTopology.html)。

```
SELECT topology.createtopology('my_new_topo',26986,0.5);
 createtopology
----------------
              1
(1 row)
```

## 步骤 6：升级 PostGIS 扩展
<a name="Appendix.PostgreSQL.CommonDBATasks.PostGIS.Update"></a>

PostgreSQL 的每个新发行版都支持与该发行版兼容的一个或多个 PostGIS 扩展版本。将 PostgreSQL 引擎升级到新版本不会自动升级 PostGIS 扩展。在升级 PostgreSQL 引擎之前，通常需要将 PostGIS 升级到当前 PostgreSQL 版本的最新可用版本。有关更多信息，请参阅 [PostGIS 扩展版本](#CHAP_PostgreSQL.Extensions.PostGIS)。

PostgreSQL 引擎升级后，您可以再次将 PostGIS 扩展升级到新升级的 PostgreSQL 引擎版本所支持的版本。有关升级 PostgreSQL 引擎的更多信息，请参阅。[测试将生产数据库集群升级到新的主要版本](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary)

您可以随时检查 Aurora PostgreSQL 数据库集群上可用的 PostGIS 扩展版本更新。为此，请运行以下命令。PostGIS 2.5.0 及更高版本可以使用此功能。

```
SELECT postGIS_extensions_upgrade();
```

如果您的应用程序不支持最新的 PostGIS 版本，您可以安装主要版本中提供的旧版本 PostGIS，如下所示。

```
CREATE EXTENSION postgis VERSION "2.5.5";
```

如果要从旧版本升级到特定 PostGIS 版本，还可以使用以下命令。

```
ALTER EXTENSION postgis UPDATE TO "2.5.5";
```

根据要从中进行升级的版本，您可能需要再次使用此函数。第一次运行该函数的结果决定是否需要额外的升级功能。例如，从 PostGIS 2 升级到 PostGIS 3 就是这种情况。有关更多信息，请参阅 [将 PostGIS 2 升级到 PostGIS 3](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3)。

如果您升级此扩展是为了准备进行 PostgreSQL 引擎的主要版本升级，则可以继续执行其他初步任务。有关更多信息，请参阅。[测试将生产数据库集群升级到新的主要版本](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary)

## PostGIS 扩展版本
<a name="CHAP_PostgreSQL.Extensions.PostGIS"></a>

我们建议您安装《Aurora PostgreSQL 发布说明》**的 [Aurora PostgreSQL 兼容版的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) 中列出的所有扩展版本，如 PostGIS。要获取发行版中可用的版本列表，请使用以下命令。

```
SELECT * FROM pg_available_extension_versions WHERE name='postgis';
```

您可以在《Aurora PostgreSQL 发布说明》**的以下部分中找到版本信息：
+ [Aurora PostgreSQL 14 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.14)
+ [Aurora PostgreSQL 兼容版 13 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.13)
+ [Aurora PostgreSQL 兼容版 12 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.12)
+ [Aurora PostgreSQL 兼容版 11 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.11)
+ [Aurora PostgreSQL 兼容版 10 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.10)
+ [Aurora PostgreSQL 兼容版 9.6 的扩展版本](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.96)

## 将 PostGIS 2 升级到 PostGIS 3
<a name="PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3"></a>

从版本 3.0 开始，PostGIS 光栅功能现在是一个单独的扩展，即 `postgis_raster`。此扩展有自己的安装和升级路径。这将从核心 `postgis` 扩展中删除光栅图像处理所需的数十个函数、数据类型和其他构件。这意味着，如果您的使用案例不需要光栅处理，则不需要安装 `postgis_raster` 扩展。

在以下升级示例中，第一个升级命令将光栅功能提取到 `postgis_raster` 扩展。然后，需要使用第二个升级命令将 `postgis_raster` 升级到新版本。

**从 PostGIS 2 升级到 PostGIS 3**

1. 确定 Aurora PostgreSQL 数据库集群 为此，请运行以下查询。

   ```
   SELECT * FROM pg_available_extensions
       WHERE default_version > installed_version;
     name   | default_version | installed_version |                          comment
   ---------+-----------------+-------------------+------------------------------------------------------------
    postgis | 3.1.4           | 2.3.7             | PostGIS geometry and geography spatial types and functions
   (1 row)
   ```

1. 确定 Aurora PostgreSQL 数据库集群的写入器实例上每个数据库中安装的 PostGIS 版本。换句话说，按如下方式查询每个用户数据库。

   ```
   SELECT
       e.extname AS "Name",
       e.extversion AS "Version",
       n.nspname AS "Schema",
       c.description AS "Description"
   FROM
       pg_catalog.pg_extension e
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
       LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
       AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
   WHERE
       e.extname LIKE '%postgis%'
   ORDER BY
       1;
     Name   | Version | Schema |                             Description
   ---------+---------+--------+---------------------------------------------------------------------
    postgis | 2.3.7   | public | PostGIS geometry, geography, and raster spatial types and functions
   (1 row)
   ```

   原定设置版本（PostGIS 3.1.4）和已安装版本（PostGIS 2.3.7）之间的不匹配意味着您需要升级 PostGIS 扩展。

   ```
   ALTER EXTENSION postgis UPDATE;
   ALTER EXTENSION
   WARNING: unpackaging raster
   WARNING: PostGIS Raster functionality has been unpackaged
   ```

1. 运行以下查询，以验证光栅功能现在是否已包含在自己的程序包中。

   ```
   SELECT
       probin,
       count(*)
   FROM
       pg_proc
   WHERE
       probin LIKE '%postgis%'
   GROUP BY
       probin;
             probin          | count
   --------------------------+-------
    $libdir/rtpostgis-2.3    | 107
    $libdir/postgis-3        | 487
   (2 rows)
   ```

   输出将显示版本之间仍然存在差异。PostGIS 函数为版本 3（postgis-3），而光栅函数（rtpostgis）为第 2 版（rtpostgis-2.3）。要完成升级，请再次运行升级命令，如下所示。

   ```
   postgres=> SELECT postgis_extensions_upgrade();
   ```

   您可以放心地忽略警告消息。再次运行以下查询，以验证升级已完成。当 PostGIS 和所有相关扩展未标记为需要升级时，升级即告完成。

   ```
   SELECT postgis_full_version();
   ```

1. 使用以下查询查看已完成的升级过程和单独打包的扩展，并验证其版本是否匹配。

   ```
   SELECT
       e.extname AS "Name",
       e.extversion AS "Version",
       n.nspname AS "Schema",
       c.description AS "Description"
   FROM
       pg_catalog.pg_extension e
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
       LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
           AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
   WHERE
       e.extname LIKE '%postgis%'
   ORDER BY
       1;
         Name      | Version | Schema |                             Description
   ----------------+---------+--------+---------------------------------------------------------------------
    postgis        | 3.1.5   | public | PostGIS geometry, geography, and raster spatial types and functions
    postgis_raster | 3.1.5   | public | PostGIS raster types and functions
   (2 rows)
   ```

   输出显示，PostGIS 2 扩展已升级到 PostGIS 3，并且 `postgis` 和现在独立的 `postgis_raster` 扩展都为版本 3.1.5。

升级完成后，如果您不打算使用光栅功能，则可以按如下方式删除此扩展。

```
DROP EXTENSION postgis_raster;
```