Go to content Go to navigation and search

Current Oracle Spatial Blogs

Search

RSS / Atom

Email me

textpattern

Tip #2: layer_gtypes for spatial indexes · Nov 9, 10:21 pm by Simon Greener

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:

2001 POINT
2002 LINESTRING
2003 POLYGON
2004 COMPOUND
2005 MULTIPOINT
2006 MULTILINESTRING
2007 MULTIPOLYGON
2008 MULTICOMPOUND

(We will ignore 2004 and 2008 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=linestring’);

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=multilinestring’);

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 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.

  Textile Help

<<Tip #3: Generating sample (test) point data >>Oracle Spatial Forum - Melbourne April 2007