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 '20 at 20:46
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
);
1
  • 1
    Works like a charm and excellent link. Thank you! – blabbath Mar 23 '20 at 6:37
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, privacy policy and cookie policy

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