English

How To: 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.

Code:
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.

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