We have migrated to new Google sites!
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.
The Greenplum PostGIS extension is available from the EMC Download Center and is installed using the Greenplum Package Manager (gppkg).
Enabling PostGIS Support
You must enable PostGIS support for each database that requires its usage. This is done by feeding the postgis.sql (the enabler script) file to your target database.
Run the enabler script, as follows:
psql -f postgis.sql -d your_database
Your database is now spatially enabled.
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(2 2 0,3 3 0)'::box3d;
The following example SQL statements create proper OpenGIS entries in the SPATIAL_REF_SYS and GEOMETRY_COLUMNS tables, and ensure that all geometries are created with a SRID.
INSERT INTO SPATIAL_REF_SYS
( SRID, AUTH_NAME, AUTH_SRID, SRTEXT ) VALUES
( 1, 'EPSG', 4269,
'GEOGCS["NAD83",
DATUM[
"North_American_Datum_1983",
SPHEROID[
"GRS 1980",
6378137,
298.257222101
]
],
PRIMEM["Greenwich",0],
UNIT["degree",0.0174532925199433]]'
);
CREATE TABLE geotest (
id INT4,
name VARCHAR(32)
);
SELECT AddGeometryColumn('db','geotest','geopoint',1,'POINT',2);
INSERT INTO geotest (id, name, geopoint)
VALUES (1, 'Olympia', GeometryFromText('POINT(-122.90 46.97)',1));
INSERT INTO geotest (id, name, geopoint)
VALUES (2, 'Renton', GeometryFromText('POINT(-122.22 47.50)',1));
SELECT name,AsText(geopoint) FROM geotest;
Spatial Indexes
PostgreSQL provides support for GiST spatial indexing. The GiST scheme offers indexing even on large objects, using a system of ‘lossy’ indexing where a large object is proxied by a smaller one in the index. In the case of the PostGIS indexing system, all objects are proxied in the index by their bounding boxes.
Building a Spatial Index
You can build a GiST index as follows:
CREATE INDEX indexname
ON tablename
USING GIST ( geometryfield );
Greenplum PostGIS Limitations
The Greenplum PostGIS extension does not support the following:
•estimated_extent functions.
•PostGIS long transaction support.