ST_Geometry function calls

ST_Geometry functions in Oracle and PostgreSQL are implemented in structured query language (SQL). This is a database-level language. In Oracle, it is Procedural Language Structured Query Language (PL/SQL). In PostgreSQL, it is PL/pgSQL.

When you access ST_Geometry columns using SQL functions, you are accessing the database directly. Therefore, the database must be able to access the ST_Geometry functions. In Oracle, the database also spawns an extproc process.


The st_geometry library must be stored in the PostgreSQL installation directory to allow PostgreSQL to access the ST_Geometry functions. If you install ArcSDE on the same Windows server as PostgreSQL using the ArcSDE for PostgreSQL installation wizard, the st_geometry.dll is copied to the PostgreSQL lib folder for you. If your database is on a different server than ArcSDE or you are installing on a Linux server, you must manually copy the library to the PostgreSQL installation directory. On Linux servers, copy the library to the /usr/lib/pgsql directory.


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 documentation for the Oracle 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.

To make this work, the following configuration is needed: