ArcGIS Server Banner

Using the PostGIS geometry type

Using the PostGIS geometry type

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback

PostGIS is a product from Refractions Research that can spatially enable PostgreSQL databases. PostGIS follows the Open Geospatial Consortium, Inc., Simple Features specification for SQL.

To use the PostGIS geometry type to store data in your geodatabase, you must do the following:

  1. Install PostgreSQL.
  2. You must install PostgreSQL before you install PostGIS, because PostGIS installs in the PostgreSQL installation location. Be sure to also install the PL/pgSQL procedural language when you install PostgreSQL.

  3. Install PostGIS.
  4. To obtain the version of PostGIS supported with ArcSDE (PostGIS 1.3.2), you can install the PostgreSQL StackBuilder when you install PostgreSQL. Or, consult the PgFoundry Web site at http://pgfoundry.org/ for locations from which to download the files. Be sure to download the correct installation or binary files for your operating system.

    NOTE: For Windows, the PostGIS site also provides a link to the PostgreSQL site to download the PostgreSQL installation. Do not use this PostgreSQL installation; rather, use the PostgreSQL installation provided with the ArcSDE component for PostgreSQL of the ArcGIS Server Enterprise software.

    When you install PostGIS, you will create a PostGIS template database.

    If you use the installation wizard on Windows to install PostgreSQL and the ArcSDE component, you will need to exit the wizard before proceeding with the postinstallation setup and install PostGIS. If using the StackBuilder to install, it will launch automatically after installing PostgreSQL.

  5. Use the PostGIS template database to create a database in which to store your geodatabase. See the section Preparing your database to use PostGIS geometry for details.

At this point, you are ready to create feature classes that use the PostGIS geometry type. See the following sections for information on how to do this.

If you need additional PostgreSQL or PostGIS instructions, read the section Where to find PostGIS and PostgreSQL documentation.

Preparing your database to use PostGIS geometry

To use the PostGIS geometry type in your geodatabase, your database must be enabled to use the PostGIS geometry type. To do this, you must create the database using the PostGIS template database that was created when you installed PostGIS.

If you create your geodatabase using the Windows ArcSDE Post Installation wizard, your database is created using a template other than the PostGIS template. Therefore, if you want to use the PostGIS geometry type, you need to exit the installation wizard before starting postinstallation setup, and manually create a database using the PostGIS template, an sde user, and an sde user schema in the database. Then you can complete the rest of the postinstallation steps using the wizard by clicking Custom on the first dialog box of the Post Installation wizard, clicking next, and unchecking Define SDE User Environment.

NOTE: To install PostGIS on Linux, you must install the PostgreSQL source files.

Granting users rights to create tables with PostGIS geometry type columns

When a database is enabled for PostGIS, a table—geometry_columns—is created in the public schema. You must grant, at a minimum, SELECT, INSERT, UPDATE, and DELETE privileges on this table to any users who will create data in the geodatabase.

GRANT select, insert, update, delete ON TABLE public.geometry_columns TO <user_name>;

Creating feature classes in a PostgreSQL database using PostGIS

Once the database is enabled to store PostGIS geometry, you can create spatially enabled tables that include spatial columns of type geometry. Geographic features can be inserted into the spatial columns.

Accessing the spatially enabled tables through ArcSDE can be done by applications using the existing tools offered by the ArcGIS software or by creating applications using the ArcSDE C application programming interface (API). An experienced SQL programmer can also make calls to the spatial functions.

Creating feature classes using ArcGIS

The geometry storage type used for feature classes created using ArcGIS is controlled by a parameter setting in the sde_dbtune table. This parameter is GEOMETRY_STORAGE. In geodatabases in PostgreSQL, this can be set to either ST_GEOMETRY or PG_GEOMETRY (the setting for the PostGIS geometry type).

By default, the geometry storage type for new feature classes in an ArcSDE geodatabase for PostgreSQL uses ST_Geometry storage. If you want to store most of your data in PostGIS storage types, you must alter the GEOMETRY_STORAGE parameter value under the DEFAULTS keyword in the sde_dbtune table to PG_GEOMETRY. (Use the sdedbtune command to alter parameters in the sde_dbtune table. See the ArcSDE Administration Command Reference for details on using this command.) Or, if you want to store just some of your feature classes in the PostGIS storage type, you can use the PG_GEOMETRY configuration keyword and specify that keyword when you create your feature class. In the dbtune.sde file, the PG_GEOMETRY keyword appears as follows:

##PG_GEOMETRY
GEOMETRY_STORAGE    "PG_GEOMETRY"
UI_TEXT             "User Interface text description for POSTGIS geometry storage"

END

The rest of the storage parameters are picked up from the DEFAULTS keyword. For more information on the sde_dbtune storage, see the following topics: The dbtune file and the DBTUNE table, DBTUNE configuration keywords, DBTUNE configuration parameter name-configuration string pairs, and PostgreSQL DBTUNE configuration parameters.

Creating feature classes using SQL

There are two parts to creating a feature class with a PostGIS geometry column. First, create a nonspatial table, then add a geometry column to it using the PostGIS AddGeometryColumn function.

For example, to create a table, blocks, with columns objectid, block, and res, execute the following:

CREATE TABLE blocks(objectid integer NOT NULL, block varchar(4), res smallint);

Next, add the geometry column. The syntax for the AddGeometryColumn function is as follows:

AddGeometryColumn(<schema_name>, <table_name>, 
<column_name>, <srid>, <type>, <dimension>)

To add a geometry column to the blocks table, provided you are in the schema where the table was created and that a spatial reference ID (SRID) value of 2082 exists in the public.spatial_ref_sys table in the geodatabase, execute the following:

SELECT AddGeometryColumn('blocks', 'shape', 2082, 'GEOMETRY', 2 );

Once you have set the SRID for this table, you must always use that SRID when inserting data to the table.

NOTE: To add a PostGIS geometry column to a table, the user must have SELECT, INSERT, UPDATE, and DELETE privleges on the public.geometry_columns table, as explained in the previous section.

Using existing PostGIS tables

ArcSDE can successfully use tables containing PostGIS geometry columns created externally by other applications or using SQL (also referred to as third-party tables) as long as the tables meet the following prerequisites:

Registering third-party tables containing PostGIS geometry columns

The ArcSDE administration command sdelayer –o register registers a table as a feature class. That means a record is added for the table in the sde_layers, sde_table_registry, and sde_geometry_columns system tables. Records are also added to the sde_column_registry system table for each column in the table.

The following is an example of registering a table, properties, containing point geometries (–e p) in a spatial column, shape. The table has an integer column, fid, to be used as a unique feature identifier column (–C fid,SDE) maintained by SDE.

sdelayer –o register –l properties,shape –e p –C fid,SDE –u <user> –p <pw>

As indicated above, sdelayer only adds the table to the ArcSDE system tables. To be able to use ArcGIS Desktop geodatabase functionality, such as topology, versioning, and networks, you must also register the table with the geodatabase. See Registering tables to be used by ArcGIS Desktop for more information on registering tables.

NOTE: You cannot rename spatial tables stored using the PostGIS geometry type. This is due to the fact that there is no PostGIS function to update the table name in the public.geometry_columns table.

Creating a spatial index

When you create feature classes through ArcGIS Desktop, a spatial index is automatically created. If you create a feature class using SQL, you execute a CREATE INDEX statement.

CREATE INDEX <index_name> ON <table_name>
USING GIST(<geometry_field_name> GIST_GEOMETRY_OPS);

To create a spatial index on the blocks feature class, execute the following:

CREATE INDEX blkssp_ix ON blocks
USING gist(shape gist_geometry_ops);

Where to find PostGIS and PostgreSQL documentation

You can find PostGIS documentation on the PostGIS Web site: http://www.postgis.org/documentation/

You can also download a PostGIS manual in .pdf format from the Refractions Research site at http://postgis.refractions.net/docs/postgis.pdf. For Windows, a summary manual is also installed when you install PostGIS.

For general PostgreSQL information, there is documentation on the PostgreSQL Web site: http://www.postgresql.org/docs/