Greenplum PostGIS Extension

This chapter contains the following information:

Parent topic: Greenplum Database Reference Guide

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.

Go to http://postgis.refractions.net/ for more information about PostGIS.

For information about Greenplum Database PostGIS extension support, see PostGIS Extension Support and Limitations.

Greenplum PostGIS Extension

The Greenplum Database 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.

Important: Extension packages for Greenplum Database 4.3.4.x and earlier are not compatible with Greenplum Database 4.3.5.0 due to the introduction of Pivotal Query Optimizer. Also, extension packages for Greenplum Database 4.3.5.0 are not compatible with Greenplum Database 4.3.4.x and earlier.

To use extension packages with Greenplum Database 4.3.5.0, you must install and use Greenplum Database extension packages (gppkg files and contrib modules) that are built for Greenplum Database 4.3.5.0. For custom modules that were used with Greenplum Database 4.3.4.x and earlier, you must rebuild the modules for use with Greenplum Database 4.3.5.0.

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: http://postgis.net/docs/manual-2.0/release_notes.html

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: http://postgis.net/docs/manual-2.0/PostGIS_Special_Functions_Index.html #NewFunctions

Greenplum Database PostGIS Limitations

The Greenplum Database PostGIS extension does not support the following features:

  • Topology
  • Raster
  • A small number of user defined functions and aggregates
  • PostGIS long transaction support
  • Geometry and geography type modifier

For information about Greenplum Database PostGIS support, see PostGIS Extension Support and Limitations.

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 is 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 
  $GPHOME/share/postgresql/contrib/postgis-2.0/postgis.sql
psql -d mydatabase -f 
  $GPHOME/share/postgresql/contrib/postgis-2.0/spatial_ref_sys.sql
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.

Upgrading the Greenplum PostGIS Extension

If you upgrade from PostGIS extension package version 2.0 (pv2.0) or later, you must run postgis_upgrade_20_minor.sql in your target database. This example upgrades the PostGIS extension package and runs the script:

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

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: http://postgis.net/docs/manual-2.0/postgis_installation.html#hard_upgrade

Usage

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

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.

The Greenplum Database PostGIS extension does not support the following features:

  • Topology
  • Raster

Supported PostGIS Data Types

Greenplum Database PostGIS extension supports these PostGIS data types:

  • box2d
  • box3d
  • geometry
  • geography
  • spheroid

Supported PostGIS Index

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

PostGIS Extension Limitations

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

  • Data types and functions related to PostGIS topology or raster functionality, such as TopoGeometry and ST_AsRaster are not supported by Greenplum Database.
  • 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.
  • ST_GeomFronGeoJSON function is not supported. The function requires JSON support. JSON is not supported in 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:
    CREATE TABLE geometries(id INTEGER);
SELECT AddGeometryColumn('public', 'geometries', 'geom', 0, 'LINESTRING', 2);