Skip to main content
  • EN

I'm working in an Oracle database, where there are multiple duplications of features with matching geometry that shouldn't be in a table named "TDS71"."METADATASRF". There are millions of records, and I haven't found a native FME transformer that can handle this in a timely manner (Matcher, AreaOnAreaOverlayer, etc). So I'm experimenting with deleting the duplicates in SQLCreator. For now, I'm simply just trying to return all but 1 of the duplicated geometries, and will worry about deleting after I can identify them.

 

The code below is returning many more features than expected. I'm not really sure why to be honest. For example, a feature is duplicated 6 times in the database. From the query I have currently, 15 records are returned. Does anyone have experience doing this in SQLCreator/Executor?

select a.OBJECTID, a.SHAPE
from "TDS71"."METADATASRF" a join "TDS71"."METADATASRF" b
on sdo_relate(a.SHAPE, b.SHAPE, 'mask=equal') = 'TRUE'
and a.OBJECTID < b.OBJECTID

 

I haven't tried it myself, but the top suggestion here looks interesting:

https://gis.stackexchange.com/questions/354776/delete-duplicate-geometries-from-oracle-table

There are some further suggestions in the referenced Oracle forum post:

https://forums.oracle.com/ords/apexds/post/query-is-taking-too-much-time-5986

When you have 6 identical geometries, your query will return the smallest OBJECTID 5 times, the second smallest OBJECTID will be returned 4 times, etcetera. So this query will return 15 records.

Only one instance of each OBJECTID will be returned with the following query, and the highest OBJECTID will not be returned.

select OBJECTID, SHAPE
from "TDS71"."METADATASRF"
where OBJECTID in
(select distinct a.OBJECTID
from "TDS71"."METADATASRF" a join "TDS71"."METADATASRF"
on sdo_relate(a.SHAPE, b.SHAPE, 'mask=equal') = 'TRUE'
where a.OBJECTID < b.OBJECTID)

I have no idea how fast this query would be.

Reply


Community Stats

30,117
Posts
114,313
Replies
37,747
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.