使用 PostGIS 扩展管理空间数据 - Amazon Aurora
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

使用 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 函数的所有权。PostGIS 的正常运行和未来升级要求这些函数保留原始所有权。有关 PostGIS 权限的更多信息,请参阅 PostgreSQL Security

使用以下函数将 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 升级到新版本。

从 PostGIS 2 升级到 PostGIS 3
  1. 确定 Aurora PostgreSQL 数据库集群 上 PostgreSQL 版本可用的 PostGIS 的默认版本。为此,请运行以下查询。

    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)
  2. 确定 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
  3. 运行以下查询,以验证光栅功能现在是否已包含在自己的程序包中。

    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();
  4. 使用以下查询查看已完成的升级过程和单独打包的扩展,并验证其版本是否匹配。

    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;