Desktop Help 10.0 - ArcGIS and the PostGIS geometry type

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.

When you use the PostGIS geometry storage type with ArcGIS, keep the following rules in mind:

You must use the PostGIS database template to create the PostgreSQL database you use for your geodatabase. See the section "Preparing your database to use PostGIS geometry".
The sde user and any user who creates data in the geodatabase must be granted permissions on specific PostGIS tables. See the section "Granting users rights to create tables with PostGIS geometry columns".
Feature classes that you create can only use the spatial references listed in the PostGIS public.spatial_ref_sys table. If you specify one that is not in that table, feature class creation fails.
You must specify a configuration keyword that contains the GEOMETRY_STORAGE parameter set to PG_GEOMETRY to create a feature class that uses the PostGIS geometry type.

The following sections contain more information on how to use the PostGIS geometry storage type with ArcGIS applications.

Preparing your database to use PostGIS geometry

To use the PostGIS geometry type, you must install PostGIS.

Install PostGIS after you install PostgreSQL.

To obtain the version of PostGIS supported by ArcSDE, you can install PostgreSQL StackBuilder when you install PostgreSQL on Windows. Or consult the PostGIS Web site at http://www.postgis.org/download/ or 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.

Tip:

See the ArcGIS Server system requirements page on the ESRI support page to find out which version of PostGIS is supported with ArcSDE.

When you install PostGIS, a PostGIS template database is created in the PostgreSQL database cluster. Use the PostGIS template database to create a database in which to store your geodatabase.

Note:

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: click Custom on the first dialog box of the Post Installation wizard, click Next, then uncheck Define SDE User Environment.

Granting users rights to create tables with PostGIS geometry columns

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

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

GRANT select
ON TABLE public.spatial_ref_sys
TO <user_name>;

Creating feature classes in an ArcSDE geodatabase for PostgreSQL using PostGIS geometry storage

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). Therefore, when you want to create a feature class using ArcGIS that uses the PostGIS geometry type, specify a configuration keyword that contains the GEOMETRY_STORAGE parameter set to PG_GEOMETRY.

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, 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:

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:

Each table must be owned by the user registering the table.
Each table must have a single geometry column.
The tables must contain no other columns of a user-defined type.
Tables must have a single type of geometry (points, lines, or polygons), though geometry can be multipart.
Each table must have an integer, unique, not-NULL column suitable as a registered row ID column.
Each table should have a spatial index.

For information on creating tables with a PostGIS column using SQL, see the PostGIS documentation on the PostGIS.org Web site.

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 an sde-maintained unique feature identifier column (–C fid,SDE).

sdelayer –o register –l properties,shape –e p 
-t PG_GEOMETRY –C fid,SDE -g RTREE -x 30,10,1000 
–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 a table with the geodatabase for more information.

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.

Where to find PostGIS and PostgreSQL documentation

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

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

8/19/2013