YsA.Net

So, 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 infect 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 UDT 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);

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 are more complicated.

in my previous post, I showed this example:

select * from buildings b   

where sde.st_intersects(b.shape,st_point('POINT (35.122 32.999)',1)) = 1;

In here we use 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 my next post.

This also means, that st_geometry functions and operators, are slower then internal oracle procedure because there is a context switch inside the db server.

You can find a full list of all the operators available for st_geometry here.

Now, lets talk about 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 is 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 feature, not when creating the table. That means the the same table can have feature in different spatial references.

Well that was a quick overview of the important subject concerning st_geometry. In my next post I will show you how to configure st_geometry, so you can use it in simple sql queries.