Chapter 3. Frequently Asked Questions

You can store point, line, polygon, multipoint, multiline, multipolygon, and geometrycollections. These are specified in the Open GIS Well Known Text Format (with 3d extentions).

First, you need to create a table with a column of type "geometry" to hold your GIS data. Connect to your database with "psql" and try the following SQL:

  CREATE TABLE gtest ( ID int4, NAME varchar(20) );
  SELECT AddGeometryColumn('dbname','gtest','geom',-1,'LINESTRING',2);

If the table creation fails, you probably have not loaded the PostGIS functions and objects into this database. See the installation instructions.

Then, you can insert a geometry into the table using a SQL insert statement. The GIS object itself is formatted using the OpenGIS Consortium "well-known text" format:

  INSERT INTO gtest (ID, NAME,	GEOM) 
    VALUES (1, 'First Geometry', GeometryFromText('LINESTRING(2 3,4 5,6 5,7 8)', -1));
  
  SELECT id, name,	AsText(geom) AS geom FROM gtest;

For more information about other GIS objects, see the object reference.

To view your GIS data in the table: . The return value should look something like this:

 id | name           | geom
----+----------------+-----------------------------
  1 | First Geometry | LINESTRING(2 3,4 5,6 5,7 8) 
(1 row)

There are a number of spatial operators available to PostgreSQL, and several of them have been implemented by PostGIS in order to provide indexing support. However, all the operators have been implemented with the following important simplifying assumption: all features shall be represented by their bounding boxes.

We recognize that using bounding boxes to proxy for features is a limiting assumption, but it was an important one in moving from the conception of a PostgreSQL spatial database to the implementation. Using bounding boxes makes queries faster, indexes smaller, and operators simpler. Commercial spatial databases use the same assumption -- bounding boxes are important to most indexing schemes.

The most important spatial operator from a user's perspective is the "&&" operator, which tests whether one feature's bounding box overlaps that of another. An example of a spatial query using && is:

  SELECT id,name FROM GTEST WHERE GEOM && 'BOX3D(3 4,4	5)'::box3d

Note that the bounding box used for querying must be explicitly declared as a box3d using the "::box3d" casting operation.

Fast queries on large tables is the raison d'etre of spatial databases (along with transaction support) so having a good index in important.

To build a spatial index on a table with a geometry column, use the "CREATE INDEX" function as follows:

  CREATE INDEX [indexname] ON [tablename]  
    USING GIST ( [geometrycolumn] gist_geometry_ops) WITH (islossy);

The "USING GIST" option tells the server to use a GiST (Generalized Search Tree) index. The reference to "gist_geometry_ops" tells the server to use a particular set of comparison operators for building the index: the "gist_geometry_ops" are part of the PostGIS extension. Finally, the "islossy" option tells the server that the features being indexed can be proxied by a smaller data structure -- in the case of geometries, the features are represented in the index by their bounding boxes.

The '&&' operator only checks bounding box overlaps, but you can use the "truly_inside()" function to get only those feature which actually intersect the search box. For example, by combining the use of "&&" for a fast index search and truly_inside() for an accurate final check of the result set, you can get only those features inside the search box (note that this only works for search boxes right now, not any arbitrary geometry):

  SELECT [COLUMN1],[COLUMN2],AsText([GEOMETRYCOLUMN])
    	FROM [TABLE] WHERE [GEOM_COLUMN] && [BOX3d] 
     AND	truly_inside([GEOM_COLUMN],[BOX3d]);

Early versions of PostGIS used the PostgreSQL R-Tree indexes. However, PostgreSQL R-Trees have been completely discarded since version 0.6, and spatial indexing is provided with an R-Tree-over-GiST scheme.

Our tests have shown search speed for native R-Tree and GiST to be comparable. Native PostgreSQL R-Trees have two limitations which make them undesirable for use with GIS features (note that these limitations are due to the current PostgreSQL native R-Tree implementation, not the R-Tree concept in general):

• Building an R-Tree index on a large table of geometries can take over twice as long as a GiST index on the same table.

• R-Tree indexes in PostgreSQL cannot handle features which are larger than 8K in size. GiST indexes can, using the "lossy" trick of substituting the bounding box for the feature itself.

If you do not want to use the OpenGIS support functions, you do not have to. Simply create tables as in older versions, defining your geometry columns in the CREATE statement. All your geometries will have SRIDs of -1, and the OpenGIS meta-data tables will not be filled in properly. For most current applications, this will not matter.

However, in the future it is likely that client software will use the meta-data tables to interrogate the database about available layers and projections before rendering data. An obvious early example is the Mapserver internet mapping software, which could be altered to interrogate the SPATIAL_REF_SYS table for projection information on the layers it is rendering.

For these reasons it is probably wise to learn and use the OpenGIS concepts from early on.