FAQ: What Are the Best Practices for Writing SQL in Oracle with ST

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

Article ID:000009699

Software:

ArcMap 9 x
Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

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

Answer

Discover more on this topic