Layer_GTypes for spatial indexes

It is common practice, when storing single point data in an sdo_geometry to always use its SDO_POINT structure and not the SDO_ORDINATES array.

When indexing point data, for performance reasons, the use of the layer_gtype=point parameter is always recommended.

So, if I have sdo_geometry objects constructed as follows:

MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(1,2,NULL),NULL,NULL)

inside a table/column (say called “my_points”/”geometry”), then the correct way to index this data is as follows:

CREATE INDEX my_points_geometry ON my_points(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS(‘sdo_indx_dims=2, layer_gtype=point‘);

But what about the other, principle, geometry types?

What are these geometry types?

They are (see Table 2-1 Valid SDO_GTYPE Values in Oracle Spatial 10gR2 documentation):

dl01 POINT
dl02 LINE or CURVE
dl03 POLYGON
dl04 COLLECTION
dl05 MULTIPOINT
dl06 MULTILINE or MULTICURVE
dl07 MULTIPOLYGON

The documentation says:

 The SDO_GTYPE value is 4 digits in the format dltt, where:

• d identifies the number of dimensions (2, 3, or 4)
• l identifies the linear referencing measure dimension for a three-dimensional linear referencing system (LRS) geometry, that is, which dimension (3 or 4) contains the measure value. For a non-LRS geometry, or to accept the Spatial default of the last dimension as the measure for an LRS geometry, specify 0. For information about the linear referencing system (LRS), see Chapter 7.
• tt identifies the geometry type (00 through 07, with 08 through 99 reserved for future use).

(We will ignore dl04 in this Tip.)

We have dealt with 2001. What about the others? Can we set a layer_gtype for them as well in the index parameters?

We certainly can and, in fact, I recommend that you do.

So, if we knew our table only had 2002 objects in it the index creation statement would be:

CREATE INDEX my_points_geometry ON my_points(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS(‘sdo_indx_dims=2, layer_gtype=line‘);

Where 2003 only:

CREATE INDEX my_points_geometry ON my_points(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS(‘sdo_indx_dims=2, layer_gtype=polygon‘);

Where 2005 only exists or a mix of 2001 and 2005 objects then the index creation statement would be:

CREATE INDEX my_points_geometry ON my_points(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS(‘sdo_indx_dims=2, layer_gtype=multipoint‘);

Similarly for 2006:

CREATE INDEX my_points_geometry ON my_points(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS(‘sdo_indx_dims=2, layer_gtype=multiline‘);

And 2007:

CREATE INDEX my_points_geometry ON my_points(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS(‘sdo_indx_dims=2, layer_gtype=multipolygon‘);

A side effect of doing this is that if you attempt to insert an object with a different gtype from that declared in the create index statement you will get an ORA-13375 error. This is a nice way of enforcing geometry integrity for a table/column in lieu of their not being SDO_POINT, SDO_LINESTRING and SDO_POLYGON etc geometry types which could be used in the original CREATE TABLE statement (if you need to do something like this, consider using Oracle’s SQL3/MM ISO data types: ST_POINT etc).

I hope you found this useful.

All the principles of good RTree index design are encapsulated in the TOOLS package in my free PL/SQL code.