Add the ST_Geometry type to a PostgreSQL database

Available with Standard or Advanced license.

To use the ST_Geometry type to store spatial data in a PostgreSQL database, the database administrator can run the Create Spatial Type geoprocessing tool or a Python script that calls the CreateSpatialType ArcPy function to add the type, its subtypes, and functions to the database.

Tip:

If you used the Create Enterprise Geodatabase geoprocessing tool with the Spatial Type parameter set to PostGIS to create a geodatabase in PostgreSQL, and you later decide to add the ST_Geometry type to that geodatabase, you can run the Create Spatial Type tool to add ST_Geometry to that geodatabase. Otherwise, you only run the Create Spatial Type tool in a database.

The Create Spatial Type tool and function do the following:

Create an sde login role in the PostgreSQL database cluster.
Grant the sde user superuser authority (this can be revoked after the ST_Geometry type is created).
Create a schema named sde in the database.
Grant the sde user full authority on the sde schema.
Grant the USAGE privilege on the sde schema to the public login group. You can revoke this privilege from the public group, but you must grant USAGE on the sde schema to all users who need access to the ST_Geometry type, subtypes, or functions.
Create 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 or function, you must copy the st_geometry library to the PostgreSQL server.

Note:

The st_geometry.dll file requires the Microsoft Visual C++ Redistributable Package (x64) when deployed on a Microsoft Windows server. See ArcGIS requirements for using PostgreSQL for the package version required. If this package is not present on the PostgreSQL server, download it from the Microsoft site and install it.

Use the Create Spatial Type geoprocessing tool

To run the Create Spatial Type geoprocessing tool to install the ST_Geometry type in a PostgreSQL database, complete the following steps:

Download the ST_Geometry compressed file from My Esri.

The compressed files are specific to the ArcGIS software version from which you will create the spatial type.

If the option to download is not present, contact your ArcGIS organization administrator.
Extract the contents of the file.

Note:

The st_geometry libraries are for specific operating systems and versions of PostgreSQL. Ensure you copy the correct library.
Place the st_geometry file in the PostgreSQL lib directory.
- If PostgreSQL is installed on a Windows server, place the st_geometry.dll file in the %PostgreSQL%\lib directory on the server.
- The location of the lib directory on Linux can vary depending on how you installed PostgreSQL. To determine the correct location for your PostgreSQL installation, run pg_config as the postgres user. The value that is returned for PKGLIBDIR is the lib directory where you need to place the st_geometry.so file. Sign in to the machine as the root user to copy the file to the lib location.
Note:

If your Linux operating system does not include libstdcc++.so.6.0.29 or a later release of the library, also place the libstdcc++.so.6 library in the PostgreSQL lib directory with the st_geometry.so library.
Open a project in ArcGIS Pro, open the Catalog pane, and connect to the database as a PostgreSQL superuser.

A database connection (.sde) file is created in your project folder.
Open the Create Spatial Type tool.

You can use the Search window to find the tool or open the tool from the Workspace toolset of the Data Management toolbox.
Add the database connection you created in step 2 to the Input Database Connection parameter text box.
Provide the password for the sde database user in the SDE User Password parameter text box.

Tip:

If the PostgreSQL database cluster already has an sde login role, the password you provide must match the existing sde login role's password. For example, if you have an enterprise geodatabase in this same database cluster, the sde login role already exists, and you must use its password here.
Leave the Tablespace Name parameter text box blank.
Click Run.

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

Use ArcPy functions

On any ArcGIS Pro or ArcGIS Server machine, use the CreateDatabaseConnection ArcPy function to create a database connection file (.sde) that connects to the PostgreSQL database as a PostgreSQL superuser. Next, use the .sde file and the CreateSpatialType ArcPy function to create the ST_Geometry type, subtype, and functions in the database.

Download the st_geometry library from My Esri, and place it in the PostgreSQL lib directory.

Note:

If your Linux operating system does not include libstdcc++.so.6.0.29 or a later release of the library, also download the libstdcc++.so.6 library for PostgreSQL from My Esri, and place it in the PostgreSQL lib directory with the st_geometry.so library.

The st_geometry libraries are for specific operating systems, versions of PostgreSQL, and ArcGIS versions.
- The location of the lib directory on Linux can vary depending on how you installed PostgreSQL. To determine the correct location for your PostgreSQL installation, run pg_config as the postgres user. The value that is returned for PKGLIBDIR is the lib directory where you need to place the st_geometry.so file. Sign in to the machine as the root user to copy the file to the lib location.
- If PostgreSQL is installed on a Windows server, place the st_geometry.dll file in the %PostgreSQL%\lib directory on the server.
Run the CreateDatabaseConnection function to create .sde file.

You must connect as a PostgreSQL superuser, and you must save credentials.

In the following example, the connection file is created in a directory named TempFiles, the connection file name is suserconnection.sde, credentials are provided for the superuser and are saved with the file to connect to a database named pgdb in the database cluster pginstance.
```
import arcpy

arcpy.management.CreateDatabaseConnection(
   "/user/TempFiles",
   "suserconnection.sde",
   "POSTGRESQL",
   "pginstance",
   "DATABASE_AUTH",
   "postgres",
   "Cl@rkK3nt",
   "SAVE_USERNAME",
   "pgdb"
)
```
Run the CreateSpatialType ArcPy function to install the ST_Geometry type in the PostgreSQL instance.

In this example, the connection file from the previous step is used, and the sde password is set to pw0rd4sde.
```
import arcpy

arcpy.management.CreateSpatialType(
   "suserconnection.sde",
   "pw0rd4sde"
)
```

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