Greenplum PostGIS Extension

A newer version of this documentation is available. Click here to view the most up-to-date release of the Greenplum 4.x documentation.

Greenplum PostGIS Extension

About PostGIS

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

Go to for more information.

Greenplum PostGIS Extension

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

  • Greenplum Database 4.3 supports PostGIS extension package version 2.0 (PostGIS 2.0.3).
  • Greenplum Database 4.2.6 and later supports PostGIS extension package version 1.0 and 2.0 (PostGIS 1.4 and 2.0.3)

    Only one version of the PostGIS extension package, either 1.0 or 2.0, can be installed on an installation of Greenplum Database.

  • Greenplum Database prior to 4.2.6 supports PostGIS extension package version 1.0 (PostGIS 1.4).
    Table 1. PostGIS Component Version
    PostGIS Extension Package PostGIS Geos Proj
    2.0 2.0.3 3.3.8 4.8.0
    1.0 1.4.2 3.2.2. 4.7.0

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

Major enhancements and changes in 2.0.3 from 1.4.2 include:

  • 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

See the PostGIS documentation for a list of changes:

Warning: PostGIS 2.0 removed many functions that were deprecated but available in PostGIS 1.4. Functions and applications written with functions that were deprecated in PostGIS 1.4 might need to be rewritten. See the PostGIS documentation for a list of new, enhanced, or changed functions: #NewFunctions

Greenplum PostGIS Limitations

The Greenplum PostGIS extension does not support the following features:

  • estimated_extent functions
  • PostGIS long transaction support
  • Topology
  • Raster
  • Geometry and geography type modifier

Enabling PostGIS Support

After installing the PostGIS extension package, you enable PostGIS support for each database that requires its use. To enable the support, run enabler SQL scripts that are supplied with the PostGIS package, in your target database.

For PosgGIS 1.4 the enabler script postgis.sql

psql -f postgis.sql -d your_database

Your database is now spatially enabled.

For PostGIS 2.0.3, you run two SQL scripts postgis.sql and spatial_ref_sys.sql in your target database.

For example:

psql -d mydatabase -f 
psql -d mydatabase -f 
Note: spatial_ref_sys.sql populates the spatial_ref_sys table with EPSG coordinate system definition identifiers. 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.

Your database is now spatially enabled.

Migrating from PostGIS 1.4 to 2.0

To migrate a PostGIS-enabled database from 1.4 to 2.0 you must perform a PostGIS HARD UPGRADE. A HARD UPGRADE consists of dumping a database that is enabled with PostGIS 1.4 and loading the database the data to a new database that is enabled with PostGIS 2.0.

For information about a PostGIS HARD UPGRADE procedure, see the PostGIS documentation:


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

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)'));

The following example SQL statements create a table, adds 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 to geopoints to the table.

CREATE TABLE geotest (id INT4, name VARCHAR(32) );
SELECT AddGeometryColumn('geotest','geopoint',
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;

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 );