ArcMap

Add the ST_Geometry type to an Oracle database

Available with Standard or Advanced license.

You can add the Esri ST_Geometry type, subtype, and functions to your Oracle database using the Create Spatial Type geoprocessing tool or a Python script that calls the CreateSpatialType ArcPy function.

Both the tool and the function create a user named sde in the database. The sde user owns the ST_Geometry type, subtypes, functions, and tables.

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 an Oracle database:

  1. Copy the libst_shapelib (st_shapelib for Windows) library from the DatabaseSupport directory in the ArcGIS client installation location, and move it to a location on the Oracle server.

    Be sure to use the library appropriate for your Oracle server's operating system.

    The directory on the server where you place the library must be accessible from the client machine from which you will run the Create Spatial Type tool or script. If placing the library on a UNIX or Linux machine, be sure the database administrator has read access to the directory and execute privileges on the library file.

  2. Start ArcCatalog or ArcMap and open the Catalog window.
  3. Create a connection to your Oracle database. Log in as an Oracle sys database administrator.
  4. 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.

  5. Add the database connection you created in step 3 to the Input Database text box.
  6. Type a password to be used for the sde database user in the SDE User Password text box.
  7. If you want to create a tablespace to use as the sde user's default, type it in the Tablespace Name text box.

    A 400 MB tablespace will be created in the Oracle default storage location. If you want to control where the tablespace is placed or the size of the tablespace, create the tablespace in Oracle first, and specify the existing tablespace in the Tablespace Name text box. The tool will set this pre-existing tablespace as the default for the sde user.

  8. Browse to or type the ST_Geometry Shape Library Path. This is the path to the directory on the Oracle server where you placed the libst_shapelib or st_shapelib file, including the name of the file.

    For example, if you placed the libst_shapelib.so file in the directory, libraries, on Linux server boxy, you would type /boxy/libraries/libst_shapelib.so.

  9. Click OK to run the tool.

The ST_Geometry type, subtypes, and functions are created in the schema of the sde user and an entry is made in Oracle for the shape library.

If you plan to use the ST_Transform function to perform geographic transformations, you must copy the pedata folder from your ArcMap installation directory, place it on the Oracle server, and set a PEDATAHOME environment variable on the server that points to that location.

Use the CreateSpatialType function

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

  1. Copy the libst_shapelib (st_shapelib for Windows) library from the DatabaseSupport directory in the ArcGIS client installation location, and move it to a location on the Oracle server.

    Be sure to use the library appropriate for your Oracle server's operating system.

    The directory on the server where you place the library must be accessible from the client machine from which you will run the Create Spatial Type tool or script. If placing the library on a UNIX or Linux machine, be sure the database administrator has read access to the directory and execute privileges on the library file.

  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 Oracle instance oserve/orcl, and the ST_Geometry library is located in the /ora/shape directory on the Oracle server (oserve).

    create_spatial_type.py --DBMS ORACLE -i oserve/orcl --auth DATABASE_AUTH -U sys -P M3tsy$ -p 3$@b0eg -t sde --path /net/oserve/ora/shape/libst_shapelib.so

The ST_Geometry type, subtypes, and functions are created in the schema of the sde user and an entry is made in Oracle for the shape library.

If you plan to use the ST_Transform function to perform geographic transformations using SQL, you must copy the pedata folder from your ArcMap or ArcGIS for Server installation directory, place it on the Oracle server, and set a PEDATAHOME environment variable on the server that points to that location.

Related topics