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>;

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.

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), although 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.

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.

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);

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/