ArcMap

Add the ST_Geometry type to a PostgreSQL database

Available with Standard or Advanced license.

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 that calls the CreateSpatialType ArcPy function.

The 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 where you want to install the ST_Geometry type.
  • 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.

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. Copy the st_geometry library from the DatabaseSupport directory in the ArcGIS Desktop installation location and place it in the PostgreSQL lib directory. Be sure you copy 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. Log in 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.
  2. Start ArcCatalog or ArcMap, open the Catalog window, and connect to the database as a PostgreSQL superuser.

    This creates a .sde file in C:\Users\<user_name>\AppData\Roaming\ESRI\Desktop<version_number>\ArcCatalog.

  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.

  6. Leave the Tablespace Name text box blank.
  7. Click OK to run the tool.

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

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. Copy the st_geometry library from the DatabaseSupport directory in the ArcGIS Desktop installation location and place it in the PostgreSQL lib directory. Be sure you copy 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. Log in 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.
  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 Oracle or PostgreSQL database
and create spatial type in the 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 " + arcpy.GetInstallInfo()['Version'] )

#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 "%s: error: %s\n" % (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 " \n%s: error: \n%s\n" % (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, instance, database, account_authentication, username, password, save_username_password(must be true)
	arcpy.CreateDatabaseConnection_management(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.CreateSpatialType_management(input_workspace=Connection_File_Name_full_path, sde_user_password=password, tablespace_name=tablespace, st_shape_library_path=lib_path)
		print "after spatial type...\n"
		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/9.3/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/9.3/lib/st_geometry.so

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

Related topics