English

FAQ: What are the best practices for writing SQL in Oracle with ST_Geometry attributes?

Question

What are the best practices for writing SQL in Oracle with ST_Geometry attributes?

Answer

When creating Oracle tables with the spatial type, ST_Geometry or writing SQL statements that reference ST_Geometry constructors and operators, the best practice is to fully qualify the ownership of the ST_Geometry type, constructors, and operators.

Fully qualifying means to include the owner of the object when referencing the object, for example: sde.st_geometry. The owner of the ST_Geometry type is 'sde'.

Without fully qualifying the ST_Geometry type, constructor, or operator, there is potential for ambiguity if the public synonym for the object is not present in the Oracle instance.

The following examples demonstrate creating a table in SQL*Plus without fully qualifying the ST_Geometry attribute, the error encountered when the ST_Geometry attribute is not fully qualified, and successfully creating a table with a fully-qualified attribute:

SQL> CREATE TABLE rivers
2 (name VARCHAR2(32),
3 shape st_geometry);
shape st_geometry)
*
ERROR at line 3:
ORA-00902: invalid datatype

SQL> CREATE TABLE rivers
2 (name VARCHAR2(32),
3 shape sde.st_geometry);

Table created.

The following example demonstrates an error one might encounter when attempting to execute the ST_Intersects operator without fully qualifying the operator as 'sde.st_intersects'.
SQL> SELECT states.name FROM states, rivers
2 WHERE rivers.name = 'MISSOURI'
3 AND st_intersects(states.name, rivers.name) = 1;
AND st_intersects(states.name, rivers.name) = 1
*
ERROR at line 3:
ORA-00904: "ST_INTERSECTS": invalid identifier