About PostGIS

PostGIS is a spatial database extension for PostgreSQL that allows GIS (Geographic
Information Systems) objects to be stored in the database. The Greenplum Database PostGIS
extension includes support for GiST-based R-Tree spatial indexes and functions for analysis
and processing of GIS objects.

Greenplum PostGIS Extension

The Greenplum Database PostGIS extension package is available
from Pivotal
Network. You can install the package using the Greenplum Package Manager
(gppkg). For details, see gppkg in the Greenplum
Database Utility Guide.

Greenplum Database supports the PostGIS extension with these component versions.

PostGIS 2.1.5

Proj 4.8.0

Geos 3.4.2

GDAL 1.11.1

Json 0.12

Expat 2.1.0

For the information about supported extension packages and software
versions see the Greenplum Database Release Notes.

Major enhancements and changes in PostGIS 2.1.5 from 2.0.3 include new PostGIS Raster
functions. For a list of new and enhanced functions in PostGIS 2.1, see the PostGIS
documentation PostGIS Functions new or enhanced in 2.1.

Enabling and Removing PostGIS Support

The Greenplum Database PostGIS extension contains the postgis_manager.sh
script that installs or removes both the PostGIS and PostGIS Raster features in a database.
After the PostGIS extension package is installed, the script is in
$GPHOME/share/postgresql/contrib/postgis-2.1/. The
postgis_manager.sh script runs SQL scripts that install or remove PostGIS
and PostGIS Raster from a database.

For information about the PostGIS and PostGIS Raster SQL scripts, and required PostGIS
Raster environment variables, see PostGIS Support Scripts.

Enabling PostGIS Support

Run the postgis_manager.sh script specifying the database and with the
install option to install PostGIS and PostGIS Raster. This example
installs PostGIS and PostGIS Raster objects in the database
mydatabase.

postgis_manager.sh mydatabase install

The script runs all the PostGIS SQL scripts that enable PostGIS in a database:
install/postgis.sql, install/rtpostgis.sqlinstall/spatial_ref_sys.sql,
install/postgis_comments.sql, and
install/raster_comments.sql.

The postGIS package installation adds these lines to the
greenplum_path.sh file for PostGIS Raster support.

Enabling GDAL Raster Drivers

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.

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 Formats table
at http://www.gdal.org/formats_list.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();

Removing PostGIS Support

Run the postgis_manager.sh script specifying the database and with the
uninstall option to remove PostGIS and PostGIS Raster. This example
removes PostGIS and PostGIS Raster support from the database
mydatabase.

postgis_manager.sh mydatabase uninstall

The script runs both the PostGIS SQL scripts that remove PostGIS and PostGIS Raster from
a database: uninstall_rtpostgis.sql and
uninstall_postgis.sql.

The postgis_manager.sh script does not remove these PostGIS Raster
environment variables 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 with the gppkg utility.

Usage

The following example SQL statements create non-OpenGIS tables and geometries.

The following example SQL statements create a table and add a geometry column to the table
with a SRID integer value that references an entry in the SPATIAL_REF_SYS
table. The INSERT statements add two geopoints to the table.

Spatial Indexes

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.

Building a Spatial Index

You can build a GiST index as follows:

CREATE INDEX indexname
ON tablename
USING GIST ( geometryfield );

PostGIS Extension Support and Limitations

This section describes Greenplum PostGIS extension feature support and limitations.

PostGIS Extension Limitations

Data types and functions related to PostGIS topology functionality, such as
TopoGeometry, are not supported by Greenplum Database.

Functions that perform ANALYZE operations for user-defined data
types are not supported. For example, the ST_Estimated_Extent
function is not supported. The function requires table column statistics for user
defined data types that are not available with Greenplum Database.

These PostGIS aggregates are not supported by Greenplum Database:

ST_MemCollect

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:

PostGIS Support Scripts

After installing the PostGIS extension package, you enable PostGIS support for each
database that requires its use. To enable or remove PostGIS support in your database, you
can run SQL scripts that are supplied with the PostGIS package in
$GPHOME/share/postgresql/contrib/postgis-2.1/.

Instead of running the scripts individually, you can use the
postgis_manager.sh script to run SQL scripts that enable or remove
PostGIS support. See Enabling and Removing PostGIS Support.

You can run the PostGIS SQL scripts individually to enable or remove PostGIS support. For
example, these commands run the SQL scripts postgis.sql,
rtpostgis.sql, and spatial_ref_sys.sql in the database
mydatabase.

Note: If you are installing PostGIS Raster, PostGIS objects must be installed before PostGIS
Raster. PostGIS Raster depends on PostGIS objects. Greenplum Database returns an error if
rtpostgis.sql is run before postgis.sql.

These SQL scripts add data and comments to a PostGIS enabled database.

install/spatial_ref_sys.sql - Populate the
spatial_ref_sys table with a complete set of EPSG coordinate system
definition identifiers. With the definition identifiers you can perform
ST_Transform() operations on geometries.

Note: If you
have overridden standard entries and want to use those overrides, do not load the
spatial_ref_sys.sql file when creating the new
database.

GDAL_DATA specifies the location of GDAL utilities and support files
used by the GDAL library. For example, the directory contains EPSG support files such as
gcs.csv​ and pcs.csv (so called dictionaries, mostly
in ​CSV format). The GDAL library requires the support files to properly evaluate EPSG
codes.

POSTGIS_GDAL_ENABLED_DRIVERS sets the enabled GDAL drivers in the
PostGIS environment.

POSTGIS_ENABLE_OUTDB_RASTERS is a boolean configuration option to
enable access to out of database raster bands.

Scripts that Remove PostGIS and PostGIS Raster Support

To remove PostGIS support from a database, run SQL scripts that are supplied with the
PostGIS extension package in
$GPHOME/share/postgresql/contrib/postgis-2.1/

Note: If you installed PostGIS Raster, you must uninstall PostGIS Raster before you
uninstall the PostGIS objects. PostGIS Raster depends on PostGIS objects. Greenplum
Database returns an error if PostGIS objects are removed before PostGIS Raster.

These scripts remove PostGIS and PostGIS Raster objects from a database.

After PostGIS support has been removed from all databases in the Greenplum Database
system, you can remove the PostGIS extension package. For example this
gppkg command removes the PostGIS extension package.

gppkg -r postgis-ossv2.1.5_pv2.1_gpdb5.0

Restart Greenplum Database after removing the package.

gpstop -r

Ensure that these lines for PostGIS Raster support are removed from the
greenplum_path.sh file.