Leverage st_geometry operators in Oracle's PL/SQL

Summary

Instructions provided describe how to leverage st_geometry operators in Oracle's PL/SQL (stored procedures, triggers, etc.).

Because the st_geometry functions are exposed as database operators, and typically consumed by way of SQL statements, leveraging the st_geometry functionality with PL/SQL requires referencing the underlying functions by fully qualifying their appropriate package and function name.

Procedure

For example, to leverage the sde st_astext operator in PL/SQL, the call must reference the fully qualified package where the function resides. The following line of code demonstrates setting a local variable of type CLOB to the output of the st_astext function.

local_geometry_wkt := sde.st_geometry_operators.st_astext_f(in_st_geom_feature);

All geometry operators, such as st_astext, st_centroid, st_numgeometries, etc. reside in the st_geometry_operators package. All relational operators, such as st_intersects, st_within, st_contains, etc. reside in the st_relation_operators package.

Note:
When referencing any st_* operator as a function, remember to include the _f clause after its name to reference the appropriate function in the package body.

The following example demonstrates creating an Oracle function to return a boolean if an input geometry intersects another table's st_geometry attribute. The function provides two examples of calls performing an st_buffer and st_intersects.

CREATE OR REPLACE FUNCTION lightning_strike_f (in_shape sde.st_geometry)
 RETURN BOOLEAN IS

  TYPE env_int_cur IS REF CURSOR;
  env_cur          env_int_cur;

  temp_shape       sde.st_geometry := sde.st_geometry(0,0,0,0,0,0,0,0,0,0,0,0,0,empty_blob());
  buffered_shape   sde.st_geometry := sde.st_geometry(0,0,0,0,0,0,0,0,0,0,0,0,0,empty_blob());
 
  entity_tab    dbms_sql.number_table;
  numpts_tab    dbms_sql.number_table;
  srid_tab      dbms_sql.number_table;
  minx_tab      dbms_sql.number_table;
  miny_tab      dbms_sql.number_table;
  maxx_tab      dbms_sql.number_table;
  maxy_tab      dbms_sql.number_table;
  points_tab    dbms_sql.blob_table;

  does_intersect BOOLEAN DEFAULT FALSE;

BEGIN

  buffered_shape := sde.st_geometry_operators.st_buffer_f(in_shape,10);

  OPEN env_cur FOR 'SELECT p.shape.entity, p.shape.numpts, p.shape.minx, p.shape.miny, p.shape.maxx,
   p.shape.maxy, p.shape.srid, p.shape.points FROM sewer p
   WHERE sde.st_envintersects(p.shape,'||buffered_shape.minx||','||buffered_shape.miny||',
   '||buffered_shape.maxx||','||buffered_shape.maxy||') = 1';
 
  FETCH env_cur BULK COLLECT INTO entity_tab,numpts_tab,minx_tab,miny_tab,
   maxx_tab,maxy_tab,srid_tab,points_tab;

  IF entity_tab.COUNT = 0 THEN
    does_intersect := FALSE;
   ELSE
    FOR i IN 1..entity_tab.COUNT LOOP

     temp_shape.entity := entity_tab(i);
     temp_shape.numpts := numpts_tab(i);
     temp_shape.minx := minx_tab(i);
     temp_shape.miny := miny_tab(i);
     temp_shape.maxx := maxx_tab(i);
     temp_shape.maxy := maxy_tab(i);
     temp_shape.minz := 0;
     temp_shape.maxz := 0;
     temp_shape.minm := 0;
     temp_shape.maxm := 0;
     temp_shape.area := 0;
     temp_shape.len := 0;
     temp_shape.srid := srid_tab(i);
     temp_shape.points := points_tab(i);

     IF sde.st_relation_operators.st_intersects_f(buffered_shape,temp_shape) = 1 THEN
       does_intersect := TRUE;
       EXIT;
     END IF;
           
    END LOOP;
  END IF;

  RETURN does_intersect;

END;
/