Finding identical objects with an SQL

[Reg Halstead]

Hello.

I believe this question has been asked before but I can't find a conclusive answer.

A big part of my job is comparing data so I often need to identify where two layers have identical objects. By "identical" I only mean geographically i.e. ignoring pen/brush/symbol; just like when one uses "Select by location > are identical to the source layer feature" in ArcMap.

So let's say I have 2 tables open, File_1 and File_2, and I'd like to find which objects in File_1 are geographically identical to items in File_2, then normally I would use the following SQL:

Select * from File_1, File_2 where File_1.obj Contains Entire File_2.obj and File_1.obj Entirely Within File_2.obj into query1

That SQL works for most features. Sadly it doesn't always work for features that contain holes nor for other types of multipart features, therefore I've had to write some complicated mbx files to compare other properties such as area, centroids etc. but my method still isn't as solid as I'd like.

I wish I could simply write the following ...

Select * from File_1, File_2 where File_1.obj = File_2.obj  into query1

 ... but that isn't valid as you know.

In addition to "contains", "within" and "intersects", is there another operator I could use to  find identical objects more successfully please? Or is the SQL that I'm using already the only option?

Many thanks in advance,

Reggie

[Reg Halstead]

I've found a solution and it's fairley simple! I'm going to give details here in case it's useful to anyone else searching for the same solution like I was.

I've just read my original post before commenting and I've realised that I didn't specify that the problem only occured when comparing regions that have holes. Basically, to identify where one table has items that are geographically & gemetrically identical to items in another table (regardless of styles) one can use a combination of "Contains Entire" and "Entirely Within" to do so; but this method doesn't identify matching regions with holes.

So I simply used the ConvertToPline() function to convert the regions with holes into multipart polylines; then the "Contains Entire and Entirely Within" comparision method worked perfectly! Afterwards one can simple use the ConvertToRegion() function (or revert / roll back if changes weren't saved) to return the polylines back into polygons.

What I like about the ConvertToPline() function is that it can be run on a selection of mulitple items in one go e.g.Update SELECTIONNAME set object = ConvertToPline(obj)