## Top 5 Recent Articles

##### ARTICLES CATEGORIES

- Biography (1)
- Blog (41)
- Changes (1)
- Customers (1)
- Data Models (1)
- Education (2)
- General Software (21)
- Georaptor Blog (5)
- Image Catalog (2)
- Licensing (1)
- ManifoldGIS (3)
- MySQL Blog (4)
- MySQL Spatial (3)
- Networking and Routing (including Optimization) (3)
- Oracle Spatial (171)
- Philosophy (1)
- PostGIS (30)
- Press Releases (1)
- Source code (24)
- Space Curves (1)
- Spatial DB comparison (1)
- SQL (1)
- SQL Server Blog (58)
- SQL Server Spatial (General) (15)
- SQL Server Spatial (LRS) (37)
- Stored Procedure (2)
- Training (1)
- XML (5)

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

## Documentation

- MySQL Spatial General Function Documentation
- Oracle Spatial Exporter Package Documentation
- Oracle Spatial Object Function Documentation
- Oracle Spatial Object Function Documentation (Multi Page Version)
- PostGIS pl/pgSQL Function Documentation
- SC4O Oracle Java Topology Suite (Stored Procedures) Package Documentation
- SQL Server Spatial General TSQL Function Documentation
- SQL Server Spatial LRS TSQL Function Documentation