ArcGIS and the PostGIS geometry type

PostGIS is a product that spatially enables PostgreSQL databases. PostGIS follows the Open Geospatial Consortium, Inc. (OGC), Simple Features specification for a Structured Query Language (SQL). It uses the OGC well-known binary (WKB) and well-known text (WKT) representations of geometry.

PostGIS has two spatial type options: geometry and geography. Only the geometry type is supported with ArcGIS.

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

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. Be sure to install a version of PostGIS supported by the ArcGIS release you want to use.

TipTip:

See the database system requirements page on the ArcGIS Resource Center to find out which version of PostGIS is supported with the ArcGIS release you are using.

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.

NoteNote:

If you create your geodatabase using the Create Enterprise Geodatabase geoprocessing tool, your database is created using a template other than the PostGIS template. Therefore, if you want to use the PostGIS geometry type, you must create a database manually using the PostGIS template. Then you can specify your existing database when you run the Create Enterprise Geodatabase geoprocessing tool, and the geodatabase will be created in it.

Granting users rights to create tables with PostGIS geometry columns

When a database is enabled for PostGIS, two 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 a geodatabase in 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.

You can access the spatially enabled tables through ArcGIS for Desktop or by creating applications using the ArcSDE C application programming interface (API). If you are an experienced SQL programmer, you 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, which can be created by exporting the contents of the sde_dbtune table using the sdedbtune command, 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 sde_dbtune storage, see the following topics:

Using existing PostGIS tables

ArcGIS can 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:

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

Registering third-party tables containing PostGIS geometry columns

You can connect to a PostgreSQL database that contains PostGIS tables from ArcGIS for Desktop and register them with the geodatabase. See Registering a table with the geodatabase for more information.

Where to find PostGIS and PostgreSQL documentation

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

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

11/6/2014