How To: Index St_geometry Area and Len Attributes in Oracle

Summary

Instructions provided describe how to index the st_geometry area and length attributes in Oracle to improve the performance of SQL statements that use the area or length attributes as a predicate filter.

Note:
The len attribute represents the geometry's length value.

For example, a SQL statement might be written to discover all forest stands with an area greater than 100000.

Code:
SELECT name, species, sde.st_area(shape) AS AREA 
FROM forest_stands
WHERE sde.st_area(shape) > 100000

To satisfy the filter where st_area (shape) > 100000 the database must evaluate every shape and pass it to the st_area() function. This can become an expensive operation based on the number of evaluations that must be performed. In addition, for those objects that meet the criteria of the predicate, the additional call to st_area (shape) in the SELECT list must be performed again for each row.

Alternatively, a similar SQL statement could be written that queries the st_geometry area attribute directly.

Code:
SELECT name, species, a.shape.area AS AREA 
FROM forest_stands a
WHERE a.shape.area > 100000

To satisfy the filter, the database scans the st_geometry area attribute, searching for each value that meets the filter's condition and in the SELECT list, reports the value directly verses calling the st_area() function.

To further improve performance, if the st_geometry area or length attribute is indexed, the database can leverage the index verses scanning every attribute value.

Procedure

To create an index in Oracle on a table's st_geometry's area or length attribute, connect in SQL*Plus as the data owner and execute the CREATE INDEX statement.

The following examples demonstrate creating indexes on the forest_stands table's shape.area and shape.len attributes. In this example, the name of the forest_stands st_geometry attribute is shape.
```
Code:
SQL> CREATE INDEX forest_area ON forest_stands (shape.area);

Index created.
```
The same syntax would be used to create an index on the length attribute.
```
Code:
CREATE INDEX forest_len ON forest_stands (shape.len);

Index created.
```

Article ID:000010284

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Index st_geometry area and len attributes in Oracle

Summary

Procedure

Discover more on this topic