English

FAQ: Should a property of an st_geometry attribute in Oracle be updated with SQL?

Question

Should a property of an st_geometry attribute in Oracle be updated with SQL?

Answer

No, directly updating st_geometry attributes can lead to data inconsistencies.

For example, one can query the value of the st_geometry attribute area for a specific row,

Code:
SQL> SELECT a.shape.area AREA
2 FROM us_states a
3 WHERE name = 'Florida';

AREA
----------
13.43519


or, one could use the operator st_area to return the same value.

Code:
SQL> SELECT sde.st_area(shape)
2 FROM us_states
3 WHERE name = 'Florida';

SDE.ST_AREA(SHAPE)
------------------
13.43519


Oracle allows one to directly update a property of a type, but the user needs to understand the consequences. Because one is bypassing the type implementation and updating the property value, there are no data integrity checks being performed (which is the responsibility of the st_geometry constructors to ensure all properties of the geometry being created or updated are valid).

The following demonstrates updating the st_geometry area attribute directly, but it is strongly discouraged.

Code:
SQL> UPDATE us_states a
2 SET a.shape.area = 1
3 WHERE name = 'Florida';

1 row updated.


Now, when using the st_area operator, the value returned by the query reflects the value that was updated in the previous statement.

Code:
SQL> SELECT sde.st_area(shape)
2 FROM us_states
3 WHERE name = 'Florida';

SDE.ST_AREA(SHAPE)
------------------
1


The current value for the area stored in the database will be updated by the constructor function when the area is recalculated. This new value will then replace the value, which is currently stored in the database.

The next time the geometry is updated, the value set (when using SQL) will be overwritten by the new calculated value.