To enable PostGIS support, install the Greenplum PostGIS extension package into the Greenplum Database system, and then use the CREATE EXTENSION command to enable PostGIS support for an individual database.

gppkg -i postgis-2.5.4+pivotal.2.build.1-gp6-rhel7-x86_64.gppkg

gpstop -ra

export GDAL_DATA=$GPHOME/share/gdal
export POSTGIS_ENABLE_OUTDB_RASTERS=0
export POSTGIS_GDAL_ENABLED_DRIVERS=DISABLE_ALL

PostGIS uses GDAL raster drivers when processing raster data with commands such as ST_AsJPEG(). As the default, PostGIS disables all raster drivers. You enable raster drivers by setting the value of the POSTGIS_GDAL_ENABLED_DRIVERS environment variable in the greenplum_path.sh file on all Greenplum Database hosts.

SET postgis.gdal_enabled_drivers TO 'GTiff PNG JPEG';

SET postgis.gdal_enabled_drivers = default;

To see the list of supported GDAL raster drivers for a Greenplum Database system, run the raster2pgsql utility with the -G option on the Greenplum Database master.

raster2pgsql -G 

The command lists the driver long format name. The GDAL Raster table at https://gdal.org/drivers/raster/index.html lists the long format names and the corresponding codes that you specify as the value of the environment variable. For example, the code for the long name Portable Network Graphics is PNG. This example export line enables four GDAL raster drivers.

export POSTGIS_GDAL_ENABLED_DRIVERS="GTiff PNG JPEG GIF"

The gpstop -r command restarts the Greenplum Database system to use the updated settings in the greenplum_path.sh file.

After you have updated the greenplum_path.sh file on all hosts, and have restarted the Greenplum Database system, you can display the enabled raster drivers with the ST_GDALDrivers() function. This SELECT command lists the enabled raster drivers.

SELECT short_name, long_name FROM ST_GDALDrivers();

After installing PostGIS, the default setting POSTGIS_ENABLE_OUTDB_RASTERS=0 in the greenplum_path.sh file disables support for to out-of-database rasters. To enable this feature, you can set the value to true (a non-zero value) on all hosts and restart the Greenplum Database system.

You can also enable or disable this feature for a Greenplum Database session. For example, this SET command enables the feature for the current session.

SET postgis.enable_outdb_rasters = true;				

You use the DROP EXTENSION command to remove support for the PostGIS extension and the extensions that are used with PostGIS.

Removing PostGIS support from a database does not remove these PostGIS Raster environment variables from the greenplum_path.sh file: GDAL_DATA, POSTGIS_ENABLE_OUTDB_RASTERS, POSTGIS_GDAL_ENABLED_DRIVERS. The environment variables are removed when you uninstall the PostGIS extension package.

gppkg -r postgis-2.5.4+pivotal.2

export GDAL_DATA=$GPHOME/share/gdal
export POSTGIS_ENABLE_OUTDB_RASTERS=0
export POSTGIS_GDAL_ENABLED_DRIVERS=DISABLE_ALL

gpstop -ra

# CREATE TABLE test(a int, b geometry) DISTRIBUTED RANDOMLY;

# \d test
 Table "public.test"
 Column |   Type   | Modifiers
--------+----------+-----------
 a      | integer  |
 b      | geometry |
Distributed randomly

# \d test
  Table "public.test"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
Distributed randomly

PostgreSQL provides support for GiST spatial indexing. The GiST scheme offers indexing even on large objects. It uses a system of lossy indexing in which smaller objects act as proxies for larger ones in the index. In the PostGIS indexing system, all objects use their bounding boxes as proxies in the index.

Greenplum PostGIS extension supports these PostGIS data types:

• box2d
• box3d
• geometry
• geography

For a list of PostGIS data types, operators, and functions, see the PostGIS reference documentation.

Greenplum PostGIS supports these PostGIS Raster data types.

• geomval
• addbandarg
• rastbandarg
• raster
• reclassarg
• summarystats
• unionarg

For information about PostGIS Raster data Management, queries, and applications, see https://postgis.net/docs/manual-2.5/using_raster_dataman.html.

For a list of PostGIS Raster data types, operators, and functions, see the PostGIS Raster reference documentation.

Greenplum PostGIS extension supports the GiST (Generalized Search Tree) index.

This section lists the Greenplum PostGIS extension limitations for user-defined functions (UDFs), data types, and aggregates.

• Data types and functions related to PostGIS topology functionality, such as TopoGeometry, are not supported by Greenplum Database.
• These PostGIS aggregates are not supported by Greenplum Database:
  • ST_Collect
  • ST_MakeLine

  On a Greenplum Database with multiple segments, the aggregate might return different answers if it is called several times repeatedly.

• Greenplum Database does not support PostGIS long transactions.

  PostGIS relies on triggers and the PostGIS table public.authorization_table for long transaction support. When PostGIS attempts to acquire locks for long transactions, Greenplum Database reports errors citing that the function cannot access the relation, authorization_table.

• Greenplum Database does not support type modifiers for user defined types.
  The work around is to use the AddGeometryColumn function for PostGIS geometry. For example, a table with PostGIS geometry cannot be created with the following SQL command:

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

  Use the AddGeometryColumn function to add PostGIS geometry to a table. For example, these following SQL statements create a table and add PostGIS geometry to the table:

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

• The _postgis_index_extent function is not supported on Greenplum Database 6 due to its dependence on spatial index operations.
• The <-> operator (geometry <-> geometry) returns the centroid/centroid distance for Greenplum Database 6.
• The following PostGIS functions are not currently supported because Greenplum PostGIS is compiled with Geos 3.4.2:
  • ST_Subdivide
  • ​ST_ClipByBox2D
  • ST_VoronoiLines
  • ST_VoronoiPolygons
• The TIGER geocoder extension is supported. However, upgrading the TIGER geocoder extension is not supported.
• The standardize_address() function uses lex, gaz or rules tables as parameters. If you are using tables apart from us_lex, us_gaz or us_rules, you should create them with the distribution policy DISTRIBUTED REPLICATED to work for Greenplum.