st_geometry is a user defined type of the Oracle database. A user defined type in Oracle is like a class definition in .Net. It allows you to create a type with properties and methods. It also supports inheritance, which in st_geometry is used to define an object hierarchy:
As you can see from the diagram,
st_geometry is in fact an abstract class. Which means that in order to create an "instance" of the class you need to use one of the inheritors.
st_geometry’s user defined type is defined inside the SDE schema in your database. Once you’ve installed SDE on your database you can connect to your sde schema and view
st_geometry and all of his inheritors.
This is an example of a table which contains a
CREATE TABLE ( code NUMBER(10), name VARCHAR2(200) shape st_geometry );
In practice user defined types are saved as BLOB. This means that when you define a column with a
st_geometry type, and try to query this column, you will see a BLOB.
You can create an "instance" of
st_geometry be using a constructor (yes, a constructor!):
INSERT INTO buildings(code,name,shape) VALUES(buildings_s.NEXTVAL,"Doron Plaza",st_point(10,10,1));
This query will create a new row for the building “Doron Plaza” which is positioned in (10,10). The third parameter in the
st_point constructor represents a spatial reference index, but we will discuss this later.
Properties are defined in the type definition. That means that you can access the properties like you use properties in C#:
SELECT b.name,b.shape.numpts FROM buildings;
This query will retrieve the number of points in the building’s shape.
In st_geometry introduction , I showed the following example:
SELECT * FROM buildings b WHERE sde.st_intersects(b.shape,st_point('POINT (35.122 32.999)',1)) = 1;
This statement uses an operator which returns 1, if the given shapes intersects (It actually returns a
st_geometry instance of the intersection).
If you look in the type definition, you will find a reference of the operator to a package called ST_GEOMETRY_SHAPELIB_PKG. This package defines an external procedure call. This means that the actual processing of the data is done out of the Oracle process with an external library – ST_SHAPELIB.
Simply, this means that before you can start using
st_geometry in SQL, you need to configure Oracle to "know" were this external library is. I will discuss this in st_geometry configuration.
This also means, that
st_geometry functions and operators, are slower then internal Oracle procedures because there's a context switch inside the DB server.
You can find a full list of all the operators available for
st_geometry in ESRI's documentation.
As you well know, each layer is defined with a spatial reference.
st_geometry columns contains a property which contains a number that represents a spatial reference. Notice, that number is NOT the Well-Known Id of the spatial reference.
So, what is this number?
This number is an index of a row in a table called
If you never used
st_geometry in your database and you query this table, you will see that the table is actually empty.
Why? I have no idea, but in order to insert data in an
st_geometry column, you need to fill this table.
The quickest way we found to do it is to create a dummy table using ArcCatalog with an
st_geometry column (you do that by creating a feature layer and selecting the
st_geometry configuration keyword) and the spatial reference you want to use. After you do that, you can query the stspatialreferences table and you can see a new row with your spatial reference.
You can also notice there's a column in this table – CSID which contains the well-known id of the spatial reference.
You can now use the index of this row to insert data to the table, like I presented in the example above.
Another thing you need to notice is that the spatial reference definition is done when creating a new row, not when creating the table.
That means the the same table can have rows with different spatial references.
Well that was a quick overview of the important subject concerning
In the next post I will show you how to configure
st_geometry, so you can use it in simple sql queries.