HOW TO

Leverage st_geometry operators in Oracle's PL/SQL

Last Published: April 19, 2022

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;
/
 

    Article ID: 000010459

    Software:
    • Legacy Products

    Receive notifications and find solutions for new or common issues

    Get summarized answers and video solutions from our new AI chatbot.

    Download the Esri Support App

    Discover more on this topic

    Get help from ArcGIS experts

    Contact technical support

    Download the Esri Support App

    Go to download options