Use an Oracle trigger on a table to update an st_geometry attribute

Summary

Database triggers provide the ability to generate or perform additional SQL during an event (such as an INSERT, UPDATE, or DELETE) on a table. Triggers can reference st_geometry attributes and perform feature validation or set attribute values.

Instructions provided describe how to create a trigger on a table containing x, y coordinate values to populate a st_geometry attribute each time a row is inserted.

Procedure

The steps demonstrate creating a table in SQL*Plus, thereby creating a trigger on the table, which sets an st_geometry value for each inserted row.

The scenario represents a table that records the position of ships using the x and y coordinate values and the current date. Each time a row is inserted into the table, the x and y coordinate values are used to generate an st_geometry (point) value.

1. Create the table to record each ship's location, the ship's unique identifier, the moment when the location is recorded and the x, y coordinate location.

SQL> CREATE TABLE ship_location
  2   (objectid    NUMBER(38) NOT NULL,
  3    ship_id     NUMBER NOT NULL,
  4    moment      DATE NOT NULL,
  5    x_latitude  INTEGER NOT NULL,
  6    y_latitude  INTEGER NOT NULL,
  7    shape       sde.st_geometry);

Table created.

2. Create the trigger on the table that sets the shape value for each row inserted into the table. The :NEW.shape represents the value of the st_geometry point based upon the x_latitude and y_latitude values. The value of 2 in the sde.st_geometry operator is the table's spatial reference.

SQL> CREATE OR REPLACE TRIGGER ship_loc_trig BEFORE INSERT ON
  2     ship_location FOR EACH ROW
  3    BEGIN
  4      
  5      :NEW.shape := sde.st_geometry('POINT('||:NEW.x_latitude||' '||:NEW.y_latitude||')',2);
  6
  7    EXCEPTION
  8      WHEN OTHERS THEN
  9      NULL;
 10    END;
 11    /

3. Insert a row into the ship_location table and verify the shape attribute is being correctly updated.

SQL> INSERT INTO ship_location VALUES
  2  (1,99,SYSDATE,15474942,5533109,NULL);

1 row created.

SQL> SELECT sde.st_astext(shape)
  2   FROM ship_location;

SDE.ST_ASTEXT(SHAPE)
---------------------------------
POINT (15474942.000, 5533109.000)