Use a Python script to create the ST_Geometry type in Oracle

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

When you do, a user named sde is added to the database. The sde user owns the ST_Geometry type, subtypes, functions, and tables.

The following steps explain how to create the ST_Geometry type in an Oracle database using a Python script:

Steps:

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 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 folder and execute privileges on the library file.

2. Copy this example script to the Python window in ArcGIS for Desktop, or to any Python IDE such as PythonWin or WING. Alter any variable values to match the information at your site.

   """
   Name: create_spatial_type.py
   Description: Provide connection information to an enterprise 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, instnace, 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. Run the altered script to create ST_Geometry type, subtypes, and functions in the Oracle database.

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 ArcGIS for Desktop installation directory, place it on the Oracle server, and set a PEDATAHOME environment variable on the server that points to that location.