3

I have searched a while now, but there seems to be no answer including an example on how to find and delete duplicate geometries from an Oracle DB table.
I am facing a situation where I have a table with 3million+ entries with duplicate point geometries (exact same location) and I need to remove them. The other attributes are not helpful, they are allowed to have duplicates. So it doesnt't matters which record is deleted.
So in my example I would expect to end up with the entries with pnt_id 1 and 3.

CREATE TABLE spatial_table (
    pnt_id number,  
    pnt SDO_GEOMETRY);

INSERT INTO spatial_table VALUES (
    1,
   SDO_GEOMETRY(
      2001,
      NULL,
      SDO_POINT_TYPE(12, 14, NULL),
      NULL,
      NULL));
)

INSERT INTO spatial_table VALUES (
    2,
   SDO_GEOMETRY(
      2001,
      NULL,
      SDO_POINT_TYPE(12, 14, NULL),
      NULL,
      NULL));
)

INSERT INTO spatial_table VALUES (
    3,
   SDO_GEOMETRY(
      2001,
      NULL,
      SDO_POINT_TYPE(20, 18, NULL),
      NULL,
      NULL));
)
1
  • Have you tried select distinct pnt from spatial_table?
    – user30184
    Mar 22, 2020 at 20:46

2 Answers 2

3

Try the answer offered here: https://community.oracle.com/thread/2538130?start=0&tstart=0

Especially look at the sql query as suggested by Luc Van Linden:

DELETE FROM TOTALRECORDS 
where rowid in(
SELECT c.rowid1 FROM
table(sdo_join('TOTALRECORDS', 'GEOMETRY', 'TOTALRECORDS', 'GEOMETRY')) c
WHERE rowid1 < rowid2
);
2
  • 1
    Works like a charm and excellent link. Thank you!
    – blabbath
    Mar 23, 2020 at 6:37
  • i also have something similar issue. but my geometry is 3D. so this solution is not working for me. can you help me on how to get rid of duplicate 3d geometry? Jan 11, 2023 at 14:55
0

You can try also this one which is fast too: https://docs.oracle.com/database/121/SPATL/sdo_util-remove_duplicate_vertices.htm#SPATL1244

Example: Update table1 a set a.geom1=(select sdo_util.remove_dublicate_vertices(b.geom1,0.005) from table1 b where a.fid=b.fid) where a.field1='whatever';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.