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.
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 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:
- 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.
- 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.
- 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.
- Add the database connection (.sde file) you created in step 2 to the Input Database text box.
- Type the password to be used for the sde database user in the SDE User Password text box.
- Leave the Tablespace Name text box blank.
- Click OK to run the tool.
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.
- 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.
-
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)
- Save the file with a .py extension.
- 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 in the database you specified.