Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
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.