Adding the ST_Geometry type to a PostgreSQL database

You can add the ST_Geometry type, subtype, and functions to your PostgreSQL database using the Create Spatial Type geoprocessing tool or a Python script.

The Create Spatial Type tool does the following:

• Creates an sde login role in the PostgreSQL database cluster
• Grants the sde user superuser privileges (these can be revoked after the ST_Geometry type is created)
• Creates a schema named sde in the database where you install the ST_Geometry type
• Grants the sde user full authority on the sde schema
• Grants USAGE privileges on the sde schema to the public login group (If you want, you can revoke this privilege, but you must grant USAGE on the sde schema to all users who need access to the ST_Geometry type, subtypes, or functions.)
• Creates the necessary functions, domains, tables (sde_coordinate_systems and sde_geometry_columns), and views (st_geometry_columns and st_spatial_references) in the sde schema, and the sde_spatial_references table in the public schema

Before running the tool, you must copy the st_geometry library to the PostgreSQL server.

The following steps explain how to install the ST_Geometry type in a PostgreSQL database:

Steps:

1. Copy the st_geometry library from the DatabaseSupport directory in the ArcGIS client installation location and place it in the PostgreSQL lib directory.
   • If PostgreSQL is installed on a Linux server, log in to the server as the root user and place the st_geometry.so file in the /usr/lib64/pgsql directory.
   • If PostgreSQL is installed on a Windows server, place the st_geometry.dll file in the %PostgreSQL%\lib directory.
2. If you have PostgreSQL installed on a Windows server, you must have the Microsoft Visual C++ 2008 SP1 Redistributable Package (x64) installed on the server. If it is not present on the PostgreSQL server, download it from the Microsoft site and install it.

   This is required to use the ST_Geometry type.

3. Start ArcCatalog or ArcMap and open the Catalog window.
4. Create a connection to your PostgreSQL database. Log in as the postgres user.
5. Open the Create Spatial Type tool.

   You can use the Search window to search for the tool or open the tool from the Workspace toolset of the Data Management toolbox.

6. Add the database connection you created in step 4 to the Input database workspace text box.
7. Type a password for the sde user in the SDE User Password text box.
8. Leave the Tablespace Name text box blank.
9. Click OK to run the tool.

The ST_Geometry type, subtypes, and functions are created in the schema of the sde user.