The Power of Constraints and Indexes for Spatial Constraints: stopping duplicate points
Over on Technet , the following question was asked:
My question is, can you think of a way to recognize situations like this and merge and store them in a table before, we attempt mapping and spatial queries are performed?
The following database level approach is one approach for ensuring any loader doesn’t load duplicate points.
However, the following does not actually round the data to the desired precision only checks two points at the chosen precision.
If you use a loader it will have to be modified to ignore the thrown unique constraint error and, if you so wish, to ROUND the sdo_point x and y ordinates to the desired precision before loading.
If you want to round the ordinates at the database level then use a before insert/update trigger.
Note: Results of each command are in line.
CREATE TABLE uniquePointTest ( id NUMBER, geom MDSYS.SDO_GEOMETRY ); TABLE UNIQUEPOINTTEST created. INSERT ALL INTO uniquePointTest(id,geom) VALUES(1,MDSYS.SDO_GEOMETRY(2001, 8311, MDSYS.SDO_POINT_TYPE(129.25880839,-11.06104478,NULL), NULL, NULL)) INTO uniquePointTest(id,geom) VALUES(2,MDSYS.SDO_GEOMETRY(2001, 8311, MDSYS.SDO_POINT_TYPE(136.10806808,-21.74449855,NULL), NULL, NULL)) INTO uniquePointTest(id,geom) VALUES(3,MDSYS.SDO_GEOMETRY(2001, 8311, MDSYS.SDO_POINT_TYPE(124.89153144,-8.32585026,NULL), NULL, NULL)) INTO uniquePointTest(id,geom) VALUES(4,MDSYS.SDO_GEOMETRY(2001, 8311, MDSYS.SDO_POINT_TYPE(140.70952526,-13.80536285,NULL), NULL, NULL)) INTO uniquePointTest(id,geom) VALUES(5,MDSYS.SDO_GEOMETRY(2001, 8311, MDSYS.SDO_POINT_TYPE(123.26928618,-7.74539968,NULL), NULL, NULL)) INTO uniquePointTest(id,geom) VALUES(6,MDSYS.SDO_GEOMETRY(2001, 8311, MDSYS.SDO_POINT_TYPE(127.46297638,-24.47220787,NULL), NULL, NULL)) INTO uniquePointTest(id,geom) VALUES(7,MDSYS.SDO_GEOMETRY(2001, 8311, MDSYS.SDO_POINT_TYPE(129.66333892,-11.41542026,NULL), NULL, NULL)) INTO uniquePointTest(id,geom) VALUES(8,MDSYS.SDO_GEOMETRY(2001, 8311, MDSYS.SDO_POINT_TYPE(136.02501825,-36.36963598,NULL), NULL, NULL)) INTO uniquePointTest(id,geom) VALUES(9,MDSYS.SDO_GEOMETRY(2001, 8311, MDSYS.SDO_POINT_TYPE(139.00460108,-7.39859677,NULL), NULL, NULL)) SELECT * FROM DUAL; 9 ROWS inserted. COMMIT; commited. CREATE UNIQUE INDEX UniquePointTest_idx ON UniquePointTest(geom.sdo_point.x, geom.sdo_point.y); UNIQUE INDEX UNIQUEPOINTTEST_IDX created. INSERT INTO uniquePointTest(id,geom) VALUES(10,MDSYS.SDO_GEOMETRY(2001, 8311, MDSYS.SDO_POINT_TYPE(129.258808391,-11.061044781,NULL), NULL, NULL)); 1 ROWS inserted. COMMIT; commited. -- OK, but this value is actually wrong at an ordinate precision of 8. Fix the unique constraint. DELETE FROM UniquePointTest WHERE id = 10; 1 ROWS deleted. COMMIT; commited. DROP INDEX UniquePointTest_idx; INDEX UNIQUEPOINTTEST_IDX dropped. CREATE UNIQUE INDEX UniquePointTest_idx ON UniquePointTest(ROUND(geom.sdo_point.x,8), ROUND(geom.sdo_point.y,8)); UNIQUE INDEX UNIQUEPOINTTEST_IDX created. -- Insert again.... INSERT INTO uniquePointTest(id,geom) VALUES(10,MDSYS.SDO_GEOMETRY(2001, 8311, MDSYS.SDO_POINT_TYPE(129.258808391,-11.061044781,NULL), NULL, NULL)); Error starting at line 39 IN command: INSERT INTO uniquePointTest(id,geom) VALUES (10,MDSYS.SDO_GEOMETRY(2001, 8311, MDSYS.SDO_POINT_TYPE(129.258808391,-11.061044781,NULL), NULL, NULL)) Error report: SQL Error: ORA-00001: UNIQUE CONSTRAINT (CODESYS.UNIQUEPOINTTEST_IDX) violated 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE OR INSERT statement attempted TO INSERT a duplicate KEY. FOR TRUSTED Oracle configured IN DBMS MAC mode, you may see this message IF a duplicate entry EXISTS at a different level. *Action: Either remove the UNIQUE restriction OR do NOT INSERT the KEY. COMMIT; commited.
OK, but indexes are indexes. We should be able to do this via a unique table constraint, but, as we will see there are restrictions on the functions that can be used in the definition of the uniqueness clause.
-- ****************************************************** -- Try instead via a constraint -- -- Remove existing check unique index -- DROP INDEX UniquePointTest_idx; . INDEX UNIQUEPOINTTEST_IDX dropped. . -- Create constraint with no rounding -- ALTER TABLE UniquePointTest ADD CONSTRAINT UniquePointTest_Point_CK UNIQUE(geom.sdo_point.x, geom.sdo_point.y); . TABLE UNIQUEPOINTTEST altered. . -- Try with ROUND function (should not be allowed, sadly) -- ALTER TABLE UniquePointTest DROP CONSTRAINT UniquePointTest_Point_CK; . TABLE UNIQUEPOINTTEST altered. . ALTER TABLE UniquePointTest ADD CONSTRAINT UniquePointTest_Point_CK UNIQUE(ROUND(geom.sdo_point.x,8), ROUND(geom.sdo_point.y,8)); . Error starting at line 54 IN command: ALTER TABLE UniquePointTest ADD CONSTRAINT UniquePointTest_Point_CK UNIQUE(ROUND(geom.sdo_point.x,8), ROUND(geom.sdo_point.y,8)) Error report: SQL Error: ORA-00904: : invalid identifier . -- To use the preferred constraint method, we would have to ensure all ordinates are rounded via a trigger -- CREATE OR REPLACE TRIGGER UNIQUEPOINTTEST_POINT_BIU BEFORE INSERT OR UPDATE ON UNIQUEPOINTTEST REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF ( :NEW.geom IS NOT NULL AND :NEW.geom.sdo_point IS NOT NULL ) THEN :NEW.geom.sdo_point.x := ROUND(:NEW.geom.sdo_point.x,8); :NEW.geom.sdo_point.y := ROUND(:NEW.geom.sdo_point.y,8); END IF; END; / SHOW ERRORS TRIGGER UNIQUEPOINTTEST_POINT_BIU compiled . No Errors. . -- Add in, instead the unique constraint -- ALTER TABLE UniquePointTest ADD CONSTRAINT UniquePointTest_Point_CK UNIQUE(geom.sdo_point.x, geom.sdo_point.y); . TABLE UNIQUEPOINTTEST altered. . -- do the insert -- DELETE FROM UniquePointTest WHERE id = 10; . 0 ROWS deleted. . commit; . commited. . INSERT INTO uniquePointTest(id,geom) VALUES (10,MDSYS.SDO_GEOMETRY(2001, 8311, MDSYS.SDO_POINT_TYPE(129.258808391,-11.061044781,NULL), NULL, NULL)); Error starting at line 77 IN command: INSERT INTO uniquePointTest(id,geom) VALUES (10,MDSYS.SDO_GEOMETRY(2001, 8311, MDSYS.SDO_POINT_TYPE(129.258808391,-11.061044781,NULL), NULL, NULL)) Error report: SQL Error: ORA-00001: UNIQUE CONSTRAINT (CODESYS.UNIQUEPOINTTEST_POINT_CK) violated 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE OR INSERT statement attempted TO INSERT a duplicate KEY. FOR TRUSTED Oracle configured IN DBMS MAC mode, you may see this message IF a duplicate entry EXISTS at a different level. *Action: Either remove the UNIQUE restriction OR do NOT INSERT the KEY. . commit; . commited. . DROP TABLE uniquePointTest; . TABLE UNIQUEPOINTTEST dropped.
I hope this is of interest to others out there who, like me, don’t frequent the Oracle Spatial forum all that often.