The ST_Geometry data type implements the SQL 3 specification of user-defined data types (UDTs), allowing you to create columns capable of storing spatial data such as the location of a landmark, a street, or a parcel of land. It provides International Organization for Standards (ISO) and Open Geospatial Consortium, Inc. (OGC) compliant structured query language (SQL) access to the geodatabase and database. This storage extends the capabilities of the database by providing storage for objects (points, lines, and polygons) that represent geographic features. It was designed to make efficient use of database resources; to be compatible with database features such as replication and partitioning; and to provide rapid access to spatial data.
Esri has implemented an ST_Geometry type in Oracle, PostgreSQL, and SQLite. It is this ST_Geometry data type that is explained in the ArcGIS help.
ST_Geometry itself is an abstract, noninstantiated superclass. However, its subclasses can be instantiated. An instantiated data type is one that can be defined as a table column and have values of its type inserted into it.
Although you can define a column as type ST_Geometry, you do not insert ST_Geometry values into the column since it cannot be instantiated. Instead, you insert the subclass values.
The following chart demonstrates the hierarchy of the ST_Geometry data type and its subclasses:
ST_Geometry subclasses are divided into two categories: the base geometry subclasses and the homogeneous collection subclasses. The base geometries include ST_Point, ST_LineString, and ST_Polygon, while the homogeneous collection includes ST_MultiPoint, ST_MultiLineString, and ST_MultiPolygon. As the names imply, the homogeneous collections are collections of base geometries. In addition to sharing base geometry properties, homogeneous collections have some of their own properties.
Each subclass stores the type of geometry implied by its name; for instance, ST_MultiPoint stores multipoints. A list of the subclasses and their descriptions are in the following table:
Note that each subclass inherits the properties of the ST_Geometry superclass but also has properties of its own. Functions that operate on the ST_Geometry data type accept any of the subclass entity types. However, some functions have been defined at the subclass level and only accept certain subclasses. For example, the ST_GeometryN function only takes ST_MultiLinestring, ST_MultiPoint, or ST_MultiPolygon subtype values as input.
To discover the subclass of an ST_Geometry, you can use the ST_GeometryType function. The ST_GeometryType function takes an ST_Geometry and returns the instantiated subclass in the form of a character string. To find out how many base geometry elements are contained in a homogeneous collection, you can use the ST_NumGeometries function, which takes a homogeneous collection and returns the number of base geometry elements it contains.
ST_Geometry function calls
ST_Geometry functions in Oracle and PostgreSQL are implemented in SQL. This is a database-level language. In Oracle, it is Procedural Language Structured Query Language (PL/SQL). In PostgreSQL, it is PL/pgSQL.
You access the database directly when accessing ST_Geometry columns in Oracle or PostgreSQL using SQL functions; therefore, the database must be able to access the ST_Geometry library. In Oracle, the database also spawns an extproc process.
In SQLite, ST_Geometry functions are implemented in C in the ST_Geometry library. You must access the ST_Geometry library to use the ST_Geometry functions.
Functions are implemented in PL/SQL, which calls functions in external shared library files written in the C programming language. The functions are called from PL/SQL using an alias name that maps the name of the library—in the case of ST_Geometry in Oracle, st_shapelib—to the name of the library file. (See the Oracle documentation for the CREATE LIBRARY command for details.) The first time a spatial type function is called that requires st_shapelib, the database requests the listener to spawn an extproc process for the SQL session. The extproc is given the location of st_shapelib, the name of the function to be called, and its parameters. The extproc loads st_shapelib and calls the function. When the external function completes, the extproc returns the results and remains active, waiting for additional function calls during this session. The extproc process terminates when the SQL session disconnects.
This requires the following configuration:
- The database needs to know the location of the file containing st_shapelib so it can pass this information to the listener process and on to the extproc.
- If the file_spec for the st_shapelib in the user_libraries table does not match the physical file location of the st_shapelib on the server, ST_Geometry operators and functions will not work. Therefore, you must update the library definition in the Oracle user libraries to include the correct library path to the file containing st_shapelib using the CREATE LIBRARY command.
- The database must know of the service that handles requests to the extproc. This is configured in the tnsnames.ora file.
- The extproc must be allowed to load the file containing st_shapelib. This is done by defining the environment variable EXTPROC_DLLS in the extproc.ora file.
- The extproc (usually running as the user who owns ORACLE_HOME) must have read permissions on the location of the library files and execute permissions on the files.
The st_geometry library must be stored in the PostgreSQL installation directory to allow PostgreSQL to access the ST_Geometry functions. You must copy the st_geometry.dll (Windows) file to the lib folder in the PostgreSQL installation directory. For Linux servers, copy the st_geometry.so (Linux) file to the /usr/lib/pgsql directory on the PostgreSQL server. Both of these files can be found in the DatabaseSupport folder of ArcMap or ArcGIS Server installations, or downloaded from My Esri.
You must designate the location of and load the ST_Geometry library so the SQLite client can access the SQL functions. You can set the library location in the client's PATH (Windows) or LIBPATH (Linux) system environment variable then load the library, or you can specify the path when you load ST_Geometry library. See Load the SQLite ST_Geometry library for instructions.
How is ST_Geometry implemented?
ST_Geometry is implemented as a user-defined type that can be used in databases and geodatabases in IBM Db2, IBM Informix, Oracle, PostgreSQL, and SAP HANA. ST_Geometry also can be used in SQLite, Dameng, and IBM Netezza databases.
Each database management system supports ST_Geometry as follows:
ST_Geometry by database
|Database management system||Description|
The ST_Geometry type is included with the Dameng database but you must initialize it.
The Db2 Spatial Extender, included by IBM as part of the Db2 product by default, provides ST_Geometry support. The ST_Geometry type was codeveloped by Esri and IBM.
Be aware that there are some differences in SQL syntax between Db2 for z/OS and Db2. Consult the Db2 documentation for proper syntax for the Db2 version you are using.
The Informix Spatial DataBlade, provided by IBM as part of the Informix product, installs the ST_Geometry type in the database and geodatabase. ST_Geometry is the only option used to store feature geometry in geodatabases in Informix. It was codeveloped by Esri and IBM and is a high-performance storage type that provides ISO- and OGC-compliant SQL access to spatial data.
The ST_Geometry type in Netezza is implemented through the Netezza Spatial Esri library (nzSpatial_Esri), which is part of the IBM Netezza Analytics software package.
The ST_Geometry data type is installed when you create a geodatabase in Oracle and is the default geometry storage type for geodatabases in Oracle. If you do not need geodatabase functionality, you can install the ST_Geometry type in an Oracle database using the Create Spatial Type geoprocessing tool.
The ST_Geometry data type is installed when you create a geodatabase in PostgreSQL and is the default type for storing feature geometry in geodatabases in PostgreSQL. If you do not need geodatabase functionality, you can install the ST_Geometry type in a PostgreSQL database using the Create Spatial Type geoprocessing tool.
The SAP HANA data warehouse installation includes the ST_Geometry type by default.
The ST_Geometry data type in SQLite is implemented using an st_geometry library and a geometryblob column. The ST_Geometry SQL functions are stored in the st_geometry library rather than the database.
You can use the Create SQLite Database geoprocessing tool to create an SQLite database that contains the ST_Geometry type. Alternatively, you can use the CreateOGCTables SQL function to create the ST_Geometry data type in an existing SQLite database.