How does st_geometry works?
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 st_geometry column:
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.
Operators
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.
Spatial Reference
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 sde.st_spatial_references.
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 st_spatial_references 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 st_geometry.
In the next post I will show you how to configure st_geometry, so you can use it in simple sql queries.