使用 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 引擎的更多信息,请参阅如何执行 RDS for PostgreSQL 的主要版本升级。
您可以随时检查 RDS for 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 引擎的主要版本升级,则可以继续执行其他初步任务。有关更多信息,请参阅如何执行 RDS for PostgreSQL 的主要版本升级。
PostGIS 扩展版本
我们建议您安装 《Amazon RDS for PostgreSQL 版本注释》的 Amazon RDS for PostgreSQL 的扩展版本中列出的所有扩展版本,如 PostGIS。要获取发行版中可用的版本列表,请使用以下命令。
SELECT * FROM pg_available_extension_versions WHERE name='postgis';
您可以在《Amazon RDS for PostgreSQL 版本注释》的以下部分中找到版本信息:
将 PostGIS 2 升级到 PostGIS 3
从版本 3.0 开始,PostGIS 光栅功能现在是一个单独的扩展,即 postgis_raster
。此扩展有自己的安装和升级路径。这将从核心 postgis
扩展中删除光栅图像处理所需的数十个函数、数据类型和其他构件。这意味着,如果您的使用案例不需要光栅处理,则不需要安装 postgis_raster
扩展。
在以下升级示例中,第一个升级命令将光栅功能提取到 postgis_raster
扩展。然后,需要使用第二个升级命令将 postgis_raster
升级到新版本。
从 PostGIS 2 升级到 PostGIS 3
-
确定 RDS for 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)
-
确定 RDS for 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
-
运行以下查询,以验证光栅功能现在是否已包含在自己的程序包中。
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();
-
使用以下查询查看已完成的升级过程和单独打包的扩展,并验证其版本是否匹配。
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;