HOW TO
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.
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; /
Get help from ArcGIS experts
Download the Esri Support App