Back to Top

Add the ST_Geometry type to an Oracle database

In this topic

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

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.

Once you add the ST_Geometry type to your database, you can use it to store spatial data.

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.

Note:

The ST_Geometry library requires the Microsoft Visual C++ Redistributable Package (x64) when deployed on a Microsoft Windows server. See Oracle database requirements for ArcGIS for the package version required. If this package is not present on the 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 an Oracle database:

  1. Download the st_shapelib (Windows) or libst_shapelib (Linux) library from My Esri, 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 you place 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. Open a project in ArcGIS Pro, open the Catalog pane, and connect to the database as an Oracle sys database administrator.

    This creates an .sde file in your project folder.

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

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

    The Create Spatial Type tool creates a 400 MB tablespace 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.

  7. 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.so or st_shapelib.dll file, including the name of the file.

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

  8. Click Run.

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 Pro 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 Server machine to create the Esri ST_Geometry type, subtype, and functions in an Oracle database.

  1. Download the st_shapelib (Windows) or libst_shapelib (Linux) library from My Esri, 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 you place 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 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 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, you must copy the pedata folder from your ArcGIS Pro installation directory, place it on the Oracle server, and set a PEDATAHOME environment variable on the server that points to that location.

Feedback on this topic?

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