使用 PostGIS 扩展管理空间数据

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

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

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

步骤 1：创建用户（角色）来管理 PostGIS 扩展

首先，以具有 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 扩展

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：移交扩展架构的所有权

使用 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 表的所有权

使用以下函数将 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：测试扩展

为避免需要指定架构名称，请使用以下命令将 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 地理编码器。

使用以下 SELECT 语句测试对 topology 架构的访问。这将调用 createtopology 函数，以使用指定的空间参考标识符（26986）和原定设置容差（0.5）注册新的拓扑对象（my_new_topo）。要了解更多信息，请参阅 PostGIS 文档中的 CreateTopology。

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

步骤 6：升级 PostGIS 扩展

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

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

您可以随时检查 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 引擎的主要版本升级，则可以继续执行其他初步任务。有关更多信息，请参阅测试将生产数据库集群升级到新的主要版本。

PostGIS 扩展版本

我们建议您安装《Aurora PostgreSQL 版本注释》的 Aurora PostgreSQL 兼容版的扩展版本 中列出的所有扩展版本，如 PostGIS。要获取发行版中可用的版本列表，请使用以下命令。

SELECT * FROM pg_available_extension_versions WHERE name='postgis';

您可以在《Aurora PostgreSQL 的版本注释》的以下部分中找到版本信息：

将 PostGIS 2 升级到 PostGIS 3

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

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

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

DROP EXTENSION postgis_raster;