Back to Top

Add the ST_Geometry type to a PostgreSQL database

In this topic

  1. Use the Create Spatial Type geoprocessing tool
  2. Use the CreateSpatialType function

Available with Standard or Advanced license.

To use the ST_Geometry type to store spatial data in your PostgreSQL database, use 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 option set to PostGIS to create a geodatabase in PostgreSQL, and you later decide you want 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 USAGE privileges on the sde schema to the public login group. 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.
  • 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 PostgreSQL database requirements for ArcGIS 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

The following steps explain how to run the Create Spatial Type geoprocessing tool to install the ST_Geometry type in a PostgreSQL database:

  1. Download the st_geometry library from My Esri and place it in the PostgreSQL lib directory. Be sure you download the correct st_geometry library for the version of PostgreSQL and operating system you are using.
    • 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, execute 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.
  2. Open a project in ArcGIS Pro, open the Catalog pane, and connect to the database as a PostgreSQL superuser.

    This creates an .sde file in your project folder.

  3. 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.

  4. Add the database connection (.sde file) you created in step 2 to the Input Database text box.
  5. Type the password to be used for the sde database user in the SDE User Password text box.

    Tip:

    If the PostgreSQL database cluster already has an sde login role, be sure the password you type matches the existing sde login role's password. For example, you might have an enterprise geodatabase in this same database cluster. If you do, the sde login role already exists, and you must use its password here.

  6. Leave the Tablespace Name text box blank.
  7. Click Run.

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

Use the CreateSpatialType function

You run a Python script that calls the CreateSpatialType ArcPy function on any ArcMap, ArcGIS Pro, or ArcGIS Server machine to create the ST_Geometry type, subtype, and functions in a PostgreSQL database.

  1. Download the st_geometry library from My Esri, and place it in the PostgreSQL lib directory. Be sure you download the correct st_geometry library for the version of PostgreSQL and operating system you are using.
    • 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, execute 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.
  2. Create a text file on the ArcGIS client machine, and copy the following script into the file:
    """
Name: create_spatial_type.py
Description: Provide connection information to an enterprise database
and create spatial type in the Oracle or PostgreSQL database.
Type create_spatial_type.py -h or create_spatial_type.py --help for usage
Author: Esri
"""

# Import system modules
import arcpy, os, optparse, sys


# Define usage and version
parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 1.0 for 10.1 and higher releases")

#Define help and options
parser.add_option ("--DBMS", dest="Database_type", type="choice", choices=['ORACLE', 'POSTGRESQL', ''], default="", help="Type of enterprise DBMS: ORACLE, or POSTGRESQL.")
parser.add_option ("-i", dest="Instance", type="string", default="", help="DBMS instance name")
parser.add_option ("--auth", dest="account_authentication", type ="choice", choices=['DATABASE_AUTH', 'OPERATING_SYSTEM_AUTH'], default='DATABASE_AUTH', help="Authentication type options (case-sensitive):  DATABASE_AUTH, OPERATING_SYSTEM_AUTH.  Default=DATABASE_AUTH")
parser.add_option ("-U", dest="Dbms_admin", type="string", default="", help="DBMS administrator user")
parser.add_option ("-P", dest="Dbms_admin_pwd", type="string", default="", help="DBMS administrator password")
parser.add_option ("-D", dest="Database", type="string", default="none", help="Database name:  Not required for Oracle")
parser.add_option ("-p", dest="Password", type="string", default="", help="SDE user password")
parser.add_option ("-t", dest="tablespace", type="string", default="", help="Default tablespace for SDE user")
parser.add_option ("--path", dest="libpath", type="string", default="", help="path to the ST shape library including library file name.")

# Check if value entered for option
try:
	(options, args) = parser.parse_args()

	
#Check if no system arguments (options) entered
	if len(sys.argv) == 1:
		print("{}: error: {}\n".format(sys.argv[0], "No command options given"))
		parser.print_help()
		sys.exit(3)
	

	#Usage parameters for spatial database connection
	database_type = options.Database_type.upper()
	instance = options.Instance
	account_authentication = options.account_authentication.upper()
	password = options.Password 
	tablespace = options.tablespace
	database = options.Database.lower()
	dbms_admin = options.Dbms_admin
	dbms_admin_pwd = options.Dbms_admin_pwd
	lib_path = options.libpath

	if( database_type ==""):	
		print("{}: error: {}\n".format(sys.argv[0], "DBMS type (--DBMS) must be specified."))
		parser.print_help()
		sys.exit(3)		

	# Local variables
	instance_temp = instance.replace("\\","_")
	instance_temp = instance_temp.replace("/","_")
	instance_temp = instance_temp.replace(":","_")
	Conn_File_NameT = instance_temp + "_" + database
	
	if os.environ.get("TEMP") == None:
		temp = "c:\\temp"	
	else:
		temp = os.environ.get("TEMP")
	
	if os.environ.get("TMP") == None:
		temp = "/usr/tmp"		
	else:
		temp = os.environ.get("TMP")  
	
	Connection_File_Name = Conn_File_NameT + ".sde"
	Connection_File_Name_full_path = temp + os.sep + Conn_File_NameT + ".sde"
	
	
	# Check for the .sde file and delete it if present
	arcpy.env.overwriteOutput=True
	if os.path.exists(Connection_File_Name_full_path):
		os.remove(Connection_File_Name_full_path)
	
	
	print("\nCreating Database Connection File...\n")	
	# Process: Create Database Connection File...
	# Usage:  out_file_location, out_file_name, DBMS_TYPE, instnace, database, account_authentication, username, password, save_username_password(must be true)
	arcpy.management.CreateDatabaseConnection(out_folder_path=temp, out_name=Connection_File_Name, database_platform=database_type, instance=instance, database=database, account_authentication=account_authentication, username=dbms_admin, password=dbms_admin_pwd, save_user_pass="TRUE")
	for i in range(arcpy.GetMessageCount()):
		if "000565" in arcpy.GetMessage(i):   #Check if database connection was successful
			arcpy.AddReturnMessage(i)
			arcpy.AddMessage("\n+++++++++")
			arcpy.AddMessage("Exiting!!")
			arcpy.AddMessage("+++++++++\n")
			sys.exit(3)            
		else:
			arcpy.AddReturnMessage(i)
			arcpy.AddMessage("+++++++++\n")	
	
	# Process: Create spatial type...
	try:
		print("Create spatial type...\n")
		arcpy.management.CreateSpatialType(input_database=Connection_File_Name_full_path, sde_user_password=password, tablespace_name=tablespace, st_shape_library_path=lib_path)
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
		arcpy.AddMessage("+++++++++\n")
	except:
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
			
	if os.path.exists(Connection_File_Name_full_path):
		os.remove(Connection_File_Name_full_path)
			
#Check if no value entered for option	
except SystemExit as e:
	if e.code == 2:
		parser.usage = ""
		print("\n")
		parser.print_help()   
		parser.exit(2)
  3. Save the file with a .py extension.
  4. Run the script, providing options and information specific to your site.

    For example, this script creates the ST_Geometry type in the PostgreSQL database spdata, on the pgserve database cluster. The ST_Geometry library is located in the /net/pgserve/opt/PostgreSQL/12.4/lib directory on the PostgreSQL server (pgserve).

    create_spatial_type.py --DBMS POSTGRESQL -i pgserve --auth DATABASE_AUTH -U postgres -P M3tsy$ -D spdata -p 3$@b0eg -t sde --path /net/pgserve/opt/PostgreSQL/12.4/lib/st_geometry.so

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

Feedback on this topic?

In this topic
  1. Use the Create Spatial Type geoprocessing tool
  2. Use the CreateSpatialType function