Greenplum数据库中使用PostGIS扩展

本文包含以下内容：
关于 PostGIS
Greenplum 数据库中的PostGIS 扩展
提供PostGIS能力
升级Greenplum的PostGIS扩展
迁移PostGIS 1.4到2.0
使用
PostGIS 功能和限制
1. 关于 PostGIS
关于PostGIS的介绍，本文不再赘述，请参考本公众号相关专题文章。

2. Greenplum 数据库中的PostGIS 扩展
可从Pivotal Network网站（https://network.pivotal.io ）获得Greenplum数据库PostGIS扩展。可以使用Greenplum软件包管理器（gppkg）安装它。 有关详细信息，请参见《 Greenplum数据库实用程序指南》中的gppkg。

Greenplum数据库4.3支持PostGIS扩展软件包2.0版（PostGIS 2.0.3）。
Greenplum数据库4.2.6和更高版本支持PostGIS扩展包版本1.0和2.0（PostGIS 1.4和2.0.3）
在Greenplum数据库的安装中，只能安装PostGIS扩展软件包的一个版本（1.0或2.0）。
4.2.6之前的Greenplum数据库支持PostGIS扩展软件包版本1.0（PostGIS 1.4）。
重要说明：由于引入了Pivotal Query Optimizer，用于Greenplum Database 4.3.4.x和更早版本的扩展程序包与Greenplum Database 4.3.5.0和更高版本不兼容。
另外，用于Greenplum数据库4.3.5.0和更高版本的扩展程序包与Greenplum数据库4.3.4.x和更早版本不兼容。
要将扩展包与Greenplum Database 4.3.5.0及更高版本一起使用，必须安装和使用为Greenplum Database 4.3.5.0及更高版本构建的Greenplum Database扩展包（gppkg文件和contrib模块）。 对于与Greenplum Database 4.3.4.x及更早版本一起使用的自定义模块，必须重建与Greenplum Database 4.3.5.0及更高版本一起使用的模块。

从1.4.2开始的2.0.3的主要增强和更改包括：

Support for geographic coordinates (latitude and longitude) with a GEOGRAPHY type and related functions.
Input format support for these formats: GML, KML, and JSON
Unknown SRID changed from -1 to 0
3D relationship and measurement support functions
Making spatial indexes 3D aware
KNN GiST centroid distance operator
Many deprecated functions are removed
Performance improvements
警告：PostGIS 2.0删除了许多已过时但在PostGIS 1.4中可用的功能。 使用PostGIS 1.4中已弃用的函数编写的函数和应用程序可能需要重写。 有关新功能，增强功能或已更改功能的列表，请参见PostGIS文档:
http://postgis.net/docs/manual-2.0/PostGIS_Special_Functions_Index.html#NewFunctions

Greenplum数据库不支持的PostGIS特性：

拓扑
栅格
少数用户定义的函数和集合
PostGIS长事物支持
几何和地理类型修改
3. 提供PostGIS能力
安装PostGIS扩展软件包后，可以为每个需要使用PostGIS的数据库启用PostGIS支持。 要启用支持，请在目标数据库中运行PostGIS软件包随附的启动器SQL脚本。
对于PosgGIS 1.4，启用程序脚本为postgis.sql

psql -f postgis.sql -d your_database

您的数据库现在已启用空间能力。
对于PostGIS 2.0.3，可以在目标数据库中运行两个SQL脚本postgis.sql和spatial_ref_sys.sql。
例如：

psql -d mydatabase -f
$GPHOME/share/postgresql/contrib/postgis-2.0/postgis.sql
psql -d mydatabase -f
$GPHOME/share/postgresql/contrib/postgis-2.0/spatial_ref_sys.sql

4. 升级Greenplum的PostGIS扩展
如果从PostGIS扩展软件包2.0版（pv2.0）或更高版本升级，则必须在目标数据库中运行postgis_upgrade_20_minor.sql。 本示例升级PostGIS扩展软件包并运行脚本：

gppkg -u postgis-ossv2.0.3_pv2.0.1_gpdb4.3-rhel5-x86_64.gppkg

psql -d mydatabase -f $GPHOME/share/postgresql/contrib/postgis-2.0/postgis_upgrade_20_minor.sql

5. 迁移PostGIS 1.4到2.0
要将启用PostGIS的数据库从1.4迁移到2.0，必须执行PostGIS HARD UPGRADE。 硬升级包括转储已启用PostGIS 1.4的数据库并将该数据库的数据加载到已启用PostGIS 2.0的新数据库。

有关PostGIS 手动过程的信息，请参见PostGIS文档：http://postgis.net/docs/manual-2.0/postgis_installation.html#hard_upgrade

6. 使用
以下示例SQL语句创建非OpenGIS表和几何。

CREATE TABLE geom_test ( gid int4, geom geometry,
name varchar(25) );
INSERT INTO geom_test ( gid, geom, name )
VALUES ( 1, 'POLYGON((0 0 0,0 5 0,5 5 0,5 0 0,0 0 0))', '3D Square');
INSERT INTO geom_test ( gid, geom, name )
VALUES ( 2, 'LINESTRING(1 1 1,5 5 5,7 7 5)', '3D Line' );
INSERT INTO geom_test ( gid, geom, name )
VALUES ( 3, 'MULTIPOINT(3 4,8 9)', '2D Aggregate Point' );
SELECT * from geom_test WHERE geom &&
Box3D(ST_GeomFromEWKT('LINESTRING(2 2 0, 3 3 0)'));


下面的示例SQL语句创建一个表，并向表中添加一个几何列，该列的SRID整数值引用了SPATIAL_REF_SYS表中的一个条目。 INSERT语句将添加到表的地理位置。

CREATE TABLE geotest (id INT4, name VARCHAR(32) );
SELECT AddGeometryColumn('geotest','geopoint', 4326,'POINT',2);
INSERT INTO geotest (id, name, geopoint)
VALUES (1, 'Olympia', ST_GeometryFromText('POINT(-122.90 46.97)', 4326));
INSERT INTO geotest (id, name, geopoint)|
VALUES (2, 'Renton', ST_GeometryFromText('POINT(-122.22 47.50)', 4326));
SELECT name,ST_AsText(geopoint) FROM geotest;

空间索引
PostgreSQL支持GiST空间索引。
GiST方案甚至可以在大型对象上提供索引。
它使用有损索引编制系统，其中较小的对象充当索引中较大对象的代理。
在PostGIS索引系统中，所有对象都将其边界框用作索引中的代理。

建立空间索引
可以按以下方式构建GiST索引：

CREATE INDEX indexname
ON tablename
USING GIST ( geometryfield );

7. PostGIS 功能和限制
Greenplum数据库PostGIS扩展不支持以下功能：

拓扑结构
栅格
支持的数据类型：

box2d
box3d
geometry
geography
spheroid
支持的索引

Greenplum数据库PostGIS扩展支持GiST（通用搜索树）索引。

PostGIS扩展限制
本节列出了Greenplum数据库PostGIS扩展限制，包括用户定义函数（UDF），数据类型和集合。

Greenplum数据库不支持与PostGIS拓扑或栅格功能相关的数据类型和功能，例如TopoGeometry和ST_AsRaster。
不支持ST_Estimated_Extent函数。该功能需要表列统计信息来显示Greenplum数据库无法提供的用户定义数据类型。
不支持ST_GeomFromGeoJSON函数。该功能需要JSON支持。 Greenplum数据库不支持JSON。
Greenplum数据库不支持以下PostGIS聚合：

ST_MemCollect
ST_MakeLine
在具有多个细分的Greenplum数据库上，如果重复调用多次，则聚合可能会返回不同的答案。

Greenplum数据库不支持PostGIS长事务。

PostGIS依赖触发器和PostGIS表public.authorization_table来提供长期事务支持。当PostGIS尝试获取长交易的锁时，Greenplum数据库会报告错误，指出该函数无法访问关系authorization_table。

Greenplum数据库不支持用户定义类型的类型修饰符。
解决方法是将AddGeometryColumn函数用于PostGIS几何对象。例如，无法使用以下SQL命令创建具有PostGIS几何形状的表

CREATE TABLE geometries(id INTEGER, geom geometry(LINESTRING));

使用AddGeometryColumn函数将PostGIS几何添加到表中。 例如，以下这些SQL语句创建一个表并将PostGIS几何添加到表中：

CREATE TABLE geometries(id INTEGER);
SELECT AddGeometryColumn('public', 'geometries', 'geom', 0, 'LINESTRING', 2);


本文来源：https://blog.csdn.net/eqmaster/article/details/108763432