Is it feasible converting the spatial property (SDO_GEOMETRY) of a table in Oracle as an unique index or spatial constraint for avoiding spatial duplicates?
I am working on point tables and I don't want having spatial duplicates, I mean two points for the same location.
The question has several possible answers:
1) If you are looking only at avoid getting duplicate points into your table - and that means points with exactly the same coordinates, down to all possible decimals, and the points are using the SDO_POINT_TYPE construct, then yes, indeed, you can just define a unique index on the X and Y properties:
create unique index us_cities_unique on us_cities (location.sdo_point.x, location.sdo_point.y);
(This assuming that table US_CITIES has a column called LOCATION).
Then when you try and insert an object with the exact same coordinates as an existing one, you get the proper failure:
SQL> insert into us_cities (id, location) select 10001,location from us_cities where id=1;
insert into us_cities (id, location) select 10001,location from us_cities where id=1
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.US_CITIES_UNIQUE) violated
2) If on the other hand you want to avoid storing points with coordinates that can be slightly different but still point to the same location (= the two points are separated by just a few centimeters for example) or your shapes store the point coordinates in the SDO_ORDINATES structure or you want to generalize the problem of avoiding duplicate shapes to any shape (not just 2D or 3D points), then the problem becomes more complicated as well as more interesting.
One possible approach then is to use a trigger that will run an SDO_EQUAL search to locate any existing identical shape in your table and throw an exception (= RAISE_APPLICATION_ERROR) if it finds one. That is a bit more complicated than it sounds due to the restrictions in Oracle of reading from the triggering table inside a trigger (= the "mutating table" problem) so you need a bit of thinking to write that trigger properly.
And of course, this trigger will obviously have performance implications, since each and every insert (or update) in that table will force a spatial query on the table. Guaranteeing data quality comes at a cost.
3) If you have an existing table with duplicates and you want to detect and remove those, then your best approach is to use the SDO_JOIN technique to discover (and subsequently remove them).
Note that blindly removing duplicates may not be trivial: should you have three different objects with the same geometry (but different attributes) which one should you keep ? That requires additional logic.
