Update Triggers and SDO_GEOMETRY Equality

It is not uncommon to want to construct BEFORE INSERT row level triggers that detect and process changes to SDO_GEOMETRY columns and assign other column values. However, the rules that govern the comparison of objects such as numbers, dates, strings etc do not apply to complex objects such as SDO_GEOMETRY. As such, to correctly detect changes to such objects one must have recourse to different techniques.

This blog articles offers a few examples of what can be done and needs to be done.

This article is based on the problem outlined in this Oracle Spatial forum thread to which I responded.

The problem that we need to solve is set the GEOM_MOD_DATE column with a value only when a change to the GEOM SDO_GEOMETRY column is detected by the trigger. The processing is to be done via a trigger so that we get a single, consistent, approach to detecting changed data from multiple client (vendor) software processing.

The table we shall process is the following:

 DROP TABLE oper_zone_valve;
 --
 CREATE TABLE oper_valve
 (
   oper_valve_id     NUMBER (38),
   valve_number      VARCHAR2 (8 BYTE),
   valve_size        VARCHAR2 (4 BYTE),
   geom_mod_date     DATE,
   geom              MDSYS.sdo_geometry
 );

Comparing SDO_GEOMETRY objects

The SDO_GEOMETRY object does not have a ORDER MEMBER method that compares two SDO_GEOMETRY objects. However, if you have a Spatial license, you do have recourse to the SDO_GEOM.RELATE function. Using this we can construct a BEFORE UPDATE function as follows:

 CREATE OR REPLACE TRIGGER oper_valve_geom_but
 BEFORE UPDATE OF geom
 ON oper_valve
 REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
 BEGIN
    -- If not updates have been detected against the GEOM column, short-circuit the test
    IF ( NOT UPDATING('GEOM') ) THEN
     RETURN;
   END IF;
   -- Only need to compare actual geom data if both are not null.
   IF ( :OLD.geom IS NOT NULL AND :NEW.geom IS NOT NULL ) THEN
     -- Check if geometry has changed internally
      IF ( sdo_geom.relate(:OLD.geom,'DETERMINE',:NEW.geom,0.005) != 'EQUAL' ) THEN
        :NEW.geom_mod_date := SYSDATE;
      END IF;
   ELSIF ( ( :OLD.geom IS NULL AND :NEW.geom IS NOT NULL ) OR
        ( :OLD.geom IS NOT NULL AND :NEW.geom IS NULL ) ) THEN
      :NEW.geom_mod_date := SYSDATE;
   ELSE
     -- NULL obviously does not equal NOT NULL!
     :NEW.geom_mod_date := NULL;
   END IF;
 END;
 /
 SHOW ERRORS

Now, we can test this.

 SET NULL NULL
 SET serveroutput ON SIZE unlimited
 --.
 INSERT INTO oper_valve (oper_valve_id,geom) VALUES (1, MDSYS.sdo_geometry(2001,NULL,SDO_POINT_TYPE(0,0,0), NULL,NULL));
 --.
 1 ROWS inserted.
 --.
 COMMIT;
 --.
 commited.
 --.
 SELECT oper_valve_id, to_char(geom_mod_date,'YYYY-MM-DD HH24:MI:SS') AS geom_mod_date, geom
   FROM oper_valve;
 OPER_VALVE_ID GEOM_MOD_DATE       GEOM
 ------------- ------------------- --------------------------------------------------------------------
             1 NULL                MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(0,0,0),NULL,NULL)
 --.
 UPDATE oper_valve
    SET geom = sdo_geometry(2001, NULL,SDO_POINT_TYPE(0,0,0),NULL,NULL) WHERE oper_valve_id = 1;
 --.
 1 ROWS updated.
 --.
 COMMIT;
 --.
 commited.
 --.
 SELECT oper_valve_id, to_char(geom_mod_date,'YYYY-MM-DD HH24:MI:SS') AS geom_mod_date, geom
   FROM oper_valve;
 --.
 OPER_VALVE_ID GEOM_MOD_DATE       GEOM
 ------------- ------------------- --------------------------------------------------------------------
             1 NULL                MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(0,0,0),NULL,NULL)
 --.
 UPDATE oper_valve
    SET geom = sdo_geometry(2001, NULL,SDO_POINT_TYPE(10,0,0),NULL,NULL)
  WHERE oper_valve_id = 1;
 --.
 1 ROWS updated.
 --.
 COMMIT;
 --.
 commited.
 --.
 SELECT oper_valve_id,  to_char(geom_mod_date,'YYYY-MM-DD HH24:MI:SS') AS geom_mod_date, geom
   FROM oper_valve;
 --.
 OPER_VALVE_ID GEOM_MOD_DATE       GEOM
 ------------- ------------------- --------------------------------------------------------------------
             1 2012-11-12 09:49:16 MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(10,0,0),NULL,NULL)
 --.
 EXECUTE dbms_lock.sleep(5);
 --.
 anonymous block completed
 --.
 UPDATE oper_valve
    SET geom = NULL
  WHERE oper_valve_id = 1;
 --.
 1 ROWS updated.
 --.
 COMMIT;
 --.
 commited.
 --.
 SELECT oper_valve_id,  to_char(geom_mod_date,'YYYY-MM-DD HH24:MI:SS') AS geom_mod_date, geom
   FROM oper_valve;
 --.
 OPER_VALVE_ID GEOM_MOD_DATE       GEOM
 ------------- ------------------- --------------------------------------------------------------------
             1 2012-11-12 09:49:21 NULL

As you can see, the processing is correct.

WHEN clause

The UPDATE TRIGGER syntax includes the possibility of including a WHEN clause in the trigger header that allows for a more declarative style for controlling processing.

 CREATE OR REPLACE TRIGGER valve_geom_but
 BEFORE UPDATE OF geom
 ON oper_valve
 REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
 WHEN ( ( OLD.geom IS NOT NULL
      AND NEW.geom IS NOT NULL
      AND sdo_geom.relate(OLD.geom,'DETERMINE',NEW.geom,0.005)<>'EQUAL' )
   OR ( ( OLD.geom IS NULL     AND NEW.geom IS NOT NULL ) OR
        ( OLD.geom IS NOT NULL AND NEW.geom IS NULL )
      ) )
 BEGIN
   :NEW.geom_mod_date := SYSDATE;
 END;
 /
 SHOW ERRORS

As you can see this looks really “clean”.

Sadly, though, it doesn’t work:

 -- Results
 --
 Error report:
 ORA-04076: invalid NEW OR OLD specification
 04076. 00000 -  "invalid NEW or OLD specification"
 *Cause:    An invalid NEW OR OLD specification was given FOR a COLUMN.
 *Action:   Re-specify the COLUMN USING the correct NEW OR OLD specification.

Actually, this error is not about the NEW/OLD specification clause but about the WHEN clause as can be seen in the following:

 CREATE OR REPLACE TRIGGER valve_geom_but
 BEFORE UPDATE OF geom
 ON oper_valve
 REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
 WHEN ( NOT ( OLD.geom IS NULL AND NEW.geom IS NULL ) )
 BEGIN
   IF ( :OLD.geom IS NOT NULL AND :NEW.geom IS NOT NULL ) THEN
     IF ( sdo_geom.relate(:OLD.geom,'DETERMINE',:NEW.geom,0.005)!='EQUAL' ) THEN
        :NEW.geom_mod_date := SYSDATE;
     END IF;
   ELSE
      :NEW.geom_mod_date := SYSDATE;
   END IF;
 END;
 /
 SHOW ERRORS
 -- Result
 --
 TRIGGER VALVE_GEOM_BUT compiled
 No Errors.

Again, the tests are correct.

Note: we cannot use a function from an external package in our WHEN clause.

No Spatial?

OK, so I hear you saying: “We only have Locator: we are not licensed for Spatial. What can we do?”

There are things that can be done.

JTS Relate

You could consider installing my Spatial Companion For Oracle (SC4O) package which contains the Java Topology Suite open source implementation of RELATE. That function can be seen here on my web site, with full package documentation here.

I leave it to you, dear reader, to replace the lines containing SDO_GEOM.RELATE with SC4O.ST_RELATE (there is a slight difference in what is returned so the comparison in the IF statement needs changing).

PL/SQL Function

We have a very simple SDO_GEOMETRY test: Is one SDO_GEOMETRY equal to another? As such this test can be coded reasonably simply via a PL/SQL function as follows.

 -- Because this code uses the ability of Oracle to compare two nested tables we need a nested table into which to CAST the VARRAYs that make up the SDO_GEOMETRY SDO_ELEM_INFO_ARRAY and SDO_ORDINATE_ARRAY
 --
 CREATE OR REPLACE TYPE T_Numbers AS TABLE OF NUMBER;
 /
 SHOW errors
 TYPE compiled
 -- Now create function
 --
 CREATE OR REPLACE
 FUNCTION ST_Equal(p_geom1 IN sdo_geometry,
                   p_geom2 IN sdo_geometry)
 RETURN varchar2
 AUTHID CURRENT_USER
 AS
    v_ok varchar2(10);
    v_vertex1 mdsys.vertex_type;
    v_vertex2 mdsys.vertex_type;
 BEGIN
   IF (    p_geom1.get_gtype() != p_geom2.get_gtype() ) THEN
      -- Is:
      -- Point != Multipoint with same point?
      -- Line  != MultiLine  with exactly same points?
      -- Poly  != MultiPoly  with exactly same points? 
      -- For simplicity we say yes.
      -- If not, change this to suite your processing.
      RETURN 'FALSE';
   ElsIf (    p_geom1.sdo_elem_info IS NOT NULL
          AND p_geom2.sdo_elem_info IS NOT NULL
          AND p_geom1.sdo_ordinates IS NOT NULL
          AND p_geom2.sdo_ordinates IS NOT NULL) THEN
      SELECT CASE WHEN ( CAST(p_geom1.sdo_elem_info AS codesys.t_numbers) =
                         CAST(p_geom2.sdo_elem_info AS codesys.t_numbers)
                         AND
                         CAST(p_geom1.sdo_ordinates AS codesys.t_numbers) =
                         CAST(p_geom2.sdo_ordinates AS codesys.t_numbers) )
                  THEN 'TRUE'
                  ELSE 'FALSE'
              END AS equals
        INTO v_ok
        FROM dual;
     RETURN v_ok;
   ElsIf ( p_geom1.get_gtype() = 1 AND p_geom2.get_gtype() = 1 ) THEN
      -- Both points may be coded in sdo_point or one in sdo_ordinates etc
      v_vertex1 := sdo_util.getVertices(p_geom1)(1);
      v_vertex2 := sdo_util.getVertices(p_geom2)(1);
      IF ( v_vertex1.x = v_vertex2.x AND
           v_vertex1.y = v_vertex2.y AND
           NVL(v_vertex1.z,-999999999999999999) = NVL(v_vertex2.z,-999999999999999999) ) THEN
         RETURN 'TRUE';
      ELSE
        RETURN 'FALSE';
      END IF;
   ELSE
      RETURN 'FALSE';
   END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
          RETURN 'TRUE';
 END ST_Equal;
 /
 SHOW ERRORS
 -- Results
 --
 FUNCTION ST_EQUAL compiled
 No Errors.

I will not present all the tests I used to check this function except for the following:

 SELECT ST_equal(SDO_GEOMETRY(2001,NULL,sdo_point_type(0,1,NULL),NULL,NULL),
                 SDO_GEOMETRY(2001,NULL,sdo_point_type(0,1,NULL),NULL,NULL)) AS equal
  FROM dual;
 --Results
 --
 EQUAL
 -----
 TRUE
 --
 SELECT ST_equal(SDO_GEOMETRY(2001,NULL,sdo_point_type(0,1,2),NULL,NULL),
                 SDO_GEOMETRY(2001,NULL,sdo_point_type(0,1,NULL),NULL,NULL)) AS equal
  FROM dual;
 --Results
 --
 EQUAL
 -----
 FALSE
 --
 SELECT ST_equal(SDO_GEOMETRY(2001,NULL,sdo_point_type(0,1,NULL),NULL,NULL),
                 SDO_GEOMETRY(2001,NULL,NULL,sdo_elem_info_array(1,1,1),sdo_ordinate_array(0,1))) AS equal
  FROM dual;
 --Results
 --
 EQUAL
 -----
 TRUE
 --
 SELECT ST_equal(SDO_GEOMETRY(2001,NULL,sdo_point_type(0,1,NULL),NULL,NULL),
                 SDO_GEOMETRY(2002,NULL,NULL,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,1,2,3))) AS equal
  FROM dual;
 --Results
 --
 EQUAL
 -----
 FALSE
 --
 SELECT ST_equal(SDO_GEOMETRY(2002,NULL,NULL,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,1,2,3)),
                 SDO_GEOMETRY(2002,NULL,NULL,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,1,2,3))) AS equal
  FROM dual;
 --Results
 --
 EQUAL
 -----
 TRUE
 --
 SELECT ST_equal(SDO_GEOMETRY(2002,NULL,NULL,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,1,2,3)),
                 SDO_GEOMETRY(2003,NULL,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(0,1,2,3))) AS equal
   FROM dual;
 --Results
 --
 EQUAL
 -----
 FALSE
 --
 SELECT ST_equal(SDO_GEOMETRY(2003,NULL,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(0,1,2,3)),
                 SDO_GEOMETRY(2003,NULL,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(0,1,2,3))) AS equal
   FROM dual;
 --Results
 --
 EQUAL
 -----
 TRUE

Now we can use it in our trigger as follows.

 CREATE OR REPLACE TRIGGER valve_geom_but
 BEFORE UPDATE OF geom
 ON oper_valve
 REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
 WHEN ( NOT ( OLD.geom IS NULL AND NEW.geom IS NULL ) )
 BEGIN
   IF ( :OLD.geom IS NOT NULL AND :NEW.geom IS NOT NULL ) THEN
     IF ( ST_Equal(:OLD.geom,:NEW.geom)='FALSE' ) THEN
        :NEW.geom_mod_date := SYSDATE;
     END IF;
   ELSE
      :NEW.geom_mod_date := SYSDATE;
   END IF;
 END;
 /
 SHOW ERRORS
 -- Result
 --
 TRIGGER VALVE_GEOM_BUT compiled
 No Errors.

Again, the tests are correct.

I hope this is instructive and helpful for someone out there.

Leave a Reply

Your email address will not be published. Required fields are marked *